SQLite3 database class (32-bit)

PaulSquires · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **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.

SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)

Re: SQLite3 database class (32-bit)