SQLite looking at the examples

[F_Dixon]

im reading some info about sqlite I am interested in using the export command as well as the import.
I ran the sample and wrote to the disk, it was not very quick, I though I would try using the in memory and just load and save the data as needed. I havent found the right way to use the sqlite_exe to access some of the other commands.

maybe i would be better off just using the built in data classes.

[F_Dixon]

when i ran one of the sqlite in memory sample that made and wrote to the DB in a loop it was very quick.
I changed the line to give the DB a file name and it wrote it to disk.
I did not see the line that said to change it from 10,000 to 1,000
items...it took 137.63 seconds to do 1000. never waited for the 10,000 to finish.
I thought that was pretty slow for what I wanted to do. I was thinking about 5000 lines and that was to much time I thought.

my next idea was to use a in memory date base and then save it to disk, but I dont see a away to do that yet. I cant tell sqlite to change modes with TB.
I started to read a small tutorial on sqlite this morning and it mentioned it the 4th paragraph that sqlite was transactional and very slow to do many small actions. That to wrap them with a BEGIN AND A COMMIT statement.
I added 2 lines to the example and the 1000 lines went from 137.63 seconds to run to .149 second. wow.

SQLite_Exe ("BEGIN;")
SQLite_Exe ("COMMIT;")

was the only change. before and after the for/next.

[F_Dixon]

check out this vba code to use winsqlite.dll
https://renenyffenegger.ch/notes/dev...Lite/VBA/index
and
https://github.com/ReneNyffenegger/winsqlite3.dll-4-VBA
code for using and test.

well maybe not so good after all

[F_Dixon]

I am working on the example i found for the winaqlite3.dll
I figured it i could get this to work I would look at the regular sqlite.
a lot of the work on it is done or close, i am learning as i go.

The little bit i have below is my starting point.
I have changed the types to get it so i can step through it.
my question is about function openDB(
one of the params is the openDB im pretty sure that is a file handle for the database file.
> file handles are just a reference id so that i can refer to it later i think <
that is what i am asking. is if i am right about that.
I will show what i have figured out below the first code. it pretty ugly.

so im just askin about the file handle.
the pointer stuff is ugle but i think i got it mostly right.

I do have another question, now that i mentioned the pointers.

some of the function return a pounter, i have no idea how to get a function to return a pointer with varptr or codeptr.
could i use a type ?

thanks for looking at this.
Fred.

'==================================================
declare ptrSafe function sqlite3_open        lib "winsqlite3.dll" (  _
     byVal    zFilename        as string , _
     byRef    ppDB             as longPtr  _
) as longPtr ' }



'==================================================
function openDB(fileName as string) as longPtr ' {

    dim res as longPtr

    res = sqlite3_open(fileName, openDB)
    if res <> SQLITE_OK then
       err.raise 1000, "openDB", "sqlite_open failed, res = " & res
    end if

    debug.print("SQLite db opened, db = " & openDB)

end function ' }




'==================================================
sub main() ' {

    dim db as longPtr

    db = openDB(environ("temp") & "\test.db")

****************************************************************
****************************************************************

function openDB(  fileName as string) as long ' {
    dim res as long
    '----
    local varptr2 as long 
    local varptr2_ as long value 2
    varptr2 =  varptr(varptr2_)
    local n as long value peek(varptr2)

    res = sqlite3_open(fileName, varptr2)
    if res <> %SQLITE_OK then
       err.raise 1000, "openDB", "sqlite_open failed, res = " & res
    end if

    debug.print("SQLite db opened, db = " & openDB)

end function ' }

'===========
sub tbmain() ' {

    'dim db as dword 'longPtr
    dim db_ as long     'dim db as long value VarPtr(db_)
    
    dim filenamePTR_ as asciiz
    filenamePTR_ = ".\mytest.db"
    dim filenamePTR = varptr(filenamePTR_)

'[breakpoint] <Any note here. Breakpoint will be set to line following this statement>
    'db = openDB(environ("temp") & "\test.db")
   'db = openDB(".\test.db")
    'call  openDB(".\test.db") to varptr(db) ' ->-> db = openDB(".\test.db")
    call  openDB( filenamePTR_ ) to varptr(db)
'[book] <openDB(>

'===========
'[book] <' sqlite3_open {
declare  function sqlite3_open        lib "winsqlite3.dll" ALIAS "sqlite3_open" (  _
     byVal    zFilename        as string , _
     byRef    ppDB             as long'Ptr  _
) as long'Ptr ' }

[etermark]

If you’re finding the disk writes slow, going in-memory is definitely a good move! It can speed things up a lot, especially for temporary data. As for accessing those other commands with sqlite_exe, it can be a bit tricky if you're just starting out. Honestly, if you're mostly working with simple data, using the built-in data classes might save you some hassle.