|
Post by Chris Iverson on Apr 16, 2020 20:22:03 GMT -5
Ah, so the DLL call for sqlite3_column_text IS succeeding, and not crashing. It's attempting to use the returned null pointer with WINSTRING() that causes LB to crash.
Easy check for that is to add a test if the return value is 0 before calling WINSTRING().
if ret <> 0 then SQL3ColumnText$ = winstring(ret)
By doing this, you'll get an empty string back from the function if it tries to return a NULL value. Alternatively, you could make an ELSE statement that sets the string to the text value "NULL".
I'd actually recommend adding this to each of the functions I've made so far that use winstring(), just in case. Really, that's something I should've already been checking for. By my reckoning, that's ColumnName, ColumnText, and ErrStr, so far. Don't think I've used it for anything else.
Making this change, I get this result when I change the SELECT in my demo to your suggested command:
SQL3Init() - 0 SQL3Open() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101
cid name type notnull dflt_value pk 0 a INT 0 0 1 b TEXT 0 0
SQL3Finalize() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0
|
|
|
Post by pierre on Apr 17, 2020 5:25:54 GMT -5
Thank you Chris ! I applied your changes, and now it works ! I got the malicious idea to test this 'pragma table_info' query, for it had already been subject to some discussions on this forum. While testing the query in LB 4.5.1 with the 'standard' SQLite wrapper, the program did not crash, but did not show any result value.
Just for your information, the 'standard' wrapper calls the 'SQ3_4_LB_GetRecordset' function which internally is tracking back to the old 'sqlite3_get_table' function. To fully understand all this, we should study the wrapper's sources written in Purebasic but that would go far beyond my capacities.
In any case, I like very much your approach : translating the internal SQLite functions directly into Liberty BASIC language. I will continue testing. pierre
|
|
|
Post by ppro06 on Apr 17, 2020 9:06:17 GMT -5
I actually found it fairly easy to get the standard SQLite3 interface working in LB. There's a couple places that might be spotty, but overall, it works well. That LB wrapper is better for quick and general queries, but I was even able to get bound parameters in prepared statements working(best way to prevent SQL injection is bound parameters). All functions(that aren't my own custom ones) are closely named after their actual SQLite3 counterparts, with similar usage. I wrote this all just looking up the documentation on the SQLite3 website. Here's the code I quickly whipped up tonight. gosub [SQL3PrepGlobals]
print "SQL3Init() - ";SQL3Init()
hDB = 0 print "SQL3Open() - ";SQL3Open("test.db", hDB)
ret = SQL3ExecSingle(hDB, "CREATE TABLE test(a INT, b TEXT);") print "SQL3ExecSingle() - ";ret
if ret = SQLITE.DONE then print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(12,'blah');") print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(13,'stuff');") else print SQL3ErrStr$(ret) end if
'Attempting prepared statement with bound parameters SQL$ = "INSERT INTO test(a, b) VALUES(?, ?);" hStatement = 0 ret = SQL3Prepare(hDB, SQL$, hStatement) if ret <> SQLITE.OK then print "Bound prepared statement failed - ";ret;" - ";SQL3ErrStr$(ret) goto [skipBind] end if
ret = SQL3BindInt(hStatement, 1, 153) if ret <> SQLITE.OK then print "Bind int failed - ";ret;" - ";SQL3ErrStr$(ret) goto [finalSkipBind] end if
ret = SQL3BindText(hStatement, 2, "someoasidjrlkawergj") if ret <> SQLITE.OK then print "Bind text failed - ";ret;" - ";SQL3ErrStr$(ret) goto [finalSkipBind] end if
ret = SQL3Step(hStatement)
if ret <> SQLITE.DONE then Print "Executing bound statement failed - ";ret;" - ";SQL3ErrStr$(ret) end if
[finalSkipBind] ret = SQL3Finalize(hStatement)
[skipBind] SQL$ = "SELECT * FROM test;" hStatement = 0
ret = SQL3Prepare(hDB, SQL$, hStatement) if ret <> SQLLITE.OK then Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret) goto [skipSelect] end if
print print
do ret = SQL3Step(hStatement) if ret <> SQLITE.DONE and ret <> SQLITE.ROW then print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret) exit do end if
numCols = SQL3DataCount(hStatement)
if numCols <> 0 then for x = 0 to numCols - 1 print SQL3ColumnText$(hStatement, x), next x
print end if loop while ret <> SQLITE.DONE
print print
print "SQL3Finalize() - ";SQL3Finalize(hStatement)
[skipSelect]
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "DROP TABLE test;")
print "SQL3Close() - ";SQL3Close(hDB)
print "SQL3Shutdown() - ";SQL3Shutdown()
end
'This function is provided as a shorthand for statements that can be executed 'in a single step, and don't return anything(CREATE, DROP, INSERT, etc) Function SQL3ExecSingle(hDB, SQL$) 'Prepare/compile the statement hStatement = 0 ret = SQL3Prepare(hDB, SQL$, hStatement) if ret <> SQLITE.OK then 'Statement preparation failed. Error code may have more info. goto [endFunc] end if
'Execute the statement. ret = SQL3Step(hStatement)
[endFunc] dummy = SQL3Finalize(hStatement) SQL3ExecSingle = ret End Function
Function SQL3Init() open "sqlite3" for DLL as #sqlite3
CallDLL #sqlite3, "sqlite3_initialize",_ SQL3Init as long End Function
Function SQL3Shutdown() CallDLL #sqlite3, "sqlite3_shutdown",_ SQL3Shutdown as long
close #sqlite3 End Function
Function SQL3Open(fileName$, byref hDB) struct a, b as ulong
CallDLL #sqlite3, "sqlite3_open",_ fileName$ as ptr,_ a as struct,_ SQL3Open as long
hDB = a.b.struct End Function
Function SQL3Close(hDB) CallDLL #sqlite3, "sqlite3_close",_ hDB as ulong,_ SQL3Close as long End Function
Function SQL3Prepare(hDB, SQL$, byref hStatement) sqlLen = len(SQL$)
struct a, b as ulong
CallDLL #sqlite3, "sqlite3_prepare_v2",_ hDB as ulong,_ SQL$ as ptr,_ sqlLen as long,_ a as struct,_ _NULL as long,_ SQL3Prepare as long
hStatement = a.b.struct End Function
Function SQL3Finalize(hStatement) CallDLL #sqlite3, "sqlite3_finalize",_ hStatement as ulong,_ SQL3Finalize as long End Function
Function SQL3Step(hStatement) CallDLL #sqlite3, "sqlite3_step",_ hStatement as ulong,_ SQL3Step as long End Function
Function SQL3Reset(hStatement) CallDLL #sqlite3, "sqlite3_reset",_ hStatement as ulong,_ SQL3Reset as long End Function
Function SQL3ClearBindings(hStatement) CallDLL #sqlite3, "sqlite3_clear_bindings",_ hStatement as ulong,_ SQL3ClearBindings as long End Function
Function SQL3ErrStr$(errorCode) CallDLL #sqlite3, "sqlite3_errstr",_ errorCode as long,_ ret as ulong
SQL3ErrStr$ = winstring(ret) End Function
Function SQL3ColumnCount(hStatement) CallDLL #sqlite3, "sqlite3_column_count",_ hStatement as ulong,_ SQL3ColumnCount as long End Function
Function SQL3DataCount(hStatement) CallDLL #sqlite3, "sqlite3_data_count",_ hStatement as ulong,_ SQL3DataCount as long End Function
Function SQL3ColumnText$(hStatement, column) CallDLL #sqlite3, "sqlite3_column_text",_ hStatement as ulong,_ column as long,_ ret as ulong
SQL3ColumnText$ = winstring(ret) End Function
Function SQL3ColumnBytes(hStatement, column) CallDLL #sqlite3, "sqlite3_column_bytes",_ hStatement as ulong,_ column as long,_ SQL3ColumnBytes as long End Function
Function SQL3BindInt(hStatement, param, value) CallDLL #sqlite3, "sqlite3_bind_int",_ hStatement as ulong,_ param as long,_ value as long,_ SQL3BindInt as long End Function
Function SQL3BindText(hStatement, param, text$) textLen = len(text$)
CallDLL #sqlite3, "sqlite3_bind_text",_ hStatement as ulong,_ param as long,_ text$ as ptr,_ textLen as long,_ SQLITE.STATIC as long,_ SQL3BindText as long End Function
[SQL3PrepGlobals] Global SQLITE.OK : SQLITE.OK = 0 Global SQLITE.ERROR : SQLITE.ERROR = 1 Global SQLITE.BUSY : SQLITE.BUSY = 5 Global SQLITE.LOCKED : SQLITE.LOCKED = 6 Global SQLITE.MISMATCH : SQLITE.MISMATCH = 20 Global SQLITE.MISUSE : SQLITE.MISUSE = 21 Global SQLITE.ROW : SQLITE.ROW = 100 Global SQLITE.DONE : SQLITE.DONE = 101 Global SQLITE.INTEGER : SQLITE.INTEGER = 1 Global SQLITE.FLOAT : SQLITE.FLOAT = 2 Global SQLITE.BLOB : SQLITE.BLOB = 3 Global SQLITE.NULL : SQLITE.NULL = 4 Global SQLITE.TEXT : SQLITE.TEXT = 5 Global SQLITE.STATIC : SQLITE.STATIC = 0 Global SQLITE.TRANSIENT : SQLITE.TRANSIENT = -1 return I worked with the wrapper
|
|
|
Post by ppro06 on Apr 17, 2020 9:09:59 GMT -5
Thank you for the wrapper, for now the interface is working as well as I had wanted it to.
|
|
|
Post by Chris Iverson on Apr 17, 2020 13:10:14 GMT -5
Just for your information, the 'standard' wrapper calls the 'SQ3_4_LB_GetRecordset' function which internally is tracking back to the old 'sqlite3_get_table' function. To fully understand all this, we should study the wrapper's sources written in Purebasic but that would go far beyond my capacities.
And THAT would explain the existence of the old wrapper, especially if sqlite3_get_table() was formerly one of the recommended ways to run data-returning queries. get_table() returns an array of pointers to strings. This is impossible for LB to handle natively, as LB does not do numeric pointer dereferencing. It would require a lot of manual math to compute proper pointers that you could use with API calls to get pointers that you could use with winstring(). If the current API in SQLite3 is a newer interface, it's definitely one that's far easier to use with LB.
|
|
|
Post by pierre on Apr 17, 2020 14:46:49 GMT -5
I suppose the functions you use are probably newer than the get_table function, the use of which is not recommended anymore.
I have one more question:
You wrote a function that was not used in your example program: the 'SQL3ColumnBytes'. Apparently this function returns the lenght of a string in bytes. Under what circumstances should we need that function ?
Thanks in advance
pierre
|
|
|
Post by Chris Iverson on Apr 17, 2020 15:29:13 GMT -5
That would likely be used alongside a function I haven't written yet, SQL3ColumnBlob(). Basically, for returning a blob of binary data.
I don't actually know if it's necessary yet, as I haven't tried it. However, if you're storing binary data in BLOBs in SQLite, I don't know if SQL3ColumnText() would actually return the data properly. In particular, I don't know if WINSTRING() would. (In particular, string-handling functions assume strings stop at the first null byte, which isn't the case for arbitrary binary data. If you're using functions that cut off at the first null byte, binary blobs will get truncated. The length is used to retrieve ALL the bytes, regardless of what the byte values are.)
If it doesn't(and I suspect it won't), then that byte count would be needed, to be combined with a separate Windows API call to retrieve the data that LB can't naturally.
EDIT: This would probably need SQL3BindBlob(), as well, as you wouldn't be able to specify binary blobs in standard text queries. You'd need to use bound parameters to do so. At least, I think you'd have to. Worth testing to be sure.
|
|
|
Post by pierre on Apr 17, 2020 15:44:53 GMT -5
OK. I understand. Thanks, Chris. I'm not accustomed to blobs, but it's never too late...
pierre
|
|
|
Post by Chris Iverson on Apr 17, 2020 16:07:01 GMT -5
Well, I was correct about needing to copy the data, but I was wrong about needing the BindBlob() function for it to work - binding binary data works with BindText(), since it just copies the number of bytes you specify.
Instead of adding a ColumnBlob() function, I changed the ColumnText() option to always copy using the windows API instead of using winstring().
As an example of why it's needed, modify my earlier example in these ways:
Change the table creation to this:
CREATE TABLE test(a INT, b BLOB);
And change the BindText call to this(we add a null byte in the middle of the string we're binding):
stuff$ = "someoas" + chr$(0) + "idjrlkawergj" print "stuff$ = ";stuff$
ret = SQL3BindText(hStatement, 2, stuff$)
You'll see the output gets truncated:
SQL3Init() - 0 SQL3Open() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 stuff$ = someoas idjrlkawergj
a b 12 blah 13 stuff 153 someoas
SQL3Finalize() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0
See how the output in column b got cut off at the null byte?
If we change the ColumnText function to this, instead:
Function SQL3ColumnText$(hStatement, column) CallDLL #sqlite3, "sqlite3_column_text",_ hStatement as ulong,_ column as long,_ ret as ulong
if ret <> 0 then numBytes = SQL3ColumnBytes(hStatement, column)
SQL3ColumnText$ = space$(numBytes)
CallDLL #kernel32, "RtlMoveMemory",_ SQL3ColumnText$ as ptr,_ ret as ulong,_ numBytes as long,_ dummy as void end if End Function
We get the full output, even past the null byte:
SQL3Init() - 0 SQL3Open() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 stuff$ = someoas idjrlkawergj
a b 12 blah 13 stuff 153 someoas idjrlkawergj
SQL3Finalize() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0
|
|
|
Post by pierre on Apr 17, 2020 17:59:53 GMT -5
Wow ! You are working at high speed ! I would need a couple of days , even more, to find all that out.
On my screen, the chr$(0) prints as a dot, but never mind. I see how it works.
In the database, the column type is declared as BLOB, but normal text s stored as text, by type affinity I suppose. The 'abnormal' text is stored as BLOB. Brilliant !
Could this be used to store and retrieve images ?
pierre
|
|
|
Post by Chris Iverson on Apr 17, 2020 18:23:24 GMT -5
Yes, and I found out that the example still works if you change the column type in the declaration back to TEXT. SQLite3's typing system is very forgiving and helpful, especially for LB. These two pages go into further detail about the automatic conversions SQLite will make: www.sqlite.org/datatype3.htmlsqlite.org/c3ref/column_blob.htmlYes, it could be used to store and retrieve images. That kind of binary data is exactly why I looked into how to modify the functions to support it. EDIT: In fact, there's even a standard for using SQLite databases as a kind of archive file, like ZIP: www.sqlite.org/sqlar.html
|
|
|
Post by pierre on Apr 18, 2020 16:06:36 GMT -5
Thanks, Chris. I have learned a lot in just a couple of days ! I tested the BLOB input / output with some small image files. The results were, let's say, ambivalent. Some files worked, others not. Surprisingly (or perhaps not) it was not SQLite that caused the trouble. Every BLOB inserted into the database would be retrieved properly. It was LB 4.5.1 that apparently did not always realize a correct reading of the original image files..The same was true for LB5 alpha. I read the files for binary, saved the content in a string variable and then wrote it back to the disk (renaming the file, of course). In some cases I obtained an identical image of the original file, but not always. Some bmp files worked, others not;
jpg, ico or pdf files did not work at all....Some big files caused an error in LB : 'input past the end of file'.
Are there some limitations for LB's capacity to read binary files ? pierre
|
|
|
Post by Chris Iverson on Apr 18, 2020 17:33:51 GMT -5
There's only two limitations I can think of that you'd run in to doing things like this; LB's own memory limit(I think about 250MB or so, so you couldn't read a chunk larger than that in at once), and a filesize limitation of 4GB(LB might be able to read more data than 4GB, but I'm pretty sure we've had instances in the past where things like lof() don't behave properly due to them using 32-bit variables under the hood for the file size).
If you're doing something like this, as long as the chunk you read in isn't too big to fit in LB's memory space, you shouldn't have a problem:
open "file1" for binary as #file a$ = input$(#file, lof(#file)) close #file
open "file2" for output as #file print #file, a$; close #file
|
|
|
Post by pierre on Apr 19, 2020 7:19:26 GMT -5
OK , thanks, that was it.
I was using the 'input' statement only, as showed in the 'binary files' section of the LB help file. With your example using 'input$' and 'lof()', no more problems.
I just accessed a photograph in jpg file form, 2644 Ko of length, and inserted it into SQLite as a BLOB, then retrieved it, saving it back to disk.
The 3 files: the original, the one saved by LB 4.5.1 and the one recovered from SQLite, are rigourously identical and the copies are working,
that is to say the images show up properly.
So, BLOBS are no problem anymore between LB 4.5.1 and SQLite !
|
|
|
Post by Rod on Apr 19, 2020 8:44:54 GMT -5
So the thing about a blob is it may contain control characters. Getting the whole file a$ = input$(#file, lof(#file)) is important. So too the save, print #file, a$; Notice the ; which suppresses the CRLF pair that Liberty adds which easily corrupt databases, image file and blob data.
|
|