It is not possible, unfortunately. You must do the conversion to and
from strings yourself.
I've thought about adding a hook for additional conversions based on
declared types, but there's no declared type information at all for
parameters, and the declared type for results is fragile: a column name
has a declared type but no other kind of expression does.
Ryan
On 4/23/19 20:03, David Storrs wrote:
tl;dr I'm having trouble getting JSON support working in the db module
when using SQLite and would really appreciate some direction, or
confirmation that it's impossible. I suspect that it's impossible,
since the docs list the accepted Racket types as exact-integer?, real?,
string?, and bytes?. I was hoping that having the JSON extension in
would add conversion ability to this, but it looks like not.
Longer:
SQLite does not natively support JSON, but there's an extension that can
be dynamically- or statically linked. https://sqlite.org/json1.html
When working with a Postgres database, the DB module will handle
transforming things (e.g. hashes) to and from JSON on insert/select,
which is insanely useful and convenient. I'd like to get the same
behavior in SQLite, especially since that would let me use the json_agg
function which would be a reasonable replacement for Pg's ARAAY_AGG
feature, of which I make heavy use.
Here's what I've done so far:
0. I've read the docs on the db module carefully, which has me concerned
about whether this is possible at all. Still, optimism!
1. I've compiled the JSON1 extension into the libsqlite.* files
2. I've verified that JSON is working via the sqlite CLI client (i.e.,
not the Racket db module)
3. I've put the libsqlite.* files in my /Applications/Racket_v7.1/lib
directory (one of the entries in (get-lib-search-dirs)).
At this point I tried this:
> (require json db)
> (define db (sqlite3-connect #:database "foo.db"))
> (query db "create temporary table blogsnort (id integer primary key,
data json))
(simple-result '((insert-id . #f) (affected-rows . 0)))
> (query db "insert into blogsnort (data) values ($1)" (hash 'a 1))
; query: cannot convert given value to SQL type
; given: '#hash((a . 1))
; type: parameter
; dialect: SQLite
; [,bt for context]
I tried setting the storage class on the 'data' column to TEXT (I wasn't
sure if the JSON extension added a 'JSON' type but figured it was worth
trying), but that made no difference.
Am I right that it's simply impossible and that I'll need to manually
convert to/from strings?
--
You received this message because you are subscribed to the Google
Groups "Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to racket-users+unsubscr...@googlegroups.com
<mailto:racket-users+unsubscr...@googlegroups.com>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
