Hello everybody!
I have just started to use Nim Sqlite library. It works fine but I faced that it changes data type upon getting rows from DB. For instance this sample program:
import db_sqlite
let db = open("mytest.db", "", "", "")
db.exec(sql"DROP TABLE IF EXISTS my_table")
db.exec(sql"""CREATE TABLE my_table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
int_field1 INTEGER NULL,
int_field2 INTEGER NULL,
int_field3 INTEGER NULL
)""")
db.exec(sql"BEGIN")
db.exec(sql"INSERT INTO my_table (int_field1, int_field2, int_field3) VALUES (1, 200, 100500)")
db.exec(sql"COMMIT")
let return1 = db.getRow(sql"SELECT * FROM my_table")[1]
let return2 = db.getRow(sql"SELECT * FROM my_table")[2]
let return3 = db.getRow(sql"SELECT * FROM my_table")[3]
db.close()
echo return1, " Type: ", type(return1)
echo return2, " Type: ", type(return2)
echo return3, " Type: ", type(return3)
returns:
1 Type: string
200 Type: string
100500 Type: string
You are not doing anything wrong, the getRow() returns a seq[string].
You can see here (manual - getRow) that a Row is returned from getRow(). And you can see here (manual - Row) that a Row is a seq[string].
You need too use parseInt and friends to change the types.
A SQLIte query is always returned as string (that's how the C api does it too).
You can use Norm ( https://github.com/moigagoo/norm ) if you want a SQLite ORM that can directly convert your data. I'm using it A LOT and it's awesome !
The SQLite 3 interface has a call sqlite3_column_int() which returns a column value of a result row as an int. So if one calls this function from Nim the result should be available as an int, not a string.
(Just reading the API documentation, didn't try it myself)
For reading a BLOB column, I recently wrote a helper function like below - note that it will only work with instantRows:
import std / [db_sqlite, sqlite3]
proc blobColumnAt*(row: InstantRow, index: int32): string =
## Retrieve the value at index column as BLOB.
result.setLen column_bytes(row, index)
copyMem(result[0].addr, column_blob(row, index), result.len)
You could write a similar (actually, simpler) one using column_int instead of column_bytes and column_blob, and dropping copyMem and setLen. I believe a bunch of funcs like that should be actually added to db_sqlite, but haven't opened an issue and/or PR about that myself yet.
thanks for reply, good instrument!
but a step back: what is a proper way to put blob into sql? For example, if to try to put zip-compressed text data it falls with DbError:
import db_sqlite
import zip/zlib
let db = open("mytest.db", "", "", "")
let str = "Asamplelongtextdatatobezipcompressedjusttocheckhowitworkswithsqlitedatabase"
db.exec(sql"DROP TABLE IF EXISTS my_table")
db.exec(sql"""CREATE TABLE my_table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text_field TEXT,
zip_text BLOB
)""")
db.exec(sql"BEGIN")
db.exec(sql"INSERT INTO my_table (text_field, blob) VALUES (?,?)", str, compress(str))
db.exec(sql"COMMIT")
let return1 = db.getRow(sql"SELECT * FROM my_table")[1]
let return2 = db.getRow(sql"SELECT * FROM my_table")[2]
db.close()
echo return1, " Type: ", type(return1)
echo return2, " Type: ", type(return2)
I made a PR a while back that has been merged into develop (but not yet released) with an example of how to deal with binary data with the db_sqlite.
If you read the bvleeding edge doc there is a nice example : https://nim-lang.github.io/Nim/db_sqlite.html#basic-usage-storing-binary-data-example
I ended using Norm because I don't like writing SQL by hand : https://github.com/moigagoo/norm