Did you know that at a string column/field... if add something (having zeros in front) like 0000000000... will saved value only 0 (drop zeros) --> changing field/column to varchar(numofchars) will save all zeros !
Did you know that at a string column/field... if add something (having zeros in front) like 0000000000... will saved value only 0 (drop zeros) --> changing field/column to varchar(numofchars) will save all zeros !
You must be implementing something differently from me. I get all the zeros!
B4X:
Dim Query As String = $"UPDATE SIV SET IsSync = ? WHERE SIVDate = ?"$
DB.ExecNonQuery2(Query, Array As Object("000000000000000000000000000000", "06-01-2022"))
Query = $"SELECT IsSync FROM SIV WHERE SIVDate = ?"$
Dim rs As ResultSet = DB.ExecQuery2(Query, Array As String("06-01-2022"))
Do While rs.NextRow
Log("Updated Value:" & rs.GetString("IsSync"))
Loop
rs.Close
I am doing a test from a sqlite database by executing query in DB Browser and also B4X. Value never converted to single 0.
SQL:
INSERT INTO "main"."SIV"("SIVCode","CompanyNo","StoreCode","SIVDate","Description","IsSync")
VALUES ('1','1','1','19-01-2022',NULL,'0000000000000000000000000');
I am uploading my database with String Fields to see the "effect"... if you change it to varchar (the database fields) with a specific characters will accept leading zeros-0 like TEXT...
B4X:
mydir = "D:\B4J\timologisi\timologisi\B4J"
datadb.InitializeSQLite(mydir,"test.db",False)
datadb.ExecNonQuery2("INSERT INTO exampletable VALUES(?)",Array("00000000")) ' with field-column like String will not accept leading zeros... but only one !
datadb.Close
I am uploading my database with String Fields to see the "effect"... if you change it to varchar (the database fields) with a specific characters will accept leading zeros-0 like TEXT...
B4X:
mydir = "D:\B4J\timologisi\timologisi\B4J"
datadb.InitializeSQLite(mydir,"test.db",False)
datadb.ExecNonQuery2("INSERT INTO exampletable VALUES(?)",Array("00000000")) ' with field-column like String will not accept leading zeros... but only one !
datadb.Close
I have a SQLite database with my Android application. I have noticed I accidentally defined a table using a "String" datatype instead of "Text" datatype. Here is the code with string: private fi...
...Well the reason of "tip" is just for learning from others mistakes... i was using string by default (because of mysql/mssql/access)... and need to turn an app(access) to sqlite... just used string and leading zeros disappeared... that was a shock - but after searching found VARCHAR / TEXT (suggested by you) will be the right choice... So caution for string columns ! ---generally speaking is very strange why sqlite3 making that...
By the way - limiting the fields with VARCHAR(chars) your database works faster
I never learn to use String as database table data type. It may not a common SQL standard. In most databases, we use VARCHAR or nVARCHAR to deal with text based data where you don't need to trim the extra spaces compare to using CHAR. TEXT in SQLite is also not standard. In some database engine, there are other data types such as LongText and Memo. String is commonly use as a Class, array of bytes or char or function in programming languages. However, you may also argue that SQL is also a type of programming languages.
Sometimes it's fun to use the wrong tool for the job. ? This is implemented in pure SQL. It doesn't do anything like CREATE FUNCTION or other nonportables. https://github.com/chunky/sqlraytracer
I saw this thread... Taking SquareRoot in SQL https://www.b4x.com/android/forum/threads/taking-squareroot-in-sql.110722/ ...and thought of something else I saw a while back on the internets. A 3d engine, written in SQL. This is something I would categorize as both completely insane and...