Hello,
I have data seq[float64] that I wish to write in a SQLite database in a binary format.
So I tried to do it this way :
let db = open(dbName, "", "", "")
db.exec(sql"DROP TABLE IF EXISTS test")
let createTableStr = sql"""CREATE TABLE test(
id INTEGER,
data BLOB
)
"""
db.exec(createTableStr)
var data : seq[float64]
var bufSize = int(sizeof(float64)/sizeof(byte))*(data.len)
var strm = newStringStream("")
strm.writeData(unsafeAddr(data[0]), bufSize)
strm.flush()
strm.setPosition(0)
db.exec(sql"INSERT INTO test (id, data) VALUES (?, ?)", $0, strm.data)
strm.close()
db.close()
The problem with this approach is that the data gets converted to cstring which means that if I have the equivalent of 00, my datas gets truncated.
I arrived at this conclusion when I echo'd this :
echo "string ", len(strm.data)
echo "cstring ", len(strm.data.cstring)
How could I solve this issue and insert my data directly in a sqlite database without having to convert the binary data to an intermediate string representation ? Or is that the wrong approach (I didn't find any ByteStream in streams so I used StringStream) ?
something like this could work:
var data: seq[float64] = @[0.1, 0.2]
var strBuf = newString(data.sizeof())
copyMem(addr strBuf[0], addr data, data.sizeof())
## ..
## Write strBuf to your sqlite blob.
## ..
# get your data back
var data2: seq[float64] = newSeq[float64]( strBuf.len div float64.sizeof() )
copyMem(addr data2, addr strBuf[0], data2.sizeof)
assert data == data2
Thanks for your answer but I'm not sure if I understand correctly your answer :D.
Copying the data into a StringStream works. y problem lies when I have a character 0 in binary data with string (I assume other character could reproduce wierd phenomenon like that such as n).
Using toHex / parseHexStr solve the problem but using it on every INSERT and SELECT is expensive ; it takes both a lot of time and memory so it's far from ideal.
Is there a way to handle binary data that would avoid copying binary data into a string and then converting it into hexadecimal ?
I have the same problem when I use binary data obtained from msgpack.
From the SQLite perspective, I ended up with error varying results (my input data is a pseudo-random seq of float) from a truncated string to an exception dbError unrecognized token.
So I've done more tests.
With preparedStatement you can actually insert binary data but I haven't been able to retrieve it (same issue of cstring data getting truncated on select when using either getValue or getRow).
tiny_sqlite is able to do this with the minor issue that you have store your data into a seq[byte].
After having played around with buffer and seq[byte] / string, I think Nim has room to grow regarding the handling of binary data.
Most notable things that I think would help :
I'm curious if there are any ongoing discussion on these topics ? Is this something that could be implemented on the standard lib or should it be an external module ?
Prepared queries can be many times faster depending on use case. So, I think they should have been part of stdlib but I guess its too late for v1.
Here is what I meant by "diving into sqlite3.nim"
import sqlite3, db_sqlite, strutils, strformat
let dbname = ":memory:"
let db = open(dbName, "", "", "")
db.exec(sql"DROP TABLE IF EXISTS test")
let createTableStr = sql"""CREATE TABLE test(
id INTEGER,
data BLOB
)
"""
db.exec(createTableStr)
var data : seq[float64]
data.add(0)
data.add(1)
data.add(2)
data.add(3.14)
echo "Data to write: ", data
var bufSize = int(sizeof(float64)/sizeof(byte))*(data.len)
# Insert prepared
let insert_query = "INSERT INTO test (id, data) VALUES (?, ?)"
var stmt : PStmt = nil
var tail : ptr cstring
var err = prepare_v2(cast[PSqlite3](db),insert_query.cstring,insert_query.len.cint,stmt,tail)
if err != SQLITE_OK:
raise newException(CatchableError,"Query failed")
# repeat
if stmt.bind_int(1,1) != SQLITE_OK:
raise newException(CatchableError,"Bind error")
if stmt.bind_blob(2,unsafeAddr(data[0]),bufSize.int32,SQLITE_STATIC) != SQLITE_OK:
raise newException(CatchableError,"Bind error")
err = stmt.step()
if err != SQLITE_DONE:
raise newException(CatchableError,"Query couldnt be executed")
discard stmt.reset()
# until all inserted
discard stmt.finalize()
# Select prepared
var data_read : seq[float64]
let select_query = "SELECT id,data FROM test"
err = prepare_v2(cast[PSqlite3](db),select_query.cstring,select_query.len.cint,stmt,tail)
if err != SQLITE_OK:
raise newException(CatchableError,"Query failed")
while true:
err = stmt.step()
if err == SQLITE_DONE:
discard stmt.finalize()
break
else:
if err == SQLITE_ROW:
var id = stmt.column_int64(0)
bufsize = stmt.column_bytes(1)
if bufsize mod sizeof(float64) != 0:
raise newException(CatchableError,"Incorrect data")
var cnt = bufsize div sizeof(float64)
data_read.setLen(cnt)
if cnt>0:
var buf = stmt.column_blob(1)
copyMem(unsafeAddr(data_read[0]),buf,bufsize)
echo fmt"Row-> id:{id}, data: {data_read}"
else:
raise newException(CatchableError,"Query step error")
db_sqlite.close(db)
We can always extend db_sqlite :p
There's ndb which is db_sqlite but with prepared statements: https://github.com/xzfc/ndb.nim
And @Araq's ormin is pretty cool too: https://github.com/Araq/ormin