SQLite3 database class (32-bit)

Post your FreeBASIC tips and tricks here. Please don’t post your code without including an explanation.
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 30, 2016 21:42

Paul, thanks again. Your explanations are worth their worth in gold. Now I also know WHY q.GetRow acts the way it does.

As an aside, so far everything is working well. Now I have to run some test data through the program. At this point I'm not sure, but my history says I will need your help again. I have to do some AVG's on several columns to develop some values for use in my computations. Once those are figured out, I can finish up the program.

P.P.S. - In the past, I have converted several of my simpler programs to Windows versions using IUP which is, at least to me, relatively understandable. I have heard lots of good things about FireFly and I may try it in the near future.

Bill
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Apr 02, 2016 22:15

Paul, this should be my last plea for help. Please look at the following code:

Code: Select all

SUB c_c_et_cf ()            ' compute CURRENT et correction factor

    DIM stSQL AS STRING
    DIM q AS clsSqliteQuery
    DIM db AS clsSqlite
    DIM sfET_avg AS SINGLE

    stSQL = "SELECT AVG ('sfTIME_1320_ft') FROM timeslip ;"
    q.PrepareQuery(db, stSQL)
REM    sfET_avg =

    q.FinalizeQuery

REM    dfCRETCF = (((dfCRET)^3 * dfCRHP)/CR.sfWgt)^.33333
REM    dfCRETCF = (((dfexET)^3 * dfCRHP)/CR.sfWgt)^.33333
    dfCRETCF = (((sfET_avg)^3 * dfCRHP)/CR.sfWgt)^.33333

END SUB


I need to extract the result of the SQL statement and assign it to the variable sfET_avg. Suggestions?

BTW, I went to the FireFly website to download the latest version. So you're the author of FF? I haven't tried to use it yet but kudos to you for what looks like a wonderful editor. I also went on te SQLite client/server website. Is that also your program? I intend to download it also. This would just be a curiousity thing on my part. I'm really interested in how you did it.

Bill
PaulSquires
Posts: 879
Joined: Jul 14, 2005 23:41

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Apr 02, 2016 22:57

TriumphRacer wrote:I need to extract the result of the SQL statement and assign it to the variable sfET_avg. Suggestions?


Hi Bill,

In SQL you should create a variable to hold your AVG. Something like this:

Code: Select all

   stSQL = "SELECT AVG ('sfTIME_1320_ft') AS avgTime FROM timeslip ;"
   q.PrepareQuery(db, stSQL)
   q.GetRow()
   sfET_avg = Val(q.GetText(avgTime))


TriumphRacer wrote:BTW, I went to the FireFly website to download the latest version. So you're the author of FF? I haven't tried to use it yet but kudos to you for what looks like a wonderful editor. I also went on te SQLite client/server website. Is that also your program? I intend to download it also. This would just be a curiousity thing on my part. I'm really interested in how you did it.


Yes, I wrote FireFly over the last number of years. It is only in the past couple of years that I have concentrated on outputting FreeBasic source code. I have migrated all my programming from PowerBasic to FreeBasic.

SQLitening is a pretty powerful client/server platform for SQLite. I originally wrote a client/server for SQLite but i was subsequently approached by another programmer, Fred Meier, who had a much more complete and real world tested version. We decided to use his code. I maintain the website and distribute the source code Sadly, Fred passed away a couple of years ago.
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Apr 03, 2016 21:50

Thanks again, Paul. It compiled fine. The name of the function, q.GetRow(), threw me off. I thought all it could do was return a row or a series of rows. Oh well, another incorrect assumption on my part.

Now I have enter about 10 yrs. of data :'-(((((

Bill
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Apr 12, 2016 21:45

Well, have to ask for help again. I've spent the last 1 1/2 wks going over this code and can't figure out what's wrong.

First, I have 1 database with 4 tables. I need to populate the tables in the following order: baseline, event, current, timeslip.

The first 2 work fine. Here is the code for baseline:

Code: Select all

' ********************
'   this sub provides user means to save baseline data
' ********************
SUB file_save_baseline ()
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    stSecName = "Save Baseline Data (To SQL Database)"
    heading (stSecName)
    stTable = "baseline"

    db.StartTransaction
    sql = "INSERT INTO " + stTable + " (" & _
        " stTestDate," & _
        " sfElev," & _
        " sfTempFah," & _
        " sfRelHumid," & _
        " sfVapPress," & _
        " sfBaroPress," & _
        " stType," & _
        " sfMaxTrq," & _
        " sfMaxHP," & _
        " sfCorrecFact," & _
        " stCorrecType," & _
        " sfWgt," & _
        " stNote1," & _
        " stNote2," & _
        " stNote3," & _
        " stNote4," & _
        " stNote5 " & _
        ") " & _
        "VALUES('" & db.SafeSql(BL.stTestDate)  & "'," & _
        "       " & STR(BL.sfElev)          & "," & _
        "       " & STR(BL.sfTempFah)       & "," & _
        "       " & STR(BL.sfRelHumid)      & "," & _
        "       " & STR(BL.sfVapPress)      & "," & _
        "       " & STR(BL.sfBaroPress)     & "," & _
        "      '" & db.SafeSql(BL.stType)   & "'," & _
        "       " & STR(BL.sfMaxTRQ)        & "," & _
        "       " & STR(BL.sfMaxHP)         & "," & _
        "       " & STR(BL.sfCorrecFact)    & "," & _
        "      '" & db.SafeSql(BL.stCorrecType)    & "'," & _
        "       " & STR(BL.sfWgt)           & "," & _
        "      '" & db.SafeSql(BL.stNote1)  & "'," & _
        "      '" & db.SafeSql(BL.stNote2)  & "'," & _
        "      '" & db.SafeSql(BL.stNote3)  & "'," & _
        "      '" & db.SafeSql(BL.stNote4)  & "'," & _
        "      '" & db.SafeSql(BL.stNote5)  & "');"
        db.SqlExec(sql)
        db.EndTransaction
       
    IF db.LastError <> 0 THEN
        LOCATE 12, 5 : PRINT "Insert BASELINE DATA LastError: ";db.LastError
    ENDIF

    pause(0)
END SUB


It works fine, as does the event table. Here is the code for current:

Code: Select all

' ********************
'   this sub provides user means to save current data
' ********************
SUB file_save_current ()
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    stSecName = "Save Current Data (To SQL Database)"
    heading (stSecName)
    stTable = "current"

    db.StartTransaction
    sql = "INSERT INTO " + stTable + " (" & _
        " stCurDate,"   & _
        " stCurTime,"   & _
        " stAMPM,"      & _
        " sfTempFah,"   & _
        " sfRelHumid,"  & _
        " sfVapPress,"  & _
        " sfBaroPress," & _
        " stType,"      & _
        " sfWgt,"       & _
        " sfMPHcf,"     & _
        " sfETcf,"      & _
        " stNote1,"     & _
        " stNote2,"     & _
        " stNote3,"     & _
        " stNote4,"     & _
        " stNote5 "     & _
        ") "            & _
        "VALUES('" & db.SafeSql(CR.stCurDate)   & "'," & _
        "      '" & db.SafeSql(CR.stCurTime)    & "'," & _
        "      '" & db.SafeSql(CR.stAMPM)       & "'," & _
        "       " & STR(CR.sfTempFah)           & "," & _
        "       " & STR(CR.sfRelHumid)          & "," & _
        "       " & STR(CR.sfVapPress)          & "," & _
        "       " & STR(CR.sfBaroPress)         & "," & _
        "      '" & db.SafeSql(CR.stType)       & "'," & _
        "       " & STR(CR.sfWgt)               & "," & _
        "       " & STR(CR.sfMPHcf)             & "," & _
        "       " & STR(CR.sfETcf)              & "," & _
        "      '" & db.SafeSql(CR.stNote1)      & "'," & _
        "      '" & db.SafeSql(CR.stNote2)      & "'," & _
        "      '" & db.SafeSql(CR.stNote3)      & "'," & _
        "      '" & db.SafeSql(CR.stNote4)      & "'," & _
        "      '" & db.SafeSql(CR.stNote5)      & "');"
        db.SqlExec(sql)
        db.EndTransaction

        IF db.LastError <> 0 THEN
            LOCATE 12, 5 : PRINT "Save CURRENT DATA LastError: ";db.LastError
        ENDIF
    pause(0)
END SUB


This code does not save the data to the current table. I've compared this line by line with the code for the baseline table and I can't see any difference. I'm hoping a fresh set of eyes will spot my error. Oh, I don't get any db.LastError's to report.

Bill
PaulSquires
Posts: 879
Joined: Jul 14, 2005 23:41

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Apr 13, 2016 1:12

TriumphRacer wrote:

Code: Select all

        db.SqlExec(sql)
        db.EndTransaction

        IF db.LastError <> 0 THEN
            LOCATE 12, 5 : PRINT "Save CURRENT DATA LastError: ";db.LastError
        ENDIF
    pause(0)
END SUB


This code does not save the data to the current table. I've compared this line by line with the code for the baseline table and I can't see any difference. I'm hoping a fresh set of eyes will spot my error. Oh, I don't get any db.LastError's to report.

Bill


I would suggest that you check LastError immediately after the db.SqlExec call. That is where the error is most likely being thrown. I expect the db.EndTransaction is executing correctly so the LastError gets reset to zero at that time....so, by the time that you are actually testing the LastError value it has already been reset to zero.
PaulSquires
Posts: 879
Joined: Jul 14, 2005 23:41

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Apr 13, 2016 1:19

Also, check that you are specifying the correct field names contained in your current table. The names of the fields you are using in your sql statement are different than the ones contained in the database that you emailed to me a couple of weeks ago. However, I imagine that you probably modified the table structures since then.

Return to “Tips and Tricks”

Who is online

Users browsing this forum: No registered users and 4 guests