Hi,

I have a client app using ODBC to connect to an SQLA17 database. The client calls a procedure that performs an INSERT on a table. There is one column on that table defined as UNIQUEIDENTIFIER. For that column, the client passes in a COALESCE('A NULL here', newid()) value.

The insert fails with an error: "Impossible to convert smallint to uniqueidentifier" However, the same call works in Interactive SQL.

This leads me to believe that in the ODBC driver (before getting to the INSERT) the Null that I pass is converted to a smallint with a default value, which causes the COALESCE('not a NULL anymore', newid()) to return a smallint value that is then converted to a uniqueidentifier, which explains the error.

Does this make sense? Is this a breaking change in the odbc driver for sa-17? (The same code works with sa-16)

Thanks


EDIT 1

So the client calls a procedure passing in a Null:

ALTER PROCEDURE PROC_NAME(
   ...
   IN _GLOBALID uniqueidentifier,
   ...etc...)
BEGIN
   INSERT INTO TABLE_NAME(...,GLOBALID_COL,...)
   VALUES(..., COALESCE(_GLOBALID,newid()),...)
END

The profiler says the client calls this proc with a Null. When the client calls it the exception is raised. When I call it from Interactive Sql, no exception.


EDIT 2

We are using SQL 19.0.9.4803

The error happens when the client calls the procedure (described previously).

When we comment the INSERT we still get the error:

ALTER PROCEDURE PROC_NAME(
   ...
   IN _GLOBALID uniqueidentifier,
   ...etc...)
BEGIN
--   INSERT INTO TABLE_NAME(...,GLOBALID_COL,...)
--   VALUES(..., COALESCE(_GLOBALID,newid()),...)
END

When we change the param type and keep the commented INSERT, we don't get the error:

ALTER PROCEDURE PROC_NAME(
    ...
    IN _GLOBALID unsigned smallint,
       ...etc...)
BEGIN
    --   INSERT INTO TABLE_NAME(...,GLOBALID_COL,...)
    --   VALUES(..., COALESCE(_GLOBALID,newid()),...)
END

When we uncomment the INSERT, we get the error:

ALTER PROCEDURE PROC_NAME(
   ...
   IN _GLOBALID unsigned smallint,
   ...etc...)
BEGIN
   INSERT INTO TABLE_NAME(...,GLOBALID_COL,...)
   VALUES(..., COALESCE(_GLOBALID,newid()),...)
END

EDIT 3

Below a trace from the client calling the procedure on a dbsrv16 with a DB supporting ver 16:

=,>,1,OPEN,131184 +6,<,1,PREPARE,call "INSERT_LIGNE_OFFRE"(:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?) =,>,1,PREPARE,458865 +7,<,1,EXEC,458865 =,H,1,,integer,25511
=,H,1,,integer,832773
=,H,1,,integer,832773
=,H,1,,varchar,'0'
=,H,1,,varchar,'1'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,integer,0
=,H,1,,any,<null>
=,H,1,,varchar,'0'
=,H,1,,integer,0
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'1'
=,H,1,,varchar,'0'
=,H,1,,integer,130
=,H,1,,any,<null>
=,H,1,,integer,0
=,H,1,,varchar,'{\rtf1\ansi\ansicpg1252\deff0\deflang1036{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg;}} \viewkind4\uc1\pard\f0\fs17 Composant\par } '
=,H,1,,varchar,'<modele><pos>Composant</pos><easycom>6200</easycom><complement>0,00</complement><remplacement>0,00</remplacement></modele>'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
+1,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,integer,0
=,H,1,,varchar,'0'
=,H,1,,any,<null>
=,H,1,,varchar,'Sous-total'
=,H,1,,any,<null>
=,H,1,,any,<null>
=,H,1,,any,<null>
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,any,<null>
=,H,1,,integer,0
=,H,1,,integer,0
=,H,1,,integer,0
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,any,<null>
=,H,1,,varchar,''
=,H,1,,integer,4600
+79,>,1,EXEC =,<,1,COMMIT

You can see the parameter in question in bold. The type that is seen in the trace is ANY

Now for the same test using the same client but with a dbsrv17 and a DB supporting ver 17:

=,>,1,OPEN,65546 +7,<,1,PREPARE,call "INSERT_LIGNE_OFFRE"(:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?,:?) =,>,1,PREPARE,131083 +4,<,1,EXEC,131083 +2,H,1,,integer,85911
=,H,1,,integer,525129
=,H,1,,integer,525129
=,H,1,,varchar,'0'
+1,H,1,,varchar,'1'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,integer,0
=,H,1,,smallint,<null>
=,H,1,,varchar,'0'
=,H,1,,integer,0
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'1'
=,H,1,,varchar,'0'
=,H,1,,integer,130
=,H,1,,smallint,<null>
=,H,1,,integer,0
=,H,1,,varchar,'{\rtf1\ansi\ansicpg1252\deff0\deflang1036{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg;}} \viewkind4\uc1\pard\f0\fs17 Poste\par } '
=,H,1,,varchar,'<modele><pos>Poste</pos><easycom>6200</easycom><complement>0,00</complement><remplacement>0,00</remplacement></modele>'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
+1,H,1,,varchar,'0'
=,H,1,,integer,0
=,H,1,,varchar,'0'
=,H,1,,smallint,<null>
=,H,1,,varchar,'Sous-Total Poste'
=,H,1,,smallint,<null>
=,H,1,,smallint,<null>
=,H,1,,smallint,<null>
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,smallint,<null>
=,H,1,,integer,0
=,H,1,,integer,0
=,H,1,,integer,0
+1,H,1,,varchar,'0'
=,H,1,,varchar,'0'
=,H,1,,smallint,<null>
=,H,1,,varchar,''
=,H,1,,integer,4600
=,E,1,-157,Impossible de convertir smallint en uniqueidentifier =,>,1,EXEC +4,<,1,ROLLBACK

This time, the type that is seen in the trace is SMALLINT


EDIT 4

Below the source code:

SELF:Fill({
oLd:IDDOCUMENT,
oLd:IDLIGNE,
oLd:ORDRE,
oLd:TAUX_REMISE,
oLd:QUANTITE,
oLd:PRIX_VENTE,
oLd:ALTERNATIVE,
oLd:IDPOSTE,
AsNull(oLd:DLIVRAISON),
oLd:SAUT_PAGE,
oLd:BLANK_NB,;
oLd:LENTETE,
oLd:LPIED,
oLd:LIMAGE,
oLd:LDETAIL,
oLd:ECOTAXE_MONTANT,
oLd:TYPELIGNE,
AsNull(oLd:IdFournisseur),
oLd:IDTVA,
oLd:DESIGNATION,
oLd:MODELE_INFO,
oLd:PRIX_CATALOGUE,;
oLd:PRIX_UNITAIRE,
oLd:ACHAT,
oLd:NEGO,
oLd:IDUNITE,
oLd:PRIX_CATALOGUE_DEVISE,
AsNull(oLd:IDMONNAIE),
AsNull(oLd:TRANSMISSION),
AsNull(oLd:LIVRAISON),
AsNull(oLd:FACTURATION),;
AsNull(oLd:CODE_ACCORD),
oLd:TAUX_REMISE_ACHAT,
oLd:LAUTOPRICING,
oLd:lEXPRESS,
oLd:POIDS,
oLd:VOLUME,
oLd:POIDS_LOGISTIQUE,
oLd:VOLUME_LOGISTIQUE,
AsNull(oLd:CODE_SH),;
oLd:IDGRILLE,
oLd:IDCATEGORIE,
oLd:IDFONCTION,
oLd:ECOTAXE_TAUX,
oLd:ECO_CONCEPTION,

AsNull(oLd:GLOBALID),

oLd:EXTERNALID,
iif(!Empty(oLd:TYPE_ARTICLE),oLd:TYPE_ARTICLE,ARTICLE_NON_DEF)})

The relevant part above in bold.

Notice how the oLd:GLOBALID value is passed to the AsNull method, defined below:

FUNCTION AsNull(uParam AS USUAL) AS USUAL PASCAL 
RETURN IIF(Empty(uParam),NIL,uParam)

This method checks if the input param is empty and returns either NIL, or the data passed in. In this case, we get a USUAL Nil, which is the VB Variant equivalent of Visual Objects.

So, as per EDIT 3, we can now see that the USUAL Nil is translated to any when run on a dbsrv16 and to smallint when run on a dbsrv17. Why do you think this happens?

asked 05 Sep, 06:00

tzup's gravatar image

tzup
360101325
accept rate: 0%

edited 10 Sep, 10:31

COALESCE('A NULL here', newid())

What do you mean with 'A NULL here'?

The following returns 'uniqueidentifier' as expected both with 16.0.0.2673 and 17.0.9.4838:

select exprtype('select coalesce(null, newid())', 1);

and the following returns SQLCODE -157 aka "Cannot convert smallint to uniqueidentifier":

select exprtype('select coalesce(cast (null as smallint), newid())', 1);

So how do you supply the NULL to your INSERT statement within ODBC - by means of a parametrized statement? If so, with what data type? Or is that a statement within your stored procedure?

(05 Sep, 07:10) Volker Barth

Hi Volker, please see my edit. Thanks!

(05 Sep, 08:54) tzup
Replies hidden

Hm, as the code runs within a stored procedure and therefore within the database server, I don't think it has to do with an ODBC issue.

What v17 build are you using?

Does the error appear if you set the UUID parameter to newid() explicitly, say by adding

SET _GLOBALID = COALESCE(_GLOBALID, newid());

before the insert and then just using "_GLOBALID" in the INSERT statement?

Otherwise, a request level log might help...

(05 Sep, 08:59) Volker Barth

I cannot reproduce this with an ODBC connection from PowerBuilder. Perhaps you can get an ODBC trace showing the call and it failing for review.

Edit:

1) I tested both call p(NULL) and exec p NULL to ensure that this was not a TSQL'ism. I can only reproduce the error if I have a smallint value.
2) Perhaps a request log with SQL+HOSTVars would work as well as an ODBC trace. From the command line, add -zr SQL+HostVars -zo <filespec>. From SQL, execute call sa_server_option('RequestLogging','SQL+HostVars'); call sa_server_option('RequestLogFile','<filespec>');

To turn off logging, stop the server and remove the -zr and -zo or call sa_server_option('RequestLogging','NONE'); call sa_server_option('RequestLogFile','');

(05 Sep, 12:13) Chris Keating

Well, that looks the problem is not the INSERT statement but the parameter value itself - i.e. how do you set the procedure's parameter _GLOBALID?

(05 Sep, 12:13) Volker Barth
Comment Text Removed
Comment Text Removed

FWIW most ODBC documentation is silent on what "any" means. This page may not help, but it does make many references to "any" in a discussion of how ODBC flails around trying to deal with "data type impedance mismatch" :)

(06 Sep, 08:24) Breck Carter

It might help YOU to show us the exact client-side code that (a) declares the procedure that it calls, and (b) calls the procedure.

It's kinda hard to answer a question about client server communications without having any idea what kind of client software is used... on the other hand, you will probably figure it out yourself.

(06 Sep, 08:28) Breck Carter

To your EDIT 3:

You still have not shown how you set the according parameter value...

(06 Sep, 09:50) Volker Barth

Working on that :) Don't have access to the source code (yet).

(06 Sep, 09:55) tzup

> Don't have access to the source code

...ah, been there, done that, gone through rehab afterwards :)

Please excuse the harshness of my previous comment.

(06 Sep, 14:01) Breck Carter

Hello, I am working with Vlad as Project Manager. Vlad has some days of vacation and another developer will be back on monday next.We will post our sample of code next week. thanks for your advices, it is already helpfull.

(07 Sep, 03:50) Guillaume

Who's Vlad in this context? "Tzup"?

(07 Sep, 04:58) Volker Barth

Definitely not me :)
I was only reading this thread, not posting anything.

(07 Sep, 05:11) Vlad

another Vlad. I made the mistake myself. the Vlad I know is tzup :-)

(07 Sep, 05:26) Guillaume

Here is a sample of our source code. The used language is Visual Object.

We have a “Fill” method to instantiate the parameter of the called SQL procedure

SELF:Fill({oLd:IDDOCUMENT,oLd:IDLIGNE,oLd:ORDRE,oLd:TAUX_REMISE,oLd:QUANTITE,oLd:PRIX_VENTE,oLd:ALTERNATIVE,oLd:IDPOSTE,AsNull(oLd:DLIVRAISON),oLd:SAUT_PAGE,oLd:BLANK_NB,;
                oLd:LENTETE,oLd:LPIED,oLd:LIMAGE,oLd:LDETAIL,oLd:ECOTAXE_MONTANT,oLd:TYPELIGNE,AsNull(oLd:IdFournisseur),oLd:IDTVA,oLd:DESIGNATION,oLd:MODELE_INFO,oLd:PRIX_CATALOGUE,;
                oLd:PRIX_UNITAIRE,oLd:ACHAT,oLd:NEGO,oLd:IDUNITE,oLd:PRIX_CATALOGUE_DEVISE,AsNull(oLd:IDMONNAIE),AsNull(oLd:TRANSMISSION),AsNull(oLd:LIVRAISON),AsNull(oLd:FACTURATION),;
                AsNull(oLd:CODE_ACCORD),oLd:TAUX_REMISE_ACHAT,oLd:LAUTOPRICING,oLd:lEXPRESS,oLd:POIDS,oLd:VOLUME,oLd:POIDS_LOGISTIQUE,oLd:VOLUME_LOGISTIQUE,AsNull(oLd:CODE_SH),;
                oLd:IDGRILLE,oLd:IDCATEGORIE,oLd:IDFONCTION,oLd:ECOTAXE_TAUX,oLd:ECO_CONCEPTION,AsNull(oLd:GLOBALID),oLd:EXTERNALID,iif(!Empty(oLd:TYPE_ARTICLE),oLd:TYPE_ARTICLE,ARTICLE_NON_DEF)})

The AsNull function gets any type of data. Check if the data if empty and then return NIL or the data

FUNCTION AsNull(uParam AS USUAL) AS USUAL PASCAL
                RETURN IIF(Empty(uParam),NIL,uParam)

We get a USUAL nil. USUAL Visual Object is equivalent to Variant in VB.
(10 Sep, 05:16) Guillaume

As to the EDIT 4:

we can now see that the USUAL Nil is translated to any when run on a dbsrv16 and to smallint when run on a dbsrv17. Why do you think this happens?

Sorry, I'm not familiar with VisualObjects at all. Apparently, the parameter binding process (*) as done by VisualObjects is still a black box for us, so it's difficult (at least) to make a reasonable guess here. I'm out of my wits.

(*) Within ODBC, you would usually call an API like SQLBindParameter() to map (among others) the datatype of your programm to the SQL statements parameters, so a GUID would be bound as a SQL_C_GUID.

(10 Sep, 10:40) Volker Barth

Oh, I thought this binding was done by the dbodbc17.dll driver.

(10 Sep, 10:44) tzup

No, that is part of the client using ODBC. In your case it might be part of the VisualObjects code, so not necessarily in the code you write:)

(Note, if you supply the parameter values as literals, no explicit data binding appears (or it is done by your code while building the statement's text.)

(10 Sep, 11:10) Volker Barth
1

Here is where the ODBC trace might come back in play. It will show exactly how the client application is binding values. It is possible that it is getting meta data via an ODBC call also. The trace should show that also.

(10 Sep, 19:37) Chris Keating

Isn't EDIT 3 the ODBC trace? Or am I mistaking it with something else. Then, how would I setup the ODBC trace?

(11 Sep, 07:44) tzup

Edit 3 is a request log. It is specific to SQL Anywhere. Here is a document on how to enable ODBC Tracing. https://blogs.msdn.microsoft.com/selvar/2007/11/10/odbc-tracing/

This can also be achieved with the following ODBC settings during the connect:

SQL_OPT_TRACE,SQL_OPT_TRACE_ON;SQL_OPT_TRACEFILE,<log_file>

(11 Sep, 08:27) Chris Keating

Follow Chris Keating's instructions... this is how you will know you got it right :)...

(11 Sep, 08:57) Breck Carter

Breck did remind me that you should make sure that you turn off logging after it is collected. It does generate lots of information and if you forget to turn it off (or it tries to ignore that request), you will find your application to be quite slow.

(11 Sep, 15:23) Chris Keating
More comments hidden
showing 4 of 23 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×132
×106

question asked: 05 Sep, 06:00

question was seen: 195 times

last updated: 11 Sep, 15:23