Database access from HBasic programs
Contents
Prerequisites
warning To
use database access you need to install QT in a version higher than 3.0
and compile the database pluging matching the kind of your database server.
Remember to include the package hbasic_dbaccess if you create new database
examples. If you load the predefined examples from the HBasic distribution
this package will be loaded automatically.
Accessing a table from a HBasic program
Before you may use the methods described below you always have to connect
to a database table. This connection will be set up with two components
from the package
hbasic_dbaccess. The first component called dbconnection
creates the database connection and the second component called
recordset
defines an edit buffer that may be used to display or change the records
of a database table.
Preparing a database connection normally looks like the following code
segment.
Dim dbc As dbconnection
Dim t As recordset
dbc.connect( "hbasic", "root", "orange" )
t.open( dbc, "tab1" )
First an instance of the two components will be created with the Dim
statement. In HBasic predefined components will be created without a
New statement. The
database connection will be initialised with
a call to the method connect of the dbconnection component. The parameters
are the name of the database you want to connect to, the user that should
be used and the password for the user. As you can see in the example my
test database is called
hbasic and I connect with the user
root and the password
orange. Replace this names with the name
and user of your database if you want to start the examples
The
recordset that will be used for further data changes will
be initialised with a call of the open method of the recordset component.
The first parameter is the database connection and the second parameter
is the name of the table that should be used in the recordset.
You will find this lines always on the first lines of the following source
code examples.
Reading all records of a table
This examples sets up a loop which will step through all rows of a
table
tab1 and show the value of the column
col1 for
each dataset.
The select example selects all rows from the table tab1 and
prints the value of column col1 for each row.
Dim v As Variant
Sub button1_clicked()
Dim dbc As dbconnection
Dim rs As recordset
dbc.connect( "hbasic", "root", "orange" )
rs.open( dbc, "tab1" )
While Not( rs.eof()) Do
v = rs.getvalue( "col1" )
Print v
rs.movenext()
Wend
End Sub
Example ex_db_read.bas: Read the value of col1 from all
table columns in tab1
Positioning the edit cursor
If you want to change data within a table of your database you have
to set the current position of the edit cursor to the depending row
first. This may be done by using one or more of the following commands.
Command
Description
MoveNext
Move edit cursor to next record of table
MovePrev
Move edit cursor to previous record of table
MoveFirst
Move edit cursor to first record of table
MoveLast
Move edit cursor to last record of table
Seek( position_n )
Set edit cursor to position_n in table
After each of this operations you may test if HBasic has found a valid
record with the function EOF(). If HBasic cannot position on a valid
record the function EOF will give a result of TRUE. You may for
example loop through all records of a table with the following While
command:
While Not t.eof()
Print t.getValue( "col1" )
t.MoveNext()
Wend
Other methods for database components
The package
hbasic_dbaccess provides the following components
and methods.
Component db_connection
connect( db_name, user_name, user_password)
Create new connection to database <db_name>
Component recordset
open( db_connection, table_name )
Open recordset for table table_name
bool eof( )
return TRUE if no more recordsets can be read
delete( )
Delete current row from table
update( )
Store values changed with setValue to database
addNew( )
Add new row to table and make it current row
variant getValue( column_name )
Read the value of column_name and return as variant
setValue( column_name, new_value )
Change value of column_name to new_value
Update current row in database
You may change the data in the current database record with the method
setValue( <columnname>, new_value ). This method will only change
the data in the current edit buffer in memory. Call the method
update to store the changed data in the database.
t.setValue( "col1", 123 )
t.Update()
Change values of the current row in table tab1. Since we
do not position the row-pointer we change the first row in the
table.
Dim v As Variant
Sub button1_clicked()
Dim dbc As dbconnection
Dim rs As recordset
dbc.connect( "hbasic", "root", "" )
rs.open( dbc, "tab1" )
v = 1111
rs.setValue( "col1", v )
rs.update()
End Sub
Example ex_db_update.bas: Update the value of column col1
in table tab1
You may insert a new record into a table represented by a recordset.
Add a new record to the table with the method
addNew(). This
will create a new record in the edit buffer. You can now change
the values of the edit buffer with the setValue( <columnname>,
new_value ) method. As with the update method you have to call the
method
update to store the changed data in the database.
t.addNew()
t.setValue( "col1", 3333 )
t.Update()
Insert a new row into table tab1 and set values for new row.
Dim v As Variant
Dim pos As Long
Sub button1_clicked()
Dim dbc As dbconnection
Dim rs As recordset
dbc.connect( "hbasic", "root", "" )
rs.open( dbc, "tab1" )
v = 1111
pos = 1
While pos <= 10
rs.addnew()
v = pos
rs.setvalue( "col1", v )
v = pos * 11
rs.setvalue( "col2", v )
v = pos * 111
rs.setvalue( "col3", v )
rs.update()
pos = pos + 1
Wend
End Sub
Example ex_db_insert.bas: Insert a new row into table
tab1
Delete current database row
Position the edit-cursor on the db record that should be deleted
and call the method
delete for the recordset component.
t.Delete()
Delete the current row from table tab1. Since we do not
position the row-pointer we delete the first row in the table.
Dim v As Variant
Sub button1_clicked()
Dim dbc As dbconnection
Dim rs As recordset
dbc.connect( "hbasic", "root", "" )
rs.open( dbc, "tab1" )
rs.delete()
End Sub
Example ex_db_delete.bas: Delete the first recordset from
table tab1