GetInsertCommand generates exception: "Dynamic SQL generation is not supported with no base table."

[wat...@gmail.com]

Hi there. I'm trying out this port of SQLite and I ran into a problem. I'm using it in an application that currently uses System.Data.SQLite, so all of the objects should be the exact same (the only thing I had to change was the format of the connection string, which is different for this port than for the original).

So far, all of the objects and commands I've tried to use seem to work, but I'm having a problem using the GetInsertCommand method of the SqliteCommandBuilder class. When I do, I get the following error:

"Dynamic SQL generation is not supported with no base table."

Here's some sample code:

            Dim da As SQLiteDataAdapter, cmd As SQLiteCommandBuilder
            da = New SQLiteDataAdapter("SELECT * FROM " & sTable, con)
            cmd = New SqliteCommandBuilder
            cmd.DataAdapter = da
            da.InsertCommand = cmd.GetInsertCommand   <---EXCEPTION HAPPENS HERE
            da.Update(dt)

There are two things to note:

First, this same code works just fine with every other embedded db (with a .NET provider) that I've ever used (including System.Data.SQLite). And I've tried a LOT of them. :o)

Second, every other .NET provider I've used has a second constuctor for the CommandBuilder which allows you to specify the DataAdapter when instantiating the object. For example: "cmd = New SqliteCommandBuilder(da)". This doesn't work on this port, so I have to set the DataAdapter for the cmd separate from the constructor. I'm not sure if that is what's causing the problem or not.

Does anyone know what I would need to do differently to get this to work?

WATYF

[wat...@gmail.com]

Well, it appears that this project is abandoned, so this is just for anyone who runs across this...

There are several problems with this as it stands... these are the ones I discovered before I gave up (I hit a dead end with the DataAdapter.Fill method).

1) The first problem is that all of the GetInsertCommand/GetDeleteCommand/GetUpdateCommand methods are broken. None of them properly assign a BaseTableName to the tableschema, and therefore you always get the error I mentioned above whenever using any of those methods. I solved this by parsing the table name out of the SQL statement from the sourceCommand, passing that table name to the SQLiteDataReader and then using the following line to assign the table name in GetSchemaTable:

schemaRow["BaseTableName"] = baseTable;

(baseTable is the variable I use to pass the table name)

You must replace the existing (similar) line with that one.

2) But that only gets you through the GetInsertComand method without errors... then you have another problem: The Parameter.Direction isn't being set to "Input". As a result, every record you Insert is empty. So, I had to go into all of the constructors for SqliteParameter and set the Direction to ParameterDirection.Input

3) But that only gets the data into the table in SOME format. It's still not very useful, because the GetSchemaTable method defaults all of the DataTypes (and ProviderTypes) of the columns to "String" (or "AnsiString"). So, I fixed this by adding these two lines to the GetSchemaTable method:

dataTableSchema.Columns.Add ("DataType", typeof(String));

schemaRow["DataType"] = this.GetDataTypeName(i);

You must replace the existing (similar) lines with those.

This stores the string value corresponding to each column's datatype in the schematable.

Then I went to both of the CreateParameter methods and added this line:

DbType dbType = GetDbTypeFromString((string)schemaRow["DataType"]);

You have to replace the existing (similar) line.

Then I created a function (GetDbTypeFromString) that checked the string in the DataType of the schema and returned the corresponding DbType (this was done with a big switch/case statement).

So at that point I had everything working right for the InsertCommand...

But then I ran into a new problem: Selecting data from the database returns EVERYTHING in String format. Doesn't matter what the type is for the column in the database, it's gonna return a string.

So, if you use...

(this is VB, btw)
                    da = New SqliteDataAdapter(sSQL, con)
                    dt = New DataTable
                    da.Fill(dt)

...then all of the columns in the DataTable (dt) that gets returned from the database are formatted as strings. So integers are converted to strings. Bit (Boolean) values are converted to "True" and "False" (the actual strings)... .and so on.

Now, if I use that exact same code to hit the same database using the System.Data.SQLite provider (which is very interchangeable with this project, btw, except for the connection string), then I get a datatable with all of the right DataTypes for each column.

And since SqliteDataAdapter.Fill is simply inherited from DbDataAdapter.Fill, I don't know what this project is doing differently from System.Data.SQLite. As a result, I'm at an impasse.

I really wanted to use this, because it's 100% C# and I've been looking for a good embedded database for almost 10 years now, and a pure C# port of SQLite really would have met almost all of my criteria, but as it stands, I can't really get useful out of the database.

If anyone runs across this and knows why the data is always being returned as Strings, I'd be interested to hear of a workaround.

WATYF

[wat...@gmail.com]

Oh, and btw, for anyone having problems with the connection string, the main difference (for me) was that instead of:

"Data Source=C:\MyPath\MyDB.db"

...you have to use:

"uri=file:C:\MyPath\MyDB.db"


WATYF