octave-db

Octave-db is a software that provides bindings for executing SQL queries and other database operations on various databases directly from Octave. It supports Postgres, MySQL, SQLite, and standard ODBC databases. To maintain a uniform interface throughout the various database types, Octave-db offers a simplified interface that you can use to call any database: execute queries on a given database with the command, sql(db,query). In the event that the query returns anything, for example, SELECT, it is converted to a cell array.

Octave-db also provides a way to remember a database through default_db(db), so that you can subsequently issue queries with just sql(query). The software offers four types of database objects; postgres_db, mysql_db, sqlite3_db, and odbc_db, which can be constructed by merely calling them with connection parameters, contingent on the database type.

Octave-db exposes the entire C client API for each database, such as libpq, libmysqlclient, among others. Users can execute these APIs, as in the case with the following examples.

To create a table, insert data, and execute a query on an SQLite database, you would use the following commands:

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;"))

To execute a query on a PostgreSQL database, set a default database, create a table, insert data, and execute the query:

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;")
octave:10> assert(a{1,1}==1);
octave:11> assert(strcmp(a{1,2},"a"));

For a MySQL database, use the following commands to initialize, connect, and fetch data:

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("