|
Post by metro on Apr 27, 2020 18:33:58 GMT -5
I'm out of town for the rest of the week, when I get back, I'll have a look at it. Thanks again.. Dan Don't loose ya shirt again if ya goin to Vegas,
If you get a chance Dan a code snippet of how to extract the PDF info would be useful to me
I currently just copy and paste manually.
stay safe
Laurie
|
|
|
Post by meerkat on Apr 27, 2020 19:41:59 GMT -5
With the stock market drop, I'm lucky if I have a shirt to loose. Actually, the market is back, and I got my money back. So now I can eat again..
It's a little more complex than some code. Been a while, but I think I used Google conversion to get a flat file, and then wrote code to parse it. I'll have to look at it again..
Fair dinkum! Dan
|
|
|
Post by metro on Apr 27, 2020 19:48:00 GMT -5
It's a little more complex than some code. Been a while, but I think I used Google conversion to get a flat file, and then wrote code to parse it. I'll have to look at it again.. Fair dinkum! Dan Good onya Dan Bonza job
Laurie
|
|
|
Post by Chris Iverson on Apr 27, 2020 20:11:56 GMT -5
I was experimenting with some database dumpers(in particular, dbdump.c compiled as an EXE, can't get the DLL to work yet), and it made me realize that, if you're storing binary data in BLOBs in SQlite, you really should be binding as blob, not text.
The way you bind it changes the affinity of the value, even if the field is different. For that last test program I had, dumping test.db would result in this:
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test(a INT, b BLOB); INSERT INTO test(rowid,a,b) VALUES(1,12,'blah'); INSERT INTO test(rowid,a,b) VALUES(2,13,x'746573742064617461'); INSERT INTO test(rowid,a,b) VALUES(3,153,'someoas'); COMMIT;
You'll notice that last string got cut off at the null byte, because the output program was assuming it was text.
That middle line is what you get if you bind as blob; it outputs a bunch of hex values.
Here's the same database, after updating my code with a SQL3BindBlob() function:
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test(a INT, b BLOB); INSERT INTO test(rowid,a,b) VALUES(1,12,'blah'); INSERT INTO test(rowid,a,b) VALUES(2,13,x'746573742064617461'); INSERT INTO test(rowid,a,b) VALUES(3,153,x'736f6d656f61730069646a726c6b61776572676a'); COMMIT;
Here's the bind blob function(used identically to bindtext):
Function SQL3BindBlob(hStatement, param, blob$) blobLen = len(blob$)
CallDLL #sqlite3, "sqlite3_bind_blob",_ hStatement as ulong,_ param as long,_ blob$ as ptr,_ blobLen as long,_ SQLITE.STATIC as long,_ SQL3BindBlob as long End Function
|
|
|
Post by metro on Apr 28, 2020 3:31:02 GMT -5
Showing my age again , I' struggling to understand why, if I rem out this line 'print ....... "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "DROP TABLE test;") why does re running Chris's code multiple times only add more records via the bound parameters....ie "153 someoasidjrlkawergj" yet the SQL3ExecSingle() records are not updating. What am I misunderstanding ?
Thanks in advance
gosub [SQL3PrepGlobals]
'INITIALISE dll print "SQL3Init() - ";SQL3Init()
hDB = 0 print "SQL3Open() - ";SQL3Open("test.db", hDB)
' ADDED BY CHRIS see functions for explanation 'And with that, you should be able to use the functions provided by the extension. 'You can use it, for example, to read data straight into the table(for these examples, I've created a test file named "test.txt" with the contents "test data"): ' print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(13, READFILE('test.txt'));") ' the above line would need to be after the table create
'Listing files in a tree(all files are walked down the tree): 'SQL Query - SELECT name,mode,mtime FROM fsdir('.') WHERE name NOT LIKE './bld/%' AND name NOT LIKE './sqlite/%';
'Reading a file: 'SQL Query - SELECT * FROM fsdir('test.txt'); '(The parameters on the query prevent it from recusing into the /bld and /sqlite folders, which on my computer contain the SQLite C source code 'and the compiler build output. Don't think you need hundreds of files listed here for an example.)
'28/04/20 Chris added the function for blobs SQL3BindBlob(hStatement, param, blob$)
print "SQL3EnableLoadExtension() - ";SQL3EnableLoadExtension(hDB, 1) print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "fileio")
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');") ret = SQL3ExecSingle(hDB, "INSERT or REPLACE INTO test(a, b) VALUES(12,'blah2');") ret = SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(13,'stuff');") ret= SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(14,'MORE blah');")
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$="DELETE from test WHERE a = 153;" ' ret= SQL3ExecSingle(hDB,SQL$)
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 numRecs=1: DIM recordSet$(10000)
do recordSet$="" 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 maxRows=numCols-1 Line$= SQL3ColumnText$(hStatement, x) recordSet$(numRecs)= recordSet$(numRecs)+Line$+"|" next x print print recordSet$(numRecs) end if numRecs=numRecs+1 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()
for x = 1 to numRecs-2 print recordSet$(x) next
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
'ADDED By Chris for reading data in fromtxt file etc Function SQL3EnableLoadExtension(hDB, onoff) SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION = 1005
CallDLL #sqlite3, "sqlite3_db_config",_ hDB as ulong,_ SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION as long,_ onoff as long,_ SQL3EnableLoadExtension as long End Function
Function SQL3LoadExtension(hDB, extFileName$) CallDLL #sqlite3, "sqlite3_load_extension",_ hDB as ulong,_ extFileName$ as ptr,_ _NULL as long,_ _NULL as long,_ SQL3LoadExtension as long End Function
Function SQL3BindBlob(hStatement, param, blob$) blobLen = len(blob$)
CallDLL #sqlite3, "sqlite3_bind_blob",_ hStatement as ulong,_ param as long,_ blob$ as ptr,_ blobLen as long,_ SQLITE.STATIC as long,_ SQL3BindBlob as long End Function
|
|
|
Post by pierre on Apr 28, 2020 6:30:12 GMT -5
metro,
instead of "CREATE TABLE etc..." you must use "CREATE TABLE IF NOT EXISTS etc.....".
In your example, the very first time, the table is created and the INSERT commands work normally. If you restart without dropping the table, the programs tumbles on the CREATE TABLE command, skips the INSERT section and executes oly the PREPARE, BIND & INSERT section.
Don't blame your age ! Most of us are old....Except Chris, who is young and brilliant ! With his functions, he shows us possibilities that the "old" SQLite wrapper didn't provide: user inputs with bound parameters (without the hassle of sanitizing input strings, doubling quotes etc..), storing Blobs, import & export them with the extended SQLite functions... Without all this, meerkat was compelled to write many, many lines of programming code in order to have some of these things working !
pierre
|
|
|
Post by metro on Apr 28, 2020 7:58:32 GMT -5
metro, instead of "CREATE TABLE etc..." you must use "CREATE TABLE IF NOT EXISTS etc.....".
In your example, the very first time, the table is created and the INSERT commands work normally. If you restart without dropping the table, the programs tumbles on the CREATE TABLE command, skips the INSERT section and executes oly the PREPARE, BIND & INSERT section.
Don't blame your age ! Most of us are old....Except Chris, who is young and brilliant ! With his functions, he shows us possibilities that the "old" SQLite wrapper didn't provide: user inputs with bound parameters (without the hassle of sanitizing input strings, doubling quotes etc..), storing Blobs, import & export them with the extended SQLite functions... Without all this, meerkat was compelled to write many, many lines of programming code in order to have some of these things working !
pierre
Thanks pierre, that did the trick. Normally I would include "old and stupid", now I realise I'm just stupid.
Sometimes ya cant see the forest for the trees, Thanks for the help
PS , Chris is a fantastic asset to this forum. Thanks Chris
|
|
|
Post by BeeTrap on Apr 28, 2020 9:07:37 GMT -5
I want to thank everyone that has posted in this thread. I have wanted to "attempt" to learn SQLite3 and this has given me some inspiration! MOST of the older threads were way over my head, this one isn't as daunting, yet! Remember that many "newbies" are lurking and eating up what y'all say...... Thanks again, cwr.
|
|
|
Post by pierre on Apr 28, 2020 9:30:20 GMT -5
Chris Iverson said:
Edit: Caution ! Disregard this post. I was mixing up the function names. see Chris's answer below.
Thanks Chris.
Yes, if you compare it to your first 'standard' version of the 'SQL3BindText' function, then the use of 'SQL3BindBlob' is mandatory for Blob data, and indeed, it works.
But your second version of the 'SQLBindText' function, which I call the function with the 'kernel32 detour', still works very well for text AND Blob.
Following ppro06's suggestion, I looked at the minGW utility, but alas, I have to face the facts, compiling C programs goes far beyond my poor programming skills.
Eager to know if you can get the dll to work ....
pierre
|
|
|
Post by Chris Iverson on Apr 28, 2020 11:34:03 GMT -5
Pierre: I never wrote a second version of BindText().
You're thinking of ColumnText$(), which does still work fine, since LB treats strings and blobs of bytes the same.
I'm not talking about retrieving data from the database; I'm talking about putting it in there in the first place.
If you use sqlite3_bind_text()(SQL3BindText() in LB) to bind a blob of binary data into a SQLite database, it will be stored with a type-hint indicating it's text, not binary data. You can still get the full data back, if you're paying attention and measure out the bytes using sqlite3_column_bytes(), but if you assume it's just a null-terminated string(which many applications will reasonably do if they see a "text" type-hint), the data will get cut off.
You can see that the data DOES get cut off using a database dumper:
INSERT INTO test(rowid,a,b) VALUES(3,153,'someoas');
That's what comes out when I use a database dumper to dump my test database, if I store that "blob" that contains a null-byte as text. It discarded part of the value!
If I use BindBlob to insert that value into the database, I get this:
INSERT INTO test(rowid,a,b) VALUES(3,153,x'736f6d656f61730069646a726c6b61776572676a');
Which is the full string I was storing, converted into hex digits.
So, in summary: you can use ColumnText$() to retrieve the full data successfully, but if you're inserting binary blobs, you should use BindBlob() to store them.
I've been trying to get a DLL version of dbdump working, but I can't get one that works consistently without crashing. I have one now that seems to work 90% of the time, but will give weird errors or crash the remaining 10%. I think it's running into some weird race condition with LB's memory manager, but I can't say for sure. (A string from LB pointed to in the function sometimes gets randomly corrupted after a second pass. It happens more often when I make the "dump" a function in LB, which has even stricter scope.)
|
|
|
Post by pierre on Apr 28, 2020 13:39:53 GMT -5
Chris Iverson said:
OK Chris, fair enough ! I take your point. I am sorry, I was mixing up the function names..... Another time, forgive my blindness. I was refering to the fact that before you wrote the BindBlob function, I could already load, bind and insert a large image file and then get exactly the same data back. But that was because I retrieved the data using your last version of ColumnText$ that calls the ColumnBytes function...
Another time, sorry, and thanks for all you do for us !
pierre
|
|
|
Post by Chris Iverson on Apr 28, 2020 15:06:55 GMT -5
Don't worry about it! I'm just trying to make sure that the sqlite databases will be as compatible as possible, without causing problems. Still don't have a working DLL yet, and I think I just have to re-write some internal code for it to work, but I'm attaching an EXE you can use in the meantime. dbdump.exe, command line tool that takes three parameters: sqlite file name, schema name(you usually want "main"), and table name. dbdump test.db main test You can leave off the table name to dump all tables, and you can leave off "main" to have it use "main" by default. dbdump test.db (This is just dbdump.c compiled into an EXE.) Attachments:dbdump.exe (110 KB)
|
|
|
Post by pierre on Apr 28, 2020 15:28:16 GMT -5
Chris Iverson said: Great ! Thank you, Chris.
pierre
|
|
|
Post by pierre on Apr 28, 2020 15:38:07 GMT -5
BeeTrap said:
You're welcome BeeTrap. I am also a newcomer and I am learning a lot on this forum. I like databases and SQLite is just what I need. But notice that Liberty Basic is also capable of doing database programming. Check out the Simple Contacts Database thread in the Database Programming section. There you have the two approaches : 1) all in native LB code written by Rod 2) the sqlite solution written by meerkat All you need to make a good start. pierre
|
|
|
Post by Chris Iverson on Apr 28, 2020 16:25:43 GMT -5
Well, here's what I've got so far for a dbdump DLL. I've run it quite a few times without issues, so hopefully I've worked around the weirdness I was seeing successfully(I did this by copying the strings internally before working with them, so LB can trash its copy all it wants to.) It's attached to this post. I've got the following new functions to add: Function fputs(stuff, hFile) if stuff = 0 then exit function stuff$ = winstring(stuff) print #dbdumpfile, stuff$; End Function
Function SQL3DBDump(file$, hDB, schema$, table$) callback FPUTSCB, fputs(ulong, long), void
open file$ for output as #dbdumpfile open "dbdump.dll" for dll as #dbdump
calldll #dbdump, "sqlite3_db_dump",_ hDB as ulong,_ schema$ as ptr,_ table$ as ptr,_ FPUTSCB as ulong,_ 0 as long,_ SQL3DBDump as long
close #dbdump close #dbdumpfile End Function In my demo code, insert these lines just before the line that would drop the table: schema$ = "main" table$ = "test" print "SQL3DBDump() - ";SQL3DBDump("testdb-dump.txt", hDB, schema$, table$) You can also provide schema$ and table$ as empty strings, to default to "main" schema, and all tables. print "SQL3DBDump() - ";SQL3DBDump("testdb-dump.txt", hDB, "", "") Let me know if you see any weird crashes or outputs. Attachments:dbdump.dll (82 KB)
|
|