Skip site navigation (1) Skip section navigation (2)

ODBC driver fails when text arrays are passed in asarguments to backend plpgsql functions

From: Kristis Makris <kristis(dot)makris(at)datasoft(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: ODBC driver fails when text arrays are passed in asarguments to backend plpgsql functions
Date: 2001-06-12 16:04:43
Message-ID: (view raw or flat)
Lists: pgsql-odbc
Hello all,

I'm facing a problem with the latest 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,
                 ----------------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

   myArray  TEXT[];

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 Makris                          Datasoft Coproration


pgsql-odbc by date

Next:From: kingkingDate: 2001-06-12 16:06:50
Subject: Image to Postgresql database
Previous:From: Robby SlaughterDate: 2001-06-12 04:05:03
Subject: No dice trying to connect to an ODBC data source

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group