Wiki Diff EDBI, revision #3 to tip

%%disallow={camelcase}

=== Overview ===

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

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

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

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

=== Getting EDBI ===

You can read more about EDBI and download from http://jeremy.cowgar.com/edbi/
%%disallow={camelcase}

!!CONTEXT:edbi.e

== 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
<eucode>
edbi:open("sqlite3://people.db")
edbi:open("mysql://localhost?dbname=people")
edbi:open("pgsql://localhost?dbname=people")
</eucode>

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

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

=== 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

# edbi_open
# edbi_close
# edbi_get_error_code
# edbi_get_error_message
# edbi_execute
# edbi_query
# edbi_next
# edbi_closeq
# edbi_last_insert_id
# edbi_total_changes
# 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

<eucode>
--
-- 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()
</eucode>

=== API Reference

@[db_handle|]
==== db_handle
<eucode>
include edbi.e
public type db_handle(object o)
</eucode>

Defines a valid database connection handle.



@[dbr_handle|]
==== dbr_handle
<eucode>
include edbi.e
public type dbr_handle(object o)
</eucode>

Defines a valid database result set handle.



@[set_driver_path|]
==== set_driver_path
<eucode>
include edbi.e
public procedure set_driver_path(sequence v)
</eucode>

Set an alternate DBI driver path.



@[open|]
==== open
<eucode>
include edbi.e
public function open(sequence connection)
</eucode>

Open a database connection.



@[close|]
==== close
<eucode>
include edbi.e
public procedure close(db_handle h = 0)
</eucode>

Close a database connection



@[error_code|]
==== error_code
<eucode>
include edbi.e
public function error_code(db_handle h = 0)
</eucode>

Get the current error code, if any



@[error_message|]
==== error_message
<eucode>
include edbi.e
public function error_message(db_handle h = 0)
</eucode>

Get the current error message (text), if any



@[execute|]
==== execute
<eucode>
include edbi.e
public function execute(sequence sql, sequence data = {}, db_handle h = 0)
</eucode>

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

===== See Also:
[[:total_changes]], [[:last_insert_id]]



@[last_insert_id|]
==== last_insert_id
<eucode>
include edbi.e
public function last_insert_id(sequence seq_name = "", db_handle h = 0)
</eucode>

Get the unique id of the last inserted record.

===== See Also:
[[:execute]]



@[total_changes|]
==== total_changes
<eucode>
include edbi.e
public function total_changes(db_handle h = 0)
</eucode>

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

===== See Also:
[[:execute]]



@[query|]
==== query
<eucode>
include edbi.e
public function query(sequence sql, sequence data = {}, db_handle h = 0)
</eucode>

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

===== See Also:
[[:next]]



@[next|]
==== next
<eucode>
include edbi.e
public function next(dbr_handle dbr)
</eucode>

Retrieve the next available row of data.

===== See Also:
[[:query]]



@[closeq|]
==== closeq
<eucode>
include edbi.e
public procedure closeq(dbr_handle dbr)
</eucode>

Close an active query result.

===== See Also:
[[:query]]



@[query_row|]
==== query_row
<eucode>
include edbi.e
public function query_row(sequence sql, sequence data = {}, db_handle db = 0)
</eucode>

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|]
==== query_object
<eucode>
include edbi.e
public function query_object(sequence sql, sequence data = {}, db_handle db = 0)
</eucode>

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]]



Search



Quick Links

User menu

Not signed in.

Misc Menu