|
Post by ppro06 on May 4, 2020 5:45:37 GMT -5
Hello Pierre, Yes SQlite in exporting files has been a let down. I have been banging my head against the brick wall trying to figure out how to read, and write single records, for instance. CSV is covered, however other formats like text, XML, then there are archive formats like 'docx'. I am experimenting on the sqlite3_db_data function for importing the dumped data as well as the fsdir under fileio for reading and importing other formats. Do take a look at them. Will report back when I have results.
|
|
|
Post by meerkat on May 4, 2020 8:40:24 GMT -5
ppro06
Try something like this to export to csv and inport. You create the csv export SQL command so you have full control of what is exported. And other options on the inport. Maybe something like this will help:
sqliteconnect #sql, "e:\LB5\form\data\form.db" ' substitute with your file
' ------------ Export CSV ------- csvFile$ = "e:\data\temp\a.csv" open csvFile$ for output as #f sql$ = "SELECT * FROM formDtl" ' change formDtl to your file,to limit what is exported use WHERE #sql execute(sql$) WHILE #sql hasanswer() csv$ = #sql nextrow$("','") print #f, "'";left$(csv$,len(csv$)-2) WEND close #f
' ---------- Inport CSV ---------- open csvFile$ for input as #f while not(eof(#f)) line input #f, csv$ sql$ = "INSERT INTO formDtl VALUES(";csv$;")" #sql execute(sql$) WEND close #f
Hope this helps.. Have a great day.. Dan
|
|
|
Post by pierre on May 4, 2020 10:54:04 GMT -5
hello ppro06,
I don't know exactly what you are looking for, so here are some observations:
meerkat's CSV example is written for LB5 alpha. I did something smilar for LB 4.5.1 in one of my posts above
READFILE & WRITEFILE are o.k. for storing Blob files in SQLite. This only makes sens if you provide for a separate column in your table, to store the indication of the file type (bmp, png, jpg etc..) to use when retrieving and saving the Blob back to disk.
The dbdump file creates an image of the database in SQL format, so that the tables can be recreated and the data imported into any SQLite environment, nothing more is required for importing the dumped data. The SQLITE3ExecSingle command Chris provided could be used for that.
If we want to use numerous repetitive INSERT commands, there is a 'sqlite3_exec' function that does 'prepare', 'step' and 'finalize' in one step, but that requires a callback function I am unable to write.
Apart from csv files, I don't know of any other file format directly accepted by SQLite (perhaps the 'zipfile ' extension what would perhaps be something like uncompress a compressed csv file (only one) and then read that file ? but I don't really know,it would only make sens for a bulk import of numerous data) . You mentioned a 'sqlite3_db_data' function ? that doesn't seem to be a sqlite3 function. Perhaps are you refering to the 'dbdata.c' extension? From my understanding this extension has more to do with internal forensic work: reading directly from a b tree database page.....? that would not give
you anything more than the raw data that otherwise can be retrieved.
The 'fsdir' command from the 'fileio' exension does not seem to give you more than a mere listing of a directory from the file system, without apparently any possible reference to the content of each file ?
Now, does it make sens to be able to read numerous file formats xml, docx, xlsx ? Most applications read/write csv files wich serve as a vehicule to move data around. If it should be done, than it should be done in Liberty BASIC, not in SQLite, but -another time -I am not able to achieve that kind of things.
pierre
|
|
|
Post by meerkat on May 4, 2020 12:59:12 GMT -5
BTW: In the above example it is better to insert multiple records with a single INSERT. It is considerably faster (many times faster in some cases) than using separate single row INSERT statements.
For example change the above Import CSV code to something like this:
' ------------ import csv ----------------------- sqliteconnect #sql, "e:\LB5\form\data\form.db" csvFile$ = "e:\data\temp\a.csv" open csvFile$ for input as #f
while not(eof(#f)) i = i + 1 line input #f, a$ csv$ = csv$ + cma$ + "(" + a$ + ")" cma$ = "," if i > 150 then sql$ = "INSERT INTO formDtl VALUES";csv$ #sql execute(sql$) csv$ = "" i = 0 cma$ = "" end if WEND if csv$ <> "" then sql$ = "INSERT INTO formDtl VALUES";csv$ #sql execute(sql$) end if close #f
HTH dan.
|
|
|
Post by pierre on May 4, 2020 16:42:41 GMT -5
meerkat said:
Yes, thank you meerkat, with a slight change in your first program, this works fine in LB5 alpha. The effect is indeed instantaneous, but I had only a relative small amount of data to play with.
I think ppro06 was refering to LB 4.5.1 code and there the interface with SQLite is different.
pierre
|
|
|
Post by metro on May 5, 2020 2:17:52 GMT -5
Hello Pierre, Yes SQlite in exporting files has been a let down. I have been banging my head against the brick wall trying to figure out how to read, and write single records, for instance. CSV is covered, however other formats like text, XML, then there are archive formats like 'docx'. I am experimenting on the sqlite3_db_data function for importing the dumped data as well as the fsdir under fileio for reading and importing other formats. Do take a look at them. Will report back when I have results. I have found a little time to try to get a grip of the direct use of the Sqlite dll, I hope there isn't a limit to dumb questions. I have found using "BEGIN TRANSACTION) " speeds things up dramatically but am not sure if my method of attack is the correct way to insert multiple lines
So the first dumb question : what does hStatement relate to? I have tried to read the SQlite documents but having the concentration span of a goldfish I seem to be re-reading pages to get it to sink in any help appreciated.
gosub [SQL3PrepGlobals]
print "SQL3Init() - ";SQL3Init() 'INITIALISE dll
hDB = 0 print "SQL3Open() - ";SQL3Open("play.db", hDB) print "SQL3EnableLoadExtension() - ";SQL3EnableLoadExtension(hDB, 1) print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "fileio")
ret = SQL3ExecSingle(hDB, "CREATE TABLE IF NOT EXISTS test(a INT, b TEXT);") ret = SQL3ExecSingle(hDB, "BEGIN TRANSACTION ;") ' WOW so much faster
print "SQL3ExecSingle() - ";ret if ret = SQLITE.DONE then '########################################################################## for q = 1 to 50 SQL$= "INSERT or REPLACE INTO test(a, b) VALUES("; q ;",'MORE blah');" ret= SQL3ExecSingle(hDB,SQL$) next end if '##########################################################################
ret = SQL3ExecSingle(hDB, "COMMIT;") 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
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 end if
numRecs=numRecs+1 loop while ret <> SQLITE.DONE
print "SQL3Finalize() - ";SQL3Finalize(hStatement)
[skipSelect] 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
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
Function SQL3CreateModule(hDB, ImporTable$)
CallDLL #sqlite3, "sqlite3_create_module",_ hDB as ulong,_ "csv" as ptr,_ csvtabCreate as ptr,_ ImporTable$ as ptr,_ SQL3CreateModule as long End Function
'Function SQL3DeclarevTab(hDB, vTabschema$) ' CallDLL #sqlite3, "sqlite3_declare_vtab",_ ' hDB as ulong,_ ' vTabschema$+chr$(0) as ptr,_ ' SQL3DeclarevTab as long
'End Function
|
|
|
Post by pierre on May 5, 2020 3:29:30 GMT -5
metro said: hello metro,
don't worry, here is another 'goldfish'.
My understanding is the following:
1) hStatement is a handle for the SQL statement, thus the Query, just as hDB is a handle for the database connection.
2) SQLite commands are "auto-commit" : every INSERT, UPDATE etc, commits (= writes to the disk) at the end of the statement.
When you use for example a BEGIN TRANSACTION ............ COMMIT enclosing many different INSERT commands, then SQLite writes the data to a memory cache
and commits only at the end of the series, so in your program: only one disk access instead of 50.
The size of the memory cache can be adjusted through the PRAGMA cache_size.
pierre
|
|
|
Post by ppro06 on May 5, 2020 5:34:53 GMT -5
Hello Pierre and Dan, Thank you for your responses, however reading and writing csv is not my concern, it's the other formats that sqlite allows. As I understand it sqlite accepts 2 other table formats for import;XML and sqlite. Text files are the only read as blobs. Unfortunately the data that I'm working with is in a flat text file. To input it I have to painstakingly convert it to csv...Zip function for export is good because you can for instance dump the database into an archive for data security. Pierre, Fsdir can also read whole documents as blobs when used to "select *from Fsdir('filepath$');". According to sqlite documentation, dumped data tables are in 'shadow', eponymous tables. So I am thinking to import the data into a new database, a function which handles such will allow for their import. I was thinking the dbdata function might be the one. Whichever function it is, it requires some special compile-time permissions.
|
|
|
Post by pierre on May 5, 2020 6:30:27 GMT -5
ppro06 said: Thanks ppro06, your point.
I had forgotten that "SELECT * FROM fsdir('test.txt');" also works. I was thinking of the huge directory listing Chris was showing us.
pierre
|
|
|
Post by Chris Iverson on May 5, 2020 14:15:03 GMT -5
1) hStatement is a handle for the SQL statement, thus the Query, just as hDB is a handle for the database connection. Correct. There are two steps to running a SQL query in SQLite: compilation and execution. SQL3Prepare() will compile the SQL query into an internal bytecode, and will give you a handle to the compiled code. (Syntax errors, etc, are discovered and rejected here.) Once you have the compiled query, you can use SQL3Step() to execute it. You keep calling SQL3Step() until the return value indicates that the query has fully executed(SQLITE.DONE). Once you've finished executing the query, you tell SQLite to delete the compiled query and any memory associated with it using SQL3Finalize(). SQL3ExecSingle() is just a wrapper around calling all three of those functions all at once. One interesting effect of this is, if you reset the compiled query after execution, you can actually execute the query multiple times without needing to recompile it every time. Many programs will do this with some of the very complicated queries they have to run sometimes; they'll prepare/compile the queries in advance, and just execute them when needed. Once you've executed a query, as long as you don't finalize it, you can call the SQL3Reset() function on it to let it be executed again. You can even take advantage of this with bound parameters; once you've executed a query with bound parameters, you can call SQL3ClearBindings() to delete the bound data and let you bind again. This means that, even with queries that require parameters, you can set it up to compile only once, and just reset and clear bindings after executing to be ready again. For basic queries, you won't see much of a difference(and in fact, it may take longer if you're rebinding parameters due to the LB function call overhead), but for some really complex ones, preparing queries in advance can really help.
|
|
|
Post by pierre on May 5, 2020 15:06:08 GMT -5
Chris Iverson said :
YES, that is just what I was doing.
Starting from the examples given by meerkat and metro (multiple inserts), I extended the test (100 records) to a multiple insert with bound parameters. I left the 'a' column (int) with a fixed value and did text binding for column 'b'.
I had to modify my subprogram 'Bind&InsertText' to replace the call to the Finalize function by a call to the Reset function, and put the call to the Finalize function out of the multiple insert loop.
This is the output:
SQL3Init() - 0 SQL3Open() - 0 SQL3ExecSingle() - 101
MULTIPLE RECORDS WITH A SINGLE INSERT elapsed time 13282 milliseconds
SAME INSERT INSIDE A TRANSACTION elapsed time 141 milliseconds
SAME INSERT INSIDE A TRANSACTION (WITH BINDING) elapsed time 156 milliseconds
SQL3DBDump() - 0 SQL3ExecSingle() - 101 Table test dropped
SQL3Close() - 0 SQL3Shutdown() - 0 database closed
Done
pierre
|
|
|
Post by metro on May 5, 2020 19:49:16 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. I second that motion.
Well, despite using the old wrapper for quite a while I have a lot of blanks to fill for me to become proficient using SQLite
even more now thanks to Chris.
I'm going to keep throwing up questions because the response to my last question was very enlightening , Thanks Chris & pierre.
Please meerkat pierre and ppro06 keep posting your code it helps more that you may realise
|
|
|
Post by pierre on May 6, 2020 7:37:37 GMT -5
hello ppro06, To follow up your observation about he 'fsdir' command : I just wanted to verify.
Executing the query : 'SELECT * FROM fsdir('test.txt');' we obtain a virtual table in SQLite : name mode mtime data test.txt 33206 1588413610 test data What we want is the data. Copy the content of the column 'data' to our main database: dummy = SQL3ExecSingle(hDB, "INSERT INTO test VALUES(null, (SELECT data FROM fsdir('test.txt')));") Then, a 'SELECT * from test' will give us: a b NULL test data
The text of our file 'test.txt' is there. The dbdump shows us that the data is stored internally as Blob.
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test (a INT, b TEXT); INSERT INTO test(rowid,a,b) VALUES(1,NULL,x'746573742064617461'); COMMIT; SQLite will always show the plain text, but the record will not be found by a simple text search.
Now, I remain somewhat puzzled. You told us, that csv import is not your concern. Your data resides in plain text files, without an easy way to convert it to csv.... ?? You were saying that SQLite seems to accept XML tables ...I couldn't find any documentation about SQLite importing directly XML and I have no idea how to do that....
pierre
|
|
|
Post by ppro06 on May 7, 2020 2:04:09 GMT -5
hello ppro06, To follow up your observation about he 'fsdir' command : I just wanted to verify.
Executing the query : 'SELECT * FROM fsdir('test.txt');' we obtain a virtual table in SQLite : name mode mtime data test.txt 33206 1588413610 test data What we want is the data. Copy the content of the column 'data' to our main database: dummy = SQL3ExecSingle(hDB, "INSERT INTO test VALUES(null, (SELECT data FROM fsdir('test.txt')));") Then, a 'SELECT * from test' will give us: a b NULL test data
The text of our file 'test.txt' is there. The dbdump shows us that the data is stored internally as Blob.
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test (a INT, b TEXT); INSERT INTO test(rowid,a,b) VALUES(1,NULL,x'746573742064617461'); COMMIT; SQLite will always show the plain text, but the record will not be found by a simple text search.
Now, I remain somewhat puzzled. You told us, that csv import is not your concern. Your data resides in plain text files, without an easy way to convert it to csv.... ?? You were saying that SQLite seems to accept XML tables ...I couldn't find any documentation about SQLite importing directly XML and I have no idea how to do that....
pierre Hello Pierre, Yes, that is exactly what the fsdir function does, even in the command line interface as long as the data you are trying to read isn't in the formats that sqlite accepts, it will be read as a blob. I said I am not concerned about csv format because at the time I had already figured out how to both read it, and write it. My frustration when I expressed the sentiment, "Yes SQlite in exporting files has been a let down", came about when I couldn't figure out how to export single table rows/records into text file format (this is no longer the case). As for the file that I have been working with I said that I am able to convert it to csv, but with difficulty. Now coming to import formats, I have just been looking for other ways of importing data into the database that may be easier. the links below say what formats sqlite can maybe read and write to. You can comb through. 1) sqlite.org/affcase1.html2) sqlite.org/appfileformat.html3) sqlite.org/cli.html
|
|
|
Post by ppro06 on May 8, 2020 4:05:53 GMT -5
Hello Everyone, I have made a snippet of code to import dumped text into a database, please try it out and compare it with original database. I would like to know how well it is working.
Dim impLine$(10000) 'adjust to > number of expected lines filedialog "open","*.*", filepath$ 'filepath$ = dumpfile if DBase$ <> "" then 'DBase$ = DB name open filepath$ for input as #DBimp end if print "reading file ..." while not(eof(#DBimp)) impLine$(noq)=inputto$(#DBimp,";") noq = noq + 1 if qline >= 10000 then print "error" wend close #DBimp for qline = 1 to noq query$ = ""+impLine$(qline)+";" gosub [ExecuteEngine] next qline print "you have imported "; noq ; "lines of code"
If ret = SQLITE.DONE then print "Done" else print "error" print ret end if [ExecuteEngine] ret = SQL3ExecSingle(hDB, query$) print "SQL3ExecSingle() - "; ret if ret = SQLITE.DONE then print "SQL3ExecSingle() - ";ret else print SQL3ErrStr$(ret) end if return
|
|