"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
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