SQLite3 Client/Server

[Paul Squires]

Hi Everyone,

I just posted a new set of sources for the SQLite3 Client/Server project. You can read all about it at http://planetsquires.com/support/index.php?topic=2411.0

The source code is public domain... just like SQLite itself. If you haven't heard about SQLite then you are missing out on one of the smallest, fastest, coolest SQL enabled database systems ever written. http://www.sqlite.org/

(Updated Jan 27, 2008)

[Paul Squires]

Here is a quick example of starting session with the database server, creating a database, a table, an index, and finally performing a SELECT and iterating the results. It is all pretty simple compared to many other database systems.

'//
'//  PUBLIC DOMAIN SOFTWARE - November 23, 2007
'//  The author or authors of this code dedicate any and all 
'//  copyright interest in this code to the public domain. 
'//  Anyone is free to copy, modify, publish, use, compile, 
'//  sell, or distribute the original code, either in source 
'//  code form or as a compiled binary, for any purpose, 
'//  commercial or non-commercial, and by any means.
'//

#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 = "create.db3"

   
   '//
   '//  Create the session. We must have a session variable
   '//  in order to interact with all other sql functions.
   '//
   sql3_StartSession hSession, 0   ' do not use Unicode support

 
   
   '//
   '//  Connect to the server (make sure that server is running
   '//  or we will get an error 57). 
   '//
   '//  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). You
   '//  just need to make sure to use %SQL_LOCAL in the sql3_Use statement.
   '//
   sSQL = "SERVER = localhost; PORT = default; UID = pss234; PASSWORD = sqlrocks"
   
   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). In this
   '//  case we use %SQL_REMOTE to create the database on the server.
   '//  To create a local database we would specify %SQL3_LOCAL.     
   '//
   errcode = sql3_Use( hSession, sDatabase, %SQL3_REMOTE )
   If errcode Then 
      ? "Error:" & Str$(errcode) & " during sql3_use.":  GoTo ExitOut
   End If
         

   '//
   '//  Create a simple table with an index in the database.
   '//
   sSQL = "CREATE TABLE [parts] ( "   & _
           "[MANUF] Text Not Null, "  & _
           "[REDREF] Text, "          & _
           "[PRODUCT] Text, "         & _
           "[LANGUAGE] Text, "        & _
           "[CPU_OS] Text, "          & _
           "[MEDIA] Text, "           & _
           "[TYPE] Text, "            & _
           "[PGROUP] Text, "          & _
           "[PRICE] NUMERIC); "       & _
           "CREATE INDEX [MANUF] ON [parts] ([MANUF]);"
   errcode = sql3_exec( hSession, sSQL ) 
   If errcode Then 
      ? "Error:" & Str$(errcode) & " during sql3_exe (Adding table/index).":  GoTo ExitOut
   End If 


   '//
   '//  Add a new record to the database.
   '//  In order to minimize database locking problems, you should wrap your SQL
   '//  request in an "IMMEDIATE" or "EXCLUSIVE" transaction rather than a
   '//  "DEFERRED" transaction.                      
   '//  
   sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
          "INSERT INTO parts (manuf) VALUES ('PlanetSquires" & Time$ & "');" & _
          "COMMIT TRANSACTION;"
   errcode = sql3_exec( hSession, sSQL ) 
   If errcode Then 
      ? "Error:" & Str$(errcode) & " during sql3_exe (Adding record).":  GoTo ExitOut
   End If 
   
   
   
   '//
   '//  Create a recordset. Once the recordset is created then we can move through it.
   '//  Ensure to set the row cache size to a realistic level. It is faster to bring
   '//  a number of rows to the client from the server rather than one row at a time. 
   '//  The server will compress the rows prior to sending them to the client but
   '//  there is always overhead associated with the TCP call and network transmission.
   '//  In this case, we set a row cache of 1000 rows. The client will request these
   '//  many rows from the server when needed.
   '//
   sSQL = "SELECT * FROM parts;"
   errcode = sql3_select( hSession, sSQL, rs, 1000 ) 
   If errcode Then 
      ? "Error:" & Str$(errcode) & " during sql3_select (Select).":  GoTo ExitOut
   End If
 
 
   
   '//
   '//  Get the column names
   '//
   nCols = sql3_rsColCount( hSession, rs )
   sColumns = ""
   For i = 0 To nCols - 1    ' column numbers are zero based
       sColumns = sColumns & sql3_rsColName( hSession, rs, i ) & ", "
   Next
   sColumns = RTrim$(sColumns, Any ", ")
   
 
   
   '
   ' Additional functions
   '
   ' Convert a Column name into its Column number (needed for the sql3_rsColText function).
   '      nColNumber = sql3_rsColNumber( hSession, rs, "MANUF" )
   '  
   
   
   
   ' Save our results to a test file
   f& = FreeFile
   Open "_debug.txt" For Output As #f&
   
   ' Save our column names
   Print #f&, sColumns
  

   t1# = Timer
   
   '//
   '//  Iterate the recordset by fetching each row from the recordset. If necessary,
   '//  additional rows will be automatically retrieved from the server as needed.
   '//
   Do Until sql3_rsFetch( hSession, rs ) = %SQL3_EOF
       
       sData = ""
       
       ' Get the data for each column in the row.
       For j = 0 To nCols - 1
          ' All row/field data is retrieved in text form. If you need
          ' it in numeric form then it is your responsibility to convert
          ' it using functions like BASIC's Val function.
          sData = sData & sql3_rsColText( hSession, rs, j ) & ", "  
       Next             
                           
       Incr nRows
       Print #f&, "Row:" & Str$(nRows) & "  " & RTrim$(sData, Any ", ") 
       
   Loop
    
   t2# = Timer

   Close #f&

    
   ? "Time to iterate" & Str$(nRows) & " rows: " & Format$( t2#-t1#, "###.##0") & " seconds."


ExitOut:

   '//
   '//  End the session and destroy session/connection/recordset
   '//
   If hSession Then 
      If rs Then sql3_rsDestroy hSession, rs
      sql3_Disconnect hSession 
      sql3_EndSession hSession 
   End If
   
End Function

[Paul Squires]

First post updated with link to SQLite3 C/S 0006 (Jan 26, 2008). Fixed a GPF problem. %SQL3_LOCAL mode is lightning fast - the same as accessing the SQLite3 DLL directly.

(All source code is included).

Version 0006

- Added sql_rsColInt and sql3_rsColInt64 to retrieve 32 bit and 64 bit integers from a recordset.

- Added sql3_Fix. Used to escape embedded quotes in strings.

- Fixed problem with %SQL3_ENDSESSION that was not removing the database handle from the internal linked list which could caues a GPF.

[Paul Squires]

First post updated once again to version 0007.

Version 0007 (2008-01-27)
- %SQL3_ENDSESSION error in v0006 was only fixed for %SQL3_LOCAL and not also for %SQL3_REMOTE.
- Added the SQLite3 error codes to the sql3client.inc file.

[Martin Francom]

For those interested, there have been some major changes to
the SQLite code. Here's a link to the new release:

The SQLite site has a new update (version 3.5.5) available that incorporates the new register based machine.
http://www.sqlite.org/releaselog/3_5_5.html

[Shawn Anderson]

You know what would be really useful? If someone created a visual table designer for sqlLite. MS Access has its "design view" and in MySQL you can create and view tables in a browser. From my limited used of sqlLite, everything has to be done programmatically, unless I'm missing something.

Mostly I would think this would be useful if one inherited code from someone else and had to examine the table structures. I mostly work with MySql and often will print out the table structure page for reference as I'm programming. Also it would be useful to have a grid where one could browse through the data.

Just an idea for someone smarter than me.

[Marco Pontello]

Check this: http://sqlitebrowser.sourceforge.net/

Bye!

[Chris Holbrook]

...a visual table designer for sqlLite.

As sqlite doesn't require all the b**cks like Oracle, you can just create a table like:

create table shawn ( a,b,c);

Is this worthy of a visual designer, or (as is often the case) am I missing something?

[Shawn Anderson]

Marco's post what just what I was thinking.
It certainly isn't required, but I think it would be useful.

[Shawn Anderson]

Hey Paul,
I can't get your download link to work on your forum.
is it still correct?
thanks!

[Steve Miltiadous]

firefox users

sqlite manager addon

https://addons.mozilla.org/en-US/firefox/addon/5817

[Shawn Anderson]

Nice! looks like the mySql manager

[Paul Squires]

I use the free, personal, edition of SQLite Expert. It works very well. Extremely easy to use. http://www.sqliteexpert.com/

[Paul Squires]

Hey Paul,
I can't get your download link to work on your forum.
is it still correct?
thanks!

Hi Shawn, the latest download links and changes are outlined in this post: http://planetsquires.com/support/index.php?topic=2415.0

I am at Version 0009. If you use the code (which is free/public domain) and have some ideas for improvement then by all means please let me know.

[Shawn Anderson]

I had no idea so many tools existed. thanks

[Mike Doty]

Completely different

Fred Meier's version is the one being used:

http://planetsquires.com/sqlite_client_server.htm

[Paul Squires]

Yes, we switched over to a brand new code base and it rocks! The new code is very robust, wicked fast, and supports encryption and compression. It also has server logging and ability to restrict user access to certain files. and, as we proved last week, you can connect to SQLitening Servers over the internet. Installation is a matter of copying a couple of DLL's. Simple.

Still work to be done on the project but Fred and I are working on it (Fred, more so than me). Documentation is the big project at this point.

SQLitening is a client/server (and local if you need it) implementation of the SQLite3 database system. SQLite is used by millions of applications and users around the world. It is SQL based and supports many advanced features such as transactions.

[Chris Holbrook]

It is SQL based and supports many advanced features such as transactions.

Paul, for those of us who already have some investment in "vanilla" SQLite, does your product also mimic the standard SQLite API?

[Paul Squires]

Paul, for those of us who already have some investment in "vanilla" SQLite, does your product also mimic the standard SQLite API?

Yes it does - very much so. There is a very light wrapper over the top of the SQLite function calls but probably no heavier than what you're using now. Download the package and browse the source code - you can then make up your own mind whether it suits your needs or not. Fred designed the code to be very light.

To be honest, I would love for you to become involved in helping with the project because you have a very good background in using PB and SQLite together. The more people that can help, the better.

[keith shelton]

Paul I noticed in firefly, it only allows you to choose between pbwin8.0 and pbwin7.0, not pbwin9.0.
KS