OpenEuphoria

DBI for Euphoria

• Posted by jeremy (admin) Mar 15, 2009
• 1000 views

Being mainly a database programmer by trade, a DBI is important to me for some apps. Other apps I want all the raw power of a database server exposed to me. I have been thinking of a DBI for a while and this is what I came up with. Please comment if you would like to see it designed differently. This will not be a core product, but something developed and distributed separately by me, not core Euphoria (it may change, who knows).

include dbi/core.e as dbi ... This is the only include file you will need to deal with.

Your application need not "include" different database drivers, they will be dynamically loaded. Your application may even load database drivers that were not around at the time your application was developed. Anyone can develop a DBD (database driver). It is a DLL/SO file that adheres to a published "DBD" spec (according to the DBI). DBD's can exist for any database including PostrgreSQL, MySQL, ODBC, EDS, SQLite, Firebird, etc...

The connecting of the database should happen in a manner that is only changed by 1 connection string. The application should not need to know about what type of database it is connecting to, however, it could determine the connected database easily if it wishes.

The DBI will automatically convert to proper Euphoria types. Atom, Integer, String, Date, Time and Date/Time values will be understood directly.

The DBD would have to implement a small number of functions: dbd_connect, dbd_close, dbd_last_id, dbd_execute, dbd_query, dbd_field, dbd_field_count, dbd_field_name, dbd_field_type. The DBI will take care of all the nice functionality. The drivers should be written in C for speed.

On with some example code:

include dbi/core.e as dbi 
include std/datetime.e as dt 
include std/map.e as m 
 
dbi:handle dbh = dbi:connect("mysql://people?user=john") 
--dbi:handle dbh = dbi:connect("sqlite3://people.db") 
--dbi:handle dbh = dbi:connect("eds://people.edb") 
--dbi:handle dbh = dbi:connect("pgsql://people?user=jim&password=secret&host=jim.com&ssl=true") 
if dbi:error(dbh) then 
	printf(1, "DBI failed to connect: %s\n", { dbh }) 
	abort(1) 
end if 
 
if not dbi:execute(dbh, "INSERT INTO people VALUES (%s,%s,%t)", { "John", "Doe", dt:now() }) 
then 
	printf(1, "Could not insert people record: %s\n", { dbi:error_message(dbh) }) 
	dbi:close(dbh) 
	abort(1) 
end if 
printf(1, "Last inserted id: %d\n", { dbi:lastid(dbh, "people_id") }) 
 
dbi:result rs = dbi:query(dbh, "SELECT * FROM people WHERE name LIKE %s", { "%e%" }) 
if dbi:error(dbh) then 
	printf(1, "DBI failed to query people table: %s\n", { dbi:error_message(dbh) }) 
	dbi:close(dbh) 
	abort(1) 
end if 
 
while dbi:next(rs) do 
	printf(1, "Person: %s %s was born on %s\n", { dbi:field(rs, "fname"), dbi:field(rs, "lname"),  
		dt:format("%m/%d/%Y", dbi:field(rs, "dob") }) 
	printf(1, "Person: %s %s\n", dbi:fields(rs, { "fname", "lname", "dob" })) 
	printf(1, "Person: %s %s\n", dbi:fields(rs, { 1, 2 })) 
 
	m:map p = dbi:fetch_map(rs) 
	printf(1, "Person: %s %s\n", { m:get(p, "fname"), m:get(p, "lname") }) 
end while 
dbi:close_result(rs) 
 
printf(1, "Min name: %s, Max name: %s\n", { dbi:query_one(dbh, "SELECT min(fname) FROM people"), 
	dbi:query_one(dbh, "SELECT max(fname) FROM people") }) 
printf(1, "John Doe: %s %s\n", { dbi:query_one_row(dbh, "SELECT * FROM people WHERE id=%d", { 10 }) }) 
 
m:map p = m:new() 
m:put(p, "fname", "Jack") 
m:put(p, "lname", "Smith") 
m:put(p, "id", 0) 
 
if not dbi:save_map(dbh, "people", p) then 
	printf(1, "Could not save map to database table people: %s\n", { dbi:error_message(dbh) }) 
end if 
 
dbi:close(dbh) 

Any thoughts or comments?

Jeremy