Board index » delphi » Cannot get parameterized queries to work for ORACLE

Cannot get parameterized queries to work for ORACLE

Hi,

I'm using Delphi 2 C/S and Oracle 7.3. I'm trying to do the following:

  Query.SQL.Add('SELECT * FROM Foo');
  Query.SQL.Add('  WHERE FooId = :ID');
  Query.Prepare;

  Query.ParamByName('ID').AsString := 'Duh';
  Query.Open;

Using the Oracle SQL Links driver, I get an EDBEngineError exception
for the 'Query.Open' instruction. The message is something like
'Capability not supported' (I'm using the German version, and the German
message is 'Operation nicht anwendbar'). Then I tried the same source on
an Interbase database using the Interbase SQL Links driver, and all is
well.

Why does the Oracle SQL Links driver not support such a very basic
capability? Is this a bug?

I appreciate any feedback,

-Andrej

PS: I'm using Oracle SQL Links driver version 3.0

 

Re:Cannot get parameterized queries to work for ORACLE


Quote
Andrej Gabara wrote in message <34AD06F5.BF4E2...@stud.uni-sb.de>...
>Hi,

>I'm using Delphi 2 C/S and Oracle 7.3. I'm trying to do the following:

>  Query.SQL.Add('SELECT * FROM Foo');
>  Query.SQL.Add('  WHERE FooId = :ID');
>  Query.Prepare;

>  Query.ParamByName('ID').AsString := 'Duh';
>  Query.Open;

<snip>
>Why does the Oracle SQL Links driver not support such a very basic
>capability? Is this a bug?

Because your doing it wrong. The string must be in quotes. Try
Query.ParamByName('ID').AsString := '''Duh''';

Thats three single quotes on each side.

        -- Kirk

Re:Cannot get parameterized queries to work for ORACLE


Just a wild stab in the dark.

But could it be that ID is a reserved word?

Have you tried the same code on LocalSQL?

HNY

Railton....

Quote
Andrej Gabara wrote:

> Hi,

> I'm using Delphi 2 C/S and Oracle 7.3. I'm trying to do the following:

>   Query.SQL.Add('SELECT * FROM Foo');
>   Query.SQL.Add('  WHERE FooId = :ID');
>   Query.Prepare;

>   Query.ParamByName('ID').AsString := 'Duh';
>   Query.Open;

> Using the Oracle SQL Links driver, I get an EDBEngineError exception
> for the 'Query.Open' instruction. The message is something like
> 'Capability not supported' (I'm using the German version, and the German
> message is 'Operation nicht anwendbar'). Then I tried the same source on
> an Interbase database using the Interbase SQL Links driver, and all is
> well.

> Why does the Oracle SQL Links driver not support such a very basic
> capability? Is this a bug?

> I appreciate any feedback,

> -Andrej

> PS: I'm using Oracle SQL Links driver version 3.0

Re:Cannot get parameterized queries to work for ORACLE


Quote
kroma wrote:

> >  Query.ParamByName('ID').AsString := 'Duh';

> Because your doing it wrong. The string must be in quotes. Try
> Query.ParamByName('ID').AsString := '''Duh''';

> Thats three single quotes on each side.

I don't think that I'm doing it wrong. It should be
        Query.ParamByName('ID').AsString := 'Duh';

'''Duh''' doesn't make sense. Besides, I've tested the app with
Interbase and it works fine. Only ORACLE gives me headaches.

Re:Cannot get parameterized queries to work for ORACLE


Hi Railton,

I haven't tested it on LocalSQL because it works on Interbase. I'm
not using LocalSQL because it is a bit different to real SQL.

Thank's
Andrej

Quote
Railton Frith wrote:

> Just a wild stab in the dark.

> But could it be that ID is a reserved word?

> Have you tried the same code on LocalSQL?

> HNY

> Railton....

> Andrej Gabara wrote:

> > Hi,

> > I'm using Delphi 2 C/S and Oracle 7.3. I'm trying to do the following:

> >   Query.SQL.Add('SELECT * FROM Foo');
> >   Query.SQL.Add('  WHERE FooId = :ID');
> >   Query.Prepare;

> >   Query.ParamByName('ID').AsString := 'Duh';
> >   Query.Open;

> > Using the Oracle SQL Links driver, I get an EDBEngineError exception
> > for the 'Query.Open' instruction. The message is something like
> > 'Capability not supported' (I'm using the German version, and the German
> > message is 'Operation nicht anwendbar'). Then I tried the same source on
> > an Interbase database using the Interbase SQL Links driver, and all is
> > well.

> > Why does the Oracle SQL Links driver not support such a very basic
> > capability? Is this a bug?

> > I appreciate any feedback,

> > -Andrej

> > PS: I'm using Oracle SQL Links driver version 3.0

Re:Cannot get parameterized queries to work for ORACLE


Oracle 7.3.x (or whatever) fully supports the capability. Make sure
that you are not pointing to a local data source, are "clear"ing the
Query's SQL text, and other error checking like that. Problem is not
with Oracle or SQL links.

Regards,
Nasir

Re:Cannot get parameterized queries to work for ORACLE


Hi Andrej,
I have been fighting a similar problem with Delphi 1 with paramaterized
queries.  It works fine with Interbase, but not with the company
production tables that are created with a DBX utility.  I tried it with
Params, ParamByName, and SQLStr - all with no paramater being passed to
my query.  It must be a function of the database being slightly
different depending on the product that is used for its creation.  The
work-around that we found was to assign a string variable prior to the
Add statement like the following:
          StringVar := CurrentString;
          ...
          ...
          qry.SQL.Add('SELECT * from "Table1" WHERE FieldID = ' +
StringVar + 'ORDER BY Whatever');

Maybe this will work for you as well.  Any other suggestions would be
greatly appreciated.
Doug Beers
dbe...@mich.com
Doug_Be...@Guardian.com

Quote
Andrej Gabara wrote:
> Hi Railton,

> I haven't tested it on LocalSQL because it works on Interbase. I'm
> not using LocalSQL because it is a bit different to real SQL.

> Thank's
> Andrej

> Railton Frith wrote:

> > Just a wild stab in the dark.

> > But could it be that ID is a reserved word?

> > Have you tried the same code on LocalSQL?

> > HNY

> > Railton....

> > Andrej Gabara wrote:

> > > Hi,

> > > I'm using Delphi 2 C/S and Oracle 7.3. I'm trying to do the
> following:

> > >   Query.SQL.Add('SELECT * FROM Foo');
> > >   Query.SQL.Add('  WHERE FooId = :ID');
> > >   Query.Prepare;

> > >   Query.ParamByName('ID').AsString := 'Duh';
> > >   Query.Open;

> > > Using the Oracle SQL Links driver, I get an EDBEngineError
> exception
> > > for the 'Query.Open' instruction. The message is something like
> > > 'Capability not supported' (I'm using the German version, and the
> German
> > > message is 'Operation nicht anwendbar'). Then I tried the same
> source on
> > > an Interbase database using the Interbase SQL Links driver, and
> all is
> > > well.

> > > Why does the Oracle SQL Links driver not support such a very basic

> > > capability? Is this a bug?

> > > I appreciate any feedback,

> > > -Andrej

> > > PS: I'm using Oracle SQL Links driver version 3.0

Other Threads