|
Post by pierre on Apr 19, 2020 9:38:20 GMT -5
Yes, thank you Rod, I forgot to ask about that. I applied the semicolon at the end without really knowing why it was there. The reason is obvious, but could easiliy be overseen. I'll try not to forget it in the future.
pierre
|
|
|
Post by pierre on Apr 21, 2020 9:59:03 GMT -5
Chris, I owe you an apology. You were absolutely right about the 'SQL3DataCount' function. Even if its name could be somewhat ambiguous, this function actually counts the columns and not the records, as I wrongly supposed. The number of records is in fact implied in SQLite's answer to the prepared statement and the 'do ....loop while ...' section just goes through the recordset until exhaustion. N.B. I tried your functions with the 'SELECT ......WHERE .... = ....' and the 'SELECT .....WHERE ... LIKE...' clauses. No problems encountered. I'll continue testing. pierre
|
|
|
Post by ppro06 on Apr 23, 2020 1:22:40 GMT -5
Dear all, I am back again. Chris' wrapper has worked very well up to this point. I have come to realise that sqlite3.dll does not support importing/exporting data, they are only available as extensions here sqlite.org/src/file/ext/misc/fileio.c . That is also true for the other .dot commands which are not supported outside the sqlite command shell. SQLite documentation recommends using extensions [ie, sqlite3_load_extension()] to call said functions allows the program created to import and export data. Has anybody worked with library extensions in their code or is it better just to make new dlls to increase functions?
|
|
|
Post by Chris Iverson on Apr 23, 2020 1:43:59 GMT -5
It would be far, far easier to build the extension code as a separate DLL and have LB make a single call to that DLL to have it load itself into SQLite.
The extension plugin API for SQLite requires multiple callbacks, as well as the kind of pointer manipulation that is very difficult to do well in LB. It might be possible, but it would require a lot of overly verbose, arcane code to shuffle memory around as needed.
And that's assuming SQLite doesn't use multiple threads in the background for data processing. I don't think it does, but LB callbacks and multithreading do NOT mix.
I can take a look to see what would be needed to compile this; it honestly looks straightforward enough.
|
|
|
Post by Chris Iverson on Apr 23, 2020 17:16:20 GMT -5
Aaaand I got it working. Compiled straight from the SQLite3 source code, didn't have to edit it at all. I've attached fileio.dll to this post. Download it and put it in the same folder as sqlite.dll. I've made the following changes to the test program. First, new functions: 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 And using those functions to enable extension loading, and then tell SQLite to load the fileio extension(add these lines immediately after the database is opened): print "SQL3EnableLoadExtension() - ";SQL3EnableLoadExtension(hDB, 1)
print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "fileio") 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'));") Result: SQL3Init() - 0 SQL3Open() - 0 SQL3EnableLoadExtension() - 0 SQL3LoadExtension() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 stuff$ = someoas idjrlkawergj
a b 12 blah 13 test data 153 someoas idjrlkawergj
SQL3Finalize() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0 Or, using SQL queries to browse the filesystem: Reading a file: SQL3Init() - 0 SQL3Open() - 0 SQL3EnableLoadExtension() - 0 SQL3LoadExtension() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 stuff$ = someoas idjrlkawergj SQL Query - SELECT * FROM fsdir('test.txt');
name mode mtime data test.txt 33206 1587677864 test data
SQL3Finalize() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0 Listing files in a tree(all files are walked down the tree): SQL3Init() - 0 SQL3Open() - 0 SQL3EnableLoadExtension() - 0 SQL3LoadExtension() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 stuff$ = someoas idjrlkawergj SQL Query - SELECT name,mode,mtime FROM fsdir('.') WHERE name NOT LIKE './bld/%' AND name NOT LIKE './sqlite/%';
name mode mtime . 16895 1587680044 ./bld 16895 1587676014 ./ERROR.LOG 33206 1587156939 ./fileio.dll 33206 1587677281 ./folder 16895 1587678060 ./folder/ERROR.LOG 33206 1587156939 ./folder/test.txt 33206 1587677864 ./sqldiff.exe 33279 1580156852 ./sqlite 16895 1587675617 ./sqlite.zip 33206 1587675163 ./sqlite3.bas 33206 1587680042 ./sqlite3.def 33206 1580156954 ./sqlite3.dll 33206 1580156954 ./sqlite3.exe 33279 1580156915 ./sqlite3_analyzer.exe 33279 1580156877 ./test.db 33206 1587680044 ./test.txt 33206 1587677864
SQL3Finalize() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0 (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.) Attachments:fileio.dll (141 KB)
|
|
|
Post by ppro06 on Apr 24, 2020 0:26:02 GMT -5
Aaah super! and not a lot of lines required. Thanks, now I know what to do to include the needed functions.
|
|
|
Post by pierre on Apr 24, 2020 8:13:47 GMT -5
So, this was meant to show how to input files in SQLite and save them back to disk. Very handy extension, now it can be done from within SQLite.
I got the same result by reading a blob file for binary into a Liberty BASIC string variable, insert it into the database, then reading the database,
assign the blob value to another string variable and save it back to disk as a binary file. By all means, this shows the power that can be given to SQLite using the C language interface. But one has to know C and have a C compiler at one's disposal. That is unfortunately not my case. I see a very interesting extension, that is apparently not in the "old" wrapper: the 'sqlite3_db_dump' function. www.sqlite.org/src/artifact/baf6e37447c9d696** This file implements a C-language subroutine that converts the content ** of an SQLite database into UTF-8 text SQL statements that can be used ** to exactly recreate the original database. ROWID values are preserved. Would that be possible in Liberty BASIC 4.5.1 ? pierre
|
|
|
Post by ppro06 on Apr 27, 2020 7:39:57 GMT -5
Hello Pierre, I think it should be possible,if I understand you correctly,by linking the library in the same way as for this fileio function. Also there some open-sourced c-compilers you can use to make the shared library from the function source code provided you set the path to the sqlite3ext header file. The easiest I have found is MinGW.
|
|
|
Post by pierre on Apr 27, 2020 9:31:08 GMT -5
OK ,thank you, ppro06. I'll check it out. Hope I'll be able to understand all that.
I thought of this extension, because it has the same function as the '.dump' command in the SQLite console shell. I use that command to backup a database. I just found the SQLite 'VACUUM INTO .....' command that appears to do about the same thing from within the database connection. So I'll give it a try too.
pierre
|
|
|
Post by meerkat on Apr 27, 2020 9:53:38 GMT -5
Not sure exactly what you are trying to do. If you are simply trying to dump and reload a DB, it should already be done in a program called 'ionSQL.bas'. I think I still have a copy somewhere. With it you can dump the DB with options to [export] the schema and data or both. Data can be dumped with full inserts, and the option to [export] numeric data without the quotes. You can load it back with a [import] button. Or click the [SQL] button and cut and paste it into the provided screen and execute it..
Not sure this is what you are after, so if not, ignore this..
Dan
|
|
|
Post by Chris Iverson on Apr 27, 2020 11:46:57 GMT -5
Unfortunately, the sqlite db dump won't work the same way that the fileio extension did. It uses a weird callback setup where it calls your function for each line it's saving into a file, and you're responsible for doing something with it. It would take a little work to turn that into a purely external DLL, or to set up a callback in LB to handle saving the file.
I can take some time to take a quick look at how it would be done, but it's not going to be quite as simple as fileio or some of the other extensions would be.
I've not used ionSQL myself, but I've heard of it. If it can provide a proper dump of SQLite DBs, it's probably worth checking out.
|
|
|
Post by pierre on Apr 27, 2020 11:54:36 GMT -5
oh yes, of course, thank you, meerkat and Chris, that was actually what I wanted to do, dump a database.
I know ionSQL, but there are many things to discover in that program and until now I hadn't paid attention to the Export options.... So, apparently, problem solved !
Thanks again to both of you and forgive my blindness..... I was just focused on the functions Chris proposed recently and I really did'n know how to handle that callback problem.
pierre
|
|
|
Post by meerkat on Apr 27, 2020 14:02:10 GMT -5
Ya! (eye on SQLite). It's something I wrote a long time ago. I think there are a few bugs. So if anyone runs into any problems please let me know. The [SQL] button is very useful for trying out SQL statements before you put it in code. It gens BASIC code from your SQL commands. If I remember correctly, you should be able to look at any DB, create and modify tables, and index.Do list with sorts with Add,Change,Delete. Import and export CSV or Schema and data. That's about all I remember. I use the RB version because of the added features of RB.
Dan
|
|
|
Post by pierre on Apr 27, 2020 16:22:29 GMT -5
ionSQL:
As far as I can remember, I had a few small problems but I didn't consider them to be real bugs:
1) In some imported database, or a database created in DB Browser for SQLite, table names and field names were written between double quotes. ionSQL didn't like that. Recreating the tables without the double quotes solved the problem.
2) ionSQL got confused when reading field values with embedded commas. The program apparently interpreted the comma as a field separator. Leaving out the commas from the field values solved the problem.
3) In your recent example given for the 'simple contact database', you wrote in the [dispGrid] section: colTot = colTot + (siz * 10) + 5. That did'nt work well, apparently the spacing between the A C D S columns was not correct. My copy of ionSQL generated the values 13 and 6. With these values no more problems.
4) Generally, your programs prefer the standard field types like 'varchar(20)' , to the SQLite 'text' field type.
That is all I can remember for the moment, the study of ionSQL remaining for me a 'work in progress'.
pierre
|
|
|
Post by meerkat on Apr 27, 2020 17:35:09 GMT -5
Thanks pierre for the info.. ionSQL was the first program I wrote in LB. So I was learning LB as I wrote it, and didn't exactly understand what I was doing. There has to be a better way to do this than the interface I came up with. It probably should be re-written now that I understand LB a little better.
I'm out of town for the rest of the week, when I get back, I'll have a look at it. Feel free to make any changes you come up with. If you do, please post the changes. Or better yet, here is your chance to re-write it. You gotta know LB a lot better than I do..
Thanks again.. Dan
|
|