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;

January 7, 2010

IQToolkitCodeGen (alpha version)

Filed under: Development — Tags: , , — Tom Brothers @ 11:40 am

When I first started using LINQ to VFP I didn’t mind manually writing Entity classes and mapping the data but after awhile I found it to become a tedious task.  So I decided to write a code generation application that would work with LINQ to VFP and the IQToolkit SQL Server Provider.  This application has really turned out to be a play thing for me so that I can get some hands on learning with WPF and Spark View Engine.  But before I’d allow myself to get to far into playing around I wanted to make sure I got the core functionality done.  At this point I have an alpha build available for anyone interested. 

image


Settings Overview:

image

As expected, you can use a full connection string to access VFP or SQL Server.  There are also a few unconventional connection options.  You can specify the full path to a dbc file or a specify a directory for free tables.  Both of these options will be used to create a connection string for VFP.  You can also provide a modified version of a SQL Server connection string that includes a pipe delimited list of databases (Example:  Data Source=.;Initial Catalog=Northwind|AspNet;Integrated Security=True). 

image
The Data Context Settings were designed with two different Data Contexts types in mind.  The first Data Context is an Entity Provider which is basically a class that has a property for each Entity.  This type of Data Context is what the IQToolkit Tests use.  The other type of Data Context is a based on a Repository Pattern which uses Generic methods instead of accessing an Entity property. 

The Data Context Settings allow you to specify the class name, output file, and namespace for both types of these Data Contexts.  The Base Class setting is for the Repository Data Context.

image
The Entity Settings allow you to specify the file extension, namespace, output path, and template.  There is only one Entity template available. 
 
image
The Mapping Settings can be used to create an Attribute Mapping class or an Xml Mapping file.


By default, the Entity Provider Template was designed to work with the Attribute Mapping Template and the Repository Provider Template was designed to work with the Xml Mapping Template.


Getting Started:

Enter all the setting values and then click the Load Data button.  Then the grids will be populated with the Tables, Columns, and Associations information.

The Tables grid is pretty simple to use.  Just check the checkbox in the Include column if you want an Entity class created.  The Entity class name can be specified using the grid’s Entity Name column.

image

The Columns grid shows the Columns of the selected row in the Tables grid.  This grid has a few more available options than the Table grid but is just as easy to use.  Check the checkbox in the Include column if you want the Entity class to include the property for the selected column.  In this grid, you can also change the name and type of the property as well as identify the primary key and indicate if it is an auto generated key.

image

The last grid shows the Associations.  This grid has an Include column and a Property Name column that can be set.

image

Click the Generate Files button after all the mapping information has been entered to complete the process.


Don’t like my templates?

The template files are distributed with this application so feel free to modify the templates as needed. Additionally, if you feel that you need to add a new template just add it to the appropriate folder and it will show up in the combo box after resetting the application.

This is a diagram shows the Template classes along with classes that they reference.

image

January 1, 2010

Using the Spark View Engine in a Desktop Application

Filed under: Development — Tags: — Tom Brothers @ 1:46 am

I recently decided to write a code generation application using the Spark View Engine.  I started off by reviewing the provided Samples Solution and found the EmailOrTextTemplating Project.  This Project seemed to be a perfect example of what I needed.  Using the DefaultMessageBuilder class as a starting point, I created a new WinForm Application which included a very simple template named MyTemplate.spark.  This file contained one expression “${MyText}”.  After creating the template file, I added a reference to Spark.dll and then I modified the Program file with the following:

using System;
using System.IO;
using System.Windows.Forms;
using Spark;
 
namespace SparkViewEngineTest {
    static class Program {
        [STAThread]
        static void Main() {
            SparkViewEngine engine = new SparkViewEngine();
 
            var descriptor = new SparkViewDescriptor().AddTemplate("MyTemplate.spark");
 
            MyTemplate view = (MyTemplate)engine.CreateInstance(descriptor);
            view.MyText = "blah";
 
            StringWriter sw = new StringWriter();
            view.RenderView(sw);
 
            MessageBox.Show(sw.ToString());
        }
    }
 
    public abstract class MyTemplate : AbstractSparkView {
        public string MyText { get; set; }
    }
}

When I ran the program I got an error indicating that the template file didn’t exist.  The path provided in the exception showed that the template file was expected to be found in a “Views” subfolder.  I thought that this was some weird constraint so I moved my file into a “Views” subfolder.  After moving the file I re-ran the application.  Then I got a new exception complaining about “’SparkViewBase’ does not exist in the namespace ‘Spark’”.  At this point I was wondering what the heck I missed.  So I went back to the ExmailOrTextTemplating Project to see what I overlooked.  After a little while I noticed that the web.config has some Spark specific settings.  The one setting that caught my attention was PageBaseType.  So I went back to my code and set the PageBaseType setting to the MyTemplate class.  This change corrected the last error.  Perfect, now I have some working code (or so I thought).

using System;
using System.IO;
using System.Windows.Forms;
using Spark;
 
namespace SparkViewEngineTest {
    static class Program {
        [STAThread]
        static void Main() {
            SparkViewEngine engine = new SparkViewEngine();
            engine.Settings.PageBaseType = typeof(MyTemplate).FullName;
    var descriptor = new SparkViewDescriptor().AddTemplate("MyTemplate.spark");
 
            MyTemplate view = (MyTemplate)engine.CreateInstance(descriptor);
            view.MyText = "blah";
 
            StringWriter sw = new StringWriter();
            view.RenderView(sw);
 
            MessageBox.Show(sw.ToString());
        }
    }
 
    public abstract class MyTemplate : AbstractSparkView {
        public string MyText { get; set; }
    }
}

With working code in hand, I created a Test Project and started writing some tests for my code generation templates.  Everything worked as expected.  Great.  Then I moved on to writing the rest of the code generation application.  During testing of the code generation application I received an exception when rendering the templates.  As it turns out, all my template testing up until this point was against template files using a relative path.  But my application was using full paths.  Not testing this scenario was clearly an oversight on my part… but who would have thought that this mattered…?

Ok… back to my Spark View WinForm Test Application.  When I tested a full path I got this exception.

image

Dang… using the Spark View Engine turned out to be a little more challenging than expected.  And what the heck is that exception all about…?  Alright, time to get the Spark View Engine source code to see if I can get some clues on how to resolve this issue.  After a little debugging, I traced the source of the exception back to ViewLoader.PartialViewFolderPaths.

image

I found that I could move the “if” statement above the two yield statements I no longer got this exception.  But modifying the source code and consequently having a custom build, of Spark.dll, as part of my code generation application was not what I wanted.  At this point I felt that the Sample Projects weren’t enough to help me get started.  So I did a search on stackoverflow and found this entry “Using Spark View Engine in a stand alone application.”  I found two things of interest on this page.  The first interesting thing was that there was apparently another sample project available for review.  Unfortunately though, this project was hidden in this source code solution and not part of the samples solution.  The second interesting think was that the example code was setting a ViewFolder property on the Spark View Engine.  Ah… finally… the missing link.

I changed my code to include setting the ViewFolder property and passed the AddTemplate the template file relative path then everything worked again.  This change also made it so that I no longer needed to put my template files in a Views folder as I’ve previously believed needed to be done.

using System;
using System.IO;
using System.Windows.Forms;
using Spark;
using Spark.FileSystem;
 
namespace SparkViewEngineTest {
    static class Program {
        [STAThread]
        static void Main() {
            FileInfo fi = new FileInfo(@"MyTemplate.spark");
            SparkViewEngine engine = new SparkViewEngine();
            engine.ViewFolder = new FileSystemViewFolder(fi.DirectoryName);
            engine.Settings.PageBaseType = typeof(MyTemplate).FullName;
            
            var descriptor = new SparkViewDescriptor().AddTemplate(fi.Name);
 
            MyTemplate view = (MyTemplate)engine.CreateInstance(descriptor);
            view.MyText = "blah";
 
            StringWriter sw = new StringWriter();
            view.RenderView(sw);
 
            MessageBox.Show(sw.ToString());
        }
    }
 
    public abstract class MyTemplate : AbstractSparkView {
        public string MyText { get; set; }
    }
}

** One thing to note… The ArgumentNullException was still thrown when a full path was passed  to AddTemplate.


Here is the code I ended up with after a little refactoring.

using System;
using System.IO;
using System.Windows.Forms;
using Spark;
using Spark.FileSystem;
 
namespace SparkViewEngineTest {
    static class Program {
        [STAThread]
        static void Main() {
            FileInfo fi = new FileInfo("MyTemplate.spark");
 
            using (SparkViewEngineHelper<MyTemplate> helper = new SparkViewEngineHelper<MyTemplate>(fi.Name)) {
                helper.View.MyText = "Blah";
                MessageBox.Show(helper.ToString());
            }
 
            using (SparkViewEngineHelper<MyTemplate> helper = new SparkViewEngineHelper<MyTemplate>(fi.FullName)) {
                helper.View.MyText = "Blah2";
                MessageBox.Show(helper.ToString());
            }            
        }
    }
 
    public abstract class MyTemplate : AbstractSparkView {
        public string MyText { get; set; }
    }
 
    public class SparkViewEngineHelper<T> : IDisposable
        where T : AbstractSparkView {
 
        private SparkViewEngine engine;
        public T View { get; private set; }
 
        public SparkViewEngineHelper(string templateFile) {
            this.View = this.CreateView(templateFile);
        }
 
        public void ToFile(string file) {
            File.WriteAllText(file, this.ToString());
        }
 
        public override string ToString() {
            StringWriter sw = new StringWriter();
            this.View.RenderView(sw);
            return sw.ToString();
        }
 
        private T CreateView(string templateFile) {
            FileInfo fi = new FileInfo(templateFile);
 
            if (!fi.Exists) {
                throw new FileNotFoundException(templateFile);
            }
 
            this.engine = new SparkViewEngine();
            this.engine.ViewFolder = new FileSystemViewFolder(fi.DirectoryName);
            this.engine.Settings.PageBaseType = typeof(T).FullName;
 
            SparkViewDescriptor descriptor = new SparkViewDescriptor().AddTemplate(fi.Name);
 
            return (T)engine.CreateInstance(descriptor);
        }
 
        void IDisposable.Dispose() {
            if (this.engine != null && this.View != null) {
                this.engine.ReleaseInstance(this.View);
            }
        }
    }
}

Blog at WordPress.com.