Outdated egg!

This is an egg for CHICKEN 3, the unsupported old release. You're almost certainly looking for the CHICKEN 4 version of this egg, if it exists.

If it does not exist, there may be equivalent functionality provided by another egg; have a look at the egg index. Otherwise, please consider porting this egg to the current version of CHICKEN.

  1. Outdated egg!
  2. Introduction
  3. Authors
  4. Requirements
  5. Examples
    1. Basic usage
    2. Prepared statements
  6. On Locator Objects (LOBs)
  7. Definitions
    1. Connecting and setup
    2. Querying
      1. Prepared statements
  8. Transactions
  9. License

Introduction

The sqlora egg is a simple wrapper for libsqlora8, a simple C-interface for accessing Oracle databases.

Currently it has been tested on Linux against Oracle 9 and 10 databases.

Authors

Graham Fawcett

Requirements

Examples

Basic usage

 
(use sqlora8)
(sqlo-init)
(sqlo-connect "scott/tiger")
(print (sqlo-ora-version))
(receive (rows names types)
    (sqlo-query "select userid, first, last, email, age from people")
  (for-each print rows))

Prepared statements

There isn't any clean syntax for prepared statements yet, but here's one way to do it:

(let ((prep-stmt (sqlo-query "select * from people where userid=:1"
                             '("") 	; empty arguments
			     keep-alive: #t)))
  ;; note, the query has been executed once at this point. I plan to
  ;; fix this in a future version.
  (for-each (lambda (uid) (print (sqlo-query prep-stmt (list uid))))
	    '("fred" "mary" "felix")))

On Locator Objects (LOBs)

Sorry, but there is currently *no* support for LOBs. Queries returning LOB values will result in errors. I hope to fix this in a future release.

Definitions

Connecting and setup

 
[procedure] (sqlo-init (#!key (threaded #f) (max-conns 1) (max-cursors 100)))

This procedure must be called once, to initialize the libsqlora8 library. Calling it again will result in an error. Keyword arguments are provided to indicate whether multithreaded access is required, how many connections to allocate, and how many cursors per connection to allocate.

Note: in this version, only single-threaded access has received significant testing. See threading notes in (sqlo-connect), below.

[parameter] (*sqlora-conn*)

Refers to the current connection, the one that will be used by (sqlo-query) and other procedures. This parameter is automatically set for you by the (sqlo-connect) and (sqlo-attach) procedures.

[procedure] (sqlo-connect connstring)

Given an Oracle connection string, return a connection object, and also set the *sqlora-conn* parameter.

It is not expected that you will access the connection object directly. Rather, (sqlo-query) and other connection-manipulation procedures operate on the *sqlora-conn* parameter that has been defined for this thread.

[procedure] (sqlo-attach connstring)
[procedure] (sqlo-session-begin uid pwd)

Together, (sqlo-attach) and (sqlo-session-begin) are equivalent to (sqlo-connect). You can call these two procedures instead if you wish to test for the server-connection before you log in.

[procedure] (sqlo-disconnect)

Disconnect the current thread's connection (the *sqlora-conn* parameter), and reset the parameter to #f.

[procedure] (sqlo-ora-version)

Returns the version-string of the Oracle database you are connected to.

Querying

 
[procedure] (sqlo-table-exists? table)
[procedure] (sqlo-exists? table colname colval #!optional (where ""))

The (sqlo-table-exists?) procedure tests whether a table exists in the current database. It can be defined using the lower-level procedure, (sqlo-exists?), as follows:

 (define (sqlo-table-exists? table)
  (sqlo-exists? "USER_TABLES" "TABLE_NAME"
                (string-upcase table)))
[procedure] (sqlo-exec statement)

Execute an SQL statement, ignoring any rows/values returned. Mainly useful for queries that do not return useful data (e.g. INSERT queries).

[procedure] (sqlo-query statement #!optional (params #f) #!key (metadata #t) (keep-alive #f) (no-fetch #f))

Send an SQL query to the database, and return three values: (1) a list of the rows resulting from the query (where each row is a list of values), (2) a list of column names, and (3) a list of column types.

If the statement contains parameter placeholders (e.g. "SELECT email FROM people WHERE userid=:1"), then a list of parameters should be provided as the second argument (e.g., '("fred")).

If the metadata keyword is #f, then the second two values will not be returned, and #f will be retuned in their place.

Prepared statements

If the keep-alive keyword is a non-false value, then *four* values are returned: a cursor reference, a list of rows, a list of names and a list of types. The cursor reference can be used in subsequent (sqlo-query) calls in place of the statement string. This is the current interface for making "prepared statement" calls, and can be used to improve query speed, esp. with complex queries. When finished, close the prepared-statement cursor with a call to (sqlo-close).

[procedure] (sqlo-close cursor)

Explicitly close and deallocate a cursor. Usually only needed if you are using prepared statements (see sqlo-query).

Transactions

 
[procedure] (sqlo-commit)
[procedure] (sqlo-rollback)

Commit or roll back the current transaction.

[procedure] (sqlo-set-autocommit flag)

Enable or disable autocommit behaviour.

[procedure] (sqlo-autocommit)

Return 1 or 0 if autocommit is on or off, respectively.

License

sqlora is licensed under the BSD License. Copyright (C) 2007 Graham Fawcett <graham.fawcett@gmail.com>