SqLite Client/Server Library

External libraries (GTK, GSL, SDL, Allegro, OpenGL, etc) questions.
birosys
Posts: 10
Joined: Aug 28, 2007 20:39

SqLite Client/Server Library

Postby birosys » Jun 02, 2008 22:57

From the http://planetsquires.com/
"SQLite Client/Server a great multi-connection, multi-threaded server application that allows the SQLite3 database system to handle multiple SQL requests from different users over a network or internet. Source code is freely available."

I translate headers for freebasic.

Code: Select all

'
'  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.
'

' Type used for each database that is opened


Type SQLITE3_MSG_TYPE  ' Fill used to be compatible with Visual Basic
   nAction   As Long   ' action to take
   nData     As Long   ' memory address to SQL string
   nLen      As Long   ' length of the SQL string
   lData     As Long   ' generic holder used to pass/receive 32 bit numeric data to the dll
   lData64   As Double   ' generic holder used to pass/recieve 64 bit numeric data to the dll
   hSession  As Long   ' handle for the current session
   rsClient  As Long   ' client side recordset     
   rsServer  As Long   ' server side recordset (if applicable)
   rsLastRow As Long   ' used for determining next row to return from server side recordset (if applicable)
End Type


' Actions
Const SQL3S_STARTSESSION    = 100
Const SQL3S_ENDSESSION      = 110
Const SQL3S_USE             = 120
Const SQL3S_CLOSE           = 130
Const SQL3S_CONNECT         = 140
Const SQL3S_EXEC            = 150
Const SQL3S_SELECT          = 160
Const SQL3S_VALIDATEUSER    = 170
Const SQL3S_DISCONNECT      = 180 
Const SQL3S_EOF             = 190
Const SQL3S_RSFETCH         = 200
Const SQL3S_RSCOLCOUNT      = 210
Const SQL3S_RSCOLNAME       = 220
Const SQL3S_RSDESTROY       = 230
Const SQL3S_RSCOLTEXT       = 240
Const SQL3S_RSCOLINT        = 250
Const SQL3S_RSCOLINT64      = 260
Const SQL3S_RSCOLNUMBER     = 270
Const SQL3S_Dim           = 300
Const SQL3S_REMOTE          = 310


' Error Codes
Const ERR_ACTION           = 1000
Const ERR_DATA             = 1010
Const ERR_BADUSER          = 1020
Const ERR_INVALIDSOCKET    = 1030
Const ERR_BADRECORDSET     = 1040
Const ERR_BADSESSION       = 1050
Const ERR_NODATABASE       = 1060
Const ERR_BADUSEMODE       = 1070
Const ERR_BADCOLNAME       = 1080
Const ERR_DISCONNECTED     = 9999


' SQLIte3 error codes
Const SQLITE_OK          = 0    ' /* Successful result */
'beginning-of-error-codes */
Const SQLITE_ERROR       = 1    ' /* SQL error or missing database */
Const SQLITE_INTERNAL    = 2    ' /* NOT USED. Internal logic error in SQLite */
Const SQLITE_PERM        = 3    ' /* Access permission denied */
Const SQLITE_ABORT       = 4    ' /* Callback routine requested an abort */
Const SQLITE_BUSY        = 5    ' /* The database file is locked */
Const SQLITE_LOCKED      = 6    ' /* A table in the database is locked */
Const SQLITE_NOMEM       = 7    ' /* A malloc() failed */
Const SQLITE_READONLY    = 8    ' /* Attempt to write a readonly database */
Const SQLITE_INTERRUPT   = 9    ' /* Operation terminated by sqlite3_interrupt()*/
Const SQLITE_IOERR       = 10   ' /* Some kind of disk I/O error occurred */
Const SQLITE_CORRUPT     = 11   ' /* The database disk image is malformed */
Const SQLITE_NOTFOUND    = 12   ' /* NOT USED. Table or record not found */
Const SQLITE_FULL        = 13   ' /* Insertion failed because database is full */
Const SQLITE_CANTOPEN    = 14   ' /* Unable to open the database file */
Const SQLITE_PROTOCOL    = 15   ' /* NOT USED. Database lock protocol error */
Const SQLITE_EMPTY       = 16   ' /* Database is empty */
Const SQLITE_SCHEMA      = 17   ' /* The database schema changed */
Const SQLITE_TOOBIG      = 18   ' /* String or BLOB exceeds size limit */
Const SQLITE_CONSTRAINT  = 19   ' /* Abort due to contraint violation */
Const SQLITE_MISMATCH    = 20   ' /* Data type mismatch */
Const SQLITE_MISUSE      = 21   ' /* Library used incorrectly */
Const SQLITE_NOLFS       = 22   ' /* Uses OS features not supported on host */
Const SQLITE_AUTH        = 23   ' /* Authorization denied */
Const SQLITE_FORMAT      = 24   ' /* Auxiliary database format error */
Const SQLITE_RANGE       = 25   ' /* 2nd parameter to sqlite3_bind out of range */
Const SQLITE_NOTADB      = 26   ' /* File opened that is not a database file */
Const SQLITE_ROW         = 100  ' /* sqlite3_step() has another row ready */
Const SQLITE_DONE        = 101  ' /* sqlite3_step() has finished executing */
'end-of-error-codes */


Dim As Any Ptr hndl
Dim Shared sqlite3Client As Function (ByVal msg As Long) As Long
hndl=DylibLoad("sql3client")
sqlite3Client=DylibSymbol(hndl,"SQLITE3CLIENT")



' Wrappers for Sqlite3Server and PowerBASIC //
   

'
'  Create a new session
'
Function sql3_StartSession( ByRef hSession As Long, _
                            ByRef nUnicode As Long _
                            ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction = SQL3S_STARTSESSION
   msg.lData   = nUnicode
   Function = sqlite3Client( VarPtr(msg) )
   hSession = msg.hSession

End Function


'
'  End/destroy a session
'
Function sql3_EndSession( ByRef hSession As Long ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_ENDSESSION
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )
   hSession = 0

End Function


   
'
'  Create a connection to the server
'
Function sql3_Connect( ByRef hSession       As Long, _
                       ByRef sConnectstring As String _
                       ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_CONNECT
   msg.nData    = StrPtr( sConnectstring )
   msg.nLen     = Len( sConnectstring )
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Select the database that subsequent actions will
'  be applied to. If the database does not exist then
'  it will be created.
'
Function sql3_Use( ByRef hSession  As Long, _
                   ByRef sDatabase As String, _
                   ByRef nMode     As Long _
                   ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_USE
   msg.nData    = StrPtr( sDatabase )
   msg.nLen     = Len( sDatabase )
   msg.lData    = nMode                ' Const SQL3S_Dim, Const SQL3S_REMOTE
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Close a previously opened database
'
Function sql3_Close( ByRef hSession As Long ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_CLOSE
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Terminate a connection to the server
'
Function sql3_Disconnect( ByRef hSession As Long ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_DISCONNECT
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Execute an SQL statement that does not return any
'  record set data (e.g. INSERT, UPDATE, DELETE )
'
Function sql3_Exec( ByRef hSession As Long, _ 
                    ByRef sSQL     As String _
                    ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_EXEC
   msg.nData    = StrPtr( sSQL )
   msg.nLen     = Len( sSQL )
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )
   
End Function



'
'  Execute an SQL statement that returns record
'  set data (e.g. SELECT )
'
Function sql3_Select( ByRef hSession   As Long, _ 
                      ByRef sSQL       As String, _
                      ByRef rs         As integer, _
                      ByRef nCacheSize As Long _
                      ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_SELECT
   msg.nData    = StrPtr( sSQL )
   msg.nLen     = Len( sSQL )
   msg.hSession = hSession
   msg.lData    = nCacheSize  ' number of rows (records) to return from server at one time when needed
   
   Function = sqlite3Client( VarPtr(msg) )
   rs       = msg.rsClient

End Function
                                           

'
'  Free memory held by a recordset
'
Function sql3_rsDestroy( ByRef hSession As Long, _
                         ByRef rs       As integer _
                         ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSDESTROY
   msg.rsClient = rs
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )
   rs       = 0

End Function



'
'  Retrieve the value of the specified column (name)
'  in the current row of the recordset.
'
Function sql3_rsFetch( ByRef hSession As Long, _
                       ByRef rs       As integer _
                       ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSFETCH
   msg.rsClient = rs
   msg.hSession = hSession

   Function = sqlite3Client( VarPtr(msg) )
   
End Function



'
'  Retrieve the TEXT value of the specified column (zero based number)
'  in the current row of the recordset.
'  If a column name is specified then the column number is looked up
'  prior to getting the recordset column value. If no column name is
'  specified then we use the column number (nColNum).
'
Function sql3_rsColText( ByRef hSession As Long, _
                         ByRef rs       As integer, _
                         ByRef sColName As String, _
                         ByRef nColNum  As Long _
                         ) As String

   Dim As SQLITE3_MSG_TYPE msg
   Dim s As ZString ptr
   
   msg.nAction  = SQL3S_RSCOLTEXT
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   s=msg.nData
   Function = *s

End Function


'
'  Retrieve the INTEGER value of the specified column (zero based number)
'  in the current row of the recordset.
'  If a column name is specified then the column number is looked up
'  prior to getting the recordset column value. If no column name is
'  specified then we use the column number (nColNum).
'
Function sql3_rsColInt( ByRef hSession As Long, _
                        ByRef rs       As integer, _
                        ByRef sColName As String, _
                        ByRef nColNum  As Long _
                        ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLINT
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData

End Function



'
'  Retrieve the Double value of the specified column (zero based number)
'  in the current row of the recordset.
'  If a column name is specified then the column number is looked up
'  prior to getting the recordset column value. If no column name is
'  specified then we use the column number (nColNum).
'
Function sql3_rsColInt64( ByRef hSession As Long, _
                          ByRef rs       As integer, _
                          ByRef sColName As String, _
                          ByRef nColNum  As Long _
                          ) As double

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLINT64
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData64

End Function



'
'  Retrieve the column number based on the specified column name.
'
Function sql3_rsColNumber( ByRef hSession As Long, _
                           ByRef rs       As integer, _
                           ByRef sColName As String _
                           ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLNUMBER
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData

End Function


'
'  Retrieve the name of the specified column
'  in the recordset. Columns start at zero (0).
'
Function sql3_rsColName( ByRef hSession As Long, _
                         ByRef rs       As integer, _
                         ByRef nColNum  As Long _
                         ) As String

   Dim As SQLITE3_MSG_TYPE msg
   Dim s As ZString ptr
   
   msg.nAction  = SQL3S_RSCOLNAME
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   s=msg.nData
   Function = *s

End Function


'
'  Retrieve the number of columns in the current recordset.
'
Function sql3_rsColCount( ByRef hSession As Long, _
                          ByRef rs       As integer _
                          ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLCOUNT
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData

End Function


'
'   Escape any internal string quotes.
'
Function sql3_Fix( ByVal sString As String) As String
    ' fix single quotes
    Dim As Integer I
    Dim As String s,d
    For i =1 To Len(sString)
       s=Mid(sString,i,1)
       If s="'" Then
          s="''"
         d=d & s
       End If
    Next i
    Function = d
End Function



and here is sample program

Code: Select all

'
'  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.
'




#Include "sql3client.bi"
#Include "vbcompat.bi"



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

   sDatabase = "sample.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 Dim drive rather than by the server.
   '  This allows you to use the program without any server running
   '  at all (i.e. like a traditional, Dim, database system). You
   '  just need to make sure to use %SQL_Dim in the sql3_Use statement.
   '
   sSQL = "SERVER =localhost ; PORT = default; UID = pss234; PASSWORD = sqlrocks"
   
   errcode = sql3_connect( hSession, sSQL )
   If errcode Then
      print "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 use the database as it exists on the server.
   '  To use a Dim database we would specify SQL3S_Dim.     
   '
   errcode = sql3_Use( hSession, sDatabase, SQL3S_REMOTE )
   If errcode Then
      print "Error:" & Str$(errcode) & " during sql3_use.":  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
      print "Error:" & Str$(errcode) & " during sql3_exe (Adding record).":  GoTo ExitOut
   End If
   

   sSQL = "SELECT COUNT(RowID) AS RowCount FROM Parts;"
   If sql3_select( hSession, sSQL, rs, 1 ) = 0 Then
      If sql3_rsFetch( hSession, rs ) <> SQL3S_EOF Then
         print "SQL executed is: " & sSQL & Chr(13) & Chr(10) & _
           "RowCount: " & sql3_rsColText( hSession, rs, "RowCount", 0 )
      End If   
   End If
   If rs Then sql3_rsDestroy hSession, rs
   

   
   '
   '  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 RowID, * FROM parts where MANUF = '3COM';"
   errcode = sql3_select( hSession, sSQL, rs, 1000 )
   If errcode Then
      print "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)
   
 
   
   '
   ' Additional functions
   '
   ' Convert a Column name into its Column number (needed for the sql3_rsColText function).
   '      nColNumber = sql3_rsColNumber( hSession, rs, "ROWID" )
   ' 
   
   
   ' Save our results to a test file
   Dim As Integer f
   Dim As Double t1,t2
   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 ) = SQL3S_EOF
       
       sData = ""
       
       ' Get the data for each column in the row (RowID is always Column 0)
    '   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             
       
 
       ' Instead of getting all column information in text form via column
       ' number we could get each column data via the column name. This is
       ' obviously a little slower because the column name must be converted
       ' to a column number.
       '
       sData = sData & _
               sql3_rsColText( hSession, rs, "ROWID", 0 )    & ", "  & _
               sql3_rsColText( hSession, rs, "Manuf", 0 )    & ", "  & _
               sql3_rsColText( hSession, rs, "REDREF", 0 )   & ", "  & _
               sql3_rsColText( hSession, rs, "PRODUCT", 0 )  & ", "  & _
               sql3_rsColText( hSession, rs, "LANGUAGE", 0 ) & ", "  & _
               sql3_rsColText( hSession, rs, "CPU_OS", 0 )   & ", "  & _
               sql3_rsColText( hSession, rs, "MEDIA", 0 )    & ", "  & _
               sql3_rsColText( hSession, rs, "TYPE", 0 )     & ", "  & _
               sql3_rsColText( hSession, rs, "PGROUP", 0 )   & ", "  & _
               sql3_rsColText( hSession, rs, "NUMERIC", 0 )
       
       
       ' You can retrieve INT and INT64 values using the following functions:
       '       sql3_rsColInt( hSession, rs, "NUMERIC", 0 )
       '       sql3_rsColInt64( hSession, rs, "ROWID", 0 )
       
       
       ' Note: When iterating over thousands of rows and concatenating
       ' many columns per row, you may find that this takes a fair amount
       ' of time. A big culprit for this slowness is the string concatenation
       ' that must be done. This is one of the slowest programming functions
       ' so using a faster, buffered, approach may greatly speed up your
       ' application. SQLite3 C/S uses such a buffer. Refer to the AppendStr2
       ' function located in the modMemory.inc source file.
                             
       nRows=nRows+1
       Print #f, RTrim$(sData)
       
   Loop
   
   t2 = Timer

   Close #f

   
   print "SQL executed is: " & sSQL & Chr(13) & Chr(10) & _
     "Time to iterate" & Str$(nRows) & " rows: " & Format( t2-t1, "###.##0") & " seconds." & Chr(13) & Chr(10) & _
     "Detail output to: _debug.txt"


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

                       

[/url]
PaulSquires
Posts: 789
Joined: Jul 14, 2005 23:41
Contact:

Postby PaulSquires » Jun 02, 2008 23:55

Cool. Thanks for translating the code to FB. I have BLOB support added for the next version. I am in the process of adding support to run the server as a Window Service. Once that is done then the next version will be uploaded.

SQLite3 is an awesome database that is so small and easy to use with no installation or configuration requirements. The client/server is my attempt to use the database in a multiuser scenario in a easy way. It is still a very young project but it does work well based on my testing.
birosys
Posts: 10
Joined: Aug 28, 2007 20:39

Postby birosys » Jun 03, 2008 7:43

Hi PaulSquires

Thank you for your response, and thank you for great software.
SqLite Server is very promising project.
Simple, Small and Clean code.
I hope so that FB community will accept this great project very well.

Sorry for my poor english, and thank you again.

Milan
dani.user
Posts: 284
Joined: Sep 30, 2006 10:41

Postby dani.user » Jun 03, 2008 11:11

I find it pretty useless.
Sqlite is great for projects that need a small, embedded database. If I were to write a program that should connect to a database server, I would currently most certainly use a more advanced database system specifically designed for that purpose.
ciw1973
Posts: 157
Joined: Jun 12, 2007 15:03
Location: Isle of Man (United Kingdom)

Postby ciw1973 » Jun 03, 2008 12:48

If you'd written a single user app with SQLite as a backend, it'd be a nice quick way to make it multi-user.

I remember seeing this a while back, but thought it was a commercial offering just available for PowerBASIC.
PaulSquires
Posts: 789
Joined: Jul 14, 2005 23:41
Contact:

Postby PaulSquires » Jun 03, 2008 20:54

dani.user wrote:I find it pretty useless.
Sqlite is great for projects that need a small, embedded database. If I were to write a program that should connect to a database server, I would currently most certainly use a more advanced database system specifically designed for that purpose.

Useless is a bit harsh. Sure, lots of people use SQLite as a standalone, embedded database, but many more quickly find that they want to extend their SQLite based applications to multiuser. The client/server set up is the best way to do this and my project is just a simple way to do it. If your project involves heavy database accesses or needs more advanced server features then I agree wholeheartedly with you - a more robust solution should be used. :-)
dani.user
Posts: 284
Joined: Sep 30, 2006 10:41

Postby dani.user » Jun 04, 2008 16:23

Indeed, I was a bit harsh, sorry, didn't pay to much attention to the multiuser part
Amundo
Posts: 57
Joined: Feb 26, 2007 0:25

Postby Amundo » Sep 09, 2008 0:48

dani.user wrote:I find it pretty useless.
Sqlite is great for projects that need a small, embedded database. If I were to write a program that should connect to a database server, I would currently most certainly use a more advanced database system specifically designed for that purpose.


dani.user: So, from your experience of writing client/server database apps, which one would you recommend?

Paul: Many thanks for creating/making this available (and, of course, birosys for the translation :-) )
NormalUser
Posts: 2
Joined: Dec 28, 2009 2:18

Re: SqLite Client/Server Library

Postby NormalUser » Dec 28, 2009 2:23

I am unable to find the library "sql3client" in the below code to load.

hndl=DylibLoad("sql3client")
sqlite3Client=DylibSymbol(hndl,"SQLITE3CLIENT")


Can anybody help?



birosys wrote:From the http://planetsquires.com/
"SQLite Client/Server a great multi-connection, multi-threaded server application that allows the SQLite3 database system to handle multiple SQL requests from different users over a network or internet. Source code is freely available."

I translate headers for freebasic.

Code: Select all

'
'  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.
'

' Type used for each database that is opened


Type SQLITE3_MSG_TYPE  ' Fill used to be compatible with Visual Basic
   nAction   As Long   ' action to take
   nData     As Long   ' memory address to SQL string
   nLen      As Long   ' length of the SQL string
   lData     As Long   ' generic holder used to pass/receive 32 bit numeric data to the dll
   lData64   As Double   ' generic holder used to pass/recieve 64 bit numeric data to the dll
   hSession  As Long   ' handle for the current session
   rsClient  As Long   ' client side recordset     
   rsServer  As Long   ' server side recordset (if applicable)
   rsLastRow As Long   ' used for determining next row to return from server side recordset (if applicable)
End Type


' Actions
Const SQL3S_STARTSESSION    = 100
Const SQL3S_ENDSESSION      = 110
Const SQL3S_USE             = 120
Const SQL3S_CLOSE           = 130
Const SQL3S_CONNECT         = 140
Const SQL3S_EXEC            = 150
Const SQL3S_SELECT          = 160
Const SQL3S_VALIDATEUSER    = 170
Const SQL3S_DISCONNECT      = 180 
Const SQL3S_EOF             = 190
Const SQL3S_RSFETCH         = 200
Const SQL3S_RSCOLCOUNT      = 210
Const SQL3S_RSCOLNAME       = 220
Const SQL3S_RSDESTROY       = 230
Const SQL3S_RSCOLTEXT       = 240
Const SQL3S_RSCOLINT        = 250
Const SQL3S_RSCOLINT64      = 260
Const SQL3S_RSCOLNUMBER     = 270
Const SQL3S_Dim           = 300
Const SQL3S_REMOTE          = 310


' Error Codes
Const ERR_ACTION           = 1000
Const ERR_DATA             = 1010
Const ERR_BADUSER          = 1020
Const ERR_INVALIDSOCKET    = 1030
Const ERR_BADRECORDSET     = 1040
Const ERR_BADSESSION       = 1050
Const ERR_NODATABASE       = 1060
Const ERR_BADUSEMODE       = 1070
Const ERR_BADCOLNAME       = 1080
Const ERR_DISCONNECTED     = 9999


' SQLIte3 error codes
Const SQLITE_OK          = 0    ' /* Successful result */
'beginning-of-error-codes */
Const SQLITE_ERROR       = 1    ' /* SQL error or missing database */
Const SQLITE_INTERNAL    = 2    ' /* NOT USED. Internal logic error in SQLite */
Const SQLITE_PERM        = 3    ' /* Access permission denied */
Const SQLITE_ABORT       = 4    ' /* Callback routine requested an abort */
Const SQLITE_BUSY        = 5    ' /* The database file is locked */
Const SQLITE_LOCKED      = 6    ' /* A table in the database is locked */
Const SQLITE_NOMEM       = 7    ' /* A malloc() failed */
Const SQLITE_READONLY    = 8    ' /* Attempt to write a readonly database */
Const SQLITE_INTERRUPT   = 9    ' /* Operation terminated by sqlite3_interrupt()*/
Const SQLITE_IOERR       = 10   ' /* Some kind of disk I/O error occurred */
Const SQLITE_CORRUPT     = 11   ' /* The database disk image is malformed */
Const SQLITE_NOTFOUND    = 12   ' /* NOT USED. Table or record not found */
Const SQLITE_FULL        = 13   ' /* Insertion failed because database is full */
Const SQLITE_CANTOPEN    = 14   ' /* Unable to open the database file */
Const SQLITE_PROTOCOL    = 15   ' /* NOT USED. Database lock protocol error */
Const SQLITE_EMPTY       = 16   ' /* Database is empty */
Const SQLITE_SCHEMA      = 17   ' /* The database schema changed */
Const SQLITE_TOOBIG      = 18   ' /* String or BLOB exceeds size limit */
Const SQLITE_CONSTRAINT  = 19   ' /* Abort due to contraint violation */
Const SQLITE_MISMATCH    = 20   ' /* Data type mismatch */
Const SQLITE_MISUSE      = 21   ' /* Library used incorrectly */
Const SQLITE_NOLFS       = 22   ' /* Uses OS features not supported on host */
Const SQLITE_AUTH        = 23   ' /* Authorization denied */
Const SQLITE_FORMAT      = 24   ' /* Auxiliary database format error */
Const SQLITE_RANGE       = 25   ' /* 2nd parameter to sqlite3_bind out of range */
Const SQLITE_NOTADB      = 26   ' /* File opened that is not a database file */
Const SQLITE_ROW         = 100  ' /* sqlite3_step() has another row ready */
Const SQLITE_DONE        = 101  ' /* sqlite3_step() has finished executing */
'end-of-error-codes */


Dim As Any Ptr hndl
Dim Shared sqlite3Client As Function (ByVal msg As Long) As Long
hndl=DylibLoad("sql3client")
sqlite3Client=DylibSymbol(hndl,"SQLITE3CLIENT")



' Wrappers for Sqlite3Server and PowerBASIC //
   

'
'  Create a new session
'
Function sql3_StartSession( ByRef hSession As Long, _
                            ByRef nUnicode As Long _
                            ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction = SQL3S_STARTSESSION
   msg.lData   = nUnicode
   Function = sqlite3Client( VarPtr(msg) )
   hSession = msg.hSession

End Function


'
'  End/destroy a session
'
Function sql3_EndSession( ByRef hSession As Long ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_ENDSESSION
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )
   hSession = 0

End Function


   
'
'  Create a connection to the server
'
Function sql3_Connect( ByRef hSession       As Long, _
                       ByRef sConnectstring As String _
                       ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_CONNECT
   msg.nData    = StrPtr( sConnectstring )
   msg.nLen     = Len( sConnectstring )
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Select the database that subsequent actions will
'  be applied to. If the database does not exist then
'  it will be created.
'
Function sql3_Use( ByRef hSession  As Long, _
                   ByRef sDatabase As String, _
                   ByRef nMode     As Long _
                   ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_USE
   msg.nData    = StrPtr( sDatabase )
   msg.nLen     = Len( sDatabase )
   msg.lData    = nMode                ' Const SQL3S_Dim, Const SQL3S_REMOTE
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Close a previously opened database
'
Function sql3_Close( ByRef hSession As Long ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_CLOSE
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Terminate a connection to the server
'
Function sql3_Disconnect( ByRef hSession As Long ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_DISCONNECT
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )

End Function


'
'  Execute an SQL statement that does not return any
'  record set data (e.g. INSERT, UPDATE, DELETE )
'
Function sql3_Exec( ByRef hSession As Long, _ 
                    ByRef sSQL     As String _
                    ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_EXEC
   msg.nData    = StrPtr( sSQL )
   msg.nLen     = Len( sSQL )
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )
   
End Function



'
'  Execute an SQL statement that returns record
'  set data (e.g. SELECT )
'
Function sql3_Select( ByRef hSession   As Long, _ 
                      ByRef sSQL       As String, _
                      ByRef rs         As integer, _
                      ByRef nCacheSize As Long _
                      ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_SELECT
   msg.nData    = StrPtr( sSQL )
   msg.nLen     = Len( sSQL )
   msg.hSession = hSession
   msg.lData    = nCacheSize  ' number of rows (records) to return from server at one time when needed
   
   Function = sqlite3Client( VarPtr(msg) )
   rs       = msg.rsClient

End Function
                                           

'
'  Free memory held by a recordset
'
Function sql3_rsDestroy( ByRef hSession As Long, _
                         ByRef rs       As integer _
                         ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSDESTROY
   msg.rsClient = rs
   msg.hSession = hSession
   
   Function = sqlite3Client( VarPtr(msg) )
   rs       = 0

End Function



'
'  Retrieve the value of the specified column (name)
'  in the current row of the recordset.
'
Function sql3_rsFetch( ByRef hSession As Long, _
                       ByRef rs       As integer _
                       ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSFETCH
   msg.rsClient = rs
   msg.hSession = hSession

   Function = sqlite3Client( VarPtr(msg) )
   
End Function



'
'  Retrieve the TEXT value of the specified column (zero based number)
'  in the current row of the recordset.
'  If a column name is specified then the column number is looked up
'  prior to getting the recordset column value. If no column name is
'  specified then we use the column number (nColNum).
'
Function sql3_rsColText( ByRef hSession As Long, _
                         ByRef rs       As integer, _
                         ByRef sColName As String, _
                         ByRef nColNum  As Long _
                         ) As String

   Dim As SQLITE3_MSG_TYPE msg
   Dim s As ZString ptr
   
   msg.nAction  = SQL3S_RSCOLTEXT
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   s=msg.nData
   Function = *s

End Function


'
'  Retrieve the INTEGER value of the specified column (zero based number)
'  in the current row of the recordset.
'  If a column name is specified then the column number is looked up
'  prior to getting the recordset column value. If no column name is
'  specified then we use the column number (nColNum).
'
Function sql3_rsColInt( ByRef hSession As Long, _
                        ByRef rs       As integer, _
                        ByRef sColName As String, _
                        ByRef nColNum  As Long _
                        ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLINT
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData

End Function



'
'  Retrieve the Double value of the specified column (zero based number)
'  in the current row of the recordset.
'  If a column name is specified then the column number is looked up
'  prior to getting the recordset column value. If no column name is
'  specified then we use the column number (nColNum).
'
Function sql3_rsColInt64( ByRef hSession As Long, _
                          ByRef rs       As integer, _
                          ByRef sColName As String, _
                          ByRef nColNum  As Long _
                          ) As double

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLINT64
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData64

End Function



'
'  Retrieve the column number based on the specified column name.
'
Function sql3_rsColNumber( ByRef hSession As Long, _
                           ByRef rs       As integer, _
                           ByRef sColName As String _
                           ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLNUMBER
   msg.nData    = StrPtr( sColName )
   msg.nLen     = Len( sColName )
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData

End Function


'
'  Retrieve the name of the specified column
'  in the recordset. Columns start at zero (0).
'
Function sql3_rsColName( ByRef hSession As Long, _
                         ByRef rs       As integer, _
                         ByRef nColNum  As Long _
                         ) As String

   Dim As SQLITE3_MSG_TYPE msg
   Dim s As ZString ptr
   
   msg.nAction  = SQL3S_RSCOLNAME
   msg.lData    = nColNum
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   s=msg.nData
   Function = *s

End Function


'
'  Retrieve the number of columns in the current recordset.
'
Function sql3_rsColCount( ByRef hSession As Long, _
                          ByRef rs       As integer _
                          ) As Long

   Dim As SQLITE3_MSG_TYPE msg
   
   msg.nAction  = SQL3S_RSCOLCOUNT
   msg.rsClient = rs
   msg.hSession = hSession

   sqlite3Client (VarPtr(msg))
   
   Function = msg.lData

End Function


'
'   Escape any internal string quotes.
'
Function sql3_Fix( ByVal sString As String) As String
    ' fix single quotes
    Dim As Integer I
    Dim As String s,d
    For i =1 To Len(sString)
       s=Mid(sString,i,1)
       If s="'" Then
          s="''"
         d=d & s
       End If
    Next i
    Function = d
End Function



and here is sample program

Code: Select all

'
'  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.
'




#Include "sql3client.bi"
#Include "vbcompat.bi"



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

   sDatabase = "sample.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 Dim drive rather than by the server.
   '  This allows you to use the program without any server running
   '  at all (i.e. like a traditional, Dim, database system). You
   '  just need to make sure to use %SQL_Dim in the sql3_Use statement.
   '
   sSQL = "SERVER =localhost ; PORT = default; UID = pss234; PASSWORD = sqlrocks"
   
   errcode = sql3_connect( hSession, sSQL )
   If errcode Then
      print "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 use the database as it exists on the server.
   '  To use a Dim database we would specify SQL3S_Dim.     
   '
   errcode = sql3_Use( hSession, sDatabase, SQL3S_REMOTE )
   If errcode Then
      print "Error:" & Str$(errcode) & " during sql3_use.":  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
      print "Error:" & Str$(errcode) & " during sql3_exe (Adding record).":  GoTo ExitOut
   End If
   

   sSQL = "SELECT COUNT(RowID) AS RowCount FROM Parts;"
   If sql3_select( hSession, sSQL, rs, 1 ) = 0 Then
      If sql3_rsFetch( hSession, rs ) <> SQL3S_EOF Then
         print "SQL executed is: " & sSQL & Chr(13) & Chr(10) & _
           "RowCount: " & sql3_rsColText( hSession, rs, "RowCount", 0 )
      End If   
   End If
   If rs Then sql3_rsDestroy hSession, rs
   

   
   '
   '  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 RowID, * FROM parts where MANUF = '3COM';"
   errcode = sql3_select( hSession, sSQL, rs, 1000 )
   If errcode Then
      print "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)
   
 
   
   '
   ' Additional functions
   '
   ' Convert a Column name into its Column number (needed for the sql3_rsColText function).
   '      nColNumber = sql3_rsColNumber( hSession, rs, "ROWID" )
   ' 
   
   
   ' Save our results to a test file
   Dim As Integer f
   Dim As Double t1,t2
   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 ) = SQL3S_EOF
       
       sData = ""
       
       ' Get the data for each column in the row (RowID is always Column 0)
    '   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             
       
 
       ' Instead of getting all column information in text form via column
       ' number we could get each column data via the column name. This is
       ' obviously a little slower because the column name must be converted
       ' to a column number.
       '
       sData = sData & _
               sql3_rsColText( hSession, rs, "ROWID", 0 )    & ", "  & _
               sql3_rsColText( hSession, rs, "Manuf", 0 )    & ", "  & _
               sql3_rsColText( hSession, rs, "REDREF", 0 )   & ", "  & _
               sql3_rsColText( hSession, rs, "PRODUCT", 0 )  & ", "  & _
               sql3_rsColText( hSession, rs, "LANGUAGE", 0 ) & ", "  & _
               sql3_rsColText( hSession, rs, "CPU_OS", 0 )   & ", "  & _
               sql3_rsColText( hSession, rs, "MEDIA", 0 )    & ", "  & _
               sql3_rsColText( hSession, rs, "TYPE", 0 )     & ", "  & _
               sql3_rsColText( hSession, rs, "PGROUP", 0 )   & ", "  & _
               sql3_rsColText( hSession, rs, "NUMERIC", 0 )
       
       
       ' You can retrieve INT and INT64 values using the following functions:
       '       sql3_rsColInt( hSession, rs, "NUMERIC", 0 )
       '       sql3_rsColInt64( hSession, rs, "ROWID", 0 )
       
       
       ' Note: When iterating over thousands of rows and concatenating
       ' many columns per row, you may find that this takes a fair amount
       ' of time. A big culprit for this slowness is the string concatenation
       ' that must be done. This is one of the slowest programming functions
       ' so using a faster, buffered, approach may greatly speed up your
       ' application. SQLite3 C/S uses such a buffer. Refer to the AppendStr2
       ' function located in the modMemory.inc source file.
                             
       nRows=nRows+1
       Print #f, RTrim$(sData)
       
   Loop
   
   t2 = Timer

   Close #f

   
   print "SQL executed is: " & sSQL & Chr(13) & Chr(10) & _
     "Time to iterate" & Str$(nRows) & " rows: " & Format( t2-t1, "###.##0") & " seconds." & Chr(13) & Chr(10) & _
     "Detail output to: _debug.txt"


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

                       

[/url]
McLovin
Posts: 82
Joined: Oct 21, 2008 1:15
Contact:

Postby McLovin » Dec 28, 2009 4:06

This is an older version of the SQLite Client/Server product. That product was replaced/updated with an entirely new code base called SQLitening. It is a much better code base and much more robust and functional. You can get the code from the dedicated SQLitening forums:
http://www.sqlitening.com/support/index.php
NormalUser
Posts: 2
Joined: Dec 28, 2009 2:18

Postby NormalUser » Dec 28, 2009 4:46

Thanks McLovin

I was wondering whether anybody translated the headers for freebasic of this new version.


McLovin wrote:This is an older version of the SQLite Client/Server product. That product was replaced/updated with an entirely new code base called SQLitening. It is a much better code base and much more robust and functional. You can get the code from the dedicated SQLitening forums:
http://www.sqlitening.com/support/index.php
PaulSquires
Posts: 789
Joined: Jul 14, 2005 23:41
Contact:

Re:

Postby PaulSquires » Jan 11, 2017 1:31

NormalUser wrote:Thanks McLovin

I was wondering whether anybody translated the headers for freebasic of this new version.

McLovin wrote:This is an older version of the SQLite Client/Server product. That product was replaced/updated with an entirely new code base called SQLitening. It is a much better code base and much more robust and functional. You can get the code from the dedicated SQLitening forums:
http://www.sqlitening.com/support/index.php


Here is my first attempt at the SQLitening and FreeBasic interface:
http://sqlitening.com/support/index.php ... 2#msg25492

If you use it and find that things need to be changed, etc then please let me know.

Return to “Libraries”

Who is online

Users browsing this forum: No registered users and 1 guest