Liberty BASIC Community Forum
« SQLite »

Welcome Guest. Please Login or Register.
Apr 28th, 2017, 4:47pm


Rules|Home|Help|Search|Recent Posts|Notification


« Previous Topic | Next Topic »
Pages: 1 2  Notify Send Topic Print
 veryhotthread  Author  Topic: SQLite  (Read 2316 times)
tbohon
Full Member
ImageImageImage


member is offline

Avatar

"My mind is like a steel whatchamacallit ... !"


PM

Gender: Male
Posts: 128
xx SQLite
« Thread started on: Apr 6th, 2014, 8:02pm »

I see random questions and examples re: SQLite and LB but have no idea where to go to get the interface information I need to be able to use it. Several posts talk about an LB .dll file which isn't available from lbdownloads.com any longer ... mainly because lbdownloads.com is a totally different site now.

Any thoughts/pointers?

Tnx.

Tom
User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5251
xx Re: SQLite
« Reply #1 on: Apr 7th, 2014, 03:12am »

The actual .dll is available from SQL.

https://sqlite.org/2014/sqlite-dll-win32-x86-3080403.zip

However code to use it is a bit thinner on the ground.

Are you sure your database needs are that demanding? I ask because Liberty's random access file can produce pretty robust databases.
User IP Logged

tbohon
Full Member
ImageImageImage


member is offline

Avatar

"My mind is like a steel whatchamacallit ... !"


PM

Gender: Male
Posts: 128
xx Re: SQLite
« Reply #2 on: Apr 7th, 2014, 09:44am »

Thanks Rod. Had the .dll but wasn't sure if some sort of wrapper had been produced for LB.

As to using the LB random access file I'm going to need to store a 'comments' item ... basically tracking various training activities and exercise participation ... and don't see how that would work in a random access file. It will basically be a 'blob' of data, changing and different for each team member.

Unless I'm missing something ... ?
User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5251
xx Re: SQLite
« Reply #3 on: Apr 7th, 2014, 12:04pm »

Well databases are supposed to be regimented. But often blob data is stored elsewhere and only the index to that data stored in the database.

Depends how often the data changes and how rapidly it changes in size. But there would be nothing wrong in holding a file for every member.

But then it's not really a database, which is ordered to aid analysis, selection, sorting and listing.

There was a wrapper.dll for Liberty but it is no longer accessible. Someone may have a copy of the wrapper on file and hopefully the demo.bas
User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 165
xx Re: SQLite
« Reply #4 on: Apr 8th, 2014, 04:05am »

this may help.....PART 1
Code:
' Program to test SQ3_4_LB.dll
'
' expects SQ3_4_LB.dll and sqlite3.dll to be in programs directory
'
'
' This struct must be exactly 28 bytes long.
' It is a copy of a struct that exists in SQ3_4_LB.dll and is
' used to navigate in the results table returned by sqlite3.dll
Struct RS,_
    BOF     as long,_       ' is True when CurrPos = 1
    EOF     as long,_       ' is True when CurrPos = Rows
    Handle  as long,_       ' address of recordset data returned by sqlite3.dll (dont't use)
    Rows    as long,_       ' number of rows in recordset
    Cols    as long,_       ' number of columns in recordset
    CurrPos as long,_       ' current row in recordset
    StrAdr  as long         ' address of data item (pointer to a string)

' note: GetRecordset and MoveFirst set  CurrPos = 1 , BOF = 1 and EOF = 0
'       even if there is only one row in recordset.
'       By using  While EOF = 0 : GetValue : MoveNext : Wend
'       you can browse through the rows.
'       EOF is set to 1 by MoveNext when end of recordset is reached.
'       MoveLast/MovePrevious work accordingly.

' open SQ3_4_LB.dll
Open "SQ3_4_LB.dll" for DLL As #sq3

' detecting if sqlite3.dll could not be loaded by SQ3_4_LB.dll
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
msg$ = Winstring(result)
' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'"
if instr(msg$,"Error") then
    print msg$ : close #sq3 : end
end if

' testing some functions that return a string
calldll #sq3, "SQ3_4_LB_GetLibVersion",result as long
print "Version of sqlite3.dll  : v"+Winstring(result)

calldll #sq3, "SQ3_4_LB_Version",result as long
print "Version of SQ3_4_LB.dll : v"+Winstring(result)
print
' show About string
'calldll #sq3, "SQ3_4_LB_About", result as long
'print Winstring(result)
'print

' create a database-file   overwrite = 1 means existing file will be deleted
DB$ = "sample.db"
overwrite = 1
calldll #sq3, "SQ3_4_LB_CreateDB",DB$ as ptr, overwrite as long, DBhnd as long
If DBhnd = 0 then
    calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
    print Winstring(result) : close #sq3 : end
end if

' Create test-table to fill with data
t1   = time$("ms")
SQL$ = "Create Table TestTable (fld0 TEXT UNIQUE, fld1, fld2)"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long

SQL$ = "BEGIN TRANSACTION"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long

for n = 1 to 1000
    SQL$ = "INSERT Into TestTable (fld0, fld1, fld2) Values ('value_1_"_
      + Str$(n) + "', 'value_2_" + Str$(n) + "', 'value_3_" + Str$(n) + "')"
    calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
next n

SQL$ = "COMMIT"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
' note: Execute returns #True or #False
' you can display an error message like this
if result = 0 then
    calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
    print Winstring(result)
end if

t2   = time$("ms")

' determine count of records
SQL$ = "Select Count(*) As CountOfRecords From TestTable"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
if ret then
    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByName","CountOfRecords" as ptr, ret as long
    if ret then
        print Winstring(RS.StrAdr.struct);" Records in ";t2-t1;" milliseconds inserted"
    else
        calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
        print Winstring(result)
    end if
else
    calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
    print Winstring(result)
end if

print
print "Creating a recordset with 5 rows"

SQL$ = "Select * From TestTable Limit 5"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
if ret then
    print
    print "Amount of columns: "; RS.Cols.struct
    print "Amount of rows   : "; RS.Rows.struct
    print

    ' note: field index is zero based
    ' we have 3 fields in our recordset with indexes 0,1,2
    calldll #sq3, "SQ3_4_LB_GetFieldIndexByName","fld2" as ptr, index as long
    print "Index of the field named 'fld2': ";index
    'note: GetFieldIndexByName will return -1 if you specify a name that is not found in the recordset
    print

    calldll #sq3, "SQ3_4_LB_GetFieldNameByIndex",1 as long, ret as long
    print "Name of field with Index 1: ";Winstring(ret)
    'note: GetFieldNameByIndex will return an empty string if you specify an invalid index

    print
    print "SQ3_4_LB_RecordsetMoveNext():"
    print "-----------------------------"

    ' Browse through the records forward
    While RS.EOF.struct = 0
        calldll #sq3, "SQ3_4_LB_GetRecordsetValueByName","fld0" as ptr, result as long
        If result then
            print Winstring(RS.StrAdr.struct)
        Else
            calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
            print Winstring(result)
        End If
        calldll #sq3, "SQ3_4_LB_RecordsetMoveNext", result as void
    Wend

    print
    print "SQ3_4_LB_RecordsetMovePrevious():"
    print "---------------------------------"

    ' Browse through the records backwards
    While RS.BOF.struct = 0
        calldll #sq3, "SQ3_4_LB_GetRecordsetValueByName","fld0" as ptr, result as long
        If result then
            print Winstring(RS.StrAdr.struct)
        Else
            calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
            print Winstring(result)
        End If
        calldll #sq3, "SQ3_4_LB_RecordsetMovePrevious", result as void
    Wend

    print
    print "SQ3_4_LB_RecordsetMoveFirst():"
    print "---------------------------------"

    ' Jump to the first row in recordset
    calldll #sq3, "SQ3_4_LB_RecordsetMoveFirst", result as void
    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByName","fld0" as ptr, result as long
    If result then
        print "Content of field 'fld0' of the first row (ByName):"
        print Winstring(RS.StrAdr.struct)
    Else
        calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
        print Winstring(result)
    End If

    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByIndex",0 as long, result as long
    If result then
        print "Content of field 'fld0' of the first row (ByIndex):"
        print Winstring(RS.StrAdr.struct)
    Else
        calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
        print Winstring(result)
    End If
 
« Last Edit: Apr 8th, 2014, 04:06am by metro » User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 165
xx Re: SQLite
« Reply #5 on: Apr 8th, 2014, 04:06am »

PART 2
Code:
    print
    print "SQ3_4_LB_RecordsetMoveLast():"
    print "---------------------------------"

    ' Jump to the last row in recordset
    calldll #sq3, "SQ3_4_LB_RecordsetMoveLast", result as void
    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByName","fld0" as ptr, result as long
    If result then
        print "Content of field 'fld0' of the last row (ByName):"
        print Winstring(RS.StrAdr.struct)
    Else
        calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
        print Winstring(result)
    End If

    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByIndex",0 as long, result as long
    If result then
        print "Content of field 'fld0' of the last row (ByIndex):"
        print Winstring(RS.StrAdr.struct)
    Else
        calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
        print Winstring(result)
    End If

    ' Release Recordset after use
    calldll #sq3, "SQ3_4_LB_ReleaseRecordset", result as void

else
    calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
    print Winstring(result)
End If

' change someting in database
print
print "change fld2 in record 500 to 'new value in record 500'"
print "Last Message: ";
SQL$ = "UPDATE TestTable SET fld2 = 'new value in record 500' WHERE fld0 = 'value_1_500'"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result)

print
print "searching for the changed record"
print "Last Message: ";
SQL$ = "SELECT * FROM TestTable WHERE fld2 = 'new value in record 500'"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result)

' retrieve the changed row
print
print "content of record 500 is :"
if RS.Rows.struct then
    ' no need for a loop here but the only use for MoveToRow seems to be a for next loop
    for n = 1 to RS.Rows.struct
        calldll #sq3, "SQ3_4_LB_RecordsetMoveToRow",n as long, result as void
        calldll #sq3, "SQ3_4_LB_GetRecordsetValueOfRow", "|" as ptr,result as long
        if result then print Winstring(RS.StrAdr.struct)
    next n
else
    print "Query had no result"
end if

' now create an error
print
print "issuing an invalid command: xyz"
print "Last Message: ";
SQL$ = "xyz"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result)
print

print "LastInsertRowID was:"
calldll #sq3, "SQ3_4_LB_LastInsertRowID",DBhnd as long, RowID as long
print RowID
' this returns 1000 as we INSERTed 1000 records
' (not 500 where we UPDATEd a field)
print

' what kind of characters can be written to a field ?
' Chr$(0) can not be used of course
x$ = "": for n = 1 to 255 : x$ = x$ + chr$(n) : next n
' note: there is a chr$(39) single quote character in x$
' which is a string delimiter in SQL and would cause a syntax error
' we avoid this error by doubling the single quote character
z$ = CloneSingleQuote$(x$)
print "write all character values except zero to a field:"
SQL$ = "UPDATE TestTable SET fld2 = '"+ z$ +"' WHERE fld0 = 'value_1_500'"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
SQL$ = "SELECT fld2 FROM TestTable WHERE fld0 = 'value_1_500'"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
calldll #sq3, "SQ3_4_LB_GetRecordsetValueByIndex",0 as long, result as long
y$ = Winstring(RS.StrAdr.struct)

if y$ = x$ then
    print "retrieved ok"
else
    print "strings are not equal"
end if
print

' some useful functions:
print "Table Names of our DataBase:"
SQL$ = "SELECT tbl_name FROM sqlite_master WHERE TYPE = 'table' ORDER BY tbl_name"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
While RS.EOF.struct = 0
    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByIndex",0 as long, result as long
    print Winstring(RS.StrAdr.struct);" ";
    calldll #sq3, "SQ3_4_LB_RecordsetMoveNext", result as void
Wend
print : print

print "Index of Table TestTable:"
SQL$ = "SELECT * FROM sqlite_master WHERE TYPE = 'index' AND tbl_name = 'TestTable'"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
if RS.Rows.struct then
    While RS.EOF.struct = 0
        calldll #sq3, "SQ3_4_LB_GetRecordsetValueByIndex",1 as long, result as long
        print Winstring(RS.StrAdr.struct)
        calldll #sq3, "SQ3_4_LB_RecordsetMoveNext", result as void
    Wend
else
    print "There is no Index in TestTable"
end if
print

' Schema is the SQL string that created the Table
print "Schema of Table TestTable:"
SQL$ = "SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) "
SQL$ = SQL$ + "WHERE tbl_name = 'TestTable' AND TYPE != 'meta' ORDER BY TYPE DESC, name "
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long

While RS.EOF.struct = 0
    calldll #sq3, "SQ3_4_LB_GetRecordsetValueByIndex",0 as long, result as long
    ' there are two rows in recordset but the second is empty don't know why
    x$ = Winstring(RS.StrAdr.struct) : if x$ <> "" then print x$
    calldll #sq3, "SQ3_4_LB_RecordsetMoveNext", result as void
Wend
print


print "Field Names of Table TestTable:"
SQL$ = "Select * From TestTable Limit 1"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as struct, ret as long
for n = 0 to RS.Cols.struct -1
    calldll #sq3, "SQ3_4_LB_GetFieldNameByIndex",n as long, ret as long
    print Winstring(ret);" ";
next n
print


calldll #sq3, "SQ3_4_LB_ReleaseRecordset", result as void
' note: above there was not always a GetRecordset followed by a ReleaseRecordset
' because GetRecordset releases the previously used memory by itself.
' but in an event driven program it would be better to explicitly call ReleaseRecordset

calldll #sq3, "SQ3_4_LB_CloseDB", DBhnd as long,result as long
close #sq3
print
print "done"
end


function CloneSingleQuote$(in$)
    i = 1
    do
        i =  instr(in$, "'", i)
        if i then
            in$ = left$(in$,i) + "'" +  mid$(in$,i+1)
            i = i + 2
        end if
    loop while i
    CloneSingleQuote$ = in$
end function
 


take a look at https://groups.yahoo.com/neo/groups/libertybasic/files
Colin McMurchie has some stuff on databases including Sqlite and also LaurieM has SqLite info at the same location

good luck

EDIT you posted whilst I was modifying my post Rod the Dll is located under LaurieM at Yahoo groups
« Last Edit: Apr 8th, 2014, 04:16am by metro » User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5251
xx Re: SQLite
« Reply #6 on: Apr 8th, 2014, 04:08am »

It's the SQ3_4_LB.dll that we don't have access to. if you have a copy can you make it accessible, perhaps place it in the wiki?
« Last Edit: Apr 8th, 2014, 04:10am by Rod » User IP Logged

tbohon
Full Member
ImageImageImage


member is offline

Avatar

"My mind is like a steel whatchamacallit ... !"


PM

Gender: Male
Posts: 128
xx Re: SQLite
« Reply #7 on: Apr 8th, 2014, 09:47am »

on Apr 8th, 2014, 04:08am, Rod wrote:
It's the SQ3_4_LB.dll that we don't have access to. if you have a copy can you make it accessible, perhaps place it in the wiki?


That's the dll I was originally looking for when my search started so yes, if someone has it and can add it to the wiki that would be fantastic!
User IP Logged

tbohon
Full Member
ImageImageImage


member is offline

Avatar

"My mind is like a steel whatchamacallit ... !"


PM

Gender: Male
Posts: 128
xx Re: SQLite
« Reply #8 on: Apr 8th, 2014, 11:26am »

FOUND IT!!!!! Look on the LB forum on Yahoo under Files and then 'LauriM'. There is a directory named SQLite and in that directory is the elusive SQ3_4_LB.dll file.

I also added it to the Wiki but someone might want to double check to ensure I did that correctly, this is my first time on this particular Wiki. I DID notice that formatting is off on the Shared Code page ... indent for my entry didn't carry through for some reason .. huh

Thanks all for the direction -- I'd say I was 'off' but too many people would probably agree ... grin

Appreciate the help (again ... still ... one more time ...)
« Last Edit: Apr 8th, 2014, 11:44am by tbohon » User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5251
xx Re: SQLite
« Reply #9 on: Apr 8th, 2014, 11:52am »

A direct link to the wrapper file


Edit by Stefan: replaced session link, that is not accessible by others, with regular link
« Last Edit: Apr 8th, 2014, 4:15pm by Stefan Pendl » User IP Logged

Pablo S.
New Member
Image


member is offline

Avatar




PM


Posts: 23
xx Re: SQLite
« Reply #10 on: May 21st, 2014, 5:21pm »

Yahoo ask me my mobile. Can somebody upload the wrapper file to a public access site like depositfiles or similar?

Best regards,
-Pablo
User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5251
xx Re: SQLite
« Reply #11 on: May 22nd, 2014, 04:24am »

I put a copy here

http://gamebin.webs.com/Liberty/SQ3_4_LB.zip
User IP Logged

Pablo S.
New Member
Image


member is offline

Avatar




PM


Posts: 23
xx Re: SQLite
« Reply #12 on: May 22nd, 2014, 3:21pm »

Rod,

Thank you so much!

-Pablo
User IP Logged

hessu48
New Member
Image


member is offline

Avatar




PM


Posts: 2
xx Re: SQLite
« Reply #13 on: Dec 1st, 2014, 9:56pm »

Hi there.
I,ve tried use this sqlite example part 1 and part 2
Everything goes right, but if I insert 10000 records and
try to read records without Limit 5 then I get this error
message after about 8700 rows

a non-continuable protection violation has occurred. Check ERROR.LOG file.

Tried use Ulong instead lond with handles, but no success.
What's wrong sad
User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 165
xx Re: SQLite
« Reply #14 on: Dec 2nd, 2014, 12:43am »

Hmm, I tried 10,000 records and had no issue
I have a reverse rearch phone book with 472,000 records and no issues
Code:
SQL$ = "Select * From TestTable "  

a quick google found this
http://justbasic.conforums.com/index.cgi?board=editor&action=print&num=1295153183
could be relevant
User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
Pages: 1 2  Notify Send Topic Print
« Previous Topic | Next Topic »

Rules|Home|Help|Search|Recent Posts|Notification

Donate $6.99 for 50,000 Ad-Free Pageviews!

| |

This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls