Issues with OUT parameters in stored procedures

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

disconnecting

The 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<https://odbc.postgresql.org/docs/release.html>

After closer investigation I'm sure this is a bug in the psqlodbc driver.
According to ODBC: Procedure Parameters<https://msdn.microsoft.com/en-us/library/ms711793(v=vs.85).aspx> and ODBC: Procedure Calls<https://msdn.microsoft.com/en-us/library/ms710100(v=vs.85).aspx> 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:

www.opentext.com<http://www.opentext.com/>

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.