Me and Sqlite

[Petrus Vorster]

I have been talking to Paul about me going on this complete change from random-access files and really getting into using SQLITE.

He has been real kind in giving me some pointers and examples.

Being a hobbyist I quickly found that I am so far behind in skills compared to the rest of this forum its embarrassing.
And when someone kindly tries to explain something 'simple' I sometimes still don't get it.
I have also been reading what I could find here on the forum, Jose's examples and the Sqlite website, but that's already where I get lost.

Is there no ABC guide for absolute first-day beginners on how to use SQLITE in Firefly or someone who have the patience to just get me to understand what files I must download (From the bulk on that site), what goes where and then how to get started using it in FF?

Sorry for all my dumb questions, I really need to go forward, but I am not going to be able to do that without a little help from you guys!!

[Paul Squires]

Hi Peter,

I wrote an application last year using Jose's SQLite includes (rather than SQLitening). Here are some notes to get you started:

In FF_AppStart:

#INCLUDE Once "CSQLite.INC"

$DATABASE_NAME = "StatsData.db3"   '<-- I used SQLiteExpert Personal edition to create this

Global pSQL As ISQLite
Global pDbc As ISQLiteConnection


In FF_WinMain:
I open my database when the application loads so therefore I call a user defined function "OpenDatabase" in FF_WinMain.
OpenDatabase

...and here is that function:

Code Select Expand


''
''
''
Function OpenDatabase() As Long
     
   CloseDatabase  ' close any already database
   
   ' // Create an instance of the class and connection
   pSQL = Class "CSQLite"
   If IsNothing(pSQL) Then Exit Function
   pDbc = pSQL.Connection
   If IsNothing(pDbc) Then Exit Function

   ' Open the company data file
   pDbc.OpenDatabase App.Path & $DATABASE_NAME

End Function


Code Select Expand


''
''
''
Function CloseDatabase() As Long

   ' If a company is already open then close it.
   If IsObject(pSQL) Then pSQL = Nothing
   If IsObject(pDbc) Then pDbc = Nothing
   
End Function


Here is a simple example of retrieving data from a table in the database:

Code Select Expand


   Local pStmt      As ISQLiteStatement
   Local sSQL       As String

   sSQL = "SELECT rowid, * FROM stats_info WHERE " & _
                              "EmpRecNum = " & Str$(gEmpRecNum) & " AND " & _
                              "WeekEndingDate BETWEEN " & Str$(nDate1) & " AND " & Str$(nDate2) & ";"

   pStmt = pDbc.Prepare( sSQL )
   
   Do
      If pStmt.Step = %SQLITE_DONE Then Exit Do

      nActivityType = Val(pStmt.ColumnText( "ActivityType" ))
      nEntryDate    = Val(pStmt.ColumnText( "WeekEndingDate" ))

      ' Do stuff with the ActivityType and EntryDate
      ' ...
      ' ...
               
   Loop

   Let pStmt = Nothing


...and here is code to save or update data in a table(s) in the database.

Code Select Expand


'--------------------------------------------------------------------------------
Function CheckForDataEntryDirty( ByVal hWndForm As Dword, _
                                 ByVal hWndGrid As Dword _
                                 ) As Long


   Local NumEntries As Long
   Local i          As Long
   Local nRecNum    As Quad 
   Local sql        As String
   

   pDbc.Exec("BEGIN IMMEDIATE;")
   
   
   '//  SAVE THE EMPLOYEE NOTES
   If IsTrue(gNotesDirty) Then
      sql = "UPDATE emp_info SET " & _
                 "Notes = '" & sSafe(FF_RichEdit_GetText(HWND_FRMENTRY_TXTNOTES, %TRUE)) & "' " & _
                 "WHERE RowID = " & Str$(gEmpRecNum) & ";"   
      pDbc.Exec sql
   End If
     
   
   '//  SAVE THE DIRTY DATA ENTRY VALUES
   NumEntries = UBound(gDataEntry)
   For i = 1 To NumEntries
   
         nRecNum = gDataEntry(i).nStatsRowID
         
         If nRecNum > 0 Then     
            ' UPDATE
            sql = "UPDATE stats_info SET " & _
                       "EmpRecNum      = " & Str$(gEmpRecNum) & ", " & _
                       "WeekEndingDate = " & Str$(gDataEntry(i).nEntryDate) & ", " & _
                       "ActivityType   = " & Str$(gDataEntry(i).nActivityType) & ", " & _
                       "Hours          = " & MLG_Get(hWndGrid, gDataEntry(i).nRowNum, gDataEntry(i).nColHours) & ", " & _
                       "Units          = " & MLG_Get(hWndGrid, gDataEntry(i).nRowNum, gDataEntry(i).nColunits) & " " & _
                       "WHERE RowID = " & Str$(nRecNum) & ";"   
         Else
            ' INSERT
            sql = "INSERT INTO stats_info(EmpRecNum,WeekEndingDate,ActivityType,Hours,Units) VALUES (" & _
                       Str$(gEmpRecNum) & ", " & _
                       Str$(gDataEntry(i).nEntryDate) & ", " & _
                       Str$(gDataEntry(i).nActivityType) & ", " & _
                       MLG_Get(hWndGrid, gDataEntry(i).nRowNum, gDataEntry(i).nColHours) & ", " & _
                       MLG_Get(hWndGrid, gDataEntry(i).nRowNum, gDataEntry(i).nColunits) & ");"
         End If
         
         pDbc.Exec sql

      End If
   Next       

   pDbc.Exec("END TRANSACTION;")

End Function

''
''
''
Function sSafe( ByVal sData As String ) As String
   ' Make the incoming string safe for the SQL statement
   Replace "'" With "''" In sData
   Function = sData
End Function



I ensure to close the database when the main form is destroyed:

Code Select Expand


'--------------------------------------------------------------------------------
Function FRMMAIN_WM_DESTROY ( _
                            hWndForm      As Dword _  ' handle of Form
                            ) As Long
                           
   CloseDatabase
   
   KillMultiUser

   ' Reset the config class
   Let gclsConfig = Nothing
   
End Function


[John Montenigro]

Thanks to Peter for asking, to Paul for answering, and to Jose for providing his Includes!

With a bit of study, I think all of this will enable me to also make the break from flat files to database storage...

Thanks!
-John

[Petrus Vorster]

This does look so simple.
I will be trying this from now up until the weekend and report back to you and John on my progress.

Thanks, this will be slow, but I will pick up!

[Petrus Vorster]

Ok, so this runs completely in that Include from Jose?
No DLL or anything else required?

I do get errors on the Form_kill function though.
Let me toggle with the rest a bit.

Thanks guys.

[Paul Squires]

Your application needs to have access to the "sqlite3.dll" file. I put it in the same folder as my EXE.

[Petrus Vorster]

Sqlite3.dll done
Gets and Error on Gnotesdirty and the entire killfocus of the main form, as well as one If/Endif.

I will figure it out. Come December I want to get myself to be able to produce with this on the same level as where i was on the random files.

[Klaas Holland]

I use SQLTools from PerfectSync with a MYSQL Database, that works fine for me.
SQLTools is well documented.
You could look at this documentation too learn the basic things.

[Petrus Vorster]

Hi Klaas

A lot of great guys on this forum have given me a lot of pointers and help.
20+ years of using flat files is difficult to change.

I have read a great deal on SQL, Sqlite and so on. Most of the examples i find on the web are in C.
I dont follow the syntax of C or how to adapt examples for PB/FF.

PB examples is less frequent on the web.

I think I just dont know where to look. If you perhaps have a couple of links I should look at, I will be most thankfull.

[Petrus Vorster]

Thanks Paul for the tip about Sqlite Expert.

It looks like a great tool. I am busy aquiring the personal edition.
So i make the Database and tables with that, and then do the entries & queries with Firefly?

Cool, that will help a great deal.

Thanks, i have allready made progress!!!

[Rolf Brandt]

SQLite Expert is also a great tool for testing and developing SQL queries.

Rolf

[Petrus Vorster]

Hi Rolf

I can see it appears very powerful.
Progress is very slow. Its like learning a complete new language all on your own.
At this moment I am paging through everything, reading the help files and so on, but I have not been able to make a small application that can access the database and save or retrieve something.

Somewhat frustrating.

[Rolf Brandt]

Hello Petrus,

Very helpful in the learning process of the SQL language was (and still is) the Website of W3 Schools. Very good description of SQL and lots of simple examples to get started.

http://www.w3schools.com/sql/

Rolf

[Peter Maroudas]

I am the same as Petrus, all my life using flat files (PowerTree) simple Add,Update, Delete and cleanup re-indexing routines
which worked for 99% of the user/software needs.

I think the Query language adds a new dimension to how things are done.

Suprisingly I still have not seen any simple routines to get old school guys started
like Add, Update, Delete, Create etc (basics) and possibly some simple importing of flat file
routines to fill the Database.

---------------
Peter Maroudas

[Petrus Vorster]

Hi Peter

Rolf, Paul, John and Josê had helped me through from absolutely zero until I managed to make a couple of proper tools in our work sector.
I am certain they will help us through this too.

Times change and I now require SQL and I decided this is what i will master, come what may.
So in-between me writing exams I will try to learn this with small samples from everyone.

It may be nice if we share what we have learned somewhere on this forum, I think we may be surprised to see just how many guys are in this position we are in.

By end December i want to have written a reasonable query and save / Delete function in Firefly using this SQL.

But yes, It would be nice to get a "SQLITE for 1st Day Beginners Guide in Firefly" Book. Until Last week I didnt even know which .inc and dll I needed and where to go with it.