Page 1 of 3
4.2 Beta SQLite Questions
Posted: Tue Jan 08, 2008 5:06 pm
by Karbon
Sorry guys, I'm not sure about the most appropriate place to post this.
Can someone elaborate on the SQLite functions in the new beta? Is it just the ODBC driver bundled with PB, or some sort of native access? How do you use it? The ReadMe only mentions "UseSQLiteDatabase()" - nothing about what it does or how to use it.
Posted: Tue Jan 08, 2008 5:43 pm
by bobobo
You use the usual PB_DatabaseFunktions
UseSQLiteDatabase()
OpenDatabase(0,"d:\slqlitedb\erwin.db","","") will open an sqlitedb
..
..
CloseDatabase(0)
Remark:
you can use SQLITEDatabases OR ODBCDatabases but not both
together in one program.
--------
http://www.purebasic.fr/english/viewtop ... highlight=
Posted: Tue Jan 08, 2008 5:48 pm
by Karbon
OK, so it's *not* ODBC. Great!
I don't see any userlibs or libraries in the beta update package so what version of SQLite is supported? Can the SQLite DLL be dropped-in to support the most recent? Does it support Unicode, thread safety?
Posted: Tue Jan 08, 2008 5:57 pm
by freak
The sqlite library is statically linked. Its the file "PureLibraries\Windows\Libraries\sqlite3.lib"
I think it is the most recent version.
Unicode is supported. I am not sure about thread safety. Have to ask Fred there.
Posted: Tue Jan 08, 2008 6:01 pm
by Karbon
Thanks freak!
Is there some facility for updating that library easily? Hipp releases SQLite versions every couple of months and they generally contain worthy fixes and new features.
Either way, I'm recompiling kBilling to use the new beta version right now. A few bug reports to follow
Posted: Tue Jan 08, 2008 6:17 pm
by freak
Some investigation in the sources has shown that the lib is threadsafe, with the exception
of the DatabaseError() function which uses a global buffer. I am sure this can be changed
before the final release as well.
As for the updating: I will see if we can add a dll option for easier updating.
Posted: Tue Jan 08, 2008 6:21 pm
by Karbon
Thanks freak!!
Posted: Wed Jan 09, 2008 1:09 am
by pdwyer
freak wrote:
As for the updating: I will see if we can add a dll option for easier updating.
Nice! I'd like this too for going back versions. v3.5 is new and has some issues for me still so I'm using an older DLL generally. (still v3 though).
I can see why the lib would be nice in some situations too though
Posted: Wed Jan 09, 2008 1:18 am
by Karbon
What issues? I've been an avid user of SQLite for years and have yet to have a single issue with it (coming from way back in the early 2.X versions)
Posted: Wed Jan 09, 2008 2:12 am
by pdwyer
non unicode Japanese (or other DBCS) . Probably doesn't effect many people.
The "Like" part of a a select statement in version after about 3.34 (I think) attempt to force non case sensitivity on charachers under chr(127) but this can mean that a select statement will pick up more kanji that it's supposed to. In the future I can move off codepage 932 to unicode but it's just a hassle updating the database. Sometimes I don't want to use unicode apps due to the file formats that I'm working with so I occaisonally need to stick with the old versions of sqlite3 (which work fine otherwise).
On the SQLite bug list they just said "use a different format" but thats not always easy in the short term, especially when it was working fine.
Considering SQLite's fast updates, PB is going to need to think about their update policy for the lib. Some developers might be wanting to use a new version that fixes a critical (to them) bug while others find that a new version breaks their code. Before I start using PB's lib for anything serious I will want to know whether it will stay as is or keep pace (both have advantages but I'll want to know what I'm getting the fture of an app involved with)
Posted: Wed Jan 09, 2008 2:14 am
by Karbon
Ah, yes, I remember hearing about the Japanese problems.
You're absolutely right about the updates - that's why I brought up DLL support. I'm sure freak will work his magic and make it happen.
Posted: Mon Feb 04, 2008 10:50 pm
by the.weavster
freak wrote:Some investigation in the sources has shown that the lib is threadsafe
Do you mean using multiple threads to access a SQLite database? I did a test with an early release of SQLite 3 and it had issues.
I had two threads, with thread 1 I started a transaction using "BEGIN IMMEDIATE" I then made exactly the same call with thread 2 but I did not receive the SQLITE_BUSY error code. Both threads then made a sequence of updates which included retrieving the last insert ID and saving it in another field of the current record. When I looked at the results of the updates in the SQLite table the transactions had become conjoined producing unpredictable results.
If you're updating SQLite from different threads you may want to use a global variable as a flag that gets raised when you start an update and lowered at the end.
Posted: Wed Feb 06, 2008 4:57 am
by pdwyer
I think I remember reading about this, and I'll see if I can find it again.
SQLite is connection safe so that if you use multiple threads you should have each thread open it's own connection the the database.
something like that
Posted: Wed Feb 06, 2008 5:00 am
by pdwyer
here
(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?
http://www.sqlite.org/faq.html#q5
(6) Is SQLite threadsafe?
http://www.sqlite.org/faq.html#q6
There's some things to know and it changed a little while back so if you did your tests with a version before 3.3.1 you might have had something different happen there
Problems with Unicode
Posted: Sun Mar 16, 2008 3:23 pm
by thanos
Hello and regards to all of you.
I'm a PureBasic and SQLite newbie and i tried to made a simple application which retrieves data from a SQLite database.
I read in this post by Freak that unicode it is supported.
freak wrote:The sqlite library is statically linked. Its the file "PureLibraries\Windows\Libraries\sqlite3.lib"
I think it is the most recent version.
Unicode is supported. I am not sure about thread safety. Have to ask Fred there.
When i try to retrieve the data from one table i always take the following results:
Instead of the correct:
(The above screen is from a SQLite data manager).
I already choose UTF-8 at the Compiler Options on the PureBasic environment.
Do you have any idea how can i solve this problem?
Regards.
Thanos
Here is my code:
Code: Select all
EnableExplicit
Declare DoEvents()
Declare WindowProc()
Enumeration
#MainWindow
#ListIcon
#ProgressBar
#FontForListIcon
EndEnumeration
;- Constants
#Database = 0
#Total_Records = 15 ;- Just set a limit on retrieving records
;- Variables
Global Result.l
Global SQLiteDatabaseName$ = "C:\Aktinologos.db"
Global User$ = ""
Global Password$ = ""
Global CurrentRec.l = 0
Global strOutPut.s
Global Epwnymo.s
Global Epwnymo_uc.s
;-Start
Result = UseSQLiteDatabase()
If Result
Result = OpenDatabase(#Database, SQLiteDatabaseName$, User$, Password$)
If Result
If DatabaseQuery ( #Database , " SELECT * FROM Prom_Pel")
WindowProc() ;- Open the main gui
;-
CurrentRec = 1
While NextDatabaseRow(#Database) ;- Loop for each record
strOutPut = ""
strOutPut + GetDatabaseString(#Database, 1)
strOutPut + Chr(10) + GetDatabaseString(#Database, 2) + Chr(10) + GetDatabaseString(#Database, 3)
strOutPut + Chr(10) + GetDatabaseString(#Database, 4) + Chr(10)
;- Check if is it necessary to terminate the loop
If CurrentRec > #Total_Records
Break ;- Exit loop
EndIf
AddGadgetItem(#ListIcon, -1, strOutPut)
CurrentRec + 1
SetGadgetState(#ProgressBar, CurrentRec) ;- Update progress bar value
DoEvents() ;- Return control to the system
Wend
;- End of retrieval
Repeat
Until WaitWindowEvent() = #PB_Event_CloseWindow
Else
MessageRequester("Error", "Bad query")
EndIf
Else
MessageRequester("Error", "No Database")
EndIf
Else
MessageRequester("Error", "Bad initialization of SQLite")
EndIf
CloseDatabase(#Database)
End
Procedure WindowProc()
OpenWindow(#MainWindow, 0, 0, 645, 600, "Πελατολόγιο...", #PB_Window_ScreenCentered|#PB_Window_SystemMenu)
CreateGadgetList(WindowID(#MainWindow))
ListIconGadget(#ListIcon, 0, 0, 645, 570, "Κωδικός (ID)", 120, #PB_ListIcon_GridLines|#PB_ListIcon_MultiSelect|#PB_ListIcon_FullRowSelect)
AddGadgetColumn(#ListIcon, 1, "Επώνυμο (Last Name)", 100)
AddGadgetColumn(#ListIcon, 2, "Όνομα (First Name)", 100)
LoadFont(#FontForListIcon, "Courier New", 10, #PB_Font_HighQuality)
SetGadgetFont(#ListIcon,FontID(#FontForListIcon))
ProgressBarGadget(#ProgressBar, 5, 575, 435, 20, 0, #Total_Records) ;, #PB_ProgressBar_Smooth)
EndProcedure
Procedure DoEvents()
Protected MSG.msg
msg.MSG
If PeekMessage_(msg,0,0,0,1)
TranslateMessage_(msg)
DispatchMessage_(msg)
Else
Sleep_(1)
EndIf
EndProcedure