Db v1.0.0 documentation

Documentation generated by XL CLAIRE v3.3.37 at Fri, 17 Nov 2006

• Author : Sylvain BENILAN

Category index

1. Connection, driver module
2. Catalog methods
3. Executing queries
4. Reading result sets
5. Transaction
6. Disconnection

Method index

• beginTransaction (self:Database) -> void
• columns (self:Database, t_:string) -> list[Column]
• commitTransaction (self:Database) -> void
• disconnect (self:Database) -> void
• endOfQuery (self:Database) -> integer
• execute (self:Db/Database, sql:string) -> integer
• fetch (self:Database) -> boolean
• field (self:Database, fieldName:string) -> (string U port) U {unknown}
• field (self:Database, f:integer) -> (string U port) U {unknown}
• fields (self:Database) -> list[string]
• printInQuery (self:Database) -> port
• rollbackTransaction (self:Database) -> void
• row (self:Database) -> list[(string U port) U {unknown}]
• tables (self:Database) -> list[string]

Db categories

Connection, driver module

The module Db implements a general purpose Database engine for CLAIRE. It defines a set of API that should be implemented by a database driver module like Mysql. It is up to the driver to provide a connection method that creates an instance of the Database class used by all method of the Db module. For instance Mysql provides the following constructor :

DB :: Mysql/mySql!("database name", "host address", "user", "password")

Catalog methods

• columns (self:Database, t_:string) -> list[Column]
• tables (self:Database) -> list[string]

Db provides two special methods to get informations about the database structure. The first one (tables @ Database) retrieves the list of table names :

show_db_tables(self:Db/Database) : void ->      printf("~S tables :\n~I",              self,              for t in Db/tables(self)                  printf("~A\n", t))

The second one (columns) is use to retrieve columns of a given table :

show_table_columns(self:Db/Database, tab:string) : void ->      printf("~S, columns of table ~A :\n~I",              self,              tab,              for c in Db/columns(tab)                  printf("column ~A: nullable: ~S, SQL type: ~A\n",                      c.name, c.Db/nullable?, c.Db/sqlTypeName))

Executing queries

• endOfQuery (self:Database) -> integer
• execute (self:Db/Database, sql:string) -> integer
• printInQuery (self:Database) -> port

The execute method allow submition of direct SQL queries in a simple way, for instance :

Db/execute(DB, "SELECT * FORM my_table")  Db/execute(DB, "INSERT ...")

Oftenly we need to build the SQL statement dynamicaly. Db comes with an SQL redirection facility, the current output can be localy redirected to the statement of a query as in :

(Db/printInQuery(DB), // starts a new redirected query  printf("UPDATE ~A ...", ...) // print the SQL statement dynamicaly  let affected_rows := Db/endOfQuery(DB)  // ends redirection and execute query  in ...)  (Db/printInQuery(DB), // starts a new redirected query  printf("SELECT ~A ...", ...) // print the SQL statement dynamicaly  assert(Db/endOfQuery(DB) = -1) // ends redirection and execute query

endOfQuery returns the same status code than execute which would be -1 for a SELECT statement and the amount of affected rows otherwise. In the case of a SELECT statement we may omit the call to endOfQuery, indeed we would always use fetch for SELECT statement in order to retrieve a row of the result set. The first fetch call would automaticaly ends the rediretion and execute the query if it hasn't be done with endOfQuery, so we could write :

(Db/printInQuery(DB),  printf("SELECT ~A ...", ...)  while Db/fetch(DB) // the first fetch ends redirection and executes the query          ... )

Reading result sets

• fetch (self:Database) -> boolean
• field (self:Database, fieldName:string) -> (string U port) U {unknown}
• field (self:Database, f:integer) -> (string U port) U {unknown}
• fields (self:Database) -> list[string]
• row (self:Database) -> list[(string U port) U {unknown}]

When a SELECT statement is executed a result set can be iterated. A result set is made of row results and each row is made of fields. Rows are loaded one at a time with a call to fetch. fetch should be called repeatedly until false is returned which would mean that the result set has been loaded entirely and that the query has been deleted. For instance the following method prints the content of a table :

show_table_content(self:Db/Database, tab:string) : void ->      (Db/printInQuery(self),      printf("SELECT * FROM ~A", tab),      while Db/fetch(self) // iterate the result set          printf("~A\n", Db/row(self))) // and print each rows

Where row(self) return the list of field values of the current fetched row. It is sometimes necessary to have a field access by its name, for instance when we use database procedures :

(Db/execute(DB, "SELECT Count(*) AS my_field FROM my_table"),  while Db/fetch(DB)      let count := Db/field(DB, "my_field")      in ...

Here we use the SQL AS keyword that binds the count field to a specify name. The use of the AS keyword is a good practice since it makes database code more portable from a database to another.

Transaction

• beginTransaction (self:Database) -> void
• commitTransaction (self:Database) -> void
• rollbackTransaction (self:Database) -> void

Usualy, a user code that connects to a database make multiple queries. The major problem comes when the connected application reaches an undefined behavior while it has already submited queries that have modified the database integrity. To solve this kind of problem an application should perform queries inside a transaction that would commit all modification at one time or abort all queries of the transaction in case of undefined behavior :

try      (Db/beginTransaction(DB),      ... // some code that submit multiple queries      Db/commitTransaction(DB)) // OK, commit all the queries of the transaction  catch any      Db/rollbackTransaction(DB) // ignore all queries that have been performed                              // during the transaction

Disconnection

• disconnect (self:Database) -> void

When we are done with the database we have to disconnect from it :

Db/disconnect(DB)

Db methods

Db/beginTransaction(self:Database) -> void

beginTransaction(self) starts a new transaction with the database. All queries that are executed during the transaction are not actualy commited in the database. A transaction ends whe commitTreansaction(self) is called which would commit all queries in a definitive way whereas rollbackTransaction(self) would left the database unchanged in addition to end the transaction.

Db/columns(self:Database, t_:string) -> list[Column]

columns(self) returns a list of column objects for a given table of the given database.

Db/commitTransaction(self:Database) -> void

commitTransaction(self) commits all queries that have been executed since the call to beginTransaction(self) and ends the current transaction.

Db/disconnect(self:Database) -> void

disconnect(self) disconnects the database and cleanup all pending queries.

Db/endOfQuery(self:Database) -> integer

endOfQuery(self) executes the SQL query started by printInQuery(self) the return integer status has the same meaning as execute(self, sql)

execute(self:Db/Database, sql:string) -> integer

execute(self, sql) execute a new query with the given sql statement (direct execution) and return an integer status whith the following meaning :

• -1 means that a result set is available (SELECT statements). You'll have to use fetch, field and row API to explore te result set.
• a positive integer that represents the number affected rows (othe statements : INSERT, UPDATE, DELETE ...).

Db/fetch(self:Database) -> boolean

fetch(self) loads a new row (if any) from the result set of the current executed query. fetch must be used after the execution of a SELECT statement (for which execute or endOfQuery returns -1). fetch returns true if a row could actuly be loaded, othewise false is returned and the current query is deleted.

Db/field(self:Database, f:integer) -> (string U port) U {unknown}

field(self, f) returns from the current fetched row the value associated with the field with index f in the SQL query.

Db/field(self:Database, fieldName:string) -> (string U port) U {unknown}

field(self, fieldName) returns from the current fetched row the value associated with the field with named fieldName in the SQL query.

Db/fields(self:Database) -> list[string]

fields(self) return the list of names associated with the current query in the same way to row(self) that returns their corresponding value in the current fetched row.

Db/printInQuery(self:Database) -> port

printInQuery(self) redirect the current output port to new SQL query. Any printing operation will then be redirected to the query (an SQL statement is expected) until a call to endOfQuery(self) appends.

Db/rollbackTransaction(self:Database) -> void

rollbackTransaction(self) cancel all queries that have been executed since the call to beginTransaction(self) and ends the current transaction.

Db/row(self:Database) -> list[(string U port) U {unknown}]

row(self) returns the list of values associated with the current fetched row. The order of the list depends on how the query was written and may be system dependent. The returned list may contain :

• the unknown value when the corresponding value is NULL in the database
• a blob device when the corresponding value is a BLOB or LONGBINARY object in the database
• else a string is returned, it up to the user code to apply integer! or float! conversions

Db/tables(self:Database) -> list[string]

tables(self) returns the list of table names for the given database.