System.Data.SQLite

Hello,

I've been using the wrapper for quite a long time and never faced problems but with the latest release (10 dec. 2007) I started noticing a strange behaviour on my apps. At first I though I had a problem with my own code but after reverting the SQLite DLL back to the 1.0.46.0 version, my problem disapperared. I'm working the Compact Framework distributed binaries (running on a Windows Mobile 5.0 device with the latest Compact Framework 2 release).

This is about the way the different wrapper classes (I suppose they are actually all the same) feed .NET objects (like DataTable) and set column name mappings. It seems (at least to me) that when version 1.0.46.0 executed a SELECT query statement it mapped SQLite columns with their real names and not the prefixed aliased names which were used in the query. Since the 1.0.47 series, the mapped name is the full prefixed one and all my calls to GetOrdinal("SomeColumn") return -1 because of this.

Since I'm not a native english speaker and I doubt everyone understood what I meant, here is an example :

Command cmd = connection.CreateCommand();
cmd.CommandText = "SELECT myAlias.Column1, myAlias.Column2 FROM MyTable myAlias";
SQLiteDataReader reader = cmd.ExecuteReader();
// Suppose there is data
if (reader.Read()) {
    int i = reader.GetOrdinal("Column1");
    System.Diagnostics.Debug.WriteLine("i => " + i);
}
reader.Close();

Version 1.0.46.0 will output "i => 0" while 1.0.47.0 will output "i => -1". Actually, I even think (but didn't try such an example, I was working with a DataTable fed by a DataAdapter) that reader.GetOrdinal("myAlias.Column1") will return the correct column index but this is clearly not the way it used to work (and for a lots of previous versions).

So, am I wrong somewhere or is there a glitch about column mapping when selecting them with table aliases with the latest release?

I still want to think there may be an error on my side because I can't think this has been untested but honestly, I spent the past hours looking for something in my code until I noticed this behaviour within the wrapper. Like I said I'll stick to 1.0.46.0 with my apps since there is no critical aspect of 1.0.47.0 I'll miss but still, this is the first time I can't upgrade to the new version eyes closed :)

Oh and by the way, forgot to tell it, but you are doing a great work with this SQLite .NET wrapper :)

This is probably a change in the SQLite engine itself.  I'll look into it.

You know, I tried executing some queries using a fully-qualified name, such as SELECT A.ProductId, A.Name FROM Products AS A  and I couldn't get the same results you got.  My column names were "Name" and "ProductID".

Could you have been executing a query against a view?  I could use some more details.

Robert

Hi,

I'm not using views in the application where I noticed this behavior. The first time I noticed it however was with something different like the following : 

DataTable dt = new DataTable("MyTable");
SQLiteCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT myAlias.Column1, myAlias.Column2 FROM MyTable myAlias";
SQLiteDataReader cmd.ExecuteReader();
dt.BeginLoad();
dt.LoadData(reader);
dt.EndLoad();
reader.Close();
cmd.Dispose();

for(int i = 0; i < dt.Columns.Count; i++) {
    System.Debug.Diagnostics.WriteLine("Column: " + dt.Columns[i].Name);
}

At first I thought it was something I never noticed before but I then found the same oddity with the previous example I gave (using a SQLiteDataReader) and I never had any problems with my TableStyles column mapping. What kind of details would you expect me to give you? My app is a bit more complicated than a simple select query or data table being filled in though.

Didn't took time to test it on the Full Framework but I have another application running with SQLite on a Windows XP box, I'll give it a look on monday.

I tested using a dataadapter and a dataset, and couldn't get it to do that.  Whats the schema of that table you're referencing?  Trying to get as close to your scenario as possible

lisurc

Hi and sorry for not answering earlier,

Just tried one more time with another clean build of my app and a fresh new download of the 1.0.47.2 version but I still can't figure what I would miss that would give me different results from your tests. Here is the query used to issue the SELECT statement and the corresponding SQLite CREATE TABLE queries stored in the sqlite master table.

CREATE TABLE Table_1 (Id INTEGER PRIMARY KEY, Field_57 TEXT, Field_3 TEXT, Field_69 TEXT, Field_24 TEXT, Field_25 INTEGER, Field_85 TEXT, Field_29 INTEGER, Field_30 TEXT, Field_31 TEXT, Field_116 TEXT, Field_84 TEXT, Field_117 TEXT, UNIQUE (Field_57));

CREATE TABLE Table_2 (Id INTEGER PRIMARY KEY, Field_4 TEXT, Field_120 TEXT, Field_10 TEXT, Field_11 TEXT, Field_12 TEXT, Field_21 TEXT, Field_22 TEXT, Field_33 TEXT, Field_23 INTEGER, Field_34 TEXT, UNIQUE (Field_4));

CREATE TABLE Sync_1 (Id INTEGER, Id_Global INTEGER, Status INTEGER, PRIMARY KEY(Id, Id_Global));

SELECT DISTINCT t1.Id, t1.Field_69, t1.Field_84, t1.Field_3, t1.Field_25, t2.Field_120, t1.Field_117 FROM Sync_1 s1, Table_1 t1, Table_2 t2 LEFT JOIN Table_5 t5 ON t2.Field_4 = t5.Field_7 AND t5.Field_37 = 1 WHERE t1.Field_3 = t2.Field_4 AND s1.Id = t1.Id AND (t1.Field_69 = 'lact_confirmed' OR t1.Field_69 = 'lact_planned') ORDER BY t1.Field_25 ASC;

(Don't ask about the column and table names, they are auto-generated).

Just to ensure we're talking about the same things, I'm working with a Windows Mobile 5.0 device using the Compact Framework (GAC 2.0.7045.0, 1.0.4292.2) with the 1.0.47.2 System.Data.SQLite.dll version and the following snippet of code reproduces the problem : 

Ok, this is loads more helpful ... I now know you're doing JOIN's and ORDER BY's, and have  a WHERE clause -- all things that could potentially impact how SQLite parses, prepares and returns a query.  I'll experiment with this SQL.

Robert

I do realize this is more helpful, yes, I should have posted a whole sqlite example with the C# code.

However, and if this can help and guide your tests, a regular SELECT / FROM / WHERE is enough to trigger the error. I omitted the WHERE part in my previous examples but the following query (based on the same table schema) causes the SQLiteDataReader to have its ordinal column names to be aliased:

SELECT DISTINCT t2.Id, t2.Field_120 FROM Table_2 t2 WHERE t2.Id = 1;

Actually, the only difference I've seen so far with what I had in mind (except those you mention now) is the lack of presence of the 'AS' keyword you used to add an alias to your table (in your Product thing query) and that my aliases are longer than 1 char. But I admit I didn't test it yet on my side.

Found the culprit -- it's the DISTINCT clause triggering the naming issue.  I'll post on the list.

Robert

lisurc

I just supposed that you could use not english symbols in column names - cyrillic, for example. In this case such names must be case-sensitive. Anyway don't pay attention to my careless reading your first post and sorry for confusing you! ;)

Regards!

I have faced the problem for x64 binary recently. When will it be fixed? Any tentative date?

What should I do for x64? Any suggestion is most welcome. There are many places where  "DISTINCT t1.Col2" like things were used. For now I have built  1.0.46 for x64.

I am lucky that this issue has already been posted.

Thanks a lot.

Unfortunately this is not something I can fix in the provider -- it has to be fixed in the core engine.  I submitted a bug report but I doubt by now they intend to fix it.