Thread: ODBC 7.01.00.05 driver fails when text arrays are passed in as arguments to backend plpgsql functions

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


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