I'm having this error using datafly's encode-json from caveman with a struct/model. See the related caveman issue for details.

Incorrectly filed under sxql originally, but the problem manifests in datafly, not sxql.

When using a statement like:

(retrieve-one (select :* (from "eqItems") (limit 1)))

It ends up parsing out to:
"SELECT * FROM 'eqItems' LIMIT 1"

Which will obviously fail (badly) in MySQL.

I get a symbol conflict between sxql:for and iterate:for in Datafly today.

When inserting a row with an auto increment in sqlit3 for example, the newly created id as returned by sqlite3:last-insert-rowid (http://quickdocs.org/cl-sqlite/api) for the sqlite3 DBMS should be accessible. Sqlite3 does not support the RETURNING SQL clause. Alternatively, add possibility to use the last_insert_rowid() sql function in SxQl.

Is there any way to absctract away the differences in auto increment functionality in the different databases? I think autoincrement on sqlite does not work at all. Auto_increment only works for mysql.

render-json fails with an unbound slot error on a table row with an optional relationship, when the row has a null value for the relationship field.

The functions called are (datafly.json::convert-for-json) and (datafly.json:encode-json)

To reproduce table with mito:

(mito:deftable tweet () ((text :col-type (:varchar 64)) (author :col-type (or user :null))))

Then (render-json tweet) will fail if the user is :null with an unbound slot error.

note: I would've forked and submitted a fix / pull-request but I'm relatively new to lisp...

I don't know if this has anything to do with datafly, roswell or log4cl.
But I don't have this problem when not including datafly.
It works with ccl-bin but not sbcl

$ cat test.ros 
#!/bin/sh
#|-*- mode:lisp -*-|#
#| <Put a one-line description here>
exec ros -Q -- $0 "$@"
|#

(ql:quickload "datafly")

(defpackage :ros.script.test.3670228186
  (:use :cl))
(in-package :ros.script.test.3670228186)
(defun main (&rest argv)
  (declare (ignorable argv)))

$ ros build test.ros 

To load "datafly":
  Load 1 ASDF system:
    datafly
; Loading "datafly"
................
ERROR - Caught SB-THREAD:JOIN-THREAD-ERROR during
        '(LOG4CL-IMPL:STOP-HIERARCHY-WATCHER-THREAD :JOIN-THREAD):
        Joining thread failed: thread #<THREAD "Hierarchy Watcher" ABORTED

                                        {1007F913C3}> did not return normally.;
        Continuing.
[undoing binding stack and other enclosing state... done]
[saving current Lisp image into test:
writing 4800 bytes from the read-only space at 0x20000000
writing 3216 bytes from the static space at 0x20100000
writing 82640896 bytes from the dynamic space at 0x1000000000
done]

(connect-toplevel :sqlite3 :database-name #P"myapp.db")

(retrieve-one
  (select :*
    (from :user)
    (where (:= :name "nitro_idiot"))))

Using this code results in the following error, but using DBI directly works fine:

The slot SQLITE::HANDLE is unbound in the object
#<SQLITE-HANDLE {1007284013}>.
   [Condition of type UNBOUND-SLOT]

(let* ((query (dbi:prepare *conn* "SELECT * FROM users")) (result (dbi:execute query))) result)
#<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {100284A883}>

I technically found this while using caveman2 and traced it to the encode-json function imported from datafly.

If I have a table called "test" structured as such:

and run the following code

(encode-json 
  (retrieve-one
    (select :*
      (from :test)
      (where (:= :foo "bar")))))

The result is as follows
"{\"id\":[1,\"foo\",\"bar\"]}"
Which is looks to be problematic. I was instead expecting something along the lines of
"[{\"id\":1,\"foo\":\"bar\"}]

After looking at encode-json and then modifying the convert-for-json function to try to trace the code's flow, I found that the function identifies the returned list as an association-list instead of a regular list of embedded plists. Running (convert-for-json '((:id 1 :foo "bar"))) yields
((:ID . #(1 :FOO "bar")))
instead of something like the following
#(((:ID . 1) (:FOO . "bar")))

While this is consistent with how association-list-p works, it causes strange problems when processing data on the receiving end when the it expects an array of objects.

I've wrote a test for the expected behavior and a copy of the modified "verbose" version of convert-for-json that I used to trace the problem and sent a pull request for your perusal.

My case is

 (datafly:encode-json  '((COMMENT-ID 4 COMMENT-AUTHOR liuyan COMMENT-TEXT testcomment)))

The error result is

"{\"commentId\":[4,\"commentAuthor\",\"liuyan\",\"commentText\",\"testcomment\"]}"
And the right result should be
"[{\"commentid\":4,\"commentauthor\":\"liuyan\",\"commenttext\":\"testcomment\"}]"

Is it possible to use structure model in this case:
`

CONTENT> (defstruct a
slot1
slot2
(slot3 '(1 2 3) :type cons))
A
CONTENT> (with-connection (db)
(execute (create-table :a_storage
((id :type 'string)
(slot1 :type 'integer)
(slot2 :type 'integer))))
(execute (create-table :a_slot3_values
((id :type 'string)
(value :type 'integer))))
(execute (create-table :a_slot3_links
((id_a :type 'string)
(id_slot3 :type 'string)))))
; No value

`
I use additional table for store values of slot3 and table for a links. In this case should I to create instances of 'a' directly in the code? Doesn't exist a sugar to use AS to create 'a' automatically? Sorry, if this question isn't about datafly but I using its for learning how to work with databases. Thank you.

Since quicklisp 2014-07-14 datafly fails to load on ABCL:
http://common-lisp.net/project/cl-test-grid/library/datafly.html

The error is

SIMPLE-TYPE-ERROR : The value of BORDEAUX-THREADS::LOCK is #S(BORDEAUX-THREADS::MUTEX :NAME "Anonymous lock" :LOCK #<java.util.concurrent.locks.ReentrantLock java.util.concurrent.locks.Reent.... {1A11FBF}>), which is not of type BORDEAUX-THREADS::MUTEX-RECURSIVE.

Sorry I found time to report it only now.

A the moment it is impossible to emit booleans using the encode-json functions. It possible to do this using jonathan directly, by supplying the :false keyword. This does not working using datafly because the keyword gets removed in the typecase in convert-for-json.

This could be solved modifying this typecase, however it might be better to remove the typecase and use CLOS instead. This way the code is more extendable and the user could change the behavior them self.