Board index » delphi » SQL Parameters: multi value WHERE / using "in (:var1, :var2)

Andrew Ward

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

SQL Parameters: multi value WHERE / using "in (:var1, :var2)

hey All...

It just don't seem to work! I get an empty set! any ideas?

if i like put a standard number in as well, that works! But the vars
won't. annoying

seems like they should. I quote:

Variables or parameter markers (?) can be used in DML statements in
place of values. Variables must always be preceded by a colon (:). For
example:

SELECT LAST_NAME, FIRST_NAME
 FROM "CUSTOMER.DB"
 WHERE LAST_NAME > :var1 AND FIRST_NAME < :var2

Am i just dull? I wont to use multivalues for a query!? Don't want to
rewrite SQL everytime!

--

--Andrew Ward       mailto:andr...@techie.com
   http://www.newcollege.unsw.edu.au/andreww

'A glove,' said Crash, in a terrible voicee.
Whoever heard of a serious musician with a glove?'
-Terry Pratchett, "Soul Music"

 
Darkne

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Parameters: multi value WHERE / using "in (:var1, :var2)


Are you in your code setting the parameters?

Such as Example below?

Query1.SQL.Clear;
Query1.SQL,Add('SELECT * FROM CUSTOMER.DB');
Query1.SQL,Add('WHERE LAST_NAME > :var1 AND');
Query1.SQL,Add('LAST_NAME < :var2 ');
Query1.ParambyName('var1').AsString := 'John';
Query1.ParambyName('var2').AsString := 'Sally';
Query1.Prepare;
Query1.ExecSQL;

Try that then the Query will work at runtime, to test the Query at
design time, click on query1 and thgen click on params and set the
type and enter sample values then test the query to see if it works
remembering the set the Queries Params when necessary!

Cheers

On Mon, 24 Nov 1997 23:25:39 +1100, Andrew Ward

Quote
<NOSPAM-andr...@techie.com> wrote:
>hey All...

>It just don't seem to work! I get an empty set! any ideas?

>if i like put a standard number in as well, that works! But the vars
>won't. annoying

>seems like they should. I quote:

>Variables or parameter markers (?) can be used in DML statements in
>place of values. Variables must always be preceded by a colon (:). For
>example:

>SELECT LAST_NAME, FIRST_NAME
> FROM "CUSTOMER.DB"
> WHERE LAST_NAME > :var1 AND FIRST_NAME < :var2

>Am i just dull? I wont to use multivalues for a query!? Don't want to
>rewrite SQL everytime!

>--

>--Andrew Ward       mailto:andr...@techie.com
>   http://www.newcollege.unsw.edu.au/andreww

>'A glove,' said Crash, in a terrible voicee.
>Whoever heard of a serious musician with a glove?'
>-Terry Pratchett, "Soul Music"

Michael Johnso

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Parameters: multi value WHERE / using "in (:var1, :var2)


remember, that by default PARADOX tables are case sensitive

-Michael

Quote

Andrew War

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Parameters: multi value WHERE / using "in (:var1, :var2)


--------------FDF39ED77BE8A03203B92C9F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hey Guys... unfortuneatly, those are not the probs...

Quote
Darkness wrote:
> Are you in your code setting the parameters?

Yep! Previous message is below, but now I'll add my code. I may just go a
woosy effort and use a filter. It may do the job anyhow!

these are the SQL (design time) strings
Select
  componts."Title",  componts."PartNo" {etc!!!}
From 'componts.db'
As componts
where TypeNo in (:CurrentType, 21,:CurrentType)

I suspect that it is something to do with having vars in the 'in'
statement. I tried wacking the 21 in to try and prompt a rxn, but it is the
only one which works. CurrentType has a default val which does not work
either.

Quote
> >hey All...

> >It just don't seem to work! I get an empty set! any ideas?

> >if i like put a standard number in as well, that works! But the vars
> >won't. annoying

> >seems like they should. I quote:

> >Variables or parameter markers (?) can be used in DML statements in
> >place of values. Variables must always be preceded by a colon (:). For
> >example:

> >SELECT LAST_NAME, FIRST_NAME
> > FROM "CUSTOMER.DB"
> > WHERE LAST_NAME > :var1 AND FIRST_NAME < :var2

> >Am i just dull? I wont to use multivalues for a query!? Don't want to
> >rewrite SQL everytime!

--Andrew Ward       mailto:andr...@techie.com
   http://www.newcollege.unsw.edu.au/andreww

'A glove,' said Crash, in a terrible voicee.
Whoever heard of a serious musician with a glove?'
-Terry Pratchett, "Soul Music"

--------------FDF39ED77BE8A03203B92C9F
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>

<P>Hey Guys... unfortuneatly, those are not the probs...

Quote
<P>Darkness wrote:

<BLOCKQUOTE TYPE=CITE>Are you in your code setting the parameters?</BLOCKQUOTE>
Yep! Previous message is below, but now I'll add my code. I may just go
a woosy effort and use a filter. It may do the job anyhow!

<P>these are the SQL (design time) strings
<BR><I>Select</I>
<BR><I>&nbsp; componts."Title",&nbsp; componts."PartNo" {etc!!!}</I>
<BR><I>From 'componts.db'</I>
<BR><I>As componts</I>
<BR><I>where TypeNo in (:CurrentType, 21,:CurrentType)</I>

<P>I suspect that it is something to do with having vars <U>in</U> the
'in' statement. I tried wacking the 21 in to try and prompt a rxn, but
it is the only one which works. CurrentType has a default val which does
not work either.
<BR>&nbsp;
<BLOCKQUOTE TYPE=CITE>>hey All...
<BR>>
<BR>>It just don't seem to work! I get an empty set! any ideas?
<BR>>
<BR>>if i like put a standard number in as well, that works! But the vars
<BR>>won't. annoying
<BR>>
<BR>>seems like they should. I quote:
<BR>>
<BR>>Variables or parameter markers (?) can be used in DML statements in
<BR>>place of values. Variables must always be preceded by a colon (:).
For
<BR>>example:
<BR>>
<BR>>SELECT LAST_NAME, FIRST_NAME
<BR>> FROM "CUSTOMER.DB"
<BR>> WHERE LAST_NAME > :var1 AND FIRST_NAME &lt; :var2
<BR>>
<BR>>
<BR>>Am i just dull? I wont to use multivalues for a query!? Don't want
to
<BR>>rewrite SQL everytime!</BLOCKQUOTE>
--Andrew Ward&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <A HREF="mailto:andr...@techie.com">mailto:andr...@techie.com</A>
<BR>&nbsp;&nbsp; <A HREF="http://www.newcollege.unsw.edu.au/andreww">http://www.newcollege.unsw.edu.au/andreww</A>

<P>'A glove,' said Crash, in a terrible voicee.
<BR>Whoever heard of a serious musician with a glove?'
<BR>-Terry Pratchett, "Soul Music"
<BR>&nbsp;</HTML>

--------------FDF39ED77BE8A03203B92C9F--

Other Threads

1. BDE: "Invalid parameter" on sql open

2. SQL parameter causes "Operation not applicable"

3. Assigning blob values using "insert"

4. changing values to "no value"

5. R: Help using value parameter!!

6. Help using value parameter!!

7. using sql "COUNT(*)"

8. Returning the value of a parameter using an event on a dcom server

9. using "like" in a SQL statement

10. Set Query Parameter using Login Values in a WebSnap Form