[ODBC] Issues with OUT parameters in stored procedures - Mailing list pgsql-odbc
| From | Thomas Apsel |
|---|---|
| Subject | [ODBC] Issues with OUT parameters in stored procedures |
| Date | |
| Msg-id | 74AFC4E35F7D054BB70C4149AA30A66D9A8C34F9@otmucxg20.opentext.net Whole thread Raw |
| Responses |
Re: [ODBC] Issues with OUT parameters in stored procedures
|
| List | pgsql-odbc |
Hi,
we have here an issue that stored procedures cannot be found. This issue comes up with the newest psqlodbc version. The following information is from my colleague (he is currently not in the office) and I hope that this information is enough to verify the issue.
Would be fine if you can us a hint how to adjust the attached sample “params-out-test.c” or better you can fix the bug in the psqlodbc driver.
Thank you for your help.
Regards,
Thomas Apsel
Attached test tool (based on psqlodbc-09.05.0400/test/src/params-test.c); this yields
with new driver connected Testing SQLBindParameter (IN and OUT) with SQLExecDirect... SQLExecDirect failed 42883=ERROR: function fout(integer, integer) does not exist; Error while executing the query
with OLD driver connected Testing SQLBindParameter (IN and OUT) with SQLExecDirect... Result set: -1 disconnectingThe mylog and debug log of run with new driver attached also.
btw: found the following in psqlodbc-09.05.0400/test/src/odbc-escapes-test.c
/* TODO: This doesn't currently work. prepareQuery(hstmt, "{ ? = call concat(?, ?) }");
memset(outbuf, 0, sizeof(outbuf)); bindOutParamString(hstmt, 1, outbuf, sizeof(outbuf) - 1); bindParamString(hstmt, 2, "foo");
bindParamString(hstmt, 3, "bar");
executeQuery(hstmt); printf("OUT param: %s\n", outbuf);
*/This "known issue" is not mentioned in the psqlODBC release notes
After closer investigation I'm sure this is a bug in the psqlodbc driver.
According to ODBC: Procedure Parameters and ODBC: Procedure Calls the way DBDLL performs the call of the stored procedure - i.e. {{{ CALL exp_vol (?,?,?,?)}}} - is correct; moreover the parameter marker ? must be used for OUT parameters. (exp_vol has one IN and three OUT parameters.)
The old driver does the following when sending the statement (after parse and conversion to SELECT * FROM exp_vol ($1,$2,$3,$4))
SOCK_put_int(sock, num_params, sizeof(Int2)); /* number of parameters specified */ if (num_params > 0)
{ int i;
IPDFields *ipdopts = SC_get_IPDF(stmt); for (i = sta_pidx; i <= end_pidx; i++)
{ if (i < ipdopts->allocated &&
SQL_PARAM_OUTPUT == ipdopts->parameters[i].paramType) SOCK_put_int(sock, PG_TYPE_VOID, sizeof(UInt4)); else
SOCK_put_int(sock, 0, sizeof(UInt4)); } }That is OUT parameters are passed as NULL of type VOID to the backend.
This can be seen quite well in the raw communication
... 42 45 47 49 4E 00 BEGIN 50 00 P 00 00 3B 00 ; 53 45 4C 45 43 54 20 2A 20 46 52 4F 4D 20 65 78 SELECT * FROM exp_vol ($1,$2,$3,$4) 70 5F 76 6F 6C 20 28 24 31 2C 24 32 2C 24 33 2C 24 34 29 00 00 04 SOCK_put_int(sock, num_params, sizeof(Int2)) 00 00 00 00 SOCK_put_int(sock, 0, sizeof(UInt4)); 00 00 08 E6 SQL_PARAM_OUTPUT -> PG_TYPE_VOID 00 00 08 E6 SQL_PARAM_OUTPUT -> PG_TYPE_VOID 00 00 08 E6 SQL_PARAM_OUTPUT -> PG_TYPE_VOID 44 D 00 00 00 06 53 S 00 42 00 00 00 27 00 00 00 04 num_params 00 00 00 00 00 00 00 00 00 04 num_params 00 00 00 03 2D 39 39 "-99"
FF FF FF FF NULL FF FF FF FF NULL FF FF FF FF NULL 00 00 45 E 00 00 00 09 00 plan_name 00 00 00 00 count 43 C 00 00 00 06 50 P // SOCK_put_char(sock, 'P'); /* Portal */
00 plan_name 53 S // SOCK_put_char(sock, 'S'); /* Sync command */
00 00 00 04 ...With the release 09.05.0100 the psqlodbc driver was refactored to "Use libpq for all communication with the server".
Apparently the special handling for OUT parameters got lost during this change - the raw communication now looks
... 42 45 47 49 4E 00 BEGIN 50 00 P 00 00 3B 00 ; 53 45 4C 45 43 54 20 2A 20 46 52 4F 4D 20 65 78 SELECT * FROM exp_vol ($1,$2,$3,$4) 70 5F 76 6F 6C 20 28 24 31 2C 24 32 2C 24 33 2C 24 34 29 00 00 04 PQsendQueryGuts : pqPutInt(nParams, 2, conn) 00 00 00 17 paramTypes[0] int4 00 00 00 17 paramTypes[1] int4 00 00 00 17 paramTypes[2] int4 00 00 00 17 paramTypes[3] int4 42 B 00 "" 00 00 29 00 00 00 04 pqPutInt(nParams, 2, conn) 00 00 paramFormats[0] 00 00 paramFormats[1] 00 00 paramFormats[2] 00 00 paramFormats[3] 00 04 pqPutInt(nParams, 2, conn) 00 00 00 03 pqPutInt(nbytes, 4, conn) 2D 39 39 -99 FF FF FF FF NULL FF FF FF FF NULL FF FF FF FF NULL 00 01 pqPutInt(1, 2, conn) 00 00 pqPutInt(resultFormat, 2, conn) 44 D 00 00 00 06 <msglen> 50 P 00 "" 45 E 00 00 00 09 <msglen> 00 "" 00 00 00 00 pqPutInt(0, 4, conn) 53 S 00 00 00 04 <msglen> 51 00 00 00 0D 52 4F 4C 4C 42 41 43 4B 00 58 00 00 00 04 ...That is the drivers send to the backend all query parameters are of type int4 (aka "integer"). This explains the error as there is no function with four IN parameters of type int4.
There is code in libpq which does something equivalent to what the old driver did (on its own) for OUT parameters (in function PQsendPrepare, in libpq\fe-exec.c) but nonething like this is done in function PQsendQueryGuts which seems to be used.
Note: The issue still exists in the newest psqlodb version 09.05.0400.
Thomas Apsel
Engineering
Open Text Software GmbH
Werner-von-Siemens-Ring 20
85630 Grasbrunn/München
Phone: | +49 (0) 89 4629 1817 |
Website: |
Place of Incorporation / Sitz der Gesellschaft: Open Text Software GmbH, Werner-von-Siemens-Ring 20, 85630 Grasbrunn, Germany | Phone: +49 (0)89 4629-0 | Fax: +49 (0)89 4629-1199 | Register Court / Registergericht: München, Germany | Trade Register Number / HRB: 179298 | VAT ID Number /USt-ID: DE 118 656 356 | Managing Director / Geschäftsführer: Gordon Davies, John Doolittle, Roger Illing
This email message is confidential, may be privileged, and is intended for the exclusive use of the addressee. Any other person is strictly prohibited from disclosing or reproducing it. If the addressee cannot be reached or is unknown to you, please inform the sender by return email and delete this email message and all copies immediately.
Attachment
pgsql-odbc by date: