uuidtostr(newid()) errors with proxy table

#11

That works.

So that got me thinking, "Hey, why not just create my own function to
convert UUID to string":

CREATE FUNCTION "DBA"."fx_UUIDToStr"(in orig_uuid uniqueidentifier)
returns char(36)
not deterministic
begin
return UUIDToStr(orig_uuid)
end;

....but these still both generate the same error (even though they work on
local tables):

insert into tbltestuuid (testuuid) values ( fx_UUIDToStr(newid()) )
insert into tbltestuuid (testuuid) values ( (select fx_UUIDToStr(newid())) )

Darn. It seems like for proxy tables it's just passing the whole thing over
without even trying to determine if there are things in the statement that
should be executed locally first.

I don't know if I'm going to be able to make use of the EXECUTE IMMEDIATE
STRING, but thanks for thinking about it.

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

Quote:

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:

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

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

c3ci31s30bsbrc2pfhu3296epjsp9ed20 (AT) 4ax (DOT) com...
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:

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...
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:

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:

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...
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

--
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

--
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

#12

The EXECUTE IMMEDIATE workaround probably works because it makes use
of the discovery YOU made: that a string literal works.

You should post a message on the product_futures_discussion newsgroup
asking for this "feature gap" to be closed: an MSS proxy table INSERT
does not accept a UNIQUEIDENTIFIER in the VALUES clause. In my
opinion, the proxy table feature set is falling a bit behind the state
of the database art these days, and needs some
attention/investment/money thrown at it.

In the meantime, everyone should keep EXECUTE IMMEDIATE in their
toolkit, it's like the Aspirin of SQL, fixes many headaches

Breck

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

Quote:

That works.

So that got me thinking, "Hey, why not just create my own function to
convert UUID to string":

CREATE FUNCTION "DBA"."fx_UUIDToStr"(in orig_uuid uniqueidentifier)
returns char(36)
not deterministic
begin
return UUIDToStr(orig_uuid)
end;

...but these still both generate the same error (even though they work on
local tables):

insert into tbltestuuid (testuuid) values ( fx_UUIDToStr(newid()) )
insert into tbltestuuid (testuuid) values ( (select fx_UUIDToStr(newid())) )

Darn. It seems like for proxy tables it's just passing the whole thing over
without even trying to determine if there are things in the statement that
should be executed locally first.

I don't know if I'm going to be able to make use of the EXECUTE IMMEDIATE
STRING, but thanks for thinking about it.

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:2e8ci311oj6k6bp7vnm2p5sa0vev68n506 (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:

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

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

c3ci31s30bsbrc2pfhu3296epjsp9ed20 (AT) 4ax (DOT) com...
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:

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...
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:

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:

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...
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

--
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

--
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

--
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

uuidtostr(newid()) errors with proxy table

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: uuidtostr(newid()) errors with proxy table - 10-29-2007 , 12:15 PM

Re: uuidtostr(newid()) errors with proxy table - 10-30-2007 , 03:28 AM