Creating a databases and tables from Gambas

Here is a little program I wrote some time ago to demonstrate the creation of a database from within Gambas. The process is as follows:

1 Deletes a file test.sqlite in the users home directory (if it exists).
2 Creates an SQLite database test.sqlite and defines a table sampleTable with two integer fields, one a sequence (yes, I know SQLite does that internally anyway!) and the other a random number.
3 Fills the table with 10000 records with rollback.
4 Displays the results in a gridview using the data handling event already described in a separate section.

On Gambas you need nothing more than a gridview (gridview1) on an otherwise empty form (You could also instantiate the gridview from code.

' Gambas class file
PRIVATE $hConn AS NEW Connection
PRIVATE $res AS Result
'-------------------------------------------------
PUBLIC SUB Form_Open()
DIM iCount AS Integer
DIM hTable AS Table
DIM rTest AS result
DIM sql AS String

'define the gridview layout
GridView1.header = GridView.Horizontal
GridView1.grid = TRUE
GridView1.Rows.count = 0
GridView1.Columns.count = 2
GridView1.Columns[0].text = "ID"
GridView1.Columns[1].text = "Value"
GridView1.Columns[0].width = 55
GridView1.Columns[1].width = 55


WITH $hConn
    .Type = "sqlite"
    .host = User.home
    .name = ""
END WITH

'delete an existing test.sqlite
IF Exist(User.home & "/test.sqlite") THEN
    KILL User.home & "/test.sqlite"
ENDIF

'create test.sqlite
$hConn.Open
    $hConn.Databases.Add("test.sqlite")
$hconn.Close

'define the table sampleTable
$hconn.name = "test.sqlite"
$hConn.Open
    hTable = $hConn.Tables.Add("sampleTable")
    hTable.Fields.Add("s_seq", db.Integer)
    hTable.Fields.Add("s_rndm", db.Integer)
    hTable.PrimaryKey = ["s_seq"]
    hTable.Update

'fill the table with generated data
$hconn.Begin
    rTest = $hConn.Create("sampleTable")
    FOR iCount = 1 TO 10000
        rTest!s_seq = iCount
        rTest!s_rndm = Int(Rnd(0, 100))
        rTest.Update
    NEXT
$hConn.Commit

'read the database
sql = "select s_seq as ID, s_rndm as Value from sampleTable"
$res = $hconn.Exec(sql)

CATCH
$hConn.Rollback
Message.Error(DConv(Error.Text))

END
'-------------------------------------------------
PUBLIC SUB Form_Activate()
'change the rowcount of the gridview from 0 to the number of records.
'This triggers the data handling event

GridView1.Rows.Count = $res.Count
END
'-------------------------------------------------
PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)
	$res.moveTo(row)
	GridView1.Data.text = Str($res[GridView1.Columns[column].text])
END
'-------------------------------------------------
PUBLIC SUB Form_Close()
	$hconn.Close
END
'-------------------------------------------------
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: