SQL Statement Too Long error with VFP 9 OLEDB driver

Discussion:

(too old to reply)

Andrew Dancy

2005-02-01 16:01:52 UTC

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

Anders Altberg

2005-02-02 00:36:17 UTC

Permalink

Split the IN clause
WHERE id IN (,,,,) OR id IN (,,,,) OR id IN (,,, )
You can also put the 2000 values in a comma-delimited string and use
SELECT * FROM Table WHERE ?string LIKE '%'+id+'%'
-Anders

Post by Andrew Dancy
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
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()
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

Martin Lam

2005-02-21 10:22:14 UTC

Permalink

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