Octave database bindings

Here are bindings that allow SQL queries and other database operations on postgres, mysql, sqlite, and standard ODBC databases from within Octave.

A simplified interface that is uniform across all database types is provided: you call sql(db,query) to execute a query on a given database db. If the query returns anything (such as SELECT), this is converted to a cell array. You can use default_db(db) to have the framework remember a database so that you can issue queries with just sql(query). There are four types of database objects, postgres_db, mysql_db, sqlite3_db, and odbc_db. You can construct these by simply calling them with connection parameters (depends on database type).

In addition, the entire C client API for each database is exposed (libpq, libmysqlclient, etc).

Some examples:

octave:1> sqlite3
octave:2> db=sqlite3_db(":memory:");
octave:3> sql(db,"create table some_table (id int4,val int4);");
octave:4> sql(db,"insert into some_table (id,val) values (1,10);");
octave:5> sql(db,"insert into some_table (id,val) values (2,9);");
octave:6> sql(db,"insert into some_table (id,val) values (3,8);");
octave:7> sql(db,"insert into some_table (id,val) values (4,7);");
octave:8> a=cell2mat(sql(db,"select * from some_table;"))
a =

1 10
2 9
3 8
4 7

octave:1> postgres
octave:2> default_db(postgres_db("host=localhost dbname=testdb user=testuser password='secret' "));
octave:3> try, sql("drop table some_table;"); catch end_try_catch
octave:4> sql("create table some_table (id int4,str varchar);");
octave:5> sql("insert into some_table (id,str) values (1,'a');");
octave:6> sql("insert into some_table (id,str) values (2,'b');");
octave:7> sql("insert into some_table (id,str) values (3,'c');");
octave:8> sql("insert into some_table (id,str) values (4,'d');");
octave:9> a=sql("select * from some_table;")
a =

[1,1] = 1
[2,1] = 2
[3,1] = 3
[4,1] = 4
[1,2] = a
[2,2] = b
[3,2] = c
[4,2] = d

octave:10> assert(a{1,1}==1);
octave:11> assert(strcmp(a{1,2},"a"));

octave:1> mysql
octave:2> db=mysql_init();
octave:3> res=mysql_real_connect(db,"localhost","root","secret","testdb");
octave:4> if (swig_this(res)!=swig_this(db))
> error("connect to db failed");
> endif
octave:5> mysql_get_client_info()
ans = 5.0.45
octave:6> mysql_get_client_version()
ans = 50045
octave:7> mysql_get_host_info(db)
ans = Localhost via UNIX socket
octave:8> mysql_get_proto_info(db)
ans = 10
octave:9> mysql_get_server_info(db)
ans = 5.0.45-Debian_1ubuntu3.1-log
octave:10> if (mysql_query(db,"select 2,4,8;"))
> error("query failed: %i %s",mysql_errno(db),mysql_error(db));
> endif
octave:11> res=mysql_store_result(db);
octave:12> f1=mysql_fetch_field_direct(res,0);
octave:13> f2=mysql_fetch_field_direct(res,1);
octave:14> nc=int32(mysql_field_count(db))
nc = 3
octave:15> nr=int32(mysql_num_rows(res))
nr = 1
octave:16> c=cell(nr,nc);
octave:17> for i=0:nr-1,
> r=mysql_fetch_row(res);
> for j=0:nc-1,
> c{i+1,j+1}=r(j);
> endfor
> endfor
octave:18> c
c =

[1,1] = 2
[1,2] = 4
[1,3] = 8

There is limited type conversion support built into the simplified API; at least integer and floating point types are converted to their counterparts in Octave. Everything else is returned as a string. If there are errors, these are translated to Octave errors.

For each database type, there are a number of tests that exercise both the high-level/unified API as well as the low-level C API of each database. These tests can serve as examples/documentation, since SWIG/Octave doesn't yet provide a way to insert documentation into wrapper code.


: Releases of this package are now available only from octave-forge. Latest sources are available via octave-forge SVN.

3/15/08: In Octave package form: database-1.0.tar.gz. Note that you must download and install SWIG from SWIG's SVN repository, and it must be in your PATH when you do pkg install database-1.0.tar.gz from Octave. You must also have all the dependent database packages installed on your system (see the DESCRIPTION file for a list).

3/10/08: octave-db-031008.tar.gz

octave-db was written by Xavier Delacour. Please send feedback, bugs, and/or patches to xavier dot delacour at gmail dot com.