Current Status of El Choni's SQLite Library?

[El_Choni]

SQLite 2 always returns data as strings, IIRC. I have to check SQLite 3 on this, but I hope it does the same.

It'll take some time, I must add the complete new set of version 3 and test it.

Regards,

[fiver]

This is my first post here, having been a lurker for 6 months -an indictment of how effective this forum is ! - Sqlite3 seems the ideal lightweight database solution to me - pure c so cross-platform, free and threadsafe anytime soon. I really struggle with getting to grips with c though, so I'd like to offer a bribe to El Choni to complete a new sqlite3 PB lib or to anyone else who has time who can convert the sqlite quickstart: http://www.sqlite.org/quickstart.html into a lightly annotated PB source available for everyone. What sort of bribe? well, point me at your amazon wishlist or similar, (I'm thinking books rather than a segway!)
Take care, Fiver

[El_Choni]

The SQLite3 purelib is being tested, mainly by dige. The lib has changed a lot to solve some previous versions bugs, and is not usable yet. When it works, it'll be announced.

Thanks for the bribe, anyway I'd prefer two flight tickets to some It's-Always-Summer place than books, I already have a pile of books to read .

[fiver]

Woof! -that sounds good, I'll be happy to help with beta testing when the time comes. I'd like to buy you some tickets to somewhere sunny (and computer free ?) but I'm not that flush.
Cheers, Fiver

[Karbon]

A year later - are you any closer to a release, El Choni?

I really just need to be able to do a nest SQL query. Previously I've had to hack around using linked lists and such but that won't work in the situation I just ran into

Thanks!!

[gnozal]

I would like to know too

[El_Choni]

Yes, I'm closer, but far from finishing it. First, I want to fix TailBite, which is what I'm doing these days. I'll give Sqlite a try next week, it's not working for some reason, although the only thing I've done is make it thread safe, fix previous version bugs and support Sqlite 3. Then, I'll finish optimizing my bf2d code

Regards,

[sec]

Any news for now days?

[Karbon]

I just had the same notion!

SQLite 3 is looking mighty nice these days...

[El_Choni]

I've been playing with Sqlite3 lately, thanks to Fangbeast. I thought the functions syntax hadn't change, but it had. Anyway, I got something working (no lib or wrapper), so I'll show it to you.

I'm getting the feeling that a wrapper for Sqlite will not be very useful when we get macros and lib linking. Tell me what you think about it.

Code: Select all

#SQ_OK = 0
If OpenLibrary(0, "sqlite3.dll")
  *open = IsFunction(0, "sqlite3_open")
  *exec = IsFunction(0, "sqlite3_exec")
  *get_table = IsFunction(0, "sqlite3_get_table")
  *free_table = IsFunction(0, "sqlite3_free_table")
  *changes = IsFunction(0, "sqlite3_changes")
  *last_insert_rowid = IsFunction(0, "sqlite3_last_insert_rowid")
  *close = IsFunction(0, "sqlite3_close")
  *free = IsFunction(0, "sqlite3_free")
  ;sqlite3_errmsg(sqlite3 *db)
  DBName$ = "test.db"
  ; Create database if it doesn't exist
  Result = CallCFunctionFast(*open, DBName$, @DBHandle)
  If Result=#SQ_OK
    Debug "Opened"
      ; Create table if it doesn't exist
    Result = CallCFunctionFast(*exec, DBHandle, "CREATE TABLE bookmarks (id, url, site, category)", 0, 0, @ErrorMessage)
    ; Insert rows in table
    If Result<>#SQ_OK:Debug "CREATE result = "+PeekS(ErrorMessage):CallCFunctionFast(*free, @ErrorMessage):EndIf
    Result = CallCFunctionFast(*exec, DBHandle, "INSERT INTO bookmarks VALUES(1, 'http://www.purebasic.com', 'PureBasic', 'Download')", 0, 0, @ErrorMessage)
    If Result<>#SQ_OK:Debug "INSERT result = "+PeekS(ErrorMessage):CallCFunctionFast(*free, @ErrorMessage):EndIf
    Result = CallCFunctionFast(*exec, DBHandle, "INSERT INTO bookmarks VALUES(2, 'http://www.reelmediaproductions.com', 'PureBasic', 'Documentation')", 0, 0, @ErrorMessage)
    If Result<>#SQ_OK:Debug "INSERT result = "+PeekS(ErrorMessage):CallCFunctionFast(*free, @ErrorMessage):EndIf
    Result = CallCFunctionFast(*exec, DBHandle, "INSERT INTO bookmarks VALUES(3, 'http://msdn.microsoft.com', 'Windows', 'API documentation')", 0, 0, @ErrorMessage)
    If Result<>#SQ_OK:Debug "INSERT result = "+PeekS(ErrorMessage):CallCFunctionFast(*free, @ErrorMessage):EndIf
    Result = CallCFunctionFast(*exec, DBHandle, "INSERT INTO bookmarks VALUES(4, 'http://www.google.com', 'Search', 'Search the net')", 0, 0, @ErrorMessage)
    If Result<>#SQ_OK:Debug "INSERT result = "+PeekS(ErrorMessage):CallCFunctionFast(*free, @ErrorMessage):EndIf
    Debug ""
    Debug "Rows changed = " + Str(CallCFunctionFast(*changes))
    Debug "Last row inserted = " + Str(CallCFunctionFast(*last_insert_rowid))
    Debug ""
    ; Get table (execute SQL query)
    Result = CallCFunctionFast(*get_table, DBHandle, "SELECT id, url, site, category FROM bookmarks WHERE category LIKE 'D%'", @LResultsPtr, @Rows, @Cols, @ErrorMessage)
    If Result=0
      ; get the results
      ; display number of rows/columns
      Debug "Rows = " + Str(Rows)
      Debug ""
      Debug "Columns = " + Str(Cols)
      ; Dimensions must be: Rows+1, Cols
      ; IMPORTANT: dimensions must be exact, or behaviour will be unpredictable
      Dim *SQLData$(Rows+1, Cols)
      ; Cast array to table data
      *SQLData$() = LResultsPtr
      ; Display column headers
      For Col=0 To Cols-1
        Debug *SQLData$(0, Col)
      Next
      ; Display returned rows
      For Row=1 To Rows
        Debug ""
        Debug "Data row " + Str(Row) + " :"
        For Col=0 To Cols-1
          Debug *SQLData$(Row, Col)
        Next
      Next
      Debug ""
    Else
      MessageRequester("SQLite3 Error", "sqlite3_get_table: "+PeekS(ErrorMessage), #MB_IconError|#MB_OK)
    EndIf
    CallCFunctionFast(*free_table, LResultsPtr)
    CallCFunctionFast(*close, DBHandle)
  Else
    MessageRequester("SQLite3 Error", "Can't open database "+DBName$+Chr(10)+PeekS(ErrorMessage), #MB_IconError|#MB_OK)
  EndIf
Else
  MessageRequester("SQLite3 Error", "Can't open SQLite3.dll", #MB_IconError|#MB_OK)
EndIf
End

[Karbon]

I'd only like it so I had a drop-in replacement for kBilling

[Fangbeast]

Hope i'm not in trouble:):) Now that I have a spare power supply, I guess I have to pull the finger out and learn this properly!

[Fangbeast]

I am trying to rip the example apart to make re-usable modules and hit a snag .. The below code crashes the compier without a proper error message. I thought that I'd shorten the query entry code instead of leaving it in multiple times.

Is DBHAndle supposed to be Global, shared, protected (or something else? or am I missing something else. Regards.

Code: Select all

Procedure RunQuery(Query.s)
  result = CallCFunctionFast(*exec, DBHandle, Query.s, 0, 0, @ErrorMessage)
  If result <> #SQ_OK
    ;Debug "INSERT result = " + PeekS(ErrorMessage)
    CallCFunctionFast(*free, @ErrorMessage)
  EndIf
EndProcedure

  RunQuery("INSERT INTO bookmarks VALUES(1, 'http://www.purebasic.com', 'PureBasic', 'Download')")

[El_Choni]

For this example, use:

Code: Select all

Global *exec, *free, DBHandle

[Karbon]

Hey Choni, will your SQLite lib work with 3.94 ? I'm thinking about updating but I'm stuck in 3.93 if your lib won't make the trip!