SQLite3 database class (32-bit)

[TriumphRacer]

by PaulSquires » Feb 26, 2016 15:37
Hi Bill, hope it works for you!

The SQLite dll that I tested my code with is Version 3.8.11.1 dated 2015-07-30 (665KB).

Paul, it worked wonderfully! Of course, it then exposed all MY programming errors ;-(((((((. It also caused me to examine (and alter) my program flow.

I can see I'm going to have to upgrade my version of SQLite.

Just to satisfy my own curiousity, what within the ext bi file caused the problem?????

Bill

[PaulSquires]

Just to satisfy my own curiousity, what within the ext bi file caused the problem?????

That's a good question. I know that I tried to find out a few days ago when I wrote you but I couldn't figure it out. Seems like it has to do with the definition (or lack thereof) of "sqlite_api" within that file. I wonder if it was correctly translated from the original C header files. I have never had a need to use that header because I believe it is intended for programmers who need to write extensions/addons to the original internal sqlite functions.

"Variable not declared, sqlite3_api"

[TriumphRacer]

Paul, first, let me thank you again for wonderful program. Your example program, TEST.BAS, was SOOOOO helpful. Made so many things much, much easier. One question though. In TEST.BAS

Code: Select all

' //
' //  Create a table 
' //
' TEXT
' NUMERIC
' INTEGER
' REAL
' BLOB
Sql = "CREATE TABLE employees( firstname TEXT, lastname TEXT, salary REAL )"
db.SqlExec(sql)
? "Create Table LastError: "; db.LastError

' //
' //  Insert some data
' //
db.StartTransaction
For i = 1 To 10                           
   sql = "INSERT INTO employees (firstname, lastname, salary)" & _
         " VALUES( '" & db.SafeSql("Paul")    & "'," & _
         "         '" & db.SafeSql("Squires") & "'," & _
         "          " & Str(150000+(i*1000)) & ");"
   db.SqlExec(sql)
Next
db.EndTransaction

In the "CREATE A TABLE" section, you clearly designate salary as a REAL. However in the "INSERT SOME DATA" section, you convert it to a STR. Does this mean that when I want to use it, I'll have to convert it back to a REAL using a VAL( ) statement?

Other than that, everything has worked better than expected. Your extremely clear examples are to be credited with this.

Thanks again and I hope you don't mind me pestering you. I'm not a programmer but when I see a need, I like to learn enough to provide a usable program.

Bill

[PaulSquires]

Paul, first, let me thank you again for wonderful program. Your example program, TEST.BAS, was SOOOOO helpful. Made so many things much, much easier.

Awesome! Really happy it helped you. :-)

One question though. In TEST.BAS
In the "CREATE A TABLE" section, you clearly designate salary as a REAL. However in the "INSERT SOME DATA" section, you convert it to a STR. Does this mean that when I want to use it, I'll have to convert it back to a REAL using a VAL( ) statement?

I used STR because I need to convert the value to a STRING in order to concatenate it to the rest of the SQL query string that I was constructing to send to the sqlite engine. You can see that I did not use a single quote ' character around that string so when the SQL query is executed by sqlite it will attempt to store it as a numeric/REAL. Data types in sqlite are "weird" because you can store just about anything to the database without really having a fine regard to the actual data type. You can store strings as values, values as strings.... Sqlite has a concept known as column affinity which can be confusing when you come from database engines that have strict rules about storage values (that is, columns defined as numeric can only be given numbers, character fields only strings, etc... Errors are thrown otherwise).

When you retrieve data back from the database you may have to use VAL to convert the string to a numeric, although I think that I have methods built into the class that retrieve data as various numeric types.

[TriumphRacer]

Thanks Paul. I've done some dBASE III+ and Clipper programming in the past and the difference between that and SQLite are substantial. So while I am experiencing some "culture shock", with the great help on this forum, I'm managing to muddle my way through it.

Bill ;-))))))

[TriumphRacer]

Paul, I'm back, hat in hand, asking for more help. So far all my routines for SQLite have worked fine. i did run into a problem when I tried to incorporate them into my freeBASIC program. When I issue a sql_exec statement, and it works, the statement returns an exit code of 0 (zero) and kicks me out my FB program, Is there some way I can "trap" this exit code so it doesn't kick me out of the program??

Bill

[Kot]

Try to run it in console mode, not from editor.

[TriumphRacer]

Paul, I'm attempting to run the actual program. Below is the initial screen (please assume that all the right side vertical bars line up)

Code: Select all

╔══════════════════════════════════════════════════════════════════════════════╗
║       Performance Tuner - Console Version w/SQLite database, bc03.003-13-2016║
╚══════════════════════════════════════════════════════════════════════════════╝
│                                  MAIN MENU                                   │
│                                                                              │
│     Create New Database .................. 1                                 │
│     Open Existing Database ............... 2                                 │
│                                                                              │
│     Enter Baseline Data .................. 3                                 │
│     Enter Event Data ..................... 4                                 │
│     Enter Current Data ................... 5                                 │
│     Enter Timeslip Data .................. 6                                 │
│                                                                              │
│     Analyze Data ......................... 7                                 │
│     Compute ET & MPH Correction Factors .. 8                                 │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│     Exit Program ..... X                                                     │
│                                                                              │
│     Enter Function you wish to use ... [__]                                  │
│                                                                              │
└───────────────copyright 2002-2016, Bill Keller, Keller Racing────────────────┘

Now I choose Item 1, "Create New Database", the next few lines allow you to name it.

Code: Select all

╔══════════════════════════════════════════════════════════════════════════════╗
║       Performance Tuner - Console Version w/SQLite database, bc03.003-13-2016║
╚══════════════════════════════════════════════════════════════════════════════╝
│                           Create New SQL Database                            │
│                                                                              │
│                                                                              │
│     DO NOT ENTER FILE EXTENSION!  ENTER NAME ONLY!                           │
│                                                                              │
│     Enter the name to save the file under (8 characters max!) ..[EXAMPLE_]   │
│                                                                              │
│                                                                              │
│   Create TABLE LastError:  0                                                 │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│                                                                              │
│     Press any key to continue ...                                            │
│                                                                              │
└───────────────copyright 2002-2016, Bill Keller, Keller Racing────────────────┘

Now when I press <Enter>, even though the sql_exec worked correctly (0 error), it bails me completely out of my program. I've checked the actual database created and it's exactly as I wanted. I've tried it with and without the "LastError" routine. Same result.

Any suggestions?

Bill

[TriumphRacer]

Paul, the SQL exit code was NOT the problem. I tried all my other main menu routines and they all acted the same. At the end of the SUB, instead of returning to the calling SUB, which is the reaction I'm used to, all the procedures exited the entire program. As a workaround, I named my main menu as SUB main - END SUB and I call main() at the end of each procedure. Not the most elegant solution but it works. Any comments???

Bill

[PaulSquires]

Hi Bill,

Not sure. You may need to post the code that you are using for your main sub loop. I may be able to see why after your sub call to create the database that then the loop is exited.

[TriumphRacer]

Hi Paul. Here's my code for the entrance to my program

Code: Select all

REM GOTO start:
REM start:
main ()
SUB main()
    CLS
    'ON ERROR GOTO errtrap
    frame (stCP)
    banner (stProgName)
    REM     register()

    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    stSecName = "MAIN MENU"
    heading (stSecName)

    LOCATE 6, 7: PRINT "Create New Database .................. 1"
    LOCATE 7, 7: PRINT "Open Existing Database ............... 2"

    LOCATE 9, 7: PRINT "Enter Baseline Data .................. 3"
    LOCATE 10, 7: PRINT "Enter Event Data ..................... 4"
    LOCATE 11, 7: PRINT "Enter Current Data ................... 5"
    LOCATE 12, 7: PRINT "Enter Timeslip Data .................. 6"


    LOCATE 14, 7: PRINT "Analyze Data ......................... 7"
    LOCATE 15, 7: PRINT "Compute ET & MPH Correction Factors .. 8"

    COLOR 15, 0
    LOCATE 21, 7: PRINT "Exit Program ..... X";
    COLOR 7, 0

    clrArea(22, 23, 2, 79)
    choice("Enter Function you wish to use ... ", 23, 7, 2)

    SELECT CASE UCASE$(stInpStrg)
        CASE IS = "1"
            db_create ()
        CASE IS = "2"
            db_open ()
        CASE IS = "3"
            baseline_data ()
        CASE IS = "4"
            event_data ()
        CASE IS = "5"
            current_data ()
        CASE IS = "6"
            timeslip_data ()
        CASE IS = "7"
            analyze ()
        CASE IS = "8"
            comp_correction_fact()            ' compute correction factors
        CASE IS = "X"
            db.CloseDatabase ()
            CLS
            END
        CASE ELSE
            Wrong
    END SELECT

END SUB

As you can see, at the beginning, I used to simply use a GOTO to get things rolling. I can live with the DECLARE SUB main () thing. All my procedures work as planned now. However, another SQL question. The program constructs the database correctly. However when I try to do an INSERT statement, it doesn't work. Here's my INSERT statement:

Code: Select all

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

    db.StartTransaction
    sql = "INSERT INTO " + stTable + "(" & _
        "stTestDate," & _
        "sfElev," & _
        "sfTempFah," & _
        "sfRelHumid," & _
        "sfVapPress," & _
        "sfBaroPress," & _
        "stType," & _
        "sfMaxTrq," & _
        "sfMaxHP," & _
        "sfCorrecFact," & _
        "stCorrecType," & _
        "sfWgt," & _
        "sfMPHcf," & _
        "sfETcf," & _
        "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)           & "'," & _
        "       " & STR(BL.sfMPHcf)         & "'," & _
        "       " & STR(BL.sfETcf)          & "'," & _
        "      '" & 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
LOCATE 12, 5 : PRINT "Create BASELINE entry LastError: ";db.LastError
    pause(0)
END SUB

Do you see anything out of whack? I have tried the stTable with and without the "example" prefix.

Bill

[Andrew Lindsay]

Hey,
Not knowing what the rest of the structure is here, it seems that the menu is a once through check. That is, I think you need to encapsulate the whole menu in a WHILE - WEND loop or similar.

i.e.

Code: Select all

While UCASE$(stInpStrg) <> "X"
    CLS
    'ON ERROR GOTO errtrap
    frame (stCP)
    banner (stProgName)
    REM     register()
         .
         .
         .
REM        CASE IS = "X"                                    ' As we no longer check for "X" here
REM            db.CloseDatabase ()                          ' we can remove the select case from here
REM            CLS                                          ' and leave closing the database and entry 
REM            END                                          ' outside the loop
        CASE ELSE
            Wrong
    END SELECT
    WEND
    db.CloseDatabase ()
    CLS
    END
    

[PaulSquires]

Hi Bill,

Andrew is right on with the need to have your code in a loop.

I took a look at your sql statement. I see two errors for sure:

"stNote5," & _
");" & _
"VALUES('" & db.SafeSql(BL.stTestDate) & "'," & _

Notice that right after stNote5 that you have a comma. If that is the last field in the list then it is never followed by a comma.
Notice the line ");". You need to remove the semicolon and replace it with a space. The semicolon should only follow the end of the complete sql statement. You placed it between the INSERT and VALUES commands. It should only be placed at the very end of the statement (which you have correctly done).

" '" & db.SafeSql(BL.stNote5) & ");"

[TriumphRacer]

"stNote5," & _
");" & _
"VALUES('" & db.SafeSql(BL.stTestDate) & "'," & _

Notice that right after stNote5 that you have a comma. If that is the last field in the list then it is never followed by a comma.
Notice the line ");". You need to remove the semicolon and replace it with a space. The semicolon should only follow the end of the complete sql statement. You placed it between the INSERT and VALUES commands. It should only be placed at the very end of the statement (which you have correctly done).

ARGHHHHH!!!!!!! As soon as you pointed those errors out, I knew what I did. I got lost in the punctuation and didn't pay attention. Thanks again Paul. When you're a neophyte, sometimes all it takes is a seasoned set of eyes to see stupid errors. ;'-(((((

And thanks to Andrew also. Although I've never had to use a loop before (not that I'm an experienced programmer), I can see the benefit to using one.

Bill

[TriumphRacer]

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
LOCATE 12, 5 : PRINT "Create TABLE LastError: ";db.LastError
    pause(0)
END SUB

Paul, I made the changes you suggested. I also tried several derivatives of stTable = "baseline". Tried it with different cases (upper and lower), tried it with prefix (example.baseline), also upper and lower case. I use SQLite Database Browser v2.0 to check the database structure and contents. When I create the database, and then view it, the structure is fine. However after running the INSERT statement, there is nothing inside the database. I must admit, I'm getting kind of lost trying to insert SQLite syntax into freeBASIC. Do you see anything else I'm doing incorrectly???

Bill