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:
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.
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.
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
Attached is Jose's help file for his sqlite classes, etc.
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.
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...
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.
This is the way for MYSQL with SQLTools into a Listview
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
FINALLY!
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
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!!
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&
Since the parameter for the ColumnText method is a variant, you can use:
xempl.salaryref = pStmt.Columntext("Salary")
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!
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... ;D