SQLite3 database class (32-bit)

Post your FreeBASIC tips and tricks here. Please don’t post your code without including an explanation.
PaulSquires
Posts: 879
Joined: Jul 14, 2005 23:41

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 17, 2016 23:40

Looks like your VALUES are wrong. Most of the ones with STR have a trailing apostrophe but no leading apostrophe. If it is a numeric value that you are trying to save into the table then don't wrap it in any apostrophes.

Code: Select all

"       " & STR(BL.sfElev)          & "'," & _     ' <------- trailing apostrophe
        "       " & STR(BL.sfTempFah)       & "'," & _  ' <------- trailing apostrophe
        "       " & STR(BL.sfRelHumid)      & "'," & _  ' <------- trailing apostrophe
        "       " & STR(BL.sfVapPress)      & "'," & _  ' <------- trailing apostrophe

...etc...

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

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 18, 2016 22:36

Well Paul, again I must hang my head in shame! Got rid of the trailing apostrophes and all went well. I thought I had used your example in TEST.BAS exactly and I hadn't.

Now that I have the one table working correctly, should I use the <database name>.<table name> syntax to enter data in the other tables? I have a total of 4 tables contained in the one database.

Paul, thanks a million for all your help. I hope I didn't bore you to much. ;-))

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

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 19, 2016 13:39

Hi Bill, no problem at all. Just happy that I can help :-)

TriumphRacer wrote:Now that I have the one table working correctly, should I use the <database name>.<table name> syntax to enter data in the other tables? I have a total of 4 tables contained in the one database.


It is not necessary to use the <database name>.<table name> syntax. I normally just open the database and post queries using <table name> only.
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 20, 2016 19:56

Paul, thanks again. I have what I hope will be my final question. I need to extract some data from the SQLite database and assign it to a FB variable. Again, I went to your TEST.BAS example and tried my hand at it. Here is the code I came up with:

Code: Select all

' ********************
'   this sub provides user means to open an existing SQL database
' ********************
SUB db_open ()
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    stSecName = "Open an Existing SQL Database"
    heading (stSecName)
    stFileStat = "open"

    FileName ()

    stDB = LTRIM(RTRIM(stInpStrg + stFileExt))
    db.OpenDatabase(stDB)
    pause(0)

    REM     choose row from BASELINE for computations
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    LOCATE 7, 7 : PRINT "Choose record from BASELINE table for computations - "
    LOCATE 9, 7 : choice(" ",8, 5, 6)
    uiRecNo = VAL(stInpStrg)

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"

     q.PrepareQuery(db, sql)
REM        db.SqlExec(sql)

DIM nRec AS UNSIGNED INTEGER
nRec = nRec + 1
FOR i = 0 TO q.ColumnCount - 1
    BL.bl_rec_no = q.GetText(bl_rec_no)
    BL.stTestDate = q.GetText(stTestDate)
    BL.sfElev = q.GetText(sfElev)
NEXT
q.FinalizeQuery

LOCATE 5, 7 : PRINT "BL.bl_rec_no = " ; BL.bl_rec_no
LOCATE 6, 7 : PRINT "BL.stTestDate = " ; BL.stTestDate
LOCATE 7, 7 : PRINT "BL.sfElev = " ; BL.sfElev

    pause(0)
    main ()
END SUB


Here's the error msg when I attempt to compile:

Code: Select all

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
ptbcx3xx-sql.bas(375) error 41: Variable not declared, bl_rec_no in 'BL.bl_rec_no = q.GetText(bl_rec_no)'
ptbcx3xx-sql.bas(376) error 41: Variable not declared, stTestDate in 'BL.stTestDate = q.GetText(stTestDate)'
ptbcx3xx-sql.bas(377) error 41: Variable not declared, sfElev in 'BL.sfElev = q.GetText(sfElev)'


I tried with and without quotes in the "q.GetText( )" statement. A lot of what's in my code above is for testing so it can be disregarded. I believe that this is the last SQLite function I need to perform so, hopefully, I won't need to bother you anymore. Your TEST.BAS helped me a lot but you may want to consider expanding it a bit ;-)))).

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

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 20, 2016 20:58

q.GetText requires that you pass it the table field name that you want to retrieve. It is a string.
Looks like you may not have DIM'd the BL type variable that you are using to hold the returned information?
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 21, 2016 19:32

OK, let me respond in the same order.

q.GetText requires that you pass it the table field name that you want to retrieve. It is a string


Here is my table particulars:

Code: Select all

    sql = "CREATE TABLE baseline (" & _
        "bl_rec_no INTEGER   PRIMARY KEY NOT NULL," & _
        "stTestDate TEXT(10)," & _
        "sfElev DECIMAL(6, 2)," & _
        (continues on)


Code: Select all

DIM nRec AS UNSIGNED INTEGER
nRec = nRec + 1
FOR i = 0 TO q.ColumnCount - 1
    BL.bl_rec_no = q.GetText(bl_rec_no)
    BL.stTestDate = q.GetText(stTestDate)
    BL.sfElev = q.GetText(sfElev)
NEXT
q.FinalizeQuery


It looks like I have the table column names correct in both usages: bl_rec_no, stTestDate and sfElev. Am I missing some punctuation, such as a set of quotation marks? q.GetText("bl_rec_no")

Looks like you may not have DIM'd the BL type variable that you are using to hold the returned information?


Code: Select all

' *****************************
'   TYPE DEF for data for baseline SQL table
' *****************************
TYPE BASELINE
    bl_rec_no AS INTEGER        ' SQL db index
    stTestDate AS STRING * 10   ' baseline test date, 2 + - + 2 + - + 4
    sfElev AS SINGLE            ' Elev
    (continues on)
    stNote5 AS STRING * 76
END TYPE
COMMON SHARED BL AS BASELINE   


As you can see, the TYPE appears to be DIMed correctly. Do you see anything else??

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

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 22, 2016 0:20

Hi Bill,

I took a closer look and I think the problem lies in this code:

Code: Select all

DIM nRec AS UNSIGNED INTEGER
nRec = nRec + 1
FOR i = 0 TO q.ColumnCount - 1
    BL.bl_rec_no = q.GetText(bl_rec_no)
    BL.stTestDate = q.GetText(stTestDate)
    BL.sfElev = q.GetText(sfElev)
NEXT
q.FinalizeQuery


If you look at the sample code in "test.bas" you will see that after the q.PrepareQuery call, it enters a DO WHILE loop to retrieve all of the records that are returned from the SELECT sql statement:

Code: Select all

Dim nRec As Integer
Do While q.GetRow()
   nRec = nRec + 1
   ? "Display data for record #: "; nRec
   For i = 0 To q.ColumnCount - 1
      ? "Col:"; i; _
      "  Name: "; q.ColumnName(i); _
      "  Type: "; q.ColumnType(i); _
      "  DeclType: "; q.ColumnDeclType(i); _
      "  VALUE = "; q.GetText(i)    ' we could also used named fields like q.GetText("firstname")
   Next                                     
Loop
q.FinalizeQuery   ' finish with this query so destroy its resources


In your code, there is no indication that you used the q.GetRow() method which is necessary in order to retrieve a row of data from the resulting SELECT query.

Also, if you simply want to retrieve data via field names then you do not need the FOR/NEXT loop based on the q.ColumnCount.

Here is what you probably need for your code:

Code: Select all

Do While q.GetRow()
    BL.bl_rec_no = q.GetText("bl_rec_no")
    BL.stTestDate = q.GetText("stTestDate")
    BL.sfElev = q.GetText("sfElev")
Loop
q.FinalizeQuery
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 22, 2016 22:26

Thanks again Paul. The BASELINE table will never contain very many entries. The user will pick one row when he/she opens the database. The values in that row will be used for all computations thereafter.

Here is the code I have for doing that:

Code: Select all

' ********************
'   this sub provides user means to open an existing SQL database
' ********************
SUB db_open ()
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    stSecName = "Open an Existing SQL Database"
    heading (stSecName)
    stFileStat = "open"

    FileName ()

    stDB = LTRIM(RTRIM(stInpStrg + stFileExt))
    db.OpenDatabase(stDB)
    pause(0)

    REM     choose row from BASELINE for computations
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    LOCATE 7, 7 : PRINT "Choose record from BASELINE table for computations - "
    LOCATE 9, 7 : choice(" ",8, 5, 6)
    uiRecNo = VAL(stInpStrg)

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"         REM <--- Here is where the user chooses a record

     q.PrepareQuery(db, sql)
REM        db.SqlExec(sql)


Does this look OK??

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

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 24, 2016 22:34

Paul, I tried to adapt your suggestion to my code. The program will not compile. Here are the things I tried and the results:

Code: Select all

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    q.PrepareQuery(db, sql)
    DO WHILE q.GetRow()
        BL.bl_rec_no =  q.GetText("bl_rec_no")
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = q.GetText("sfElev")
    LOOP
    q.FinalizeQuery

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
ptbcx3xx-sql.bas(370) error 180: Invalid assignment/conversion in 'BL.bl_rec_no =  q.GetText("bl_rec_no")'
ptbcx3xx-sql.bas(372) error 180: Invalid assignment/conversion in 'BL.sfElev = q.GetText("sfElev")'

---------------------------------------------------------------------------------------
    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    DO WHILE q.GetRow()
        STR(BL.bl_rec_no) =  q.GetText("bl_rec_no")
        BL.stTestDate = q.GetText("stTestDate")
        STR(BL.sfElev) = q.GetText("sfElev")
    LOOP

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
ptbcx3xx-sql.bas(370) error 3: Expected End-of-Line, found '=' in 'STR(BL.bl_rec_no) =  q.GetText("bl_rec_no")'
ptbcx3xx-sql.bas(372) error 3: Expected End-of-Line, found '=' in 'STR(BL.sfElev) = q.GetText("sfElev")'

-----------------------------------------------------------------------------------------

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    q.PrepareQuery(db, sql)
    DO WHILE q.GetRow()
        BL.bl_rec_no =  q.GetText(bl_rec_no)
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = q.GetText(sfElev)
    LOOP
    q.FinalizeQuery

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
ptbcx3xx-sql.bas(370) error 41: Variable not declared, bl_rec_no in 'BL.bl_rec_no =  q.GetText(bl_rec_no)'
ptbcx3xx-sql.bas(372) error 41: Variable not declared, sfElev in 'BL.sfElev = q.GetText(sfElev)'

---------------------------------------------------------------------

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    q.PrepareQuery(db, sql)
    q.GetRow()
    DO WHILE q.GetRow()
        BL.bl_rec_no =  q.GetText("bl_rec_no")
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = q.GetText("sfElev")
    LOOP
    q.FinalizeQuery

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
ptbcx3xx-sql.bas(371) error 180: Invalid assignment/conversion in 'BL.bl_rec_no =  q.GetText("bl_rec_no")'
ptbcx3xx-sql.bas(373) error 180: Invalid assignment/conversion in 'BL.sfElev = q.GetText("sfElev")'


It appears that the problem is with attempting to extract both strings and decimals from the table. Trying to allow for that didn't seem to help.

????

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

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 24, 2016 23:05

TriumphRacer wrote:Paul, I tried to adapt your suggestion to my code. The program will not compile. Here are the things I tried and the results:


Hi Bill,

You said earlier that this was the definition of your BASELINE Type:

Code: Select all

' *****************************
'   TYPE DEF for data for baseline SQL table
' *****************************
TYPE BASELINE
    bl_rec_no AS INTEGER        ' SQL db index
    stTestDate AS STRING * 10   ' baseline test date, 2 + - + 2 + - + 4
    sfElev AS SINGLE            ' Elev
    (continues on)
    stNote5 AS STRING * 76
END TYPE
COMMON SHARED BL AS BASELINE   


Because q.GetText() returns a STRING, it would seem natural that you would have to convert it match your defined BASELINE element types. That is, you have bl_rec_no defined as an INTEGER and sfElev as a SINGLE. Therefore, they are both numeric data types so you can easily use VAL() to do the conversion:

Code: Select all

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    q.PrepareQuery(db, sql)
    DO WHILE q.GetRow()
        BL.bl_rec_no =  Val(q.GetText("bl_rec_no"))
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = Val(q.GetText("sfElev"))
    LOOP
    q.FinalizeQuery


Give that a try to see if it works okay for you.

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

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 25, 2016 22:25

Code: Select all

 sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    q.PrepareQuery(db, sql)
    DO WHILE q.GetRow()
        BL.bl_rec_no =  Val(q.GetText("bl_rec_no"))
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = Val(q.GetText("sfElev"))
    LOOP
    q.FinalizeQuery


ARGHHHHHH!! again. If you look back at what I had tried, I had my conversions done on the WRONG side of the equal sign:

Code: Select all

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"
    DO WHILE q.GetRow()
        STR(BL.bl_rec_no) =  q.GetText("bl_rec_no")
        BL.stTestDate = q.GetText("stTestDate")
        STR(BL.sfElev) = q.GetText("sfElev")
    LOOP


I'll try your suggestion and let you know. Geez, I'd be LOST without this forum!!!!!!

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

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 27, 2016 21:52

OK, yet another problem. Here is all the code for the SUB that I ended up with:

Code: Select all

SUB db_open ()
    clrArea(4, 23, 2, 79)
    stInpStrg = ""
    stSecName = "Open an Existing SQL Database"
    heading (stSecName)
    stFileStat = "open"
    stFileExt = ".db"

    FileName ()

    stDB = LTRIM(RTRIM(stInpStrg + stFileExt))
    db.OpenDatabase(stDB)

    REM     choose row from BASELINE for computations
    clrArea(4, 23, 2, 79)
    LOCATE 7, 7 : PRINT "Choose record from BASELINE table for computations - "
    LOCATE 9, 7 : choice(" ",9, 5, 6)
    uiRecNo = VAL(stInpStrg)

    sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"

    q.PrepareQuery(db, sql)
    q.GetRow()                                    <------- tried both with and without this line
    DO WHILE q.GetRow()
        BL.bl_rec_no =  VAL(q.GetText("bl_rec_no"))
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = VAL(q.GetText("sfElev"))
    LOOP
    q.FinalizeQuery

LOCATE 11, 7 : PRINT "BL.bl_rec_no = " ; BL.bl_rec_no
LOCATE 12, 7 : PRINT "BL.stTestDate = " ; BL.stTestDate
LOCATE 13, 7 : PRINT "BL.sfElev = " ; BL.sfElev
LOCATE 15, 7 : PRINT "database = ";stDB
LOCATE 16, 7 : PRINT "uiRecNo = "; uiRecNo
LOCATE 17, 7 : PRINT "q.GetRow = "; q.GetRow   <--------- initially tried it without this line, added it to check on value of q.GetRow

    pause(0)
    main ()
END SUB



And here are the results I kept getting:

Code: Select all

+------------------------------------------------------------------------------+
¦       Performance Tuner - Console Version w/SQLite database, bc03.003-26-2016¦
+------------------------------------------------------------------------------+
¦                                                                              ¦
¦                                                                              ¦
¦                                                                              ¦
¦     Choose record from BASELINE table for computations -                     ¦
¦                                                                              ¦
¦    [1_____]                                                                  ¦
¦                                                                              ¦
¦     BL.bl_rec_no =  0                                                        ¦
¦     BL.stTestDate =                                                          ¦
¦     BL.sfElev =  0                                                           ¦
¦                                                                              ¦
¦     database = example.db                                                    ¦
¦     uiRecNo = 1                                                              ¦
¦     q.GetRow = false                                                         ¦
¦                                                                              ¦
¦                                                                              ¦
¦                                                                              ¦
¦                                                                              ¦
¦                                                                              ¦
¦     Press any key to continue ...                                            ¦
¦                                                                              ¦
+---------------copyright 2002-2016, Bill Keller, Keller Racing----------------+


Any idea why my q.GetRow has a value of "false"?? I've checked the example.db using SQLite Database Browser v2.0 and the structure is correct and all the values are correct and in the correct location. So again I'm at a loss.

Could this conversion be the problem?? sql = "SELECT * FROM baseline WHERE bl_rec_no = " + STR(uiRecNo) + ";"

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

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 28, 2016 5:09

Hard to tell where the problem is.

If you like, you can email me your code and sample database and I will trace through it to find the error.
support@planetsquires.com
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Mar 28, 2016 21:13

Thanks Paul. I rec'd your email and I'll fix my code. I did want to show you what I had tried in the meantime.

Code: Select all

    q.PrepareQuery(db, sql)
    q.GetRow()
    DO WHILE q.GetRow()
        BL.bl_rec_no =  VAL(q.GetText("bl_rec_no"))
        BL.stTestDate = q.GetText("stTestDate")
        BL.sfElev = VAL(q.GetText("sfElev"))
    LOOP
LOCATE 18, 7 : PRINT "before q.FinalizeQuery q.GetRow = "; q.GetRow() <------- added this line
    q.FinalizeQuery


Now when I run the program, I get this:

Code: Select all


     BL.bl_rec_no =  0
     BL.stTestDate =
     BL.sfElev =  0

     database = example.db
     uiRecNo = 1
     q.GetRow = false
     in DO WHILE q.GetRow = true


Notice q.GetRow() is now true. Just wanted to let you know.

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

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Mar 29, 2016 12:25

Hi Bill,

You should not do this to test the q.GetRow value:

Code: Select all

LOCATE 18, 7 : PRINT "before q.FinalizeQuery q.GetRow = "; q.GetRow() <------- added this line


Doing it that way actually calls the GetRow() method again rather than telling you what the most recent return value of the call is!

You have two options:

(1) Save the return value in a variable and then display that value:
eg.
Dim nRet As Long
nRet = q.GetRow()

(2)
Save the LastError value immediately after calling the GetRow() method:
eg.
DO WHILE q.GetRow()
nRet = q.LastError

LOCATE 18, 7 : PRINT "q.GetRow = "; nRet

I guess that the lesson here is that q.GetRow() is not a variable, it is a method that actually does something every time you reference it.

Return to “Tips and Tricks”

Who is online

Users browsing this forum: No registered users and 4 guests