OpenEuphoria

Euphoria DBI (DataBase Interface)

EDBI allows the programmer to use any supporting database with a unified database interface. Database drivers are loaded dynamically and can be written for any database server or client. Multiple database connections can be managed at the same time across multiple drivers. To use a different database server or driver, simply use the correct connection string. For example

edbi:open("sqlite3://people.db")
edbi:open("mysql://localhost?dbname=people")
edbi:open("pgsql://localhost?dbname=people")

EDBI automatically maps database values and types into Euphoria native types. For instance, a VARCHAR comes back as a sequence, a NUMERIC or DECIMAL as an atom, an INTEGER as an integer, a DATE, TIME, DATETIME or TIMESTAMP as a datetime, etc...

Querying EDBI allows the use of Euphoria types directly as well

edbi:query("SELECT * FROM people WHERE zip=%d AND dob < %D", { 30293,
    datetime:subtract(datetime:new(), 18, YEARS) })

Available Drivers

At this point in time, only the reference driver is available for SQLite3. It's connection string is: sqlite3://filename. Other database drivers will come in due time, starting next with MySQL and then PostgreSQL.

Database drivers are very easy to write. Please see the source code, and look at drivers/sqlite3/ for more information and a good example.

Database drivers must implement a common, but very minimal API. The methods the database driver must implement are

1. edbi_open
2. edbi_close
3. edbi_get_error_code
4. edbi_get_error_message
5. edbi_execute
6. edbi_query
7. edbi_next
8. edbi_closeq
9. edbi_last_insert_id
10. edbi_total_changes
11. edbi_is_empty

Database drivers can be implemented in C for sheer speed or in Euphoria compiled as a DLL.

Current Status

EDBI is brand new and API changes will take place. I am seeking user input and user help for other drivers. Currently EDBI is only tested on Windows, although with a slight change to the binary driver build files, it should work with out source code change on all platforms that Euphoria supports.

EDBI can be downloaded via SVN at, http://jeremy.cowgar.com/svn/edbi/trunk by issuing the following commands

c:\projects> svn co http://jeremy.cowgar.com/svn/edbi/trunk edbi

This will include a pre-built sqlite driver.

Downloadable packages outside of SVN are not yet available as we are still early in the development stages.

An Example

--
-- Example edbi use
-- 

include std/datetime.e
include edbi/edbi.e

sequence data = {
    { "Ronald Mc'Donald", 29382, datetime:subtract(datetime:new(), 32, YEARS) },
    { "Super Man", 55555, datetime:new(1944, 5, 18) },
    { "Wonder Woman", 21232, datetime:new(1972, 9, 29) }
}

edbi:set_driver_path("drivers")
edbi:db_handle dbh = edbi:open("sqlite3://example.db")

edbi:execute("DROP TABLE people")
edbi:execute("CREATE TABLE people (name VARCHAR(30), zip INTEGER, dob datetime)")

for i = 1 to length(data) do
    edbi:execute("INSERT INTO people VALUES (%s, %d, %D)", data[i])
end for

edbi:dbr_handle dbr = edbi:query("SELECT * FROM people")

while 1 do
    object o = edbi:next(dbr)
    if atom(o) then exit end if
    printf(1, "Name=%s, Zip=%d, Dob=%s\n", { o[1], o[2], datetime:format(o[3], "%m/%d/%Y") })
end while

edbi:close()

API Reference

db_handle

include edbi.e
public type db_handle(object o)

Defines a valid database connection handle.

dbr_handle

include edbi.e
public type dbr_handle(object o)

Defines a valid database result set handle.

set_driver_path

include edbi.e
public procedure set_driver_path(sequence v)

Set an alternate DBI driver path.

open

include edbi.e
public function open(sequence connection)

Open a database connection.

close

include edbi.e
public procedure close(db_handle h = 0)

Close a database connection

error_code

include edbi.e
public function error_code(db_handle h = 0)

Get the current error code, if any

error_message

include edbi.e
public function error_message(db_handle h = 0)

Get the current error message (text), if any

execute

include edbi.e
public function execute(sequence sql, sequence data = {}, db_handle h = 0)

Execute a SQL query that does not expect any record results.

See Also:

total_changes, last_insert_id

last_insert_id

include edbi.e
public function last_insert_id(sequence seq_name = "", db_handle h = 0)

Get the unique id of the last inserted record.

See Also:

execute

total_changes

include edbi.e
public function total_changes(db_handle h = 0)

Get the total number of changes caused by the last execute SQL statement.

See Also:

execute

query

include edbi.e
public function query(sequence sql, sequence data = {}, db_handle h = 0)

Issue a SQL query that expects record data as a result.

See Also:

next

next

include edbi.e
public function next(dbr_handle dbr)

Retrieve the next available row of data.

See Also:

query

closeq

include edbi.e
public procedure closeq(dbr_handle dbr)

Close an active query result.

See Also:

query

query_row

include edbi.e
public function query_row(sequence sql, sequence data = {}, db_handle db = 0)

Query the database for just the first row. Querying the database, fetching the first row and closing the query is all handled internally with this routine.

See Also:

query_object, query, next

query_object

include edbi.e
public function query_object(sequence sql, sequence data = {}, db_handle db = 0)

Query the database for just the first object of the first row. Querying the database, fetching the first object and closing the query is all handled internally with this routine. This routine is helpful for queries such as "SELECT COUNT(*) FROM people".

See Also:

query_row, query, next