# 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:** 19\ **Page:** 2
**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, 7:26pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/21 "2023-05-15T19:26:20Z")
Sorry Rick, as often I don’t understand what you are saying. Are you saying that returning 0 when we saved ‘’ to the SQLite is a bug, or the bug is not returning 0 if the column is integer and we saved ‘[garbage]’ From what I understand from SQLite docs: [Datatypes In SQLite](https://www.sqlite.org/datatype3.html), it is a bug in Xojo to interpret that an empty string in the database should return 0 if we use .StringValue even if we define the column as Integer. Let’s see if Xojo changes the behavior or changes the documentation. ---
**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, 8:54pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/22 "2023-05-15T20:54:10Z")
> [@AlbertoD](#): > > Sorry Rick, as often I don’t understand what you are saying. I’m saying what I said. The subject is kind of extensive. If you did not understand, it will take some time for a kind of large explanation about SQL, Columns and their values (including the native values, the raw thing), etc to detail. Read about columns and types, then reread what I wrote. What I wrote was very extensive, I think that if reread it carefully, you may understand. ---
**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, 9:05pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/23 "2023-05-15T21:05:02Z")
The irony is that I believe in initialising variables. So I duly initialised the integer to an empty string (‘’) as that was the default I wanted in the database. Now, had I NOT done so, the` .StringValue` returned for the empty (null) integer would have been what I wanted - an empty string. ---
**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, 9:18pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/24 "2023-05-15T21:18:33Z")
NULL columns renders as “” when rendered as Column.StringValue And renders as Nil using Column.Value Int64 Columns not containing binary integers but strings should render as ![image](https://forum.xojo.com/uploads/default/original/2X/1/14cd47821a5caeeaced870b4be2cd3516098fd2f.png) ---
**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, 9:21pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/25 "2023-05-15T21:21:49Z")
> [@Rick\_Araujo](#): > > NULL columns renders as “” when rendered as Column.StringValue > > Int64 Columns not containing binary integers but strings should render as 0 This is not how SQLite works and what we are discussing is an interface to SQLite. ---
**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, 9:23pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/26 "2023-05-15T21:23:09Z")
This is exactly how it works when interfacing Xojo lacks tools to handle better native values (instead of fixed declared columns, as Xojo, and maybe all the rest of the DBs works). So I made a suggestion. ---
**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, 10:17pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/27 "2023-05-15T22:17:51Z")
> [@Rick\_Araujo](#): > > Int64 Columns not containing binary integers but strings should render as 0 I didn’t say that, I said: ![image](https://forum.xojo.com/uploads/default/original/2X/6/6cf2763dbc49337e01b29bf458399846742e50bd.png) * * * **If the string has another value, the result changes** ![image](https://forum.xojo.com/uploads/default/original/2X/f/fca4068d7e2d1ea027cee2c7caf5ae474577e858.png) ![image](https://forum.xojo.com/uploads/default/original/2X/3/3bdbfdd728cac9fb060955dc123e5b14cd0b02b1.png) ![image](https://forum.xojo.com/uploads/default/original/2X/3/310d5d66900226f69cc643ed83bb2fab61fd4895.png) ---
**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, 10:25pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/28 "2023-05-15T22:25:54Z")
> [@Rick\_Araujo](#): > > If you did not understand, it will take some time for a kind of large explanation about SQL, Columns and their values (including the native values, the raw thing), etc to detail. Read about columns and types Thank you Rick, I still think you are confusing SQLite with regular SQL, but hey maybe not and I just don’t understand what you are trying to say. After reading: [https://www.sqlite.org/datatype3.html](https://www.sqlite.org/datatype3.html) and [https://www.sqlite.org/stricttables.html](https://www.sqlite.org/stricttables.html) it looks like SQLite is way different that other SQL that only use ‘strict tables’. But don’t worry, it doesn’t matter what I understand or what you are trying to say but what Xojo does to fix the problem. Edit: this is what stands out with SQLite: > Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored. > > SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. From that I understand that Xojo should not return 0 for StringValue even if the column is integer if the table created is not with the STRICT modifier. ---
**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, 10:28pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/29 "2023-05-15T22:28:41Z")
> [@AlbertoD](#): > > what you are trying to say but what Xojo does to fix the problem. Well, I’m not trying, I said. What people will do with it is what you said, what they want. 😆 ---
**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, 10:30pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/30 "2023-05-15T22:30:20Z")
> [@Rick\_Araujo](#): > > what they want. 😆 😆 ---
**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, 10:57pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/31 "2023-05-15T22:57:54Z")
> [@AlbertoD](#): > > From that I understand that Xojo should not return 0 for StringValue even if the column is integer if the table created is not with the STRICT modifier. What you may be missing is the method NativeValue of the Column **Values should be normalized to the column types or crashes could occur.** BUT! If you want a more strict control on what the DB is doing, like to know if that Int64 0 origin was something else different of a string of 8 bytes 0, like a empty string, you can check the NativeValue What I requested: more info Beyond **NativeValue** , having a **NativeValueType** would be great. Because, for example, ```auto Column.Type = 19 // says the column is Int64 Column.Value = 0 Column.NativeValue = "" // In SQLite this is possible Column.NativeValueType = 18 // It's a String Column.Type = 19 // says the column is Int64 Column.Value = 0 Column.NativeValue = "" Column.NativeValueType = 0 // It's a Null type Column.Type = 19 // says the column is Int64 Column.Value = 654 Column.NativeValue = "!°rh‘r„@" // binary double representing value 654.3210 Column.NativeValueType = 7 // type Double ``` ---
**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, 11:20pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/32 "2023-05-15T23:20:07Z")
Oh you are not talking about the original problem but a new feature you want Xojo to have? ---
**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 16, 2023, 2:05am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/33 "2023-05-16T02:05:33Z")
OMG. I will just refrain to answer that, it could sound offensive. 😆 ---
**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 16, 2023, 3:19am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/34 "2023-05-16T03:19:44Z")
Oh. I made a mistake in the NULLed one. Columns have their type or NULL (In Xojo, Nil) Alternative values can be read from NativeValue + NativeValueType ```auto Column.Type = 19 // says the column is Int64 Column.Value = 0 Column.NativeValue = "" // In SQLite this is possible Column.NativeValueType = 18 // It's a String Column.Type = 19 // says the column is Int64 Column.Value = 0 Column.NativeValue = "garbage" // In SQLite this is possible Column.NativeValueType = 18 // It's a String Column.Type = 19 // says the column is Int64 Column.Value = 123 Column.NativeValue = "123.4" // In SQLite this is possible Column.NativeValueType = 18 // It's a String Column.Type = 19 // says the column is Int64 Column.Value = Nil Column.NativeValue = "" Column.NativeValueType = 0 // It's a Null type Column.Type = 19 // says the column is Int64 Column.Value = 654 Column.NativeValue = "!°rh‘r„@" // binary double representing value 654.3210 Column.NativeValueType = 7 // type Double ``` ---
**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 17, 2023, 10:48am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/35 "2023-05-17T10:48:29Z")
> [@TimStreater](#): > > This is not how SQLite works and what we are discussing is an interface to SQLite. …with the overlay of Xojo. You guys need to remember that Xojo has all this “helper” code to make the underlying SDKs behave the way that Xojo _thinks_ they should, so that they conform to the Xojo ecosystem. RowSet (and recordset before it) uses variants under the hood. IIRC, defining a column in SQLIte as an integer means that the value gets automagically cast to an integer when it’s pulled out and that calling stringvalue converts it into a string representation of that number. ---
**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 17, 2023, 10:50am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/36 "2023-05-17T10:50:02Z")
I gotta ask though… why the heck did you define a column as integer if you wanted it to return a string? If you wanted a non-number value, use NULL. That’s how every other relational database on the planet works and you’re doing yourself a disservice doing it this way if you ever think that your product could need to scale to something bigger. ---
**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 17, 2023, 11:53am UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/37 "2023-05-17T11:53:20Z")
I’m just re-purposing an unused column instead of adding a new TEXT column. Easier in this instance than bumpng the schema version number and adding code to add a new column to the user’s schema. > [@Greg\_O](#): > > RowSet (and recordset before it) uses variants under the hood. IIRC, defining a column in SQLIte as an integer means that the value gets automagically cast to an integer when it’s pulled out and that calling stringvalue converts it into a string representation of that number. But if in Xojo I put ‘abc’ into an integer SQLite column, I get ‘abc’ back. Same (apparently) for a string consisting of a single space. It’s only an empty string that gets converted to ‘0’. Based on what you say here, I would have expected _any_ string to cause ‘0’ to be returned. ---
**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 17, 2023, 1:49pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/38 "2023-05-17T13:49:30Z")
> [@Greg\_O](#): > > RowSet (and recordset before it) uses variants under the hood. IIRC, defining a column in SQLIte as an integer means that the value gets automagically cast to an integer when it’s pulled out and that calling stringvalue converts it into a string representation of that number. Something like this? ```auto Var test As Variant = "" Var test2 As String = Str(CType(test, Integer)) // 0 ``` Yes, that gives us 0. But why this gives us ‘abc’ from the database and not 0? ```auto Var test As Variant = "abc" Var test2 As String = Str(CType(test, Integer)) // 0 ``` Maybe the underlying code is something like this? ```auto Var test As Variant = "abc" Var test2 As String If test.IsNumeric Or test.StringValue.Length = 0 Then test2 = Str(CType(test, Integer)) Else test2 = test.StringValue End If ``` From what I read here: > SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. is that SQLite doesn’t care if the column is Integer or Text, if the value is Text, even if the column is Integer, the value itself is Text and needs to be treated that way. Variants are great for this (I think). So ```auto Var test As Variant = 1 //assign a number to variant Var test2 As String test = "" //assign empty string test2 = test.StringValue //we get empty string test = 123 test = "abc" test2 = test.StringValue //we get 'abc' ``` * * * > [@Greg\_O](#): > > That’s how every other relational database on the planet works and you’re doing yourself a disservice doing it this way if you ever think that your product could need to scale to something bigger. I agree 100% with this. It is not a good practice to define a column Integer and assign Text even if SQLite works this way, just for the reason that if you want the product to scale we will get problems from this design. But a bad practice (define Integer column and assign a text value), if it is allowed by SQLite and Xojo saves the empty string to the database, should pull an empty string from it. Since SQLite 3.37.0 we can have Strict tables, so in the event of planning for the future (instead of re-purposing an unused column) we can ‘force’ SQLite to work as any other relational database on the planet. Trying to add a string (even an empty one) to an Integer column will get an exception: > cannot store TEXT value in INTEGER column Team.City Strict is a good option to have to design new SQLite project that can potentially grow to other database engines. * * * In short: - it is not recommended to mix text/integer in an integer column even if SQLite allows it - in SQLite the value itself should define the datatype, not the container (by default, this change if we use STRICT) - Xojo, as it should, can save any TEXT in an INTEGER SQLite column, and gives back the text correctly but in the case when the String is empty, it returns 0 instead (the reported bug) ---
**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 17, 2023, 1:56pm UTC](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758/39 "2023-05-17T13:56:59Z")
> [@AlbertoD](#): > > From what I read here: > > > SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. > > is that SQLite doesn’t care if the column is Integer or Text, if the value is Text, even if the column is Integer, the value itself is Text and needs to be treated that way. Variants are great for this (I think). So Yes, meaning that for SQLite, rather than asking about the column’s defined type, the interface should be asking what the affinity for that column in that row is. Remember, I can store an integer in the column in one row, a string in the next row, and a float in the next row. The query has to be done on a row-by-row basis. [Previous page](https://forum.xojo.com/t/sqlite-problem-i-dont-get-the-value-inserted-returned/75758.md?page=1)