Odbc driver issue with SQLA17

0

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 '18, 06:00

tzup's gravatar image

tzup
360●12●15●26
accept rate: 0%

edited 10 Sep '18, 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 '18, 07:10) Volker Barth

Hi Volker, please see my edit. Thanks!

(05 Sep '18, 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 '18, 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 '18, 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 '18, 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 '18, 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 '18, 08:28) Breck Carter

To your EDIT 3:

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

(06 Sep '18, 09:50) Volker Barth

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

(06 Sep '18, 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 '18, 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 '18, 03:50) Guillaume

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

(07 Sep '18, 04:58) Volker Barth

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

(07 Sep '18, 05:11) Vlad

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

(07 Sep '18, 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 '18, 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 '18, 10:40) Volker Barth

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

(10 Sep '18, 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 '18, 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 '18, 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 '18, 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 '18, 08:27) Chris Keating

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

(11 Sep '18, 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 '18, 15:23) Chris Keating

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: 

MyAPP         5570-5bd8 ENTER SQLBindParameter 
        HSTMT               0x0199BB18
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09C1C670

MyAPP         5570-5bd8 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09C1C670 (-1)



MyAPP     2d0c-2e50 ENTER SQLBindParameter 
        HSTMT               0x017DAFF0
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09602C10

MyAPP     2d0c-2e50 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09602C10 (-1)
(05 Oct '18, 08:05) Guillaume

With SQL16 odbcdriver

[...]
MyAPP     2d0c-2e50 ENTER SQLBindParameter 
        HSTMT               0x017DAFF0
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09602C10

MyAPP     2d0c-2e50 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09602C10 (-1)

[...]

MyAPP     2d0c-2e50 ENTER SQLSetStmtOption 
        HSTMT               0x017DAFF0
        UWORD                        6 <sql_cursor_type>
        SQLPOINTER                 1 <sql_cursor_keyset_driven>

MyAPP     2d0c-2e50 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UWORD                        6 <sql_cursor_type>
        SQLPOINTER                 1 <sql_cursor_keyset_driven>

MyAPP     2d0c-2e50 ENTER SQLSetStmtOption 
        HSTMT               0x017DAFF0
        UWORD                        8 <sql_keyset_size>
        SQLPOINTER               128

MyAPP     2d0c-2e50 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UWORD                        8 <sql_keyset_size>
        SQLPOINTER               128

MyAPP     2d0c-2e50 ENTER SQLSetStmtOption 
        HSTMT               0x017DAFF0
        UWORD                        9 <sql_rowset_size>
        SQLPOINTER                 1

MyAPP     2d0c-2e50 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UWORD                        9 <sql_rowset_size>
        SQLPOINTER                 1

MyAPP     2d0c-2e50 ENTER SQLSetStmtOption 
        HSTMT               0x017DAFF0
        UWORD                        7 <sql_concurrency>
        SQLPOINTER                 3 <sql_concur_rowver>

MyAPP     2d0c-2e50 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UWORD                        7 <sql_concurrency>
        SQLPOINTER                 3 <sql_concur_rowver>

MyAPP     2d0c-2e50 ENTER SQLPrepare 
        HSTMT               0x017DAFF0
        UCHAR *             0x09617A10 [     120] "CALL INSERT_LIGNE_OFFRE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        SDWORD                   120

MyAPP     2d0c-2e50 EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
        UCHAR *             0x09617A10 [     120] "CALL INSERT_LIGNE_OFFRE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        SDWORD                   120

MyAPP     2d0c-2e50 ENTER SQLExecute 
        HSTMT               0x017DAFF0

MyAPP     2d0c-2e50 EXIT  SQLExecute  with return code 0 (SQL_SUCCESS)
        HSTMT               0x017DAFF0
(05 Oct '18, 08:09) Guillaume

with SQL 17 odbc driver

[...]MyAPP         5570-5bd8    ENTER SQLBindParameter 
        HSTMT               0x0199BB18
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09C1C670

MyAPP         5570-5bd8 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UWORD                       46 
        SWORD                        1 <sql_param_input>
        SWORD                      -15 <sql_c_sshort>
        SWORD                        5 <sql_smallint>
        SQLULEN                    0
        SWORD                        0 
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x09C1C670 (-1)

[...]

MyAPP         5570-5bd8 ENTER SQLSetStmtOption 
        HSTMT               0x0199BB18
        UWORD                        6 <sql_cursor_type>
        SQLPOINTER                 1 <sql_cursor_keyset_driven>

MyAPP         5570-5bd8 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UWORD                        6 <sql_cursor_type>
        SQLPOINTER                 1 <sql_cursor_keyset_driven>

MyAPP         5570-5bd8 ENTER SQLSetStmtOption 
        HSTMT               0x0199BB18
        UWORD                        8 <sql_keyset_size>
        SQLPOINTER               128

MyAPP         5570-5bd8 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UWORD                        8 <sql_keyset_size>
        SQLPOINTER               128

MyAPP         5570-5bd8 ENTER SQLSetStmtOption 
        HSTMT               0x0199BB18
        UWORD                        9 <sql_rowset_size>
        SQLPOINTER                 1

MyAPP         5570-5bd8 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UWORD                        9 <sql_rowset_size>
        SQLPOINTER                 1

MyAPP         5570-5bd8 ENTER SQLSetStmtOption 
        HSTMT               0x0199BB18
        UWORD                        7 <sql_concurrency>
        SQLPOINTER                 3 <sql_concur_rowver>

MyAPP         5570-5bd8 EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UWORD                        7 <sql_concurrency>
        SQLPOINTER                 3 <sql_concur_rowver>

MyAPP         5570-5bd8 ENTER SQLPrepare 
        HSTMT               0x0199BB18
        UCHAR *             0x048FBAB8 [     120] "CALL INSERT_LIGNE_OFFRE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        SDWORD                   120

MyAPP         5570-5bd8 EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0199BB18
        UCHAR *             0x048FBAB8 [     120] "CALL INSERT_LIGNE_OFFRE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        SDWORD                   120

MyAPP         5570-5bd8 ENTER SQLExecute 
        HSTMT               0x0199BB18

MyAPP         5570-5bd8 EXIT  SQLExecute  with return code -1 (SQL_ERROR)
        HSTMT               0x0199BB18

        DIAG [07006] [SAP][ODBC Driver][SQL Anywhere]Impossible de convertir smallint en uniqueidentifier (-157)
(05 Oct '18, 08:13) Guillaume

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

(05 Oct '18, 09:06) Volker Barth

You should call SAP tech support... have your s-user id handy.

(05 Oct '18, 10:11) Breck Carter

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.

(05 Oct '18, 10:46) Guillaume

We'll do it

(05 Oct '18, 10:46) Guillaume
More comments hidden
showing 4 of 30 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

learn more about Markdown

Question tags:

×235
×145

question asked: 05 Sep '18, 06:00

question was seen: 1,675 times

last updated: 05 Oct '18, 10:56

Related questions

ODBC: Missing or invalid encryption DLL "dbrsa17.dll"

How to configure ODBC to work with PHP under linux?

Backups suddenly failing

TLS Handshake Failure with PHP PDO though ODBC Connections Test Successful

Error Code -6 when applying a transaction log file (from live backup)

Problem using 'callback' form of 'connect', JavaScript External Environment

SA-17 Purchase Order Suspended

how to install only ODBC driver

port for ODBC client connection

Error using ''sa_dbcapi_handle" in JavaScript External Environment with WIndows