SQL Anywhere 10.0.1.3488

It seems like this is not being evaluated before being sent to ODBC...or
something:

select uuidtostr(newid())

To see why I say this, please try the following

IN MSSQL 2000 run this:

CREATE TABLE [tblTestUUID] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[testuuid] [uniqueidentifier] NULL
) ON [PRIMARY]
GO


In SQL Anywhere run this:

CREATE EXISTING TABLE "DBA"."tblTestUUID" (
"id" integer NOT NULL,
"testuuid" uniqueidentifier NULL
) AT 'uid;CaseTest;dbo;tblTestUUID';

....then try this:

// the following line returns d6e79e72-7b8f-40a0-91af-91d76c42dd42
select uuidtostr(newid())

// so I copy that UUID and paste into this, which runs fine
insert into tbltestuuid (testuuid) values
('d6e79e72-7b8f-40a0-91af-91d76c42dd42')
commit

// but neither of these work
insert into tbltestuuid (testuuid) values ( UUIDTOSTR(newid()) )
insert into tbltestuuid (testuuid) values ( newid() )

// They both give me the error: Could not execute statement. Server 'uid':
[Microsoft][ODBC SQL Server Driver][SQL Server]The name 'unknown' is not
permitted in this context. Only constants, expressions or variables allowed
here. Column names are not permitted. SQLCODE=-660, ODBC 3 State="HY000"

I could see why maybe the 2nd one might not work (I read a post about
differing endianness in MSSQL vs Sybase GUIDs), but I don't understand why
the 1st one doesn't, since the function should return a string. Am I
missing something?

What happens when you don't force the data types, like this?

CREATE EXISTING TABLE "DBA"."tblTestUUID"
AT 'uid;CaseTest;dbo;tblTestUUID';

I will be curious about what data type you end up with at the SQL
Anywhere end (and I'm too lazy this late on a Friday to start MSS to
see for myself .

Breck

On 26 Oct 2007 11:58:29 -0700, "Spencer Wasden"
<spencer.wasden (AT) mobiledataforce_xspm (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

The data types were the same (uniqueidentifier) with my original and with
the create statement you posted. I get the same errors as well.

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:dej4i3d6l2805oou5dieksr025dt2nseih (AT) 4ax (DOT) com...

Spencer,

I am just guessing, but maybe this is not an issue of UUIDs, but that the
NOT NULL IDENTITY column is not part of the insert.
Does inserting in a proxy table work if you use a char or integer column
instead of the UUID and insert a string or number but do not insert the
identity column?

Frank

On Fri, 26 Oct 2007 23:16:03 +0200, Spencer Wasden
<spencer.wasden (AT) mobiledataforce_xspm (DOT) com> wrote:

Frank may have the answer, or at least *an* answer (given the
unhelpful nature of the error message)... in MSS you cannot normally
provide a value for an IDENTITY column.

Here is an excerpt from the MSS 2005 docs: "SET IDENTITY_INSERT can be
used to disable the IDENTITY property of a column by enabling values
to be explicitly inserted."

Breck


On 27 Oct 2007 04:06:55 -0700, "Frank Ploessel"
<fpl... (AT) d_e (DOT) i_m_s_h_e_a_l_t_h.c_o_m> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

10.0.1.3488

I removed the identifier and get the same results:

--[MSSQL]
CREATE TABLE [tblTestUUID] (
[id] [int] NULL ,
[testuuid] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

--[SQL Anywhere]
CREATE EXISTING TABLE "DBA"."tblTestUUID" (
"id" integer NULL,
"testuuid" uniqueidentifier NULL
) AT 'uid;CaseTest;dbo;tblTestUUID';

// works:
insert into tbltestuuid (testuuid) values
('d6e79e72-7b8f-40a0-91af-91d76c42dd42')

// FAILS:
insert into tbltestuuid (testuuid) values ( uuidtostr(newid()) )

I wanted to see if "uuidtostr(newid())" in an insert statement would work on
an SQL Anywhere (_not_ proxy) table:

create table localUUIDTest (id int null, colUUID uniqueidentifier not null)
insert into localUUIDTest ( colUUID) values (
'd6e79e72-7b8f-40a0-91af-91d76c42dd42')
insert into localUUIDTest ( colUUID) values ( uuidtostr(newid()) )

Both inserts work fine.

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:b5i6i3heqv847icr0a677lsh1nj9t6vkqi (AT) 4ax (DOT) com...

I meant to say I removed the IDENTITY (not the identifier)

Here is another wild guess... try this:

insert into tbltestuuid (testuuid)
values ( CAST ( uuidtostr(newid()) AS CHAR ( 36 ) ) )

Breck

On 29 Oct 2007 07:16:37 -0700, "Spencer Wasden"
<spencer.wasden (AT) mobiledataforce_xspm (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

Hmm. I still get the same error. Thank you.

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message newsc3ci31s30bsbrc2pfhu3296epjsp9ed20 (AT) 4ax (DOT) com...

How about this:

EXECUTE IMMEDIATE STRING (
'insert into tbltestuuid (testuuid) values ( ''',
uuidtostr(newid()),
''')' );

Breck von Workaround


On 29 Oct 2007 09:00:48 -0700, "Spencer Wasden"
<spencer.wasden (AT) mobiledataforce_xspm (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com