System.Data.SQLite

I am using a Table with a String entry.

 When Inserting, I use a parameterised INSERT of the type DB.String.  The value I write to this parameter is also a String, so pretty straightforward there.

 When I try to read it (Again Parameterised as String) I get an Invalid Cast error!  The problem seems to lie with the fact that my String is "00005".  I can extract it as Int32 no problem.

 In an effort to get around this, I used the Datareader.GetFieldType function to try adapt.

When I tried this it worked, but I also had an error in my code.  I called the GetFieldType function, but acted in the wrong manner.  See below. 

If the type is NOT String, I read it as string. But it worked.... It seems that there's some Type confusion going on in the Databank. 

while (RD.Read())
{
if (!RD.GetFieldType(0).Equals(DbType.String))
   Result = RD.GetInt32(0).ToString();
else
   Result = RD.GetString(0);
}
 

Does any of this sound even remotely possible? 

 Shane.

My Table creation string looks like this

                                              "CREATE TABLE IF NOT EXISTS " + T_Palette.Table
                                            + " (pPaletteGUID Guid PRIMARY KEY,"
                                            + " PaletteID String,"
                                            + " fLieferscheinGUID Guid,"
                                            + " PaletteIDNichtinLieferscheinJN String,"
                                            + " Updated YESNO DEFAULT 0,"
                                            + " Deleted YESNO DEFAULT 0,"
                                            + " New YESNO DEFAULT 1,"
                                            + " Synchronised YESNO DEFAULT 0,"
                                            + " FOREIGN KEY (fLieferscheinGUID) REFERENCES " + T_LieferSOLL.Table + "(pLieferscheinGUID))";

I INSERT like this

                using (SQLiteCommand X = Conn.CreateCommand())
                {
                    X.CommandText = T_PaletteSOLL.Insert.CommandText;
                    X.Parameters.Add(new SQLiteParameter(DbType.Guid));
                    X.Parameters.Add(new SQLiteParameter(DbType.String));
                    X.Parameters.Add(new SQLiteParameter(DbType.Guid));
                    X.Parameters.Add(new SQLiteParameter(DbType.String));
                    PALGUID = Guid.NewGuid();
                    X.Parameters[0].Value = PALGUID;
                    X.Parameters[1].Value = PaletteNr_NEW().ToString();
                    X.Parameters[2].Value = INVGUID;
                    X.Parameters[3].Value = "N";
                    X.ExecuteNonQuery();
                    LogFile.Log(this.name, "Command executed");
                }

 INSERTCommand String for the above is

" INSERT OR REPLACE INTO " + T_Palette.Table
                                                    + " (pPaletteGUID, PaletteID, fLieferscheinGUID, PaletteIDNichtinLieferscheinJN)"
                                                    + " VALUES (?, ?, ?, ?)";

The Reading code I have already more or less supplied.

Shane. 

From the SQLITE FAQ:
(3) SQLite lets me insert a string into a database column of type integer!

This is a feature, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is sometimes call type affinity.

-----------------------------

I think, this behaviour of SQLITE is annoying and therefore a bug

I understand this.

 What I do NOT understand is why I cannot read a value I have placed as String as a String, but rather as an Integer.  Nowhere in my code have I set this to an Integer.  I also have not Entered the value as an integer, but SQLite seems to have "Interpreted" it as an integer.

 But why interpret when I tell it what format it has..... 

Either way, I still think it should be possible to read out an Int32 value as a String.  Why on earth should this throw an exception?

 Shane.

 I just started getting it AGAIN in another table.  I've changed absolutely NOTHING in my code.

I really hope I don't need a new Install of Visual Studio and all.  This version's only just BEEN installed.......

Anyone have any idea how I can get around this really rather annoying problem?

 Shane.

In one way, I've had this problem for a while now.  I've always wondered where the leading zeroes in my strings go when I store them in my database.

Write "0005", read "5". 

 It's clear to me now that the database has been storing my strings, inserted as string, defined as string, read as string, as Integers.

Sorry, but for the life of me, I CANNOT understand this. Even if this is a clever way to save space, a conversion back to string must be guaranteed....... 

Can someone PLEASE have a look at this possibility? Could an older version of SQLite help out?

Shane. 

I'll check into this.  It shouldn't throw an exception.

Thanks

If you need more code, let me know and I'll whip something together.

 Shane.

 Any more info on this?  It's starting to bug me. (no pun intended).

 It seems that any time I insert a String with purely numeric characters, SQLite decides to store it as an integer.  Thiw would be (almots) fine if I could read it out again as a String, but I can't, I can only access it as an Integer.  This makes working with Strings which may or may not have purely numerical content a royal pain in the ass.  I need to check the type of each read to see if it's NOT a string, read as an integer and re-format back (for example re-adding leading zeros).

 I do not see this as a feature.  I see this as SQLite doing something in the background which is strictly against what I am telling it to do.  Even if I WAS able to read the integer out as a String, I'm still missing any leading zeroes which MAY have been present before.

 Shane.

Looks like the core SQLite engine is doing the dirty work ... check this out:

C:\Src>sqlite3 :memory:
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table foo(id integer primary key, myvalue text);
sqlite> insert into foo(myvalue) values('000005');
sqlite> select * from foo;
1|000005
sqlite> create table foo2(id integer primary key, myvalue string);
sqlite> insert into foo2(myvalue) values('000005');
sqlite> select * from foo2;
1|5
sqlite> create table foo3(id integer primary key, myvalue nvarchar(50));
sqlite> insert into foo3(myvalue) values('000005');
sqlite> select * from foo3;
1|000005

Looks like when I declare the field as type "text" or "nvarchar" the text is preserved ... for type "string" however, it chooses an "optimized" approach. So change your table definition from 'string' to varchar or nvarchar and you should be good to go.

 That's absolutely fabulous news!

I DO actually remember changing type (I originally started with nvchar).  No idea why I changed, I certainly didn't expect and changes like this.

So I can very easily change my table definitions and be done with this annoying behaviour? Best news I've heard in a week (Been a tough week:(  )

Thanks for the heads-up.

Shane. 

PS Should I file a bug report on the SQLite homepage, or do you have some good contact with the developers?