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:
Executing a query directly:
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.
Now let's create the table manually:
Time to insert some books:
Now let's select the book:
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.
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.