Preparing statement

[flotsfacetieux]

Hello, I have the following issue :

(dbi:with-connection (conn :sqlite3 :database-name #P"/tmp/enemy.db")
    (dbi:execute (dbi:prepare conn "CREATE TABLE enemy (
                                                                 name STRING PRIMARY KEY,
                                                                 age INTEGER NOT NULL,
                                                                 address TEXT,
                                                                 fatal_weakness TEXT NOT NULL DEFAULT ?,
                                                                 identifying_color CHAR(20) UNIQUE);") 
         (list "None")))

DB Error: near "?": syntax error (Code: ERROR) [Condition of type DBI.ERROR:DBI-PROGRAMMING-ERROR]

cl-dbi from quicklisp cl-dbi-20210228, latest quicklisp client and latest updates

sbcl 2.1.1

Linux Ubuntu 20.04

My initial request was :

(multiple-value-bind (query-string query-args)
      (sxql:yield (sxql:create-table :enemy
         ((name :type 'string
            :primary-key t)
          (age :type 'integer
               :not-null t)
          (address :type 'text
               :not-null nil)
          (fatal_weakness :type 'text
                  :not-null t
                  :default "None")
          (identifying_color :type '(:char 20)
                     :unique t))))
    (dbi:with-connection (conn :sqlite3 :database-name #P"/tmp/enemy.db")
      (dbi:execute (dbi:prepare conn query-string) 
           query-args)))

[shakatoday]

SQLite

The parameter token seems not to be supported after DEFAULT in sqlite.

SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> CREATE TABLE enemy (
   ...>   name STRING PRIMARY KEY,
   ...>   age INTEGER NOT NULL,
   ...>   address TEXT,
   ...>   fatal_weakness TEXT NOT NULL DEFAULT ?,
   ...>   identifying_color CHAR(20) UNIQUE
   ...> );
Error: near "?": syntax error

It works on others like UPDATE

sqlite> UPDATE game SET votes = ? WHERE (name = ?);
sqlite>

PostgreSQL

Up to PostgreSQL 14, prepared statements can only work on SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

MySQL

I haven't searched nor tried.

[flotsfacetieux]

Thank you so much. So, this isn't a cl-dbi issue. I close the issue.