Changing Direction with SQlite

[Petrus Vorster]

Hi all

I have always used my own primitive flat files for handling my data.
I have peeked into Sqlite and even used SqliteExpert 3 to create the tables etc.
(I have vowed to do  this once my studies were complete, and this is now the time to do this.)

So far so good. Many tutorials on the web only refers to column outputs and related .Net tools.
I am again starting this topic from Rock-bottom, and realised i cannot even make a simple written query in Powerbasic.
(Using Josè's CSqlite class)

Just for a start, a simple query like:

Code Select Expand

sSql = "Select rowid, * From Employees ;"

(I want all the records in the table into a listview)

If someone can AGAIN please show me a few very basic tips starting with this query, i will pick it up eventually.

Thanks for all the help.

[Petrus Vorster]

Creating the actual query string is not the difficult part.
Sqlite expert writes you a perfect query string.

The little bit that i still need to figure out if after you executed the Query string.
You create a loop in which the database returns any data which it found.

How do you get that data from there into your listbox?
I assume it returns strings?

I am a bit vague on this little piece.

[Paul Squires]

Code Select Expand


' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Step.bas
' Contents: CSQLite class example
' Connects to a database and reads records.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pSQL AS ISQLite
   pSQL = CLASS "CSQLite"
   IF ISNOTHING(pSQL) THEN EXIT FUNCTION

   ' // Create a connection object
   LOCAL pDbc AS ISQLiteConnection
   pDbc = pSQL.Connection
   IF ISNOTHING(pDbc) THEN EXIT FUNCTION

   TRY
      ' // Delete our test database if it exists
      IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
      ' // Create a new database
      pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
      ' // Create a table
      pDbc.Exec("CREATE TABLE t (xyz text)")
      ' // Insert rows
      pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')")
      pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')")
'      ' // Prepare a query
      LOCAL pStmt AS ISQLiteStatement
      pStmt = pDbc.Prepare("SELECT * FROM t")
      ? "Column count:" & STR$(pStmt.ColumnCount)
      ' // Read the column names and values
      LOCAL i AS LONG
      DO
         ' // Fetch rows of the result set
         IF pStmt.Step = %SQLITE_DONE THEN EXIT DO
         ' // Read the columns and values
         FOR i = 0 TO pStmt.ColumnCount- 1
            ? pStmt.ColumnName(i)
            ? pStmt.ColumnText(i)
         NEXT
      LOOP
   CATCH
     ' // Display error information
      ? pSql.OleErrorInfo
   END TRY

   ' // Cleanup
   pStmt = NOTHING   ' // Deletes the prepared statement
   pDbc = NOTHING    ' // Closes the database
   pSQL = NOTHING

   #IF %DEF(%PB_CC32)
      WAITKEY$
   #ENDIF

END FUNCTION
' ========================================================================================



[Paul Squires]

Code Select Expand


' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Exec.bas
' Contents: CSQLite class example
' Connects to a database and reads records.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pSQL AS ISQLite
   pSQL = CLASS "CSQLite"
   IF ISNOTHING(pSQL) THEN EXIT FUNCTION

   ' // Create a connection object
   LOCAL pDbc AS ISQLiteConnection
   pDbc = pSQL.Connection
   IF ISNOTHING(pDbc) THEN EXIT FUNCTION

   TRY
      ' // Delete our test database if it exists
      IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
      ' // Create a new database
      pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
      ' // Create a table
      LOCAL sql AS STRING
      Sql = "CREATE TABLE t (xyz text)"
      pDbc.Exec(sql)
      ' // Prepare the statement
      sql = "INSERT INTO t (xyz) VALUES (?)"
      LOCAL pStmt AS ISQLiteStatement
      pStmt = pDbc.Prepare(sql)
      ' // Bind the text
      pStmt.BindText(1, "fruit")
      pStmt.Step
      ? "Row id was" & STR$(pDbc.LastInsertRowId)
      ' // Delete the prepared statement
      pStmt = NOTHING
      ' // Close the database
      pDbc.CloseDatabase
      ' // Open existing database for reading
      pDbc.OpenDatabase2(EXE.PATH$ & "Test.sdb", %SQLITE_OPEN_READONLY)
      ' // Create a table
      Sql = "SELECT * FROM t"
      pDbc.Exec(sql, CODEPTR(SQLite_Exec_Callback))
   CATCH
     ' // Display error information
      ? pSql.OleErrorInfo
   END TRY

   ' // Cleanup
   pDbc = NOTHING   ' // Closes the database
   pSQL = NOTHING

   #IF %DEF(%PB_CC32)
      WAITKEY$
   #ENDIF

END FUNCTION
' ========================================================================================

' ========================================================================================
' Callback function for the Exec method.
' Return value:
' If returns non-zero, the Exec method returns SQLITE_ABORT without invoking the callback
' again and without running any subsequent SQL statements.
' ========================================================================================
FUNCTION SQLite_Exec_Callback CDECL(BYVAL pData AS DWORD, BYVAL numCols AS LONG, BYVAL pszColValues AS ASCIIZ PTR, BYVAL pszColNames AS ASCIIZ PTR) AS LONG

   LOCAL i AS LONG

   REDIM ColValues (0 TO numCols) AS ASCIIZ PTR AT pszColValues
   REDIM ColNames (0 TO numCols) AS ASCIIZ PTR AT pszColNames
   
   FOR i = 0 TO numCols - 1
      ? "Column name: " & @ColNames(i)
      ? "Column value: " & @ColValues(i)
   NEXT

END FUNCTION
' ========================================================================================



[Paul Squires]

Code Select Expand


' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Insert.bas
' Contents: CSQLite class example
' Demonstrates the basic steps to use the CSQLite class to connect to a database and prepare
' an statement.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pSQL AS ISQLite
   pSQL = CLASS "CSQLite"
   IF ISNOTHING(pSQL) THEN EXIT FUNCTION

   ' // Create a connection object
   LOCAL pDbc AS ISQLiteConnection
   pDbc = pSQL.Connection
   IF ISNOTHING(pDbc) THEN EXIT FUNCTION

   TRY
      ' // Delete our test database if it exists
      IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
      ' // Create a new database
      pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
      ' // Create a table
      LOCAL sql AS STRING
      Sql = "CREATE TABLE t (xyz text)"
      pDbc.Exec(sql)
      ' // Prepare the statement
      sql = "INSERT INTO t (xyz) VALUES (?)"
      LOCAL pStmt AS ISQLiteStatement
      pStmt = pDbc.Prepare(sql)
      ' // Bind the text
      pStmt.BindText(1, "fruit")
      pStmt.Step
      ? "Row id was" & STR$(pDbc.LastInsertRowId)
   CATCH
     ' // Display error information
      ? pSql.OleErrorInfo
   END TRY

   ' // Cleanup
   pStmt = NOTHING   ' // Deletes the prepared statement
   pDbc = NOTHING    ' // Closes the database
   pSQL = NOTHING

   #IF %DEF(%PB_CC32)
      WAITKEY$
   #ENDIF

END FUNCTION
' ========================================================================================



[Paul Squires]

Attached is Jose's help file for his sqlite classes, etc.

[Petrus Vorster]

Thanks Paul

I was so close.
I simply needed to understand that return values.

I will keep all this and experiment a lot.

Thank you for your patience and help.

[Petrus Vorster]

Ok, Now i have actually retrieved some data. WHOOHOO.

However, the Table is set up for Employees:
SalaryRef , Surname, initials, FirstName, Branch.

If i run the query "select * from Employees" it retrieves:
Surname from Record1
Initals from record 2
Firstname from Record 3...etc

So how do i get all the columns in each record set first, and then retrieve the next record?

I should have done this years ago, its actually getting pretty interesting...

[Paul Squires]

Hi Peter,

Post the code you are using to create the SQL query and the loop you're using to retrieve the data. Every time a pStmt.Step is executed it moves to the next record in the dataset. Using SQL and SQLite is incredibly powerful and eliminates mountains of otherwise tedious data manipulation code. The biggest "struggle" is learning how to create the SQL queries especially when multiple tables and various joins are involved.

[Klaas Holland]

This is the way for MYSQL with SQLTools into a Listview

Code Select Expand

TYPE ArtikelType  BYTE
    ArtNr        AS STRING * 4
    Omschr       AS STRING * 30
    VerpEh       AS STRING * 6
    PrijsEh      AS STRING * 6
    BtwCode      AS STRING * 2
    VPrijs       AS STRING * 8
    Kosten       AS STRING * 8
    IPrijs       AS STRING * 8
    Voorr        AS STRING * 8
    UitGel       AS STRING * 8
END TYPE


    SQL_Stmt %IMMEDIATE, "SELECT * FROM Artikel "  _
                       + "GROUP BY Artikel.ArtNr "
    Do
        If SQL_Fetch <> %SQL_SUCCESS Then Exit Do

        ART.ArtNr   = SQL_ResColString(1)
        ART.Omschr  = SQL_ResColString(2)
        ART.VerpEh  = SQL_ResColString(3)
        ART.Voorr   = SQL_ResColString(9)
        ART.UitGel  = SQL_ResColString(10)

        FF_ListView_InsertItem  ListVw, RowNr, 0, Using$( "#### \                       \ ", Val(ART.ArtNr), ART.Omschr)
        FF_ListView_SetItemText ListVw, RowNr, 1, Format$(Val(ART.VerpEh))
        FF_ListView_SetItemText ListVw, RowNr, 2, Format$(Val(ART.UitGel))
        FF_ListView_SetItemText ListVw, RowNr, 3, Format$(Val(ART.Voorr))
        RowNr = RowNr + 1

    Loop


[Petrus Vorster]

FINALLY!

Code Select Expand

Type myemployee Byte
salaryref As String * 15
Surname   As String * 30
Initials  As String * 6
Firstname As String * 25
Rank      As String * 30
Branch    As String * 30

End Type

Code Select Expand

Function FORM1_COMMAND1_BN_CLICKED ( _
                                   ControlIndex     As Long,  _  ' index in Control Array
                                   hWndForm         As Dword, _  ' handle of Form
                                   hWndControl      As Dword, _  ' handle of Control
                                   idButtonControl  As Long   _  ' identifier of button
                                   ) As Long
                                   


Local pStmt      As ISQLiteStatement
Local sSQL       As String
Dim XEMPL As myemployee


   sSql = "Select * From Employees ;"

   pStmt = pDbc.Prepare( sSQL )
   
   Do
    For t& = 1 To Pstmt.columncount - 1
      If pStmt.Step = %SQLITE_DONE Then Exit Do

      xempl.salaryref = pStmt.Columntext(0)
      xempl.surname   = pStmt.Columntext(1)
      xempl.initials  = pStmt.Columntext(2)
      xempl.firstname = pStmt.Columntext(3)
     
     
     
     
      GoSub addmore 
    Next t& 
      ' Do stuff with the ActivityType and EntryDate
      ' ...
      ' ...
               
   Loop

Let pstmt = Nothing

Exit Function
addmore:

    myspot& = FF_ListView_GetItemCount(hwnd_form1_listview1)
   
     FF_ListView_InsertItem(hwnd_form1_listview1,myspot&,0,xempl.salaryref)
     FF_ListView_InsertItem(hwnd_form1_listview1,myspot&,1,xempl.surname)
     FF_ListView_InsertItem(hwnd_form1_listview1,myspot&,2,xempl.initials)
     FF_ListView_InsertItem(hwnd_form1_listview1,myspot&,3,xempl.firstname)

Return

   Function = 0   ' change according to your needs

A little bit of Paul's suggestions, and adapted a bit from Klaas, and finally, my first correct SQL query into a Listview.

Thanks gentlemen, its baby steps for me on this, but the bug has bitten. and i am sure with a couple more questions here, I will soon be able to move over completely soon!!

[Paul Squires]

Hi Peter,

Instead of using absolute ordinal column numbers, I would retrieve the text from the query using the column name. This would make it easier later should the column ordering change or the database table is modified.

Something like this, I think:

DO
   ' For t& = 1 To Pstmt.columncount - 1  (you can now eliminate the need to FOR/NEXT the columncount
      If pStmt.Step = %SQLITE_DONE Then Exit Do

      xempl.salaryref = pStmt.Columntext( pStmt.ColNameToIdx("Salary") )
      xempl.surname   = pStmt.Columntext( pStmt.ColNameToIdx("LastName") )
      xempl.initials  = pStmt.Columntext( pStmt.ColNameToIdx("Initials") )
      xempl.firstname = pStmt.Columntext( pStmt.ColNameToIdx("FirstName") )
     
     
      GoSub addmore 
  '  Next t& 

[José Roca]

Since the parameter for the ColumnText method is a variant, you can use:

xempl.salaryref = pStmt.Columntext("Salary")

[Petrus Vorster]

Thank you very much, it works great.

Next step will be to do the Insert and add functions.

Will keep you posted on the progress!

[Petrus Vorster]

Hi all.

LOL, I am on leave and thought I would pick up on the SQLITE in this time...
Ha!!!! I have moved the entire house from left to right and back again....  The wife had other plans for my leave!!!

Ok, now step two, putting stuff into my tables.
Say i have 4 textboxes on a form, Salaryref, Surname, Initials and Title.

The simplest way of just adding them to a Dbase without checking the existing files. (Baby steps)

Thanks for the help!!!
I will try to do this between the chairs, sofas and cabinets...