Thread: ODBC 7.01.00.05 driver fails when text arrays are passed in as arguments to backend plpgsql functions
ODBC 7.01.00.05 driver fails when text arrays are passed in as arguments to backend plpgsql functions
From
Kristis Makris
Date:
Hello all,
I'm facing a problem with the latest 7.01.00.05 ODBC driver used under
win2000 through MSVC++ 6.0, executing statements to a PostgreSQL 7.1.2
on a i686-pc-linux-gnu, compiled by GCC egcs-2.91.66. In particular, the
driver ?crashes? when the following postgres plpgsql function is called:
ESPRateForWResource_cleanup(TEXT[], INT4, INT4) RETURNS INT4
The statement executed is:
SELECT ESPRateForWResource_cleanup ('{''C-1'', ''C-2''}', 2, 3)
I have also tried the following with the same success:
SELECT ESPRateForWResource_cleanup ('{"C-1", "C-2"}', 2, 3)
The driver reports the following (as shown in the logs):
+++++++++++++++++==+++++++++++++++++==+++++++++++++++++==+++++++++++++++++==
conn=32424464, query='SELECT ESPRateForWResource_cleanup ('{''C-1'',
''C-2''}', 2, 3)'
[ fetched 1 rows ]
STATEMENT ERROR: func=SC_execute, desc='', errnum=-1, errmsg='Error
while executing the query (non-fatal)'
------------------------------------------------------------
hdbc=32424464, stmt=32581216, result=32439368
manual_result=0, prepare=0, internal=0
bindings=32439864, bindings_allocated=1
parameters=0, parameters_allocated=0
statement_type=0, statement='SELECT
ESPRateForWResource_cleanup ('{''C-1'', ''C-2''}', 2, 3)'
stmt_with_params='SELECT ESPRateForWResource_cleanup
('{''C-1'', ''C-2''}', 2, 3)'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
cursor_name='SQL_CUR01F12660'
----------------QResult Info
-------------------------------
fields=32439472, manual_tuples=0,
backend_tuples=32646984, tupleField=32646984, conn=32424464
fetch_count=0, fcount=1, num_fields=1, cursor='(NULL)'
message='NOTICE: Hello world - 4
', command='SELECT', notice='(NULL)'
status=6, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=0, errmsg=''
------------------------------------------------------------
henv=32581104, conn=32424464, status=1, num_stmts=16
sock=32581144, stmts=32439192, lobj_type=-999
---------------- Socket Info -------------------------------
socket=524, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=32430952, buffer_out=32435072
buffer_filled_in=16, buffer_filled_out=0, buffer_read_in=15
+++++++++++++++++==+++++++++++++++++==+++++++++++++++++==+++++++++++++++++==
There also exists a similar plpgsql function on the backend defined as:
CropForTaxParcel_cleanup(INT4[], INT4, TEXT) RETURNS INT4
This function executes without any errors.
Both functions execute with no errors when executed through the 7.1.2
psql client. Is there something wrong with the ODBC driver that causes
this? Is there any workaround to the ODBC driver not being able to
handle such a statement? Does the ODBC driver fail to handle only TEXT[]
types, or is there more to that? I noticed that the driver reports "[
fetched 1 rows ]", and all the commands coded in this function seem to
take effect, even if ODBC crashes. Does the ODBC driver *somehow*
misinterpets a reply given by the backend? Has anybody else experienced
anything like that?
Even though TEXT[] variables can be passed in as arguments in plpgsql, I
noticed that declaration of such variables is not permitted
e.g
..
DECLARE
myArray TEXT[];
BEGIN
..
END:
Could this have anything to do with the peculiar behaviour of ODBC?
I'm wondering if creating user defined arrays (as entirely new data
types within postgres) would solve this. In any case, though, should the
driver be *halting* like that?
Thanks for any help,
-Kristis
--
---------------------------------------------------------------------
Kristis Makris Datasoft Coproration
kristis.makris@datasoft.com http://www.datasoft.com
---------------------------------------------------------------------
Re: ODBC 7.01.00.05 driver fails when text arrays are passed in asarguments to backend plpgsql functions
From
Hiroshi Inoue
Date:
Kristis Makris wrote:
>
> Hello all,
>
> I'm facing a problem with the latest 7.01.00.05 ODBC driver used under
> win2000 through MSVC++ 6.0, executing statements to a PostgreSQL 7.1.2
> on a i686-pc-linux-gnu, compiled by GCC egcs-2.91.66. In particular, the
> driver ?crashes? when the following postgres plpgsql function is called:
>
> ESPRateForWResource_cleanup(TEXT[], INT4, INT4) RETURNS INT4
>
> The statement executed is:
>
> SELECT ESPRateForWResource_cleanup ('{''C-1'', ''C-2''}', 2, 3)
>
> I have also tried the following with the same success:
> SELECT ESPRateForWResource_cleanup ('{"C-1", "C-2"}', 2, 3)
>
> The driver reports the following (as shown in the logs):
>
> +++++++++++++++++==+++++++++++++++++==+++++++++++++++++==+++++++++++++++++==
> conn=32424464, query='SELECT ESPRateForWResource_cleanup ('{''C-1'',
> ''C-2''}', 2, 3)'
> [ fetched 1 rows ]
> STATEMENT ERROR: func=SC_execute, desc='', errnum=-1, errmsg='Error
> while executing the query (non-fatal)'
> ------------------------------------------------------------
> hdbc=32424464, stmt=32581216, result=32439368
> manual_result=0, prepare=0, internal=0
> bindings=32439864, bindings_allocated=1
> parameters=0, parameters_allocated=0
> statement_type=0, statement='SELECT
> ESPRateForWResource_cleanup ('{''C-1'', ''C-2''}', 2, 3)'
> stmt_with_params='SELECT ESPRateForWResource_cleanup
> ('{''C-1'', ''C-2''}', 2, 3)'
> data_at_exec=-1, current_exec_param=-1, put_data=0
> currTuple=-1, current_col=-1, lobj_fd=-1
> maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
> scroll_concurrency=1
> cursor_name='SQL_CUR01F12660'
> ----------------QResult Info
> -------------------------------
> fields=32439472, manual_tuples=0,
> backend_tuples=32646984, tupleField=32646984, conn=32424464
> fetch_count=0, fcount=1, num_fields=1, cursor='(NULL)'
> message='NOTICE: Hello world - 4
The NOTICE message(Hello world - 4) seems to cause the error.
regards,
Hiroshi Inoue