• Welcome, Guest. Please login.
 
September 17, 2019, 10:15:16 am

News:

Welcome to the SQLitening support forums!


This is how easy it is to use....

Started by Paul Squires, September 04, 2007, 08:21:11 pm

Previous topic - Next topic

Paul Squires

I put together some simple code to show just how simple it is to use the SQLite engine. :)

A lot of the code that I use is based on Stan Durham's wrappers. However, I did change all of the code that he used to create a recordset. He used a macro that used local or global TYPE variables whereas I manually allocated memory (MemAlloc) for the recordset and destroyed the memory (MemFree) when finished with it. I find that approach much more easier to understand.


#Compile Exe


'JPRO_COMPILER=PB/WIN

#Include "sql3client.inc"


Function PBMain() As Long

   Local hSession  As Dword
   Local sData     As String
   Local sSQL      As String
   Local sDatabase As String
   Local sColumns  As String
   Local rs        As Long     ' holds the recordset
   Local nRows     As Long
   Local nCols     As Long
   Local errcode   As Long
   Local i         As Long
   Local j         As Long
   

   sDatabase = "accounting.db3"
   If Dir$(sDatabase) > "" Then Kill sDatabase

   
   ' Create the session
   sql3_StartSession hSession

   
   ' Connect to the server (make sure that server is running
   ' or we will get an error 57). The UID and Password are not
   ' checked (need to finish the sql3admin utility first).
   sSQL = "SERVER = localhost; PORT = default; UID = PSS234; PASSWORD = sqlrocks"
   
   ' Comment out the sql_connect call if you want your database to
   ' be opened/created on the local drive rather than by the server.
   ' This allows you to use the program without any server running
   ' at all (i.e. like a traditional, local, database system).
   errcode = sql3_connect( hSession, sSQL )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_connect.":  GoTo ExitOut
   End If
   
   
   ' Select the database to use (or create if none already exists).     
   errcode = sql3_Use( hSession, sDatabase )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_use.":  GoTo ExitOut
   End If
         

   ' Create a table in the database (error will occur if the table
   ' already exists).
   sSQL = "CREATE TABLE customers ("       & _ 
          "     custid    CHARACTER (8),"  & _   
          "     firstname CHARACTER (20)," & _   
          "     lastname  CHARACTER (20)," & _   
          "     amountdue REAL"            & _   
          ");"

   errcode = sql3_exec( hSession, sSQL )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_exec (Create Table).":  GoTo ExitOut
   End If

         
   ' Add some data to the table. Here we add three rows all in one
   ' statement and all wrapped in a transaction.
   sSQL = "BEGIN EXCLUSIVE; " &  _
          "   INSERT INTO customers "  & _
          "      (custid, firstname, lastname, amountdue)"   & _
          "   VALUES('12345', 'Paul', 'Squires', 100.50); "  & _
          "   INSERT INTO customers "  &  _
          "      (custid, firstname, lastname, amountdue)"   & _
          "   VALUES('23321', 'Bob', 'Zale', 750.00);"       & _
          "   INSERT INTO customers "  & _
          "      (custid, firstname, lastname, amountdue)"   & _
          "   VALUES('54671', 'Steve', 'Martin', 675.00); "  & _
          "COMMIT;" 

   errcode = sql3_exec( hSession, sSQL )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_exec (Insert Into).":  GoTo ExitOut
   End If


   ' Update rows based on a criteria.
   sSQL = "UPDATE customers SET firstname = 'Robert' WHERE custid = '23321';"
   errcode = sql3_exec( hSession, sSQL )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_exec (Update).":  GoTo ExitOut
   End If
   

   ? "About to show recordset BEFORE 'Squires' is deleted"
   GoSub ShowRecordset
   
   
   ' Delete rows from the table 
   sSQL = "DELETE FROM customers WHERE lastname = 'Squires';" 
   errcode = sql3_exec( hSession, sSQL )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_exec (Delete From).":  GoTo ExitOut
   End If
   

   ? "About to show recordset AFTER 'Squires' is deleted"
   GoSub ShowRecordset         
   
   
   ? "End of demonstration  :-)"


ExitOut:
   If rs Then sql3_rsDestroy rs

   ' Disconnect from the server (if connected)
   sql3_disconnect hSession
   
   ' End the session
   If hSession Then sql3_EndSession( hSession )
   

Exit Function

   
ShowRecordset:

   ' Create a recordset and move through it
   sSQL = "SELECT * FROM customers;"
   errcode = sql3_select( hSession, sSQL, rs )
   If errcode Then
      ? "Error:" & Str$(errcode) & " during sql3_select (Select).":  GoTo ExitOut
   End If

   nRows = sql3_rsRowCount( rs )
   nCols = sql3_rsColCount( rs )

   ' Display the column headers
   sColumns = ""
   For i = 1 To nCols
       sColumns = sColumns & sql3_rsColName( rs, i ) & ", "
   Next
   sColumns = RTrim$(sColumns, Any ", ")
   
 
   ' Iterate the recordset
   For i = 1 To nRows
       
       sData = ""
       
       sql3_rsMoveTo rs, i
                   
       ' Get the data for each column in the row.
       For j = 1 To nCols
          ' insert rest of row values
          sData = sData & sql3_rsGetAt( rs, j ) & ", "
       Next             
                             
       ? "Row #:    " & Str$(i)  & $CrLf & _
         "Columns:  " & sColumns & $CrLf & _
         "Row data: " & RTrim$(sData, Any ", ")

   Next
   
   
   ' Free recordset memory
   sql3_rsDestroy rs

Return


End Function

                       



Marco Ruiz

How I make to run SQLite database with an Internet address. I do not know that to put in the Web (the database, the sqLite3server) nor in which folder of my site (httpDocs, App_Data). It gives to a aable error 57 "unable to listen.."

Paul Squires

Hi Marco,

To be honest, I have not used this over the internet because my hosting is all on Linux servers. I would be interested in hearing from someone who has got this to work via an internet connection.

Mark Strickland

Although I am converting a FF based system from Cheetah to SQLite C/S I have not used it over the Internet.  I am not sure of the performance on slower network links but it may be OK depending on the application.

If you want to simplify your life for use over the Internet think about a VPN.  You could implement one several ways:

1) Many commercial routers and even some home routers directly support VPN's
2) Use OpenVPN (www.OpenVPN.net) -- free

XP (and I assume Vista) has a built in client that many things support.

A VPN adds a layer of security as well as making the remote network appear to be on your local network via NAT (Network Address Translation).

Mark Strickland, CISSP
www.SimplyBASICsecurity.com