Prepared Statements and Where In Statements

Just starting to investigate this. We’re using Prepared Statements for all of our MySQL queries. Everything works great until I try to do an IN in the where clause. Example:

Select customer_list.address "address", customer_list.city "city", customer_list.name "name", customer_list.ID "ID" From customer_list Where customer_list.country_id In (1,2,3)

This works great as a normal query, but using the Prepared statement it’s only bringing back those that match customer_list.country_id = 1.

Anyone seen this before? Any workarounds (other than do the sql by hand)?

probably already thought of this, but I’ll throw it out there for those who didn’t :slight_smile:

  • are you sure there ARE country_id = 2 and/or country_id = 3
  • have you tried to rearrange the list (2,3,1) (3,2,1) to see if it truly is the first item, or just likes “1”?
  • since it is a single table query… do you need “country_list.”?

What does your actual prepared statement sql look like?

Yup.

Correct. Only brings back the first one

No difference.

[code]Select customer_list.address “address”,
customer_list.city “city”,
customer_list.name “name”,
customer_list.ID “ID”
From customer_list

Where customer_list.country_id In (?)
Order By name[/code]

From a small sample app I’ve narrowed down the code:

[code]dim s as string = “5,4,3,2,1”
dim ps as MySQLPreparedStatement = db.Prepare(sSQL)

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_LONG)
ps.bind(0,s)

dim rs as RecordSet = ps.SQLSelect
if db.error then
msgbox "DB Error: " + db.ErrorMessage
return
end
while rs.eof= false
dim sName as string = rs.Field(“name”).StringValue

listbox1.AddRow sName

rs.MoveNext

wend
[/code]

It will only bring back customer_list.ID = 5.

If I do the SQL in SQLSelect it brings back the data as expected. There is something weird in how IN clauses are prepared.

Perhaps you need to do:

Where customer_list.country_id In (?, ?, ?)

and then bind each value.

http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives

Seems this is not limited to your situation… there are dozens of topics on the internet… and what Paul just posted is ONE solution that worked in some cases…

but it your list is consecutive sequential numbers (ie. 1 thru 5) then why not skip the list and use “BETWEEN” instead?

here is a proposed PHP solution

$array = array(1,2,3);
$data = $db->query("SELECT * FROM table WHERE id IN (?a)",$array);

[quote=251732:@Bob Keeney][code]Select customer_list.address “address”,
customer_list.city “city”,
customer_list.name “name”,
customer_list.ID “ID”
From customer_list

Where customer_list.country_id In (?)
Order By name[/code][/quote]

What are you trying to bind ?
That statement only allows you to bind a single value, not several
And you cant bind an array

[quote=251734:@Dave S]but it your list is consecutive sequential numbers (ie. 1 thru 5) then why not skip the list and use “BETWEEN” instead?
][/quote]

Because this isn’t a between query. I just chose those numbers at random and they happen to actually be in the list.

The final solution in my test app is this:

[code]dim ari() as integer = array(5,4,3,2,1) //ID’s to select

//Create an appropriate number of ? for the prepared statement
dim ars() as string
for i as integer = 0 to ari.Ubound
ars.Append “?”
next

//Create the SQL Statement.
dim sSQL as string = kSQL.replaceall("?", Join(ars, “,”))

//Prepare it
dim ps1 as MySQLPreparedStatement = db.Prepare(sSQL)

//Now loop through and bind them
for i as integer = 0 to ari.Ubound
ps1.BindType(i, MySQLPreparedStatement.MYSQL_TYPE_LONGLONG)
ps1.bind(i,ari(i))
next
//Get the data and then display it
dim rs as recordset = ps1.SQLSelect
if db.error then
msgbox "DB Error: " + db.ErrorMessage
return
end

while rs.eof = false
dim sName as string = rs.Field(“name”).StringValue
dim sID as string = rs.Field(“country_id”).StringValue
listbox1.AddRow sName, sID
rs.MoveNext
wend[/code]

or
create temp table
insert rows to temp table
select where using " in (select * from temptable)"
drop temptable