Magma Expert Licensed User Longtime User Jan 19, 2022 #1 Well i think that a thread like that... will be useful with small tips or just knowledge tips... that will save time for all !
Well i think that a thread like that... will be useful with small tips or just knowledge tips... that will save time for all !
Magma Expert Licensed User Longtime User Jan 19, 2022 #2 adding a tip for SQLite... 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 !
adding a tip for SQLite... 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 !
aeric Expert Licensed User Longtime User Jan 19, 2022 #3 Magma said: adding a tip for SQLite... 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 ! Click to expand... You mean passing the value using B4X as parameter?
Magma said: adding a tip for SQLite... 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 ! Click to expand... You mean passing the value using B4X as parameter?
aeric Expert Licensed User Longtime User Jan 19, 2022 #5 Magma said: @aeric no generally Click to expand... 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'); Attachments data.zip data.zip 611 bytes · Views: 243
Magma said: @aeric no generally Click to expand... 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');
Magma Expert Licensed User Longtime User Jan 20, 2022 #6 @aeric.... At your database you are using TEXT (fields-columns) not string ... :-(
Magma Expert Licensed User Longtime User Jan 20, 2022 #7 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... Code: 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 Here one old post... found with the same effects... https://stackoverflow.com/questions...strings-that-start-with-zero-e-g-001/14382201 Attachments test.zip test.zip 404 bytes · Views: 240
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... Code: 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 Here one old post... found with the same effects... https://stackoverflow.com/questions...strings-that-start-with-zero-e-g-001/14382201
aeric Expert Licensed User Longtime User Jan 20, 2022 #8 Magma said: 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... Code: 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 Here one old post... found with the same effects... https://stackoverflow.com/questions...strings-that-start-with-zero-e-g-001/14382201 Click to expand... I see. I never use String and not aware it is available as SQLite field type. I always use TEXT.
Magma said: 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... Code: 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 Here one old post... found with the same effects... https://stackoverflow.com/questions...strings-that-start-with-zero-e-g-001/14382201 Click to expand... I see. I never use String and not aware it is available as SQLite field type. I always use TEXT.
aeric Expert Licensed User Longtime User Jan 20, 2022 #9 I found this answer Difference Between "Text" and "String" datatype in SQLite 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... stackoverflow.com
I found this answer Difference Between "Text" and "String" datatype in SQLite 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... stackoverflow.com
Magma Expert Licensed User Longtime User Jan 20, 2022 #10 https://topic.alibabacloud.com/a/th...arntext-of-sqlite-database_1_43_30097026.html ...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 Last edited: Jan 20, 2022
https://topic.alibabacloud.com/a/th...arntext-of-sqlite-database_1_43_30097026.html ...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
aeric Expert Licensed User Longtime User Jan 20, 2022 #11 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.
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.
Sandman Expert Licensed User Longtime User Jan 20, 2022 #12 aeric said: However, you may also argue that SQL is also a type of programming languages. Click to expand... To be fair, it's not very difficult to argue that point. A pure SQL raytracer 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 www.b4x.com
aeric said: However, you may also argue that SQL is also a type of programming languages. Click to expand... To be fair, it's not very difficult to argue that point. A pure SQL raytracer 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 www.b4x.com
Alexander Stolte Expert Licensed User Longtime User Jan 20, 2022 #13 Magma said: Click to expand... what program is this?
Magma Expert Licensed User Longtime User Jan 20, 2022 #14 Alexander Stolte said: what program is this? Click to expand... SQLiteStudio opensource - very light and fast !
Alexander Stolte said: what program is this? Click to expand... SQLiteStudio opensource - very light and fast !
Sandman Expert Licensed User Longtime User Jan 20, 2022 #15 Sandman said: To be fair, it's not very difficult to argue that point. Click to expand... And let's not forget this interactive 3d engine. We all have a friend in SQL 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... www.b4x.com
Sandman said: To be fair, it's not very difficult to argue that point. Click to expand... And let's not forget this interactive 3d engine. We all have a friend in SQL 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... www.b4x.com
