Random Dev Notes

January 23, 2010

VFP Error 1812 – Statement too long (.Net/LINQ to VFP perspective)

Filed under: Development — Tags: , — Tom Brothers @ 1:50 am

In the last 10 years or so that I’ve been working with VFP I’ve only encountered the “Statement too long” error a handful of times.  Within VFP, I found that this error could be handled in two ways.  One way to handle this error is to simply rewrite the SQL Statement.  Another way, which I only recently discovered, was to use a sys() command.

Here is a quick VFP example using the Northwind database.  I have a function that accepts a parameter which is a comma delimited list of Order Ids.  The parameter is passed into a simple SQL Select statement.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds) 
LOCAL lcSql 
 
lcSql = " SELECT * " + ;
            " FROM Orders " + ;
            " WHERE OrderId in (" + tcOrderIds + ")" + ;
            " into cursor curOrders " 
 
&lcSql

This method works fine when there are a limited amount of Order Ids but when there are a couple hundred Order Ids the statement fails.  This statement can easily be rewritten using a join statement to prevent the “Statement too long” error.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds) 
LOCAL lnIndex, laOrders[1]
 
CREATE CURSOR curOrderIds(OrderId i)
 
FOR lnIndex = 1 TO ALINES(laOrders, tcOrderIds, 1, ",")
    INSERT INTO curOrderIds value(VAL(laOrders[lnIndex]))
NEXT 
 
SELECT o.* ;
    FROM Orders o ;
    INNER JOIN curOrderIds i ON o.OrderId = i.OrderId ;
    into cursor curOrders
    
USE IN curOrderIds

An alternative option to re-writing the statement is to use the SYS(3055) command.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds) 
LOCAL lcSql 
 
lcSql = " SELECT * " + ;
            " FROM Orders " + ;
            " WHERE OrderId in (" + tcOrderIds + ")" + ;
            " into cursor curOrders " 
            
SYS(3055, 2040)
 
&lcSql

Ok so I have a simple work around for this error in VFP.  Now what about .Net…?

Here is the same example re-written in C#.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {
    conn.Open();
 
    string[] orderIds = new string[] {
        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262", 
            "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277", 
            "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292", 
            "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307", 
            "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322", 
            "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337", 
            "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352", 
            "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367", 
            "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382", 
            "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397", 
            "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"
    };
 
    DataTable dt = new DataTable();
 
    string sql = @"SELECT * FROM Orders WHERE OrderId in (" + string.Join(",", orderIds) + ")";
 
    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
    da.Fill(dt);
    da.Dispose();
}

When I run this code I get the same “Statement too long” error.

image

Out of the two ways that I know of to correct this issue… Using the SYS(3055) command would be the easiest to implement.  So I modified the code to include the SYS(3055) command.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {
    conn.Open();
 
    string[] orderIds = new string[] {
        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262", 
            "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277", 
            "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292", 
            "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307", 
            "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322", 
            "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337", 
            "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352", 
            "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367", 
            "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382", 
            "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397", 
            "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"
    };
 
    DataTable dt = new DataTable();
 
    string sql = @"SELECT * FROM Orders WHERE OrderId in (" + string.Join(",", orderIds) + ")";
 
    using (OleDbCommand cmd = conn.CreateCommand()) {
        cmd.CommandText = "SYS(3055, 2040)";
        cmd.ExecuteNonQuery();
 
        cmd.CommandText = "SYS(3055)";
        var sys3055 = cmd.ExecuteScalar();
        System.Diagnostics.Debug.WriteLine("SYS(3055) = " + sys3055.ToString());
    }
 
    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
    da.Fill(dt);
    da.Dispose();
}

No luck… the simple solution didn’t work.

image

Adding the SYS(3055) command just changed the error message to “Insufficient stack space.”  Researching this error led me back to SYS(3055) which clearly isn’t a fix for .Net.

I still have two options at this point.  I can pull all the Orders into a DataTable and then filter the results in .Net.  Using LINQ to DataSets with this option would have a nice feel to it… it would seem similar to processing the data in VFP but it would come at a heavy performance cost (assuming the data was a larger data set than the Northwind.dbc).  Needless to say I’d like to avoid this option if possible so I’ll move on to my last idea.  I need to rewrite the query to use an inner join as I did in the VFP code.  To pull this off I will need to create a temporary table to hold the Order Ids.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {
    conn.Open();
 
    string[] orderIds = new string[] {
        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262", 
        "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277", 
        "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292", 
        "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307", 
        "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322", 
        "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337", 
        "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352", 
        "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367", 
        "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382", 
        "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397", 
        "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"
};
 
    DataTable dt = new DataTable();
 
    string sql = @"
select * 
    from Orders o 
    inner join {0} t on o.OrderId = t.OrderId
";
 
    string tempTableFullPath = Path.ChangeExtension(Path.GetTempFileName(), "dbf");
 
    using (OleDbCommand cmd = conn.CreateCommand()) {
        cmd.CommandText = string.Format("create table "{0}" free (OrderId I)", tempTableFullPath);
        cmd.ExecuteNonQuery();
 
        for (int index = 0, total = orderIds.Length; index < total; index++) {
            cmd.CommandText = string.Format("insert into "{0}" values({1})", tempTableFullPath, orderIds[index]);
            cmd.ExecuteNonQuery();
        }
    }
 
    OleDbDataAdapter da = new OleDbDataAdapter(string.Format(sql, tempTableFullPath), conn);
    da.Fill(dt);
    da.Dispose();
}

This worked perfectly.

image


Great, I got a proof of concept to work using standard ADO.Net.  Now I’d like to get this to work with LINQ to VFP

Here is the example rewritten with LINQ to VFP.

NorthwindDataContext context = new NorthwindDataContext(connectionString);
 
int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();
var list = context.List<Order>().Where(o => ids.Contains(o.OrderID)).ToList();

image

Rewriting the LINQ to VFP statement isn’t as simple as it was in VFP or using standard ADO.Net.  The temporary table needs an Entity class and this Entity class will need to be mapped to the temporary table.  Plus I will need to be able to pass the full temporary file path as the Table Id.  After a few attempts I found that I could not pull this off without modifying the LINQ to VFP code.  So I modified the VfpQueryProvider to include a new method that would create and map the temporary table.  Here is an example of the rewritten LINQ to VFP statement using the temporary table.

NorthwindDataContext context = new NorthwindDataContext(connectionString);
 
int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();
 
using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {
    var list = (from o in context.List<Order>()
                join t in tempTable.EntityTable on o.OrderID equals t.Id
                select o).ToList();
}

image

* One thing to note about the TempTable class is that it implements IDisposable.  The dispose method will delete the temporary file so keep this in mind if you are using the TempTable when passing around IQueryable.  In this case you need to make sure the Dispose method isn’t called before executing the query.


Creating a temporary table is going to add a little extra processing time so this should only be used if needed.  Here is an example that of how I’m using this in my production code.

NorthwindDataContext context = new NorthwindDataContext(connectionString);
 
int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();
List<Order> list;
 
if (ids.Length > 100) {
    using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {
        list = (from o in context.List<Order>()
                join t in tempTable.EntityTable on o.OrderID equals t.Id
                select o).ToList();
    }
}
else {
    list = (from o in context.List<Order>()
            where ids.Contains(o.OrderID)
            select o).ToList();
}
 
return list;

Blog at WordPress.com.