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 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 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 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 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,
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? |
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:
and the following returns SQLCODE -157 aka "Cannot convert smallint to uniqueidentifier":
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?
Hi Volker, please see my edit. Thanks!
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
before the insert and then just using "_GLOBALID" in the INSERT statement?
Otherwise, a request level log might help...
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)
andexec 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','');
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?
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" :)
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.
To your EDIT 3:
You still have not shown how you set the according parameter value...
Working on that :) Don't have access to the source code (yet).
> 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.
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.
Who's Vlad in this context? "Tzup"?
Definitely not me :)
I was only reading this thread, not posting anything.
another Vlad. I made the mistake myself. the Vlad I know is tzup :-)
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
As to the EDIT 4:
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.
Oh, I thought this binding was done by the dbodbc17.dll driver.
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.)
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.
Isn't EDIT 3 the ODBC trace? Or am I mistaking it with something else. Then, how would I setup the ODBC trace?
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>
Follow Chris Keating's instructions... this is how you will know you got it right :)...
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.
Hello, we enabled the tracing mode using the ODBC settings during the connect. To compare the behaviour in between SQLA 16 and SQL 17, we ran exactly the same test. The test succeeded with SQLA 16. The test failed with SQLA 17.
You may find here below the two trace logs. I must cut the trace (too long for the post)
The issue is about the 46th parameter. The binded paramter are exactly the same:
With SQL16 odbcdriver
with SQL 17 odbc driver
FWIW, a pre-tag (i.e. "pre" in angle brackets) helps to insert code formatted as-is:)
As you relate to the 46th parameter - if that is supposed to be a UUID, why is it bound as a SQL SMALLINT (2 Byte)?
We cannot tell from the code who is responsible for the parameter binding, i.e. whether it is your code or third-party code (here VisualObjects, methinks). As stated above, I'm still quite sure it isn't the ODBC driver's fault as your own code or VisualObjects calls the driver's API...
You should call SAP tech support... have your s-user id handy.
Aligned, we don't know why as well. We tried different tests to understand. Still not clear for us. We come to the conclusion of a more restrictiv behaviour implemented with the dbodbc17.dll exposing us a problem in our language.
We'll do it