Board index » delphi » HELP: Parameterized Query

HELP: Parameterized Query

I'm having trouble getting a parameterized query to work properly.  Here is
what I have:

The SQL for the query is as follows:

SELECT
PartNum,
InterchangeType
FROM
INTRCHNG
WHERE
PrimaryCode = :PrimaryCode AND
ManufCode = (
  SELECT
  ManufCode
  FROM
  MANUF
  WHERE
  ManufName = :ToManuf)

The query is performed like this:

with dmFastCat.quInterchange do begin
    if Active then Close;
    ParambyName('ToManuf').AsString := cbToManuf.Text;
    ParambyName('PrimaryCode').AsInteger := PrimaryCode;
    Open;
  end;

I get an EAccessViolation when trying to perform if and only if I try to
set the ToManuf param.  If I comment that line out and replace :ToManuf
with a string in the SQL, the query is performed without error!  Both
params are defined as the correct type under Params.  What am I missing
here?

Any and all help is appreciated.  Thanks in advance.

Bret

 

Re:HELP: Parameterized Query


Quote
Bret Rumsey wrote:

> I'm having trouble getting a parameterized query to work properly.  Here is
> what I have:

> The SQL for the query is as follows:

> SELECT
> PartNum,
> InterchangeType
> FROM
> INTRCHNG
> WHERE
> PrimaryCode = :PrimaryCode AND
> ManufCode = (
>   SELECT
>   ManufCode
>   FROM
>   MANUF
>   WHERE
>   ManufName = :ToManuf)

> The query is performed like this:

> with dmFastCat.quInterchange do begin
>     if Active then Close;
>     ParambyName('ToManuf').AsString := cbToManuf.Text;
>     ParambyName('PrimaryCode').AsInteger := PrimaryCode;
>     Open;
>   end;

> I get an EAccessViolation when trying to perform if and only if I try to
> set the ToManuf param.  If I comment that line out and replace :ToManuf
> with a string in the SQL, the query is performed without error!  Both
> params are defined as the correct type under Params.  What am I missing
> here?

> Any and all help is appreciated.  Thanks in advance.

> Bret

Try putting Prepare method before you get new Params in your Query.

Regards Ilkka

Re:HELP: Parameterized Query


In article <01bc4dd6$ee9df280$c314a5ce@Prumseys>,
   "Bret Rumsey" <rums...@primenet.com> wrote:

Hi,

two ideas only:

First:
your SQL-statement looks more complicated, than
it must be:

Why not instead of:

Quote
>SELECT
>PartNum,
>InterchangeType
>FROM
>INTRCHNG
>WHERE
>PrimaryCode = :PrimaryCode AND
>ManufCode = (
>  SELECT
>  ManufCode
>  FROM
>  MANUF
>  WHERE
>  ManufName = :ToManuf)

take the SQl-statemaent:

SELECT
 PartNum, InterchangeType
FROM
 INTRCHNG I, ManufName M
WHERE
 I.ManufCode = M.ManufCode AND
 I.PrimaryCode = :PrimaryCode AND
 M.ManufName = :ToManuf

I could not really image this as the cause of
your error, but may be the Delphi-PARAM-tokenizer
has any problems with sub-queries.

Second:

This has helped me in Delphi 1.0 (before 1.02)

instead of:

Quote
>with dmFastCat.quInterchange do begin
>    if Active then Close;
>    ParambyName('ToManuf').AsString := cbToManuf.Text;
>    ParambyName('PrimaryCode').AsInteger := PrimaryCode;
>    Open;
>  end;

one more line after the line "if active ..":

     UnPrepare ;
     Prepare ;

This pushes the database backend to evaluate again the query
and also the params.

Good luck and thanks for an answer, if you found a solution,
cause I'm going to use sub-queries, too.

Ulrich Kamp from Germany
k...@hdg.de

Other Threads