4.2 Beta SQLite Questions

[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.

[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=

[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?

[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.

[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

[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.

[Karbon]

Thanks freak!!

[pdwyer]

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

[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)

[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)

[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.

[the.weavster]

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.

[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

[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

[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.

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