OpenEuphoria

1. DBI for Euphoria

• Posted by jeremy (admin) Mar 15, 2009
• 1001 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

3. Re: DBI for Euphoria

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

Ah, sorry. Database Interface. It provides a generic means of accessing a large array of database servers/files w/o changing your code.

Jeremy

5. Re: DBI for Euphoria

• Posted by cstone27 Mar 15, 2009
• 986 views

I think this would be a great addition to Euphoria. I personally think that most of what Euphoria needs to become a more mainstream language is libraries like this. I'd be happy to help on this project.

Chris Stone

7. Re: DBI for Euphoria

• Posted by mattlewis (admin) Mar 15, 2009
• 968 views

This sort of thing would be great. The tough part is to avoid getting trapped by the lowest common denominator, while still having a rich and powerful interface.

I'd certainly be interested in using this for wxEDB. It currently uses ODBC for non-EDS databases. Basically, you can view data and execute queries. One potential downside to your proposed DBI would be the requirement to distribute multiple binaries.

Matt

9. Re: DBI for Euphoria

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

Chris,

I feel the same way. In what ways would you like to help? I will be working on the core this week and hope to have a working example up by weeks end. Depending on what you would like to do to help, we can go from there?

Jeremy

11. Re: DBI for Euphoria

• Posted by cstone27 Mar 15, 2009
• 965 views
• Last edited Mar 16, 2009

I could help with most of the system from design and documentation to drivers. Just let me know what kind of help you need. I work with MS SQL and MySql on a pretty regular basis and I've done some work with Postgres and Oracle 10.

Chris