SQLite Journey

[Mike Doty]

Chris,

Just tested results (without using :inmemory: database) with and without binding and works perfectly.

[Stuart McLachlan]

I made an improved version of my code above and posted it to the Source Code Forum here: https://forum.powerbasic.com/forum/u...ts-and-binding

I created a new thread for discussion of your code for ease of future searching.

[Chris Holbrook]

I created a new thread for discussion of your code for ease of future searching.

Thanks Stuart! Can't see it , give us a clue?

[Chris Holbrook]

OK, got it

https://forum.powerbasic.com/forum/u...in-source-code

[Mike Doty]

Another nice thing is Chris added Explain code (which can be removed.)
If a common routine for display and error trapping is used and the explain is removed, it reduces to a few lines of code.

[Chris Holbrook]

Chris,
Just tested results (without using :inmemory: database) with and without binding and works perfectly.

[Mike Doty]

Hardly a challenge:

sSQL = "SELECT ID, lastname FROM mxembers WHERE lastname not like ?"

Stuart,
This is about preparing and executing the statement, returning the recordset and showing what and where if an error occurs.

Thanks again to Chris Holbrook
https://forum.powerbasic.com/forum/u...ts-and-binding

[Mike Doty]

SQLitening is also an option.
Insert, Update, Select, Delete

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN AS LONG

 LOCAL sfirst   AS STRING
 LOCAL sLast    AS STRING
 LOCAL srowid   AS STRING
 LOCAL changes  AS LONG

 slopen "test.db3","C"
 slexe  "create table if not exists clients(first TEXT,last TEXT)"

 sfirst="Heidi"
 sLast="Klum
 IF Insertclient(sFirst,sLast) = 1 THEN ? "Insert 1-record",,"INSERT"

 sFirst = "Heidi"
 sLast  = "Kaulitz
 sRowId = "1"
 IF Updateclient(sRowId,sFirst,sLast) = 1 THEN ? "Update 1-record",,"UPDATE"

 Sel "select * from clients"

 IF DeleteClient("1") = 1 THEN ? "Delete 1-record",,"DELETE"

END FUNCTION

FUNCTION InsertClient(BYVAL sfirst AS STRING, BYVAL sLast AS STRING) AS LONG
 DIM s(1 TO 2) AS STRING
 s(1) = slbuildbinddat(sfirst,"T")
 s(2) = slbuildbinddat(slast ,"T")
 slexebind "insert into clients values(?,?)",JOIN$(s(),""),"V2"
 FUNCTION = slGetChangeCount
END FUNCTION

FUNCTION UpdateClient(sRowID AS STRING,sFirst AS STRING,sLast AS STRING) AS LONG
 slexe USING$("update clients set first='&',last='&' where rowid=&",sFirst,sLast,sRowId)
 FUNCTION = slGetChangeCount
END FUNCTION

FUNCTION DeleteClient(sRowID AS STRING) AS LONG
 slexe "delete from clients where rowid="+sRowId
 FUNCTION = slGetChangeCount
END FUNCTION

FUNCTION Sel(sql AS STRING) AS LONG
 LOCAL sArray() AS STRING
 IF slselary(sql,sArray(),"Q9c E0") THEN
   ? sql + $CR+ slGetError,%MB_ICONERROR,"Sel"
 ELSE
  ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Sel"
 END IF
END FUNCTION

[Michael Mattias]

FWIW, both an ODBC Driver and OLE Provider are available for the Sqlite database.

That is, there is no need to learn SQLite's proprietary API if you are already familiar with ODBC (or a tool such as SQLtools) or OLE (or a tool such as ADO).

MCM
(another longtime DB user)