Parameterized SQL "WHERE IN" clause?

Hello, I'm trying to come up with a way to pass a variable from Visual FoxPro to Informix via the current ODBC driver that looks something like this: SELECT * FROM MyTable WHERE MyField IN (?MyListOfValues) ?MyListOfValues is a variable holding my list I want to filter on. Like: 'a', 'b', 'c', 'd', 'e'. The question mark is just VFP's way to indicate that it's a parameter value to be substituted with the contents of the variable before sending the SQL on it's way (and it works with normal WHERE x = ?Var type clauses). For some reason it no worky with the "WHERE IN"! I can hardcode the list in the select statement and it works fine, but if I try to use a dynamically set variable, I get no results. Any suggestions? FYI - I'm currently fighting with the ODBC Trace utility in WinXP, but for some reason it's not capturing any SQL, even if I issue something valid that returns a resultset. The log file is created, but it's zero bytes long. I think this log would be interesting to see how the SQL was formatted before being sent to the server. Thanks. -- William Fields MCSD - Microsoft Visual FoxPro US Bankruptcy Court Phoenix, AZ "I have no special talents, I am only passionately curious" - Albert Einstein

William Fields wrote: > Hello, > > I'm trying to come up with a way to pass a variable from Visual > FoxPro to Informix via the current ODBC driver that looks something > like this: > > SELECT * FROM MyTable WHERE MyField IN (?MyListOfValues)> > ?MyListOfValues is a variable holding my list I want to filter on. > Like: 'a', 'b', 'c', 'd', 'e'. I don't know FoxPro, so I can only assume what sort of variable you are using to store the 'a','b','c' part. Is it a string? I'll assume so. When it binds MyListOfValues during execution of the statement, it will probably make it look like this: ... WHERE MyField IN ("'a', 'b', 'c', 'd', 'e'") in other words, one specific string complete with letters, commas, spaces and quotes. No dynamic SQL binding system I've ever seen does what you are asking for. You might be able to exploit Informix's ROW types IF you can bind an array to a ? but I doubt it, especially through ODBC. On the bright side, these sorts of cases are precisely why it's acceptable to prepare and re-prepare a statement on the fly. Manually build the string and submit that without the use of ?