Liberty BASIC Community Forum
« SQLite »

Welcome Guest. Please Login or Register.
Nov 24th, 2017, 10:52am


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


« Previous Topic | Next Topic »
Pages: 1 2  Notify Send Topic Print
 veryhotthread  Author  Topic: SQLite  (Read 2946 times)
Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5567
xx Re: SQLite
« Reply #15 on: Dec 2nd, 2014, 01:55am »

That error link is nothing to do with Liberty 4.04 as Liberty is now Windows compliant. Which version of Liberty are you using and what is your OS?

It is much more likely that you are trying to import too much of the database into Liberty. There is a 70mb data limit. This will change when Liberty 4.5 is released but for now you need to leave the data in the SQL database and import segments.
« Last Edit: Dec 2nd, 2014, 1:23pm by Rod » User IP Logged

hessu48
New Member
Image


member is offline

Avatar




PM


Posts: 2
xx Re: SQLite
« Reply #16 on: Dec 2nd, 2014, 04:33am »

Ok. I use 4.04 and win 7 x86.
I tried with 4.5 too but same thing.
I,ll wait for Lb 5. (with native sqlite)I use mysql with apache, but I want use sqlite in future.


That's ok, just wondwering what's wrong.

thanks for info.

User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5567
xx Re: SQLite
« Reply #17 on: Dec 2nd, 2014, 2:11pm »

OK I played some more. The sample code does not import data into liberty. However PRINTING the data seems to be the issue.

If you rem out one or other print statements that list the records forwards or backwards the problem goes away.

So the SQL database is sound and so too the dll, also Liberty, but I think you are coming across a Windows limit in listing 20k lines of 15 characters.

I know that a textbox has a Windows limit of something like 32k characters so there must be a memory limit assigned to the display window that is getting breached.
User IP Logged

hessu
New Member
Image


member is offline

Avatar




PM


Posts: 4
xx Re: SQLite
« Reply #18 on: Aug 20th, 2015, 11:44pm »

Hi Rod.
Now it,s fixed. there were another bug.

I have another problem.

SQL$ = "CREATE TABLE Food (namn,Livsmedelsnummer,E,P)"
SQL$ = "ALTER TABLE Food ADD (F,K)"

(it,s only two lines have been split here)
I'll have 62 fieldnames, so I need use Alter add.
But this code creates nothing
Can you help me with right code?

Thanks in advance

User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 194
xx Re: SQLite
« Reply #19 on: Aug 21st, 2015, 02:44am »

Hope this helps
Code:
 INPUT" enter new field name"; FiledName$


SQL$="ALTER TABLE Food ADD COLUMN "+ FieldName$
    calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
    IF result = 0 THEN
        calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
        print Winstring(result)
    END IF
 
User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
hessu
New Member
Image


member is offline

Avatar




PM


Posts: 4
xx Re: SQLite
« Reply #20 on: Aug 21st, 2015, 09:29am »

Thanks for answering

I think this is solution

SQL$ = "CREATE TABLE Food ( Foodname,Foodnumber,E,P)"
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
SQL$ = "ALTER TABLE Food ADD COLUMN F "
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long
SQL$ = "ALTER TABLE Food ADD COLUMN K "
calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, DBhnd as long,result as long

IF result = 0 THEN
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result)
END IF

Have nice day.
User IP Logged

hessu
New Member
Image


member is offline

Avatar




PM


Posts: 4
xx Re: SQLite
« Reply #21 on: Apr 19th, 2017, 11:15am »

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)

Open "SQ3_4_LB.dll" for DLL As #sq3

DB$ = "Food.sqlite"
overwrite = 0

'======================================================================================
calldll #sq3, "SQ3_4_LB_OpenDB",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

SQL$ = "Select * From Foos order by Foodgroup asc Limit 100 "
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_GetRecordsetValueOfRow", "," as ptr,result as long
If result then
line$=Winstring(RS.StrAdr.struct)
print line$
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
calldll #sq3, "SQ3_4_LB_ReleaseRecordset", result as void
calldll #sq3, "SQ3_4_LB_CloseDB", DBhnd as long,result as long
close #sq3

print "done"
end

What's wrong with this code. Sometimes it works well
and sometimes I get violation error and I don't know what's wrong.

« Last Edit: Apr 19th, 2017, 11:17am by hessu » User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5567
xx Re: SQLite
« Reply #22 on: Apr 20th, 2017, 05:36am »

Anywhere you create a variable or struct element that will hold a Windows handle ( pointer to a resource) you must use type ulong not long.

The debugger should show you which variables are causing the problem. DBhnd for one
User IP Logged

hessu
New Member
Image


member is offline

Avatar




PM


Posts: 4
xx Re: SQLite
« Reply #23 on: Apr 24th, 2017, 05:27am »

Thank you for your answer and advice.

I changed every (DBhnd as long) to DBhnd as ulong

Same resultat

I have 61 field$ in my table.
Maybe that's why it doesn't work





Code:
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)

Open "SQ3_4_LB.dll" for DLL As #sq3

DB$ = "Livs.db"
overwrite = 0
calldll #sq3, "SQ3_4_LB_OpenDB",DB$ as ptr, DBhnd as ulong

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

SQL$ = "Select * From Livsmedel Where Livsmedelgrupp = 2"
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as ulong,RS as struct, ret as long

    While RS.EOF.struct = 0
    x=x+1
        calldll #sq3, "SQ3_4_LB_GetRecordsetValueOfRow","," 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 x
    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 ulong,result as long

close #sq3
end


 


I got this again.

Error log timestamp 4/24/2017 11:12:0

The registers were:
EIP = 0x014b5664
ESP = 0x0012fb9c
EBP = 0x0012fbb8
EAX = 0x00000000
EBX = 0xffffecfb
ECX = 0x10051b24
EDX = 0x014fc9c2
EDI = 0x10051a5c
ESI = 0x1027b27c
CS = 0x0000001b
DS = 0x00000023
ES = 0x00000023
SS = 0x00000023
End Registers



Error log timestamp 4/24/2017 11:12:00
Message 1001:
a non-continuable protection violation has occurred. Check ERROR.LOG file.

And I have no idea what's wrong
User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5567
xx Re: SQLite
« Reply #24 on: Apr 24th, 2017, 11:22am »

You are still defining handles as type long instead of type ulong. Even in structs you have to use ulong. You also need to recognise that liberty always expects a return value from any api call. I don't use SQLite often enough to offer more than general advice.
« Last Edit: Apr 24th, 2017, 11:28am by Rod » User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5567
xx Re: SQLite
« Reply #25 on: Apr 24th, 2017, 1:18pm »

Without the db file it is hard to debug. But in general if you get a protection violation then the struct or api call has the wrong type defined for one of the variables.

Runnable code will get better help, you may need to zip a complete example for folks to try.
User IP Logged

Brandon Parker
Moderator
ImageImageImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 1118
xx Re: SQLite
« Reply #26 on: Apr 24th, 2017, 9:08pm »

I use SQLite in the main program that I develop.

I found that you have to use all long value types in the structure that the intermediary DLL is written in. This is most likely due to the data types that the DLL casts the structure elements as. As far as the handle for the database goes Rod is correct. It is best to use ulong data types for handles, but for the SQLite database I store it in a long data type; I vaguely remember having an issue when I first started using SQLite if I stored it in a ulong data type. The intermediary DLL is written in another version of BASIC that I own and one of these days I'll get around to to thoroughly going through the source code for it and checking it out.

I have also found that on some occasion when attempting to return large sets of records Liberty BASIC will error with a Protection Violation, but in my experience it has always occurred when attempting to call the "SQ3_4_LB_RecordsetMoveNext" function.

At this time I just have my program trap the error and reattempt a few times and if that doesn't work it will close SQLite and reinitialize it again. Then it will just continue on and most of the time works as intended.

I would be willing to create the database on my end as well and help you get going if you can give me the precise layout of the table(s) you are attempting to use. Providing the SQL statement that you used to create the table(s) will be perfectly fine.


{:0)

Brandon
User IP Logged

Windows 7 Home Premium 64-bit Intel(R) Quad Core(TM) i5 CPU M 430 @ 2.27GHz 4GB DDR3 RAM
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