Gambas data grid code

Add data to a gambas data grid2012-10-03T08:01:00.001-07:00
Data grid Codes

 This event is raised when the GridView needs the data stored in a specified cell.

    * Row is the cell row.

    * Column is the cell column.

PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)

GridView1.Data.Text = "Gambas " & Row & "," & Column

END

Adding new records in a Gambas data grid
' 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

Gambas mysql

2012-04-17T09:55:00.000-07:00

Mysql Gambas db connection

property is null, a database is opened in memory.   If the Name specified is an
absolute path, the pathname is used. If the Name specified is a relative path and
the Host property is null, the database is located in the application temporary
directory /tmp/gambas.$UID/sqlite.     Otherwise,   Host   will   contain   the   database
directory name and the database file path is the result of concatenating the Host
and Name property values. This class is creatable and the calling convention is:

DIM hConnection AS Connection
hConnection = NEW Connection()
Connection Properties
When   the hConnection object   is   instantiated,   it   creates   a   new   void
connection   object.     You   will   then   need   to   set   properties   for   this   object.     The
properties that you can use with a connection object include Charset, Databases,
Host, Login, Password, Name, Port, Tables, Type, Users and Version.   We will
explain each of these in the following sections.
Charset
Charset   is   a   readonly   property   that   returns   the   charset   used   by   the
database.   It is possible for you to store your strings in the database directly in
UTF8 format.   However, you can also can use this property combined with the
Conv$() subroutine to convert from UTF8 format to the database charset format.
Using   the   database   charset   comes   at   a   cost   in   performance   however.     This   is
because you must use the Conv$() subroutine each and every time you read or
write string data to the database fields. For now, this is the only way you can do
this if you want to use the stringbased SQL functions and builtin sort routines of
the underlying database management system (MySQL, PostgreSQL, or SQLite).
Future   versions   of   Gambas may automatically   make   the   conversion   between
Gambas strings and the database charset.
Databases
The Databases property can be checked to obtain a collection object that
will   contain names of all   databases   that are   managed   by   the   database   server.
Syntax is:
PROPERTY Databases AS .ConnectionDatabases
The   resulting   collection   of   data   (which   is   enumerable   using   the   FOR   EACH
328
A Beginner's Guide to Gambas
statement) would be the same as if you typed SHOW DATABASES from the mysql
client CLI.

Host
You can use the Host property to set or get the host name of where the
database server is located. The host name can be either a machine name or an IP
address.     The   default   host   is   named   localhost.     In   SQLite,   host   name   is   not
relevant   since   SQLite   is   not   a   client/server   database   sytstem.     However,   from
version 0.95 forward, it is possible for you to set the home path for the SQLite
database by using this property. Syntax for using the Host property is:
PROPERTY Host AS String
Login
The Login property is used to set or get the user login data that is used for
establishing the connection to the database.   Once again, this applies to MySQL
and PostgreSQL, not SQLite.   Since SQLite has no concept of users, access to a
SQLite database is controlled by the file permission settings of the database file
itself. This means that the Login property is always going to be set to the user id
of the user that is executing the Gambas program that uses the SQLite database.
Syntax is:
PROPERTY Login AS String
Name
The Name property sets or   gets the name   of   the   database   you   want   to
connect to. If you do not specify a name, a default system database is used. For
PostgreSQL the default database is named template1 and for mySQL it is named
mysql. For SQLite, the default name is /tmp/sqlite.db. For any database access,
however, the user needs to have at least read and connect access privileges to use
these databases.     Sqlite   will   locate the   database first by trying   to use   the full
pathname if it has been given. If not, it will check to see if the Host variable has
been set. If that is the case, SQLite will look at the path name that was set in the
variable. If the environment variable GAMBAS_SQLITE_DBHOME has been set,
then SQLite will use that as the current working directory. It is also possible to
create   and   use   a   database   in   memory   by   setting   the   Name   property   to
“:memory:” rather than “mysql” or “postgresql” or “sqlite”. Syntax is:
329

PROPERTY Name AS String
Password
The Password property will return or set the password used for establishing
a connection to the database. Calling convention is:
PROPERTY Password AS String
Port
The Port property is used to get or set the TCP/IP port used for establishing
the connection to a database. The default port depends on the connection Type.
Syntax is:
PROPERTY Port AS String
Tables
The Tables property is used to obtain a virtual collection that can be used
for managing all of the tables of a database. Syntax is:
PROPERTY Tables AS .ConnectionTables
Type
The Type property represents the type of the database server you want to
connect to.   In Gambas, the currently supported databasetype are: "postgresql",
"mysql", and "sqlite". The type property uses this syntax:
PROPERTY Type AS String
Users
The Users   property returns   a   collection   of   all   users   registered   in   the
database server. Like any other collection object, it can be enumerated with FOR
EACH. Syntax is:
PROPERTY Users AS .ConnectionUsers
Version

The Version property is a readonly value that returns the version of the
330
A Beginner's Guide to Gambas
database that the driver has connected with. Syntax is:
PROPERTY READ Version AS Integer
The Concept of a Transaction
There are times when the order in which database actions (or queries) are
executed is important. Sometimes, a program must ensure all queries in a group
are run successfully and in order or process none of them at all. A classic example
is   taken   from   the   banking   environment.     If   a   given   amount   of   money   (for
example, 100 dollars) is taken from one account and posted to another account,
we would expect the following actions to occur:
UPDATE account1 SET balance = balance 100;
UPDATE account2 SET balance = balance + 100;
Both queries must execute successfully or neither must execute.   Money
cannot   be   transferred   out   of   one   account   and   fail   to   be   posted   to   the   other
account. Both of these queries form a single transaction that is composed of two
separate   actions   (debit   100   from   account1   and   credit   100   to   account2).     A
transaction is simply one or more individual database queries grouped together
between BEGIN and COMMIT statements.   Without a COMMIT statement, the
transaction is not permanent and can be reversed with the ROLLBACK statement.
MySQL automatically commits statements that are not part of a transaction
process.     The   results   of   any   SQL   UPDATE   or   INSERT   statement   that   is   not
preceded   with   BEGIN   will   immediately   be   visible   to   all   connections   to   the
database   because   the   commit   is   automatically   performed.    Within   MySQL,
transactions are only known with “transaction safe” tables (i.e., BDB and InnoDB).
The other table types COMMIT   immediately.   Most databases that are able to
accomplish this are said to be ACIDcompliant.   The ACID model is one of the
oldest and most important concepts of database theory. The four goals that must
be   met for   any   database   to   be   considered   reliable   are Atomicity, Consistency,
Isolation and Durability (ACID). Let’s examine each of these four characteristics
in detail.
Atomicity means database modifications must follow an “all or nothing”
rule.     Every   transaction   is   considered   an   “atomic”   unit.     If   any   part   of   the
transaction fails, the entire transaction fails. It is critical the DBMS maintain the
atomic nature of transactions in any circumstance.
331

A Beginner's Guide to Gambas

Consistency states   only   valid   data   will   be   written   to   a   database.     If   a
transaction   is   executed   that   violates   database   consistency   rules,   the   entire
transaction   will   be   rolled   back   and   the   database   will   be   restored   to   a   state
consistent with those rules.   Conversely, if a transaction executes successfully, it
will maintain a state of consistency.
Isolation requires   multiple   transactions   that   occur   simultaneously   not
impact each other. For example, if User A issues a transaction against a database
at the same time User B issues a transaction, both transactions should operate on
the   database   in   isolated   bliss.     The   database   should   perform   one   transaction
before   the   other.     This   prevents   a   transaction   from   reading   intermediate   data
produced as a side effect of another transaction. Isolation does not ensure either
transaction will execute first, just that they will not interfere with each other.
Durability ensures any transaction committed to the database will not be
lost.     This   is   accomplished   via   database   backups   and   transaction   logs   that
facilitate restoration of committed transactions despite any subsequent failures.
Now, let's take a look at Gambas Connection class methods available to you in
your programs.
Connection Class Methods
The Connection   class provides   most   of   the   methods   you   will   need   to
establish a connection to a database and execute one or more transactions to find,
add, change, or delete data.

Open/Close
The Open method is used to open a connection to the database. Prior to
making the call to Open, you should set the connection properties with the data
necessary to use the database. Generally, at a minimum you will need to specify
the type of database, the host, a login ID and password, and the name of the
database you wish to use. The syntax for open is:
FUNCTION Open ( ) AS Boolean
The code example below shows how to use the Open method to connect
user jrittinghouse to a MySQL database named “GambasBugs” that is stored on
Host localhost:
DIM $hConn As NEW Connection
332
A Beginner's Guide to Gambas
WITH $hConn
   .Type = "mysql"
   .Host = "localhost"
   .Login = "jrittinghouse"
   .Password = "ab32e44"
   .Name = "GambasBugs"
END WITH
TRY $hConn.Open
IF Error THEN PRINT "Database cannot be opened. Error = "; Error.Text
To close the connection, simply invoke the Close method. In our example above,
you could use this code:
$hConn.Close
and the connection would be closed. In order to subsequently use any data stored
in the database, you would need to reopen the database using the Open method.
Begin/Commit/Rollback
As we stated previously, a transaction is one or more individual database
queries   that   are   grouped   together   between   BEGIN   and   COMMIT   statements.
Without   a   COMMIT   statement,   the   transaction   is   not   permanent   and   can   be
reversed   with a ROLLBACK statement.      Remember   that MySQL   automatically
commits statements that are not part of a transaction process (defined by BEGIN
and COMMIT). In Gambas, it is recommended that you use the Find, Create, and
Edit   methods   to   make   changes   to   the   database.     This   helps to   maintain   code
independence and it allows you to write a single piece of code that will work with
any database supported by Gambas.   Gambas uses a result object to return the
results of a SQL query.
Find
The   Find   method   returns   a   readonly Result   object used   for   querying
records in the specified table. The calling convention for Find is:
FUNCTION Find (Table AS String [, Request AS String, Arguments AS , ... ] ) AS Result
Request represents a SQL WHERE statement that is used for querying the
table.    Arguments are   quoted   as   necessary   (dictated   by   SQL   syntax),   and
substituted inside the Request string.   It works like the Subst() function in this
333

A Beginner's Guide to Gambas

case. Using the Find method allows you to write requests that are independent of
the underlying database, meaning it will work with PostgreSQL or MySQL without
any code change required.
Create
The Create method is used to build a read/write Result object which can
used to create records in a table. Standard calling convention is:
FUNCTION Create ( Table AS String ) AS Result
Edit
The Edit method returns a read/write Result object used for editing records
in the specified table. Query is equivalent to an embedded SQL WHERE clause
used for filtering the table, and the Arguments are quoted as needed by the SQL
syntax and substituted in the Request string (like in sscanf in C) as we explained
previously. Standard calling convention is:
FUNCTION Edit ( Table AS String [ , Query AS String, Arguments AS , ... ] ) AS Result
Here is a code sample to show how Edit() works:
DIM MyResult AS Result
DIM sQuery AS String
DIM iParm AS Integer
sQuery = "id=&1"
' we will insert a parameter value (12) at the end of the query string
iParm = 12
$hConn.Begin
MyResult=$hConn.Edit("tblDEFAULT", sQuery, iParm)
MyResult!Name = "Mr Rittinghouse" ' Set a field value
$hConn.Update ' Update with the new value
$hConn.Commit ' make the changes permanent
$hConn.Close ' close the database connection
Exec
The Exec method executes a SQL request and returns a readonly Result
object containing the result of the request. Standard calling convention is:
FUNCTION Exec ( Request AS String, Arguments AS , ... ) AS Result

Gambas DB connection

2012-03-29T10:41:00.002-07:00

A Beginner's Guide to Gambas
property is null, a database is opened in memory.   If the Name specified is an
absolute path, the pathname is used. If the Name specified is a relative path and
the Host property is null, the database is located in the application temporary
directory /tmp/gambas.$UID/sqlite.     Otherwise,   Host   will   contain   the   database
directory name and the database file path is the result of concatenating the Host
and Name property values. This class is creatable and the calling convention is:
DIM hConnection AS Connection
hConnection = NEW Connection()
Connection Properties
When   the hConnection object   is   instantiated,   it   creates   a   new   void
connection   object.     You   will   then   need   to   set   properties   for   this   object.     The
properties that you can use with a connection object include Charset, Databases,
Host, Login, Password, Name, Port, Tables, Type, Users and Version.   We will
explain each of these in the following sections.
Charset
Charset   is   a   readonly   property   that   returns   the   charset   used   by   the
database.   It is possible for you to store your strings in the database directly in
UTF8 format.   However, you can also can use this property combined with the
Conv$() subroutine to convert from UTF8 format to the database charset format.
Using   the   database   charset   comes   at   a   cost   in   performance   however.     This   is
because you must use the Conv$() subroutine each and every time you read or
write string data to the database fields. For now, this is the only way you can do
this if you want to use the stringbased SQL functions and builtin sort routines of
the underlying database management system (MySQL, PostgreSQL, or SQLite).
Future   versions   of   Gambas may automatically   make   the   conversion   between
Gambas strings and the database charset.
Databases
The Databases property can be checked to obtain a collection object that
will   contain names of all   databases   that are   managed   by   the   database   server.
Syntax is:
PROPERTY Databases AS .ConnectionDatabases
The   resulting   collection   of   data   (which   is   enumerable   using   the   FOR   EACH
328
A Beginner's Guide to Gambas
statement) would be the same as if you typed SHOW DATABASES from the mysql
client CLI.

Host
You can use the Host property to set or get the host name of where the
database server is located. The host name can be either a machine name or an IP
address.     The   default   host   is   named   localhost.     In   SQLite,   host   name   is   not
relevant   since   SQLite   is   not   a   client/server   database   sytstem.     However,   from
version 0.95 forward, it is possible for you to set the home path for the SQLite
database by using this property. Syntax for using the Host property is:
PROPERTY Host AS String
Login
The Login property is used to set or get the user login data that is used for
establishing the connection to the database.   Once again, this applies to MySQL
and PostgreSQL, not SQLite.   Since SQLite has no concept of users, access to a
SQLite database is controlled by the file permission settings of the database file
itself. This means that the Login property is always going to be set to the user id
of the user that is executing the Gambas program that uses the SQLite database.
Syntax is:
PROPERTY Login AS String
Name
The Name property sets or   gets the name   of   the   database   you   want   to
connect to. If you do not specify a name, a default system database is used. For
PostgreSQL the default database is named template1 and for mySQL it is named
mysql. For SQLite, the default name is /tmp/sqlite.db. For any database access,
however, the user needs to have at least read and connect access privileges to use
these databases.     Sqlite   will   locate the   database first by trying   to use   the full
pathname if it has been given. If not, it will check to see if the Host variable has
been set. If that is the case, SQLite will look at the path name that was set in the
variable. If the environment variable GAMBAS_SQLITE_DBHOME has been set,
then SQLite will use that as the current working directory. It is also possible to
create   and   use   a   database   in   memory   by   setting   the   Name   property   to
“:memory:” rather than “mysql” or “postgresql” or “sqlite”. Syntax is:
329
A Beginner's Guide to Gambas
PROPERTY Name AS String
Password
The Password property will return or set the password used for establishing
a connection to the database. Calling convention is:
PROPERTY Password AS String
Port
The Port property is used to get or set the TCP/IP port used for establishing
the connection to a database. The default port depends on the connection Type.
Syntax is:
PROPERTY Port AS String
Tables
The Tables property is used to obtain a virtual collection that can be used
for managing all of the tables of a database. Syntax is:
PROPERTY Tables AS .ConnectionTables
Type
The Type property represents the type of the database server you want to
connect to.   In Gambas, the currently supported databasetype are: "postgresql",
"mysql", and "sqlite". The type property uses this syntax:
PROPERTY Type AS String
Users
The Users   property returns   a   collection   of   all   users   registered   in   the
database server. Like any other collection object, it can be enumerated with FOR
EACH. Syntax is:
PROPERTY Users AS .ConnectionUsers
Version

The Version property is a readonly value that returns the version of the
330
A Beginner's Guide to Gambas
database that the driver has connected with. Syntax is:
PROPERTY READ Version AS Integer
The Concept of a Transaction
There are times when the order in which database actions (or queries) are
executed is important. Sometimes, a program must ensure all queries in a group
are run successfully and in order or process none of them at all. A classic example
is   taken   from   the   banking   environment.     If   a   given   amount   of   money   (for
example, 100 dollars) is taken from one account and posted to another account,
we would expect the following actions to occur:
UPDATE account1 SET balance = balance 100;
UPDATE account2 SET balance = balance + 100;
Both queries must execute successfully or neither must execute.   Money
cannot   be   transferred   out   of   one   account   and   fail   to   be   posted   to   the   other
account. Both of these queries form a single transaction that is composed of two
separate   actions   (debit   100   from   account1   and   credit   100   to   account2).     A
transaction is simply one or more individual database queries grouped together
between BEGIN and COMMIT statements.   Without a COMMIT statement, the
transaction is not permanent and can be reversed with the ROLLBACK statement.
MySQL automatically commits statements that are not part of a transaction
process.     The   results   of   any   SQL   UPDATE   or   INSERT   statement   that   is   not
preceded   with   BEGIN   will   immediately   be   visible   to   all   connections   to   the
database   because   the   commit   is   automatically   performed.    Within   MySQL,
transactions are only known with “transaction safe” tables (i.e., BDB and InnoDB).
The other table types COMMIT   immediately.   Most databases that are able to
accomplish this are said to be ACIDcompliant.   The ACID model is one of the
oldest and most important concepts of database theory. The four goals that must
be   met for   any   database   to   be   considered   reliable   are Atomicity, Consistency,
Isolation and Durability (ACID). Let’s examine each of these four characteristics
in detail.
Atomicity means database modifications must follow an “all or nothing”
rule.     Every   transaction   is   considered   an   “atomic”   unit.     If   any   part   of   the
transaction fails, the entire transaction fails. It is critical the DBMS maintain the
atomic nature of transactions in any circumstance.
331
A Beginner's Guide to Gambas
Consistency states   only   valid   data   will   be   written   to   a   database.     If   a
transaction   is   executed   that   violates   database   consistency   rules,   the   entire
transaction   will   be   rolled   back   and   the   database   will   be   restored   to   a   state
consistent with those rules.   Conversely, if a transaction executes successfully, it
will maintain a state of consistency.
Isolation requires   multiple   transactions   that   occur   simultaneously   not
impact each other. For example, if User A issues a transaction against a database
at the same time User B issues a transaction, both transactions should operate on
the   database   in   isolated   bliss.     The   database   should   perform   one   transaction
before   the   other.     This   prevents   a   transaction   from   reading   intermediate   data
produced as a side effect of another transaction. Isolation does not ensure either
transaction will execute first, just that they will not interfere with each other.
Durability ensures any transaction committed to the database will not be
lost.     This   is   accomplished   via   database   backups   and   transaction   logs   that
facilitate restoration of committed transactions despite any subsequent failures.
Now, let's take a look at Gambas Connection class methods available to you in
your programs.
Connection Class Methods
The Connection   class provides   most   of   the   methods   you   will   need   to
establish a connection to a database and execute one or more transactions to find,
add, change, or delete data.

Open/Close
The Open method is used to open a connection to the database. Prior to
making the call to Open, you should set the connection properties with the data
necessary to use the database. Generally, at a minimum you will need to specify
the type of database, the host, a login ID and password, and the name of the
database you wish to use. The syntax for open is:
FUNCTION Open ( ) AS Boolean
The code example below shows how to use the Open method to connect
user jrittinghouse to a MySQL database named “GambasBugs” that is stored on
Host localhost:
DIM $hConn As NEW Connection

Gridview1 or gambas data grid

2012-02-24T02:49:00.001-08:00

Gridview and the Data Handling in gambas Event

Here is another short tutorial based around database work under Gambas. I think Gambas is a marvelous data manipulation tool. I suspect it has greater facility than the so-called 4th Generation ‘application generators’ I was using not so long ago.
The tutorial highlights a feature of gridview display which saves huge amounts of time when displaying large databases. The feature is actually used in the Gambas database example so is nothing original. I do find with a lot of Gambas examples that the the example author tries too hard to show too many capabilities with the result that the example itself becomes confusing.
The following Gambas2 code:
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.
4 Displays the results in a gridview.
On Gambas you need nothing more than a gridview (gridview1) on an otherwise empty form. The only interesting bit that may need explanation is the GridView1_Data event. This is called to fill an empty gridview cell as it is exposed (by scrolling). It saves having to load all 10000 records into the gridview with the time penalty that would imply.
Here is the code for FMain.class:

01' Gambas class file
02PRIVATE $hConn AS NEW Connection
03PRIVATE $res AS Result
04'-------------------------------------------------
05PUBLIC SUB Form_Open()
06  DIM iCount AS Integer
07  DIM hTable AS Table
08  DIM rTest AS result
09  DIM sql AS String
10   
11  'define the gridview layout
12  GridView1.header = GridView.Horizontal
13  GridView1.grid = TRUE
14  GridView1.Rows.count = 0
15  GridView1.Columns.count = 2
16  GridView1.Columns[0].text = "ID"
17  GridView1.Columns[1].text = "Value"
18  GridView1.Columns[0].width = 55
19  GridView1.Columns[1].width = 55
20   
21   
22  WITH $hConn
23    .Type = "sqlite"
24    .host = User.home
25    .name = ""
26  END WITH 
27   
28  'delete an existing test.sqlite  
29  IF Exist(User.home & "/test.sqlite") THEN 
30    KILL User.home & "/test.sqlite"
31  ENDIF 
32   
33  'create test.sqlite
34  $hConn.Open
35    $hConn.Databases.Add("test.sqlite")
36  $hconn.Close
37   
38  'define the table sampleTable
39  $hconn.name = "test.sqlite"
40  $hConn.Open
41    hTable = $hConn.Tables.Add("sampleTable")
42    hTable.Fields.Add("s_seq", db.Integer)
43    hTable.Fields.Add("s_rndm", db.Integer)
44    hTable.PrimaryKey = ["s_seq"]
45    hTable.Update
46     
47  'fill the table with generated data
48  $hconn.Begin
49    rTest = $hConn.Create("sampleTable")
50    FOR iCount = 1 TO 10000
51      rTest!s_seq = iCount
52      rTest!s_rndm = Int(Rnd(0, 100))
53      rTest.Update
54    NEXT
55  $hConn.Commit
56   
57  'read the database
58  sql = "select s_seq as ID, s_rndm as Value from sampleTable"
59  $res = $hconn.Exec(sql)
60   
61  CATCH 
62    $hConn.Rollback
63    Message.Error(DConv(Error.Text))
64     
65END
66'-------------------------------------------------
67PUBLIC SUB Form_Activate()
68  'change the rowcount of the gridview from 0 to the number of records. 
69  'This triggers the data handling event
70  GridView1.Rows.Count = $res.Count  
71END
72'-------------------------------------------------
73PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)
74  $res.moveTo(row)
75  GridView1.Data.text = Str($res[GridView1.Columns[column].text])
76END
77'-------------------------------------------------
78PUBLIC SUB Form_Close()
79  $hconn.Close
80END
81'-------------------------------------------------

We initially set the row count of the gridview to 0. Having read the database into result $res, it will have 10000 records. In other words $res.count=10000. Setting GridView1.Rows.Count = $res.Count will make the gridview (which you have put on the form) a window to this grid of 1000 rows by 2 columns. The fact that empty cells are now displayed in this window to a much bigger grid causes the gridview object to repeatedly call Gridview1_data for each empty cell in view. The event then fills the value of the blank cell from the equivalent value in the result set. In no time at all every blank cell is filled with the corresponding data from the result set.
If you scroll down the gridview, it exposes empty cells in the grid which are automatically filled by the data handling event. And so on.
You could load all the available data into the gridview in one step but it would take an unacceptably long time. This method gives the impression of blinding speed by only loading the gridview cell values which are visible. It’s just so clever.

Gambas datagrid

2012-02-18T04:55:00.000-08:00

Gambas data grid code not connected to a database

Private Sub PopulateCharges()
Dim intRow As Integer = 0
Dim strType As String = ""

If rsCharges.Available Then
rsCharges.MoveFirst
grdCharge.Rows.Count = rsCharges.Count
For Each rsCharges
grdCharge[intRow, 0].Text = rsCharges!unique_id
grdCharge[intRow, 1].Text = rsCharges!charge_source
grdCharge[intRow, 2].Text = rsCharges!charge_type
grdCharge[intRow, 3].Text = rsCharges!charge_start
grdCharge[intRow, 4].Text = rsCharges!charge_end
grdCharge[intRow, 5].Text =
rsCharges!charge_display
Inc intRow
Next
modMain.UpdateStatus(rsCharges.Count & " Charge records
returned")
LoadChargeCombos(rsCharges)
grdCharge.Row = intRow - 1
Endif
Catch
Message.Error(Error.Class.Name & ": " & Error.Where & ": "
& Error.Text)
End

I assume you have got your recordset (rsCharges) from the Db. You ned to
check how many rows are need in the grid and set this. Then populate
each row and column as above.

Gambas Sample data grid program mysql db connected

2012-02-18T04:45:00.000-08:00

' Gambas class file
PUBLIC MyConn AS Connection
PUBLIC MyRS AS Result
PUBLIC vCari AS String

PUBLIC SUB Form_Open()

DIM iCount AS Integer
DIM hTable AS Table
DIM sql AS String
DIM rsSuperMarket AS Result
DIM intRow AS Integer = 0
DIM strType AS String = ""


'define the gridview layout

GridView1.header = GridView.Horizontal
GridView1.grid = TRUE
GridView1.Rows.count = 2
GridView1.Columns.count = 4
GridView1.Columns[0].text = "Pin"
GridView1.Columns[1].text = "Description"
GridView1.Columns[2].text = "Quantity"
GridView1.Columns[3].text = "Prize"
GridView1.Columns[0].width = 100
GridView1.Columns[1].width = 100
GridView1.Columns[2].width = 100
GridView1.Columns[3].width = 100

'define the table SuperMarket for the data grid to be able to read. My 'SuperMarketInventory, database has four records(Pin, Description, Quantity and Prize.)
rsSuperMarket is the variable where records from MYSQL pass through.

rsSuperMarket = MyConn.Exec("SELECT * FROM SuperMarket") 'executing query
   IF rsSuperMarket.Available THEN
                 rsSuperMarket.MoveNext
                 GridView1.Rows.Count = rsSuperMarket.Count
                 FOR EACH rsSuperMarket
                       GridView1[intRow, 0].Text = rsSuperMarket!Pin
                       GridView1[intRow, 1].Text = rsSuperMarket!Description
                       GridView1[intRow, 2].Text = rsSuperMarket!Quantity
                       GridView1[intRow, 3].Text = rsSuperMarket!Prize

'INC intRow is a very important code that fills the table with generated data.
        INC intRow

        NEXT
          MyConn.Quote(rsSuperMarket.Count & " Charge records returned")

ENDIF
END

PUBLIC SUB Label1_MouseDown()

END

PUBLIC SUB Label2_MouseDown()

END

PUBLIC SUB txtPin_Leave()

END

PUBLIC SUB txtPin_KeyPress()

END

PUBLIC SUB btnClear_Click()

txtPin.text = ""
txtDescription.text = ""
txtQuantity.text = ""
txtPrize.text = ""
END

PRIVATE SUB clear()
txtPin.text = ""
txtDescription.text = ""
txtQuantity.text = ""
txtPrize.text = ""
END

PUBLIC SUB _new()
ME.center
DBConnect

END

PUBLIC SUB DBConnect()
MyConn = NEW Connection
MyConn.Close
MyConn.Type = "mysql"
MyConn.Host = "localhost"
MyConn.Login = "root"
MyConn.Password = "1111"
MyConn.Name = "SuperMarketInventory"
MyConn.Open
CATCH
Message.error(Error.text)
END

PUBLIC SUB _free()
MyConn.Close
Message.Info("Close. ")
END
# 'End of program

PUBLIC SUB btnClose_Click()

   FMain.Close

END

PUBLIC SUB btnAdd_Click()

     DIM MyRS AS String
     DIM sql AS String
     MyRS = "insert into Super (Pin, Description, Quantity, Prize )values( '" &
            txtPin.text & "','" & txtDescription.text & "','" & txtQuantity.text & "','" &
            txtPrize.text & "')"
     MyRS = MyConn.Exec(sql)

     sql = "update Super set "
     sql = sql & "Pin = '" &
     txtPin.text
     sql = sql & "',Description = '" &
     txtDescription.text
     sql = sql & "',Quantity = '" &
     txtQuantity.text
     sql = sql & "',Prize = '" & txtPrize.text & "' where Pin = " & vCari
     MyRS = MyConn.Exec(sql)

CATCH
Message.Error(Error.Text)

END

PUBLIC SUB btnCreateTable_Click()
DIM hTable AS Table
Myconn.name = "SuperMarketInventory"
hTable = MyConn.Tables.Add("SuperMarket")
hTable.Fields.Add("Pin", db.Integer)
hTable.Fields.Add("Description", db.String)
hTable.Fields.Add("Quantity", db.Integer)
hTable.Fields.Add("Prize", db.Integer)
hTable.Update

END