• Welcome, Guest. Please login.
 
September 17, 2019, 10:33:34 am

News:

Welcome to the SQLitening support forums!


SQLite3 Client/Server Tips & Tricks

Started by Paul Squires, February 11, 2008, 08:19:07 pm

Previous topic - Next topic

Paul Squires

February 11, 2008, 08:19:07 pm Last Edit: June 29, 2008, 07:42:13 pm by TechSupport
I have spoken to a few people regarding the use of the SQLite3 Client/Server code and many lament that it is slow in client/server "remote" mode when sending a bulk number of individual INSERT or UPDATE commands to the server. The reason for this is that there is an overhead associated with the setup and TCP call to the server. A much better and significantly faster method is to create a dynamic string locally containing many individual SQL commands and send them all at one time to the server (wrapping the entire string of commands in a TRANSACTION).

Here is actual code from FireFly3's code snippet handler that inserts or updates code in the SQLite database. It iterates a linked list and builds a large SQL string composed of many individual SQL commands. After the loop ends, the SQL string is wrapped in a transaction.

(Note: This code uses a "local" database. If used in a client/server "remote" environment then you would need to issue an sql3_connect just after creating the session).


   sDatabase = GetDataStore & $CODESTORE_FILE
   If Dir$( sDatabase ) = "" Then Exit Function
   
   sql3_StartSession hSession, 0   ' do not use Unicode support

   errcode = sql3_Use( hSession, sDatabase, %SQL3_LOCAL )
   If errcode Then GoTo ExitOut


   pListItem = List_GetFirst( g.pListSnippets )
   Do Until pListItem = 0
       
      List_GetItemInfo pListItem, "", pSnippet
       
      If pSnippet Then
     
         If @pSnippet.IsDirty Then
            fChanges = %TRUE
             
            If @pSnippet.nRowID = 0 Then
               sSQL = sSQL & "INSERT INTO Snippets (name, code) VALUES ( '" & _
                             sql3_Fix(@pSnippet.@zName) & "', '" & _
                             sql3_Fix(@pSnippet.@zCode) & "' );"
            Else
               sSQL = sSQL & "UPDATE Snippets SET name = '" & sql3_Fix(@pSnippet.@zName) & "', " & _
                             "code = '" & sql3_Fix(@pSnippet.@zCode) & "' WHERE rowid = " & Format$(@pSnippet.nRowID) & ";"
            End If
           

         End If
         
      End If
       
      pListItem = List_GetNext( pListItem )
   Loop     
   
   
ExitOut:

   If fChanges Then
      sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
              sSQL & _
             "COMMIT TRANSACTION;"

      errcode = sql3_Exec( hSession, sSQL )
   End If
   
   
   If hSession Then
      sql3_EndSession hSession
   End If



I hope that this little tidbit of information helps you.



Paul Squires

How to get the number of rows returned from a SELECT query. You need to use the COUNT function as part of your SELECT.

Example:

sSQL = "SELECT COUNT(RowID) AS RowCount FROM dvd;"
   If sql3_select( hSession, sSQL, rs, 1 ) = 0 Then
      If sql3_rsFetch( hSession, rs ) <> %SQL3_EOF Then
         MsgBox "RowCount: " & sql3_rsColText( hSession, rs, "RowCount", 0 )
      End If   
   End If
   If rs Then sql3_rsDestroy hSession, rs


As you can see, the COUNT is aliased as RowCount so you can get the value using the sql3_rsColText or sql3_rsColInt. If you need to know the number columns returned in a query recordset then use the sql3_rsColCount function.