If I pull a record Id field of type Integer from a database into my code and store it as a string. When I’m ready to write back this record number, do I use SQLITE_TEXT or SQLITE_INT64? I noticed it works both ways. What is proper practice?
It’s best to match the datatype in the database so SQLITE_INT64.
IMO this does matter. Prepared statements also do some checks on the data coming in and you would lose that with SQLITE_TEXT.
That is not correct. It depends on how the field is declared.
SQLite can read/write data anyway (for the most part you want)…
Write is as a String “1234”
read it back as INTEGER
its only when you attempt to read data that won’t FIT into the datatype that you will find an issue.
THAT being said, it is good practice to keep your code aligned with the proper datatypes…
So if you switch from SQLite to something else you will not be tripping all over your self
most other databases are more strongly typed.
Frederick says write it as INT64, Dave says TEXT. Which is it?
My opinion: don’t store it in a string variable. Store as an Int64 variable and convert to and from a string ONLY for display purposes.
Read and Write to the database in the correct DataType. This will prevent errors later - especially if you move to another database. Separate your UI from your data as much as possible.
Sorry… you misinterpeted what I meant…
I meant you COULD do it that way… but that you shouldn’t as it will bite you in the a** if you go to any other database engine.
So follow proper practices and match datatypes
If an existing app was using SQLITE_TEXT to write back, and now I modify the code to use SQLITE_INT64 instead, will that mess up the existing data in any way?
Unlikely as Sqlite is kind of “typeless” in many ways
A db that enforced typing like postgresql, oracle, ms sql etc would care a LOT more