Problem with psqlODBC and "call" (of Stored Procedures) - Mailing list pgsql-odbc

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I am working since March 2009 for my actual company. The software we
make is based on Databases. Our normal environment is a database server
(actually Informix) on Linux or Unix and database client software on
Windows Server 2003 or Windows XP. Since at many customer sites the
single DB server is overloaded, we need to split our DBs to more than
one server. Because most of our customers don't have much money, we
decided to move our applications to PostgreSQL. For this port I am
responsible. Nearly anything worked very well, I am using PostgreSQL
8.3.7 on Linux and psqlODBC on Windows XP (all of our client
applications are written in C++ with MS Visual Studio 2005 as IDE). The
decision for ODBC had been done because of portability and independence
of the programming language. Also because of portability all date and
time literals sent to the ODBC driver are in the form of the official
ODBC escape clauses (for example "{d '2009-10-01'}" for the first of
October, 2009).


Now my problem:

If I use this escape strings in "SELECT" statements, anything works
well. But nearly 95 percent of our DB statements ere stored procedure
calls; we use the ODBC standard for this in the form: "{call myproc
(par1, par2, par3, ..., parn)}". In this case, since PostgreSQL don't
know a call statement, the ODBC driver correctly transform it into a
SELECT statement. Unfortunately, the procedure parameter (datatype date)
in the escaped form is transformed removing only the curly braces and
leaving the rest untouched instead of transforming it (in a second
step??) to the correct form. This of course leads to nonsense and
results in an error message.

My personal impression is, that this is a bug in the ODBC driver, but of
course I'd like to hear your opinion. The only workaround for us would
be to change all of our application software and use the SELECT form for
PostgreSQL calls, but this would be a lot of work and would reduce the
portability of our software. So I hope you can fix this in the driver.

I attached a part of two ODBC log files (the "mylog_nnnn.log versions)
so you can see the problem. The statement string delivered from the
application is logged under: "Exec_with_parameters_resolved", while the
transformed version is logged in the nect line as: "stmt_with_params".


Expecting to hear from you and
with kind regards

Wolfgang Pasche
DigiTask GmbH
Hamburg/Haiger, Germany



P.S.: I also tried to use the other PostgreSQL ODBC driver, ODBCng.
Unfortunately, it was installed without errors, but didnt't wrote the
necessary entries into the Windows registry. I couldn't find registry
entries for this driver anywhere in the Internet, so I tried to contact
the developers via email, but didn't get an answer. Is this project
still under development or is it discontinued?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrTZrkACgkQUCcwxZk3KK1IbACfUKZbJ2nkwPLy6m/7GVG9S7Lw
qEIAoKmZ0KTCf38ObEN6jR3STE8J7ENo
=4mKA
-----END PGP SIGNATURE-----

Example for correct escape sequence transformation (SELECT statement):



[25096-0.389]SC_recycle_statement: self= 01168FC0
[25096-0.390]PDATA_free_params:  ENTER, self=01169164
[25096-0.391]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=95, stmt='select * from
egstat_dbins(100, 125, {d '2009-10-04'}, '15:36:46', 2000, 'ipadresse', 6, 0, 0)' 
[25096-0.395]   stmt_with_params = 'select * from egstat_dbins( 100, 125, '2009-10-04'::date , '15:36:46', 2000,
'ipadresse',6, 0, 0)' 
[25096-0.398]about to begin SC_execute
[25096-0.399]       Sending SELECT statement on stmt=01168FC0, cursor_name='SQL_CUR01168FC0' qflag=0,1
[25096-0.401]CC_send_query: conn=01163F68, query='select * from egstat_dbins( 100, 125, '2009-10-04'::date ,
'15:36:46',2000, 'ipadresse', 6, 0, 0)' 
[25096-0.407]send_query: done sending query 104bytes flushed


Example for wrong (missing) escape sequence transformation (CALL statement):



[22372-10.317]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=88, stmt='{call
egstat_dbins(100, 125, {d '2009-10-12'}, '13:28:26', 2000, 'ipadresse', 6, 0, 0)}' 
[22372-10.321]   stmt_with_params = 'SELECT * FROM egstat_dbins( 100, 125,  d '2009-10-12', '13:28:26', 2000,
'ipadresse',6, 0, 0)' 
[22372-10.323]about to begin SC_execute
[22372-10.324]      it's NOT a select statement: stmt=011620D0
[22372-10.326]CC_send_query: conn=01164F60, query='SELECT * FROM egstat_dbins( 100, 125,  d '2009-10-12', '13:28:26',
2000,'ipadresse', 6, 0, 0)' 
[22372-10.331]send_query: done sending query 100bytes flushed
[22372-10.332]in QR_Constructor
[22372-10.332]exit QR_Constructor
[22372-10.333]read -1, global_socket_buffersize=4096
[22372-10.334]Lasterror=10035
[22372-10.336]read 85, global_socket_buffersize=4096
[22372-10.337]send_query: got id = 'E'
[22372-10.338]send_query: 'E' - SFEHLER
[22372-10.340]send_query: 'E' - C42704
[22372-10.342]send_query: 'E' - MTyp �d� existiert nicht
[22372-10.345]send_query: 'E' - P40
[22372-10.346]send_query: 'E' - Fparse_type.c
[22372-10.349]send_query: 'E' - L200
[22372-10.351]send_query: 'E' - RtypenameType
[22372-10.353]read 6, global_socket_buffersize=4096
[22372-10.354]send_query: got id = 'Z'
[22372-10.355]SC_set_Result(11620d0, 1162a08)[22372-10.356]QResult: enter DESTRUCTOR
[22372-10.359]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'
[22372-10.391]CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='FEHLER: Typ �d� existiert nicht'