Did you know that ?

[Magma]

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]

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]

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 !

You mean passing the value using B4X as parameter?

 

[Magma]

@aeric no generally

 

[aeric]

@aeric no generally

You must be implementing something differently from me. I get all the zeros!

    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.

INSERT INTO "main"."SIV"("SIVCode","CompanyNo","StoreCode","SIVDate","Description","IsSync")
VALUES ('1','1','1','19-01-2022',NULL,'0000000000000000000000000');

 

[Magma]

@aeric....

At your database you are using TEXT (fields-columns) not string ... :-(

 

[Magma]

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...

    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]

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...

    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

I see. I never use String and not aware it is available as SQLite field type. I always use TEXT.

 

[aeric]

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]

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]

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]

However, you may also argue that SQL is also a type of programming languages.

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]

what program is this?

 

[Magma]

what program is this?

SQLiteStudio opensource - very light and fast !

 

[Sandman]

To be fair, it's not very difficult to argue that point.

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