Thread: Parse Errors with prepared query
Thanks for any guidance on the following issue:
I am running PostgreSQL 7.2.1 on redhat, accessing from Windows 2000 Professional. I installed ODBC psqlodbc-07_02_0001.zip off the web site. I have a data upload application written in Delphi which moves data from an Access table. I've used it at least ten times with an Interbase database, and have run something on the order of half a million records through it without apparent errors. It composes a statement of the form.
Insert into <Table> Fields<Field1,Field2,Field3,...> Values (?,?,?,...);
It prepares the statement and then executes it for each record in the Access table. On one table with about 33,000 record and twenty-some fields it works fine. On another table with 11 fields and about 6,000 records it fails. It fails to execute the query, and returns an error, usually of the form
ERROR: parser: parse error at or near "("
The string which the error is near is usually two or three characters. Sometimes the string is not one found anywhere in the variables I passed to the ODBC driver, but usually it is present. If I run the upload multiple times on the same table, I get errors on different records. The first error happens somewhere between record #500 and record #800, and then I get an error every 50 or 100 records. The tail of the log file (including a couple successful inserts) shows:
conn=101170728, query='Insert into calllogs (id, calldate, calltype, title, firstname, lastname, city, phone, caseno, empid, closed, consistencyid) Values('593', '10/01/2001 14:26:00', 'GH', '', 'Melody', 'Robinson', 'Long Beach', '(310) 608-3253', 'CLOSED - LB-UD LM 10-5 5:39 no longer needed asst 10-5', '2', '2', '5')'
conn=101170728, query='Insert into calllogs (id, calldate, calltype, title, firstname, lastname, city, phone, caseno, empid, closed, consistencyid) Values('594', '10/01/2001 14:36:00', 'GH', '', 'Sheryl', 'Cumingham', 'Norwalk', '(562) 863-8556', 'CLOSED - LB-called again; Busy 10-2 10:38a.m., LM 10-5 5:38, 10-6 1:19 LM', '14', '2', '7')'
conn=101170728, query='Insert into calllogs (id, calldate, calltype, title, firstname, lastname, city, phone, caseno, empid, closed, consistencyid) Values('595', '10/01/2001 14:45:00', 'GH', '', 'Lashay', 'Austin', 'Long Beach', '(562) 427-4381', 'CLOSED - LM 10-2 10:37', '14', '2', '3')'
ERROR from backend during send_query: 'ERROR: parser: parse error at or near "("'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=101170728, stmt=101188992, result=101199584
manual_result=0, prepare=1, internal=0
bindings=101211728, bindings_allocated=14
parameters=101198976, parameters_allocated=12
statement_type=1, statement='Insert into calllogs (id, calldate, calltype, title, firstname, lastname, city, phone, caseno, empid, closed, consistencyid) Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
stmt_with_params='Insert into calllogs (id, calldate, calltype, title, firstname, lastname, city, phone, caseno, empid, closed, consistencyid) Values('595', '10/01/2001 14:45:00', 'GH', '', 'Lashay', 'Austin', 'Long Beach', '(562) 427-4381', 'CLOSED - LM 10-2 10:37', '14', '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_CUR06080580'
----------------QResult Info -------------------------------
fields=101212128, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
message='ERROR: parser: parse error at or near "("', command='(NULL)', notice='(NULL)'
status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: parser: parse error at or near "("'
------------------------------------------------------------
henv=101189440, conn=101170728, status=1, num_stmts=16
sock=101189376, stmts=101189296, lobj_type=-999
---------------- Socket Info -------------------------------
socket=540, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=101181696, buffer_out=103546952
buffer_filled_in=1, buffer_filled_out=0,
Also: does anyone know, am I saving any time by using a prepared statement? It appears that the driver is just holding the statement and inserting the parameters, then passing to the server. Is that correct?
Thanks again
Ed Brown
> Ed Brown wrote:
>
> Thanks for any guidance on the following issue:
>
> I am running PostgreSQL 7.2.1 on redhat, accessing from Windows 2000
> Professional. I installed ODBC psqlodbc-07_02_0001.zip off the web
> site. I have a data upload application written in Delphi which moves
> data from an Access table. I've used it at least ten times with an
> Interbase database, and have run something on the order of half a
> million records through it without apparent errors. It composes a
> statement of the form.
>
> Insert into <Table> Fields<Field1,Field2,Field3,...> Values
> (?,?,?,...);
>
> It prepares the statement and then executes it for each record in the
> Access table. On one table with about 33,000 record and twenty-some
> fields it works fine. On another table with 11 fields and about 6,000
> records it fails. It fails to execute the query, and returns an error,
> usually of the form
> ERROR: parser: parse error at or near "("
Could you check the server side log(debug mode if possible)
and see what query issued exactly at the error ?
> Also: does anyone know, am I saving any time by using a prepared
> statement? It appears that the driver is just holding the statement
> and inserting the parameters, then passing to the server. Is that
> correct?
Yes. Unfortuantely there's no prepare functionality in
PostgreSQL yet and so the psqlodbc driver has little to
do with SQLPrepare.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/