restriction in sql clause

gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

restriction in sql clause

Post by gaurav.shetti »

say i have a query of this form

select * from cases where case_id in (a,b,c, .... )

What is the maximum no of elements or ids i can have within those brackets ?

Is there any restriction ?
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

restriction in sql clause

Post by mark »

There's no coded limit on the number of items in an in.
The SQL statement itself may be limited to 8k characters so that's a limitation if you put the values inline as literals. You can use a parameter to get around that limit as well as avoid SQL escapement issues.

<strfmt "%s," $cases>
<$qcases=(convert($ret, 'strlst' ))>
<SQL "select * from cases where case_id in ($qcases)">
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

restriction in sql clause

Post by John »

That depends a little on how you are submitting the query. There may be an 8K limit to the size of the query in Vortex if you aren't using a parameter. If you do use a parameter then there isn't a limit.
John Turnbull
Thunderstone Software
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

restriction in sql clause

Post by gaurav.shetti »

I tried using what mark had suggested. But this is the error i got
SQL command too large

I guess the variable $qcases itself is greater than 8K characters. Does that matter?
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

restriction in sql clause

Post by John »

It shouldn't matter. The message suggests it was put in as a literal, not a parameter and so got included directly into the SQL rather than by reference, e.g.

<SQL "select * from cases where case_id in (" $qcases" )">

instead of

<SQL "select * from cases where case_id in ($qcases)">
John Turnbull
Thunderstone Software
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

restriction in sql clause

Post by gaurav.shetti »

Okay i tried this
<strfmt "%s" $grpperson>
<$qgroupids=(convert($ret, 'strlst' ))>
<SQL ROW SKIP=$skip MAX=$maxRecordsPerPage
$startquery
$indexquery
"ohr_id in (" $qgroupids ")"
$endquery
>


$grpperson is a string like .. 124,124325,1233425,1342, ...

still i am getting error : SQL command too large
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

restriction in sql clause

Post by John »

That's making it part of the literal SQL instead of a parameter. You need the comma at the end of the string, which is why there is a comma in the strfmt format string. It needs to be a parameter, eg.:

<strfmt "%s," $grpperson>
<$qgroupids=(convert($ret, 'strlst' ))>
<SQL ROW SKIP=$skip MAX=$maxRecordsPerPage
$startquery
$indexquery
"ohr_id in ($qgroupids)"
$endquery
>
John Turnbull
Thunderstone Software
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

restriction in sql clause

Post by mark »

Oops, also <strfmt "%s," $grpperson> should be <sum "%s," $grpperson> to get all elements in the $grpperson array instead of only the first.

If you're getting the too long msg with the strfmt that seems to imply you already have everything concatenated into $grpperson.
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

restriction in sql clause

Post by gaurav.shetti »

yes $grpperson has everything concatenated. That is values are stored as 1,2,3,4,5 into $grpperson.
So when we do a select and we get comma separated ids.

Do i still have to do
<strfmt "%s," $grpperson> and
<sum "%s," $grpperson>
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

restriction in sql clause

Post by John »

Yes, you need a comma as the last character, e.g.

1,2,3,4,5,

What type is ohr_id?
John Turnbull
Thunderstone Software
Post Reply