Low-level database tutorial
Furnace framework ยป Database library

Prev:Result sets

Although Factor makes integrating a database with its object system easy (see db.tuples), sometimes you may want to write SQL directly and get the results back as arrays of strings, for instance, when interfacing with a legacy database that doesn't easily map to tuples.

Executing an SQL command:
sql-command ( sql -- )

Executing a query directly:
sql-query ( sql -- rows )

Here's an example usage where we'll make a book table, insert some objects, and query them.

First, let's set up a custom combinator for using our database. See Custom database combinators for more details.
USING: db.sqlite db io.files io.files.temp ; : with-book-db ( quot -- ) "book.db" temp-file <sqlite-db> swap with-db ; inline

Now let's create the table manually:
"create table books (id integer primary key, title text, author text, date_published timestamp, edition integer, cover_price double, condition text)" [ sql-command ] with-book-db

Time to insert some books:
"insert into books (title, author, date_published, edition, cover_price, condition) values('Factor for Sheeple', 'Mister Stacky Pants', date('now'), 1, 13.37, 'mint')" [ sql-command ] with-book-db

Now let's select the book:
"select id, title, cover_price from books;" [ sql-query ] with-book-db

Notice that the result of this query is a Factor array containing the database rows as arrays of strings. We would have to convert the cover_price from a string to a number in order to use it in a calculation.

In conclusion, this method of accessing a database is supported, but it is fairly low-level and generally specific to a single database. The db.tuples vocabulary is a good alternative to writing SQL by hand.