Announcement

Collapse
No announcement yet.

SQLite Journey

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #21
    Chris,

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




    Comment


    • #22
      Originally posted by Chris Holbrook View Post
      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.

      Comment


      • #23
        Originally posted by Stuart McLachlan View Post

        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?

        Comment


        • #24
          OK, got it

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

          Comment


          • #25
            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.

            Comment


            • #26
              Originally posted by Mike Doty View Post
              Chris,
              Just tested results (without using :inmemory: database) with and without binding and works perfectly.


              Comment


              • #27
                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

                Comment


                • #28
                  SQLitening is also an option.
                  Insert, Update, Select, Delete
                  Code:
                  #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

                  Comment


                  • #29
                    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)
                    Michael Mattias
                    Tal Systems (retired)
                    Port Washington WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment

                    Working...
                    X