 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
TerryC
Guest
|
 Posted: Mon Feb 27, 2006 6:03 pm Post subject: Parameters in WHERE/IN clause value list? |
 |
|
I'm trying to use a parameterized query where the parameters are
in place of a comma-separated list of integers used in a
WHERE/ IN statement.
The problem is, I get an error:
"SQL error code = -303"
"arithmetic exception, numeric overflow, or string truncation"
If, however, I recreate the SQL by substituting the list of
numbers directly into the text, it works correctly.
Is it possible to correctly use parameters for a list of integer
values in a WHERE/IN clause?
Thanks,
Terry |
|
| Back to top |
|
 |
Wayne Niddery [TeamB]
Guest
|
| Posted: Mon Feb 27, 2006 8:03 pm Post subject: Re: Parameters in WHERE/IN clause value list? |
|
|
TerryC wrote:
| Quote: |
Is it possible to correctly use parameters for a list of integer
values in a WHERE/IN clause?
|
Not a *variable length* list.
If you know you will always pass, say, 3 values, then it can work:
select * from table where field in (:a, :b, :c)
and you assign each of the 3 parameters.
But you cannot do:
select * from table where field in (:list)
and assign that one parameter something like '1001, 1002, 1003'. Interbase
will see that as a single parameter value and try to find rows that have
that entire string as a field value.
You can look at http://cc.borland.com/Item.aspx?id=15065 to see one possible
solution to this, but it has limitations. Be sure to read the posted
comments.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
SpaceShipOne; GovernmentZero |
|
| Back to top |
|
|
TerryC
Guest
|
| Posted: Mon Feb 27, 2006 9:03 pm Post subject: Re: Parameters in WHERE/IN clause value list? |
|
|
| Quote: | Is it possible to correctly use parameters for a list of
integer values in a WHERE/IN clause?
Not a *variable length* list.
If you know you will always pass, say, 3 values, then it can
work:
select * from table where field in (:a, :b, :c)
and you assign each of the 3 parameters.
But you cannot do:
select * from table where field in (:list)
and assign that one parameter something like '1001, 1002,
1003'. Interbase will see that as a single parameter value and
try to find rows that have that entire string as a field value.
You can look at http://cc.borland.com/Item.aspx?id=15065 to see
one possible solution to this, but it has limitations. Be sure
to read the posted comments.
|
Thanks for this, Wayne. I'll look it over closely. I think
this approach MIGHT work for me.
-Terry |
|
| Back to top |
|
|
Quinn Wildman
Guest
|
| Posted: Wed Mar 08, 2006 7:03 pm Post subject: Re: Parameters in WHERE/IN clause value list? |
|
|
Another possible solution would be to do an inner join with a temporary
that has the IN values.
Wayne Niddery [TeamB] wrote:
| Quote: | TerryC wrote:
Is it possible to correctly use parameters for a list of integer
values in a WHERE/IN clause?
Not a *variable length* list.
If you know you will always pass, say, 3 values, then it can work:
select * from table where field in (:a, :b, :c)
and you assign each of the 3 parameters.
But you cannot do:
select * from table where field in (:list)
and assign that one parameter something like '1001, 1002, 1003'. Interbase
will see that as a single parameter value and try to find rows that have
that entire string as a field value.
You can look at http://cc.borland.com/Item.aspx?id=15065 to see one possible
solution to this, but it has limitations. Be sure to read the posted
comments.
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
