| Martin Lam 2005-04-07, 8:05 pm |
| Hi Andrew,
I suggest you should do like this:
1. Put the IN list in a table/cursor.
2. Use SELECT ... FROM TheTable LEFT JOIN TheInListTable ON.... WHERE (only
those matched records)
This solution can handle almost unlimited # of records in the IN list.
Best regards,
Martin Lam
"Andrew Dancy" <adancy@gmail.com> wrote in message
news:1107273711.979155.222410@c13g2000cwb.googlegroups.com...
> I have been using the beta VFP 9 OLEDB driver to develop a web
> application, but have been persistantly encountering the SQL Statement
> Too Long error.
>
> I have been able to determine that the problem is an 'IN' statement in
> my query, which can have up to 2000 or so clauses! I've switched to the
> VFP 9 driver because I gather the hard limit of 25 items for an IN
> clause has been replaced by a limit based on the SYS(3055) command.
>
> My problem is that I know that issuing the SYS(3055) command fixes the
> problem, but I can't see how to do this when using the OLEDB driver. I
> have tried the following code:
>
> cmdTest.CommandText = "SYS(3055,2040)"
> cmdTest.ExecuteNonQuery()
> cmdTest.CommandText = "select id from table where id IN (...long list
> here...)"
> Dim reader As OleDbDataReader = cmdTest.ExecuteReader()
>
> with no success. I have also tried the following:
>
> cmdTest.CommandText = "SYS(3055);select id from table where id IN
> (...long list here...)"
> Dim reader As OleDbDataReader = cmdTest.ExecuteReader()
>
> and
>
> cmdTest.CommandText = "select SYS(3055) as dummy, id from table where
> id IN (...long list here...)"
> Dim reader As OleDbDataReader = cmdTest.ExecuteReader()
>
> both without success. The former doesn't throw the Statement Too Long
> error, but doesn't return any results either! The latter still throws
> Statement Too Long.
>
> Does anyone know how I can use the SYS(3055) to increase the allowed
> complexity of SQL statement when using the OLEDB driver? Alternatively
> is there any way I can set this as an environment variable or change
> the default SYS(3055) value in the database or suchlike so I don't have
> to specify it every time?
>
> Any help would be much appreciated.
> Regards,
>
> Andrew Dancy
> Applications Developer
> Lovetts plc
>
|