Bugs item #1000460, was opened at 2005-12-01 14:25
You can respond by visiting:
http://pgfoundry.org/tracker/?func=d...oup_id=1000125

Category: None
Group: None
>Status: Closed
>Resolution: Accepted
Priority: 3
Submitted By: Nobody (None)
>Assigned to: Ludek Finstrle (luf)
Summary: UseServerSidePrepare still broken

Initial Comment:
I have sent this report also to the mailing list, but somehow this seemed not have gotten through:

"Dave Page" wrote:

>> I've fixed "server side prepare" problem. Patch attached.
>>
>> Please try and report back errors
>
>Already done - works like a charm :-)

Sorry, but I cannot confirm this here.

SQLPrepare()/SQLExecute() works fine the first time it is called.

But when SQLPrepare() is executed the second time, it does use the same plan
name (in my case _PLAN022C00A0) that was used for the first statement. The
following call to SQLExecute() fails with this error message:

<1> {42P05}(7) Error while executing the query;
ERROR: prepared statement "_PLAN022C00A0" already exists

Looking at the source code, it seems that the plan name is generated from the
statement address in Prepare_and_convert(). But I have requested a new
statement handle for the second call. Is here an internal statement caching at
work?

I have attached the relevant log entries below.

Rainer


[-7654017][SQLPrepare][-7654017]PGAPI_Prepare: entering...
[-7654017]**** PGAPI_Prepare: STMT_ALLOCATED, copy
[-7654017][SQLExecute][-7654017]PGAPI_Execute: entering...
[-7654017]PGAPI_Execute: clear errors...
[-7654017]recycle statement: self= 36438176
[-7654017]PDATA_free_params: ENTER, self=36438608
[-7654017]Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=156, stmt='SELECT "intItemIDCnt" FROM "tblItem" WHERE
(("intCountryID"=276)) AND ("intTimeEnd" > 1133362836) ORDER BY "intTimeEnd",
"chrTitle", "intItemIDCnt" LIMIT 50'
[-7654017]PGAPI_NumParams: entering...
[-7654017] stmt_with_params = 'PREPARE "_PLAN022C00A0" as SELECT
"intItemIDCnt" FROM "tblItem" WHERE (("intCountryID"=276)) AND ("intTimeEnd" >
1133362836) ORDER BY "intTimeEnd", "chrTitle", "intItemIDCnt" LIMIT 50;EXECUTE
"_PLAN022C00A0"'
[-7654017] Sending SELECT statement on stmt=36438176,
cursor_name='SQL_CUR022C00A0'
[-7654017]send_query(): conn=35340896, query='PREPARE "_PLAN022C00A0" as
SELECT "intItemIDCnt" FROM "tblItem" WHERE (("intCountryID"=276)) AND
("intTimeEnd" > 1133362836) ORDER BY "intTimeEnd", "chrTitle", "intItemIDCnt"
LIMIT 50;EXECUTE "_PLAN022C00A0"'
[-7654017]in QR_Constructor
[-7654017]exit QR_Constructor
[-7654017]in TL_Constructor
[-7654017]exit TL_Constructor
[-7654017]send_query: done sending query
[-7654017]QR_fetch_tuples: cursor = '', self->cursor=0
[-7654017]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[-7654017]MALLOC: tuple_size = 100, size = 800
[-7654017] done sending the query:
[-7654017]extend_column_bindings: entering ... self=36438312,
bindings_allocated=0, num_columns=1
[-7654017]exit extend_column_bindings



[-7654017][SQLPrepare][-7654017]PGAPI_Prepare: entering...
[-7654017]**** PGAPI_Prepare: STMT_ALLOCATED, copy
[-7654017][SQLExecute][-7654017]PGAPI_Execute: entering...
[-7654017]PGAPI_Execute: clear errors...
[-7654017]recycle statement: self= 36438176
[-7654017]PDATA_free_params: ENTER, self=36438608
[-7654017]PDATA_free_params: EXIT
[-7654017]Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=48, stmt='SELECT * FROM "tblItem" WHERE "intItemIDCnt" =
?'
[-7654017]PGAPI_NumParams: entering...
[-7654017]ResolveOneParam: from(fcType)=-18, to(fSqlType)=4
[-7654017] stmt_with_params = 'PREPARE "_PLAN022C00A0"(int4) as SELECT *
FROM "tblItem" WHERE "intItemIDCnt" = $1;EXECUTE "_PLAN022C00A0"(627)'
[-7654017] Sending SELECT statement on stmt=36438176,
cursor_name='SQL_CUR022C00A0'
[-7654017]send_query(): conn=35340896, query='PREPARE "_PLAN022C00A0"(int4) as
SELECT * FROM "tblItem" WHERE "intItemIDCnt" = $1;EXECUTE
"_PLAN022C00A0"(627)'
[-7654017]in QR_Constructor
[-7654017]exit QR_Constructor
[-7654017]the server returned the error: ERROR: prepared statement
"_PLAN022C00A0" already exists
[-7654017]send_query: done sending query
[-7654017]QR_fetch_tuples: cursor = '', self->cursor=0
[-7654017]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[-7654017]MALLOC: tuple_size = 100, size = 0
[-7654017] done sending the query:
[-7654017]STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error
while executing the query'
[-7654017]CONN ERROR: func=SC_execute, desc='', errnum=108, errmsg='ERROR:
prepared statement "_PLAN022C00A0" already exists'


----------------------------------------------------------------------

>Comment By: Ludek Finstrle (luf)
Date: 2005-12-06 11:12

Message:
Patch was sended to pgsql-odbc mailing list.
Thanks Rainer for report bug, initial patch and testing the patch.


----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=d...oup_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match