[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4. Bigloo SQL database interface

The RDBMS library is a set of miscellaneous types and procedures, allowing to access SQL databases from Bigloo application. The library has two layers: generic RDBMS system interface, implemented with use of Bigloo object system, and a number of back-ends, which provide the implementation for particular database vendors.

The rdbms library is designed like ODBC and Perl DB system, i.e. it has a set of generic procedures and a number of specific drivers for different database vendors.

At the moment of writing the library supports MySQL, Oracle, PostgreSQL and SQLite. The support of ODBC and INFORMIX is planned still.

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4.0.1 Object types

This section describes the driver object types.

Bigloo class: connection

The objects of connection type implement database connections.

Bigloo class: session

The objects of session type used to SQL statements and retrieve the SQL query results. Every session object is bound to only one connection.

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4.0.2 Connection management

This section describes procedures for creating and destroying the database connections, and for transaction management.

procedure: rdbms-connect vendor-tag::bstring #!key

This procedure creates and opens new database connection. The vendor-tag is the name of the database system.

The rdbms-drivers thunk returns the list of possible vendor-tag values.

The rest of parameters specify a vendor-specific connection information.

Usually this includes:


(rdbms-connect "mysql" dbname: "test" hostname: "duron")
-| #|MYSQL-CONNECTION [IMPL: #<foreign:MYSQL:80947f8>]|

(rdbms-connect "oracle" username: "scott" password: "tiger")
-| #|oracle-connection [env::oci-env: #<foreign:oci-env:84a2e30>]
  [impl::oci-svc-ctx: #<foreign:oci-svc-ctx:84a8508>]
  [err::oci-error: #<foreign:oci-error:84a8574>]|

procedure: rdbms-drivers => list of string

Return the list of supported database types, for example:

-| ("mysql" "pgsql" "oracle" "sqlite")

Method: connection dismiss!

This destroys the connection, and releases all connection's resources, including all bound session objects.

Method: rdbms-object error-string => string

Return a (vendor-specific) description of the last error occured with rdbms-object.

Method: connection begin-transaction! #!optional (timeout 60) => bool

Check whether the implementation supports transactions. If yes, begin a transaction and return #t. Otherwise return #f.

The optional argument timeout sets transaction timeout value.

Method: connection commit-transaction!

Close the transaction for connection. Some implementations do not require the begin-transaction call before calling this method, but you should not call the commit-transaction if the implementation does not support transactions.

Method: connection rollback-transaction!

This ends the transaction, revert all changes made since transaction's beginning. Some implementations do not require the begin-transaction call before calling this method, but you should not call the rollback-transaction if the implementation does not support transactions.

Method: connection acquire => session

This creates new session for the given connection.

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4.0.3 Session management

Method: session cancel!

Cancel the query answering process, if any. Make a session ready for execution.

Method: session dismiss! => #unspecified

Destroy a session object, and release all the session's resources.

Method: session execute => bool

Execute a previously prepared statement. Return #t if the answer set is implied by the statement (i.e. the statement is SQL SELECT command). Otherwise return #f.

Method: session prepare sql::bstring => bool

Prepares an SQL query for execution. The position of optional parameters is marked up with parameter number preceded by colon sign. See example in the bind! section if this manual.

(prepare sess "insert into person values(:1, :2, :3)")
(bind! sess '(100 "Tsichevski" "Vladimir"))

Method: session bind! bindings::pair-nil

Positionally bind parameters of prepared session.

Example: prepare and bind an SQL INSERT statement. The positions of bound parameters are marked with :1, :2, and :3.

(prepare sess "insert into person values(:1, :2, :3)")
(bind! sess '(100 "Tsichevski" "Vladimir"))

Method: session has-answer? => bool

This answers #t if the answer set is implied by previously prepared statement. Note: some implementation require you have to execute the session before calling this method.

Method: session fetch! => pair-nil

Fetch next record from the query answer set, and return it as a scheme list object. If the end of the answer stream is reached, return an empty list.

Example: prepare and execute an SQL statement. Fetch and print all result records.

(prepare sess "select * from person")
(let loop()
   (let((value(fetch! self)))
      (when(pair? value)
         (print value)

Method: session describe => pair-nil

Describe the result of SQL query. Return a scheme list of column descriptions. The format of column descriptions depends on database imlementation. Typical column descriptors provides the following information:

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ] Complete example

In the following example, the connection with MySQL database named dept is established. Next, the table named person with columns named id, last_name and first_name is created and a few records inserted into the table. The contents of the table is retrieved back then and displayed. After that, the table is destroyed.

Here is the example :

(define conn
  (rdbms-connect "mysql" dbname: "test" hostname: "duron"))
(define sess (acquire conn))

(prepare sess
"create table person (
  id          INTEGER PRIMARY KEY,
  last_name    CHAR(20),
  first_name   CHAR(20))")

(execute sess)

(prepare sess "insert into person values(:1,:2,:3)")

(bind! sess '(100 "Tsichevski" "Vladimir"))
(execute sess)
(bind! sess '(101 "Taranoff" "Alexander"))
(execute sess)
(bind! sess '(102 "Ananin" "Vladimir"))
(execute sess)

(prepare sess "select * from person")
(execute sess)

(let loop ((answer-record(fetch! sess)))
  (when(pair? answer-record)
       (write answer-record)
       (loop (fetch! sess))))

(prepare sess "drop table person")
(execute sess)

(dismiss! conn)

The output of the program looks like this :

(100 "Tsichevski" "Vladimir")
(101 "Taranoff" "Alexander")
(102 "Ananin" "Vladimir")

[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

This document was generated by Vladimir Tsichevski on December, 26 2003 using texi2html