[Gambas-user] Database component syntax

[Benoît Minisini]

Here is a quick explanation of how to use the database component.

**** Opening a database:

DIM hDB AS NEW Database

hDB.Type = "postgresql"
hDB.Name = "mydabatabase"
'Optional parameters
'hDB.User = "benoit"
'hDB.Password = ""
'hDB.Port = 0
'hDB.Host = "AAA.BBB.CCC.DDD"

hDB.Open

**** Closing a database:

HDB.Close

**** Executing a query and getting the result

DIM iId AS Integer
DIM sName AS String
DIM rResult AS Result
DIM sField AS String

iID = 1
sName = "Gambas"

rResult = hDB.Exec("SELECT * INTO MyTable WHERE id = &1 AND name = &2", iId, sName)

' The iId and sName are substituted into the query, with automatic quoting
' => The executed query is "SELECT * INTO MyTable WHERE id = 1 AND 
'    name = 'Gambas'"

' Printing a result

WHILE rResult.Available

  FOR EACH sField IN rResult.Fields
    PRINT sField; " = "; rResult[sField]
  NEXT
  PRINT

  rResult.MoveNext
WEND


**** Creating a record in a table

rResult = hDB.Create("MyTable")
rResult!id = 2
rResult!Name = "Python"
rResult.Update ' => Generates an INSERT query


**** Editing records in a table

rResult = hDB.Edit("MyTable", "Name = &1", "Perl")
' => send the query "SELECT * INTO MyTable WHERE Name = 'Perl'"

WHILE rResult.Available

  rResult!Name = "PHP"
  rResult.Update ' => Generates an UPDATE query

  rResult.MoveNext
WEND

BE CAREFUL ! You cannot edit records this way if the component cannot find a primary index in your table (in postgresql, a primary index on the table "MyTable" is an index named "MyTable_pkey"). If you have no primary index on your table, you must use Exec() with your own UPDATE query.


**** Transactions

hDB.Begin
...
hDB.Commit
...
hDB.Rollback


BE CAREFUL ! The interface of the database component is not terminated, so it is subject to any change ! This is an EXPERIMENTAL component.

Enjoy it all the same :-)

-- 
Benoît Minisini
mailto:gambas at ...1...