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!!
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:
''
''
''
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
''
''
''
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:
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.
'--------------------------------------------------------------------------------
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:
'--------------------------------------------------------------------------------
Function FRMMAIN_WM_DESTROY ( _
hWndForm As Dword _ ' handle of Form
) As Long
CloseDatabase
KillMultiUser
' Reset the config class
Let gclsConfig = Nothing
End Function
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
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!
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.
Your application needs to have access to the "sqlite3.dll" file. I put it in the same folder as my EXE.
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.
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.
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.
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!!!
SQLite Expert is also a great tool for testing and developing SQL queries.
Rolf
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.
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
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
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. :-[