# SQLite problem - I don't get the value inserted returned **URL:** \ **Category:** General\ **Created:** [May 15, 2023, 11:54am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758 "2023-05-15T11:54:08Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [May 15, 2023, 11:54am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/1 "2023-05-15T11:54:08Z")
macOS Catalina, IDE 2023r1.1. I just created Issue 72754 for this. I define a column as integer in an SQLiteDatabase with no default specified. I then insert an empty string into it. On querying that column, asking for .StringValue, instead of being returned an empty string, I am returned the string “0”. This violates the SQLite guarantee that what you put into a column, you get back. ---
**Author:** ![Greg\_O](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o/32/22785_2.png) [@Greg\_O](https://forum.xojo.com/u/Greg_O)\ **Post date:** [May 15, 2023, 12:05pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/2 "2023-05-15T12:05:04Z")
Well except that Xojo does a bunch of magic behind the scenes to that you actually have column types. So what you’re describing to me is not surprising at all. ---
**Author:** ![Rick\_Araujo](https://forum.xojo.com/user_avatar/forum.xojo.com/rick_araujo/32/251_2.png) [@Rick\_Araujo](https://forum.xojo.com/u/Rick_Araujo)\ **Post date:** [May 15, 2023, 12:16pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/3 "2023-05-15T12:16:19Z")
> [@TimStreater](#): > > I define a column as integer A normal SQL column should hold 2 kinds of values: It’s defined type, or NULL SQLite is a bit flexible to this rule, but possible upper layers aren’t (like Xojo, maybe ODBC, whatever). You should stick to the general rule. Maybe you wish to go the other way around and define the column as text, and insert anything there? Including textual numbers that can be converted back to Ints (I guess Xojo handles it in column.IntegerValue) ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [May 15, 2023, 1:00pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/4 "2023-05-15T13:00:08Z")
From a RowSet, one may ask for a variety of values back, such as StringValue, IntegerValue, BooleanValue, and more. In the case of BooleanValue, for example, Xojo must already be asking for more information since SQLite doesn’t have a Boolean type. If instead of an empty string, I enter ‘abc’, then ‘abc’ is what I get back. If Xojo always returned 0 for StringValue when the column is integer, that would be one thing. But this is not happening. If I don’t initialise that column at all, Xojo returns me a column of type NULL, as expected. ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [May 15, 2023, 1:08pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/5 "2023-05-15T13:08:31Z")
> [@Rick\_Araujo](#): > > A normal SQL column should hold 2 kinds of values: It’s defined type, or NULL That is what I would normally do. In this case I am re-purposing an unused column rather than having to have the next release of my app update the user’s table (which would be perfectly do-able). Mostly Xojo returns the right string in this situation, just that in one case it does not. ---
**Author:** ![Greg\_O](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o/32/22785_2.png) [@Greg\_O](https://forum.xojo.com/u/Greg_O)\ **Post date:** [May 15, 2023, 1:40pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/6 "2023-05-15T13:40:55Z")
I’m not at my computer right now but I’m curious what happens if you call IsNumeric on an empty string. If it returns True, that would probably be your answer. ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [May 15, 2023, 1:51pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/7 "2023-05-15T13:51:39Z")
```auto Var resfl As Boolean resfl = IsNumeric ("") System.DebugLog ("Result is: " + if (resfl=True, "True", "False")) break ``` . Resulting output: 14:49:18 : zzz Launched Result is: False 14:49:40 : zzz Ended ---
**Author:** ![Greg\_O](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o/32/22785_2.png) [@Greg\_O](https://forum.xojo.com/u/Greg_O)\ **Post date:** [May 15, 2023, 2:01pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/8 "2023-05-15T14:01:39Z")
Well that shoots that theory… ---
**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\ **Post date:** [May 15, 2023, 2:34pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/9 "2023-05-15T14:34:44Z")
The database contents are ok, it looks like the bug is interpreting that “” is 0 because is an ‘Integer’ column. ---
**Author:** ![Greg\_O](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o/32/22785_2.png) [@Greg\_O](https://forum.xojo.com/u/Greg_O)\ **Post date:** [May 15, 2023, 2:38pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/10 "2023-05-15T14:38:57Z")
Likely because a Variant is used. ---
**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\ **Post date:** [May 15, 2023, 2:57pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/11 "2023-05-15T14:57:16Z")
I don’t understand how Variant could be the problem, I did the following test trying to get 0 for StringValue: ```auto Var test As Variant = 1 Var test2 As Integer = test.Type test = "" Var test3 As String = test.StringValue var test4 as integer = test.Type ``` but test3 is correct as ‘’ and test4 is 8 (correct type). Could Xojo be pulling information from the Column, see that is integer then assign 0 to the Variant (because is integer) so the Variant.StringValue can only be 0 in this case? Anyway, I hope they fix this problem. Tested with Xojo2018r3 and it shows the same problem. Edit: I don’t use Variants much, so every time I can learn a little bit more I do some tests. Sorry if I’m missing something obvious. ---
**Author:** ![Rick\_Araujo](https://forum.xojo.com/user_avatar/forum.xojo.com/rick_araujo/32/251_2.png) [@Rick\_Araujo](https://forum.xojo.com/u/Rick_Araujo)\ **Post date:** [May 15, 2023, 3:46pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/12 "2023-05-15T15:46:34Z")
As I said, I think that Xojo is taking the column type in consideration, and at a standard SQL point of view, It is not exactly wrong, some would say the opposite. ![image](https://forum.xojo.com/uploads/default/original/2X/8/847d88d9b19440e290e5c6078908bc97a63c40a6.png) And the NativeValue of “” is coming as an Int64(0) ![image](https://forum.xojo.com/uploads/default/original/2X/4/47ebb42167578ebd1c71024f892aa59aa5511e76.png) ---
**Author:** ![Ian\_Kennedy](https://forum.xojo.com/user_avatar/forum.xojo.com/ian_kennedy/32/6860_2.png) [@Ian\_Kennedy](https://forum.xojo.com/u/Ian_Kennedy)\ **Post date:** [May 15, 2023, 4:19pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/13 "2023-05-15T16:19:43Z")
I suppose the other thing it could do is spot the “” and store Null instead, assuming the column allowed Null. I think I would prefer that option rather than making it look like there’s an actual value (0). If you were to average the column, for example, you would change the result by having a zero in there. ---
**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\ **Post date:** [May 15, 2023, 4:40pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/14 "2023-05-15T16:40:53Z")
It looks like you can store a blank space and you will get a blank space back, depending on your needs may (or not) be a good option. ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [May 15, 2023, 4:54pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/15 "2023-05-15T16:54:28Z")
Yes. Any string of non-zero length works as it should. ---
**Author:** ![Rick\_Araujo](https://forum.xojo.com/user_avatar/forum.xojo.com/rick_araujo/32/251_2.png) [@Rick\_Araujo](https://forum.xojo.com/u/Rick_Araujo)\ **Post date:** [May 15, 2023, 5:27pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/16 "2023-05-15T17:27:02Z")
> [@TimStreater](#): > > Any string of non-zero length … Wow. THAT is a BUG. Inconsistency. Column type int64 should return a **Column.StringValue** “0” for “garbage”, and **Column.NativeValue** could be “garbage” (as it is) Also, a “” **(Empty string) value should be stored and retrieved as is** (lenght zero string) in **Column.NativeValue** , today it is becoming an Int64(0), (8 bytes 0), but it should end as 0 in Integer and floating point columns returning 0 for Column.IntegerValue, Column.DoubleValue, and “0” for Column.StringValue (as the column is numeric (should not hold strings), not text. But consulting **Column.NativeValueType** we could see type 18, String, instead of 19, Int64 of the column, for example. Because SQLite, Xojo should have a way to differentiate a **Column.Type** (what was designed) and a new **Column.NativeValueType** (what was returned), all other DBs both could match. ![image](https://forum.xojo.com/uploads/default/original/2X/e/e87fc34a69b8ea9a9e81b592ed27c39fe67a8c69.png) * * * ```auto Var db As New SQLiteDatabase If not db.Connect Then Raise New DatabaseException("Not connected?", 666) db.ExecuteSQL("CREATE TABLE test (int_val INTEGER); INSERT INTO test (int_val) VALUES (1),(''),('garbage');") Var rs As RowSet = db.SelectSQL("SELECT * FROM test;") Var c As DatabaseColumn Var s As String = "" Var row As Integer = 0 Do Until rs.AfterLastRow row = row+1 c = rs.ColumnAt(0) s = s + "Row "+row.ToText + " value=["+c.StringValue+"] type = "+c.Type.ToString+EndOfLine rs.MoveToNextRow Loop MessageBox s ``` ---
**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\ **Post date:** [May 15, 2023, 5:44pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/17 "2023-05-15T17:44:51Z")
Please use pure SQLite and test what is allowed and what is not. Xojo should do the same and not try to make SQLite behave like standard SQL. ---
**Author:** ![Rick\_Araujo](https://forum.xojo.com/user_avatar/forum.xojo.com/rick_araujo/32/251_2.png) [@Rick\_Araujo](https://forum.xojo.com/u/Rick_Araujo)\ **Post date:** [May 15, 2023, 5:45pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/18 "2023-05-15T17:45:32Z")
Read above, it’s pure SQLite. The problem is the Xojo interface not being able to interpret SQLite and its idiosyncrasies. I also offered a solution. A new **Column.NativeValueType** So we could interpret inconsistent values (things users inserted into columns not matching the design) ---
**Author:** ![Greg\_O](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o/32/22785_2.png) [@Greg\_O](https://forum.xojo.com/u/Greg_O)\ **Post date:** [May 15, 2023, 5:57pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/19 "2023-05-15T17:57:17Z")
Personally, if the behavior has been this way since 2018, I’d prefer they document the behavior. ---
**Author:** ![Rick\_Araujo](https://forum.xojo.com/user_avatar/forum.xojo.com/rick_araujo/32/251_2.png) [@Rick\_Araujo](https://forum.xojo.com/u/Rick_Araujo)\ **Post date:** [May 15, 2023, 6:18pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/20 "2023-05-15T18:18:51Z")
As soon as we fix serious things, less legacy errors are accumulated. The proposition has very little impact to the current behavior, just adds more tools for users interpreting the values. Breaking changes should be documented and have points of attention in each release where they are introduced. [Next page](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758.md?page=2)