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.