Board index » delphi » URGENT: Parameterized queries to Oracle not working from Delphi

URGENT: Parameterized queries to Oracle not working from Delphi

Hi,

I have a problem that I need the solution to urgently.  I'm writing a client
application for an Oracle 7.x database in Delphi.  I'm connecting to the
server with the Intersolv Oracle ODBC driver.  Until now, I've had no
problems with the server, either connecting to it, retreiving data, etc.  I
need to use paramaterized queries against the database, but when I try it,
I get an emtpy return set!  If I remove the parameter from the query and
replace it with a text string, I get the proper answer.

I am using Delphi STANDARD edition, and the regular TQuery component.
Parameterized queries work just fine against local data, just not against
the server.  So what gives?

Here's the SQL:

SELECT DISTINCT I."DESCRIPTION", P."PRODUCT_NAME", P."DC"
FROM "SLIM"."ITEM_MASTER" I, "SLIM"."PRODUCT" P
WHERE
(P."LINE_NAME" = :LineName)
AND (P."PRODUCT_NAME" = I."MASTER_PART_NUMBER")
ORDER BY
P."PRODUCT_NAME", I."DESCRIPTION", P."DC"

":LineName" is a string parameter with the value "DC Power Supply".  I've
tried other valid strings and still nothing.

I've traced the ODBC call, and something strange shows up.  In the call
that sends the actual SQL string, the parameter is replaced with a single
question mark, like this:

...
WHERE
(P."LINE_NAME" = ?)
AND ...

Obviously, there should be something else there, but is it Delphi, ODBC,
or Oracle that's filling it in incorrectly?

And please, as much as I'd normally like general speculation, I need to
talk to someone who knows the answer definitively, or has a good line on
it.  Please respond to my email address.

TIA,
Todd

--
Todd Fast
tf...@eden.com
"His mind is somewhat too much taken up with his mind." - John Earle
--------
Pencilneck Software, Cool Software Written by Geeks
Delphi components like ReportLink, the Delphi-to-Paradox report
component:
http://www.eden.com/~tfast/index.html.
--------
Todd's Gallery of Pathetic Human Regret, otherwise known as TGoPHR:
http://www.eden.com/~tfast/regret1.html
--------
Despite the obvious fact that the above ideas are products of a fevered
mind,
they are mine and not my employer's.

 

Re:URGENT: Parameterized queries to Oracle not working from Delphi


Quote
>I have a problem that I need the solution to urgently.  I'm writing a client
>application for an Oracle 7.x database in Delphi.  I'm connecting to the
>server with the Intersolv Oracle ODBC driver.  Until now, I've had no
>problems with the server, either connecting to it, retreiving data, etc.  I
>need to use paramaterized queries against the database, but when I try it,
>I get an emtpy return set!  If I remove the parameter from the query and
>replace it with a text string, I get the proper answer.

>I am using Delphi STANDARD edition, and the regular TQuery component.
>Parameterized queries work just fine against local data, just not against
>the server.  So what gives?

Hello Todd,

What happenes seems to be correct so far. If you use SQL with parameters, de
SQL statement is sent to the server with ? for each parameter. This is called
PREPARE. After that, you can send parameters, execute the SQL, send another
set of parameters, execute, etc. The SQL is send to the server only once. Then
you only have to send parameters and execure the query. The advantage is that
you don't have to send the whole SQL each time if only some parameters change.
The server allocated resources needed for executing the SQL only once, until
you UNPREPARE the SQL.

I have tried some time ago what you are trying to do. Although I forgot the
exact delails, I know it is described in the Delphi documentation with some
examples included. I think you need the methods OPEN, CLOSE and PREPARE of the
Delphi SQL component. You will find the answer there.

Suc6,

Ronald

Other Threads