2025-11-12 sql | Clojure Slack Archive

sql

onetom 2025-11-12T09:55:45.106039Z

(jdbc/execute! "jdbc:sqlite::memory:" ["select ?named_positional_param" 123])
=> [{:named_positional_param 123}]

this is odd, because according to the sqlite docs, ? can only be followed by a number, not arbitrary identifier chars: https://sqlite.org/cli.html#sql_parameters is this something provided by the https://github.com/xerial/sqlite-jdbc/ driver somehow? there is some metadata about this: (-> "jdbc:sqlite::memory:" jdbc/get-datasource jdbc/get-connection bean :metaData .supportsNamedParameters) => true

onetom 2025-11-12T09:56:19.084899Z

https://github.com/xerial/sqlite-jdbc/blob/524ac3a1ccbdbac55f21f82e7f4f2308d669cafc/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java#L616-L618

p-himik 2025-11-12T10:01:56.140109Z

$ sqlite3 test-db-rm-me
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> select ?a;

Looks more like "it seems to work at least in some cases, but it's not officially supported".

onetom 2025-11-12T10:07:18.886689Z

❯ sqlite3 -table '.param init' '.param set ?1 123' '.param set ?a "<a>"' 'select ?1, ?a'
+-----+---+
| ?1  | a |
+-----+---+
| 123 |   |
+-----+---+

meanwhile via jdbc

(jdbc/execute! "jdbc:sqlite::memory:" ["select ?2, ?1" 123 456])
=> [{:?2 456, :?1 123}]

(jdbc/execute! "jdbc:sqlite::memory:" ["select ?2 c1, ?1 c2" 123 456])
=> [{:c1 456, :c2 123}]

(jdbc/execute! "jdbc:sqlite::memory:" ["select ?b, ?a" 123 456])
=> [{:b 123, :a 456}]

(jdbc/execute! "jdbc:sqlite::memory:" ["select ?b c1, ?a c2" 123 456])

Execution error (SQLiteException) at org.sqlite.core.DB/newSQLException (DB.java:1179).
[SQLITE_ERROR] SQL error or missing database (near "c1": syntax error)

p-himik 2025-11-12T10:08:15.438229Z

Ah, I see.

onetom 2025-11-12T10:13:35.999079Z

the reason i was even trying ?some_ident is because next jdbc was giving me :some_ident as the column name, but when i used @some_ident i got the invalid keyword literal :@some_ident. $some_ident became :$some_ident, which is kinda ok just ugly, but :some_ident became ::some_ident, where the 2nd : is part of the name...

(-> (jdbc/execute! "jdbc:sqlite::memory:" ["select :some_ident" 123]) ffirst key name)
=> ":some_ident"

(im just trying to establish some coding guidelines, that's why im trying to understand these capabilities)

onetom 2025-11-12T10:17:04.197719Z

1st i was happy that i can just pass in a map as a parameter, but i haven't read the return value properly 🙂

(jdbc/execute! $db ["select ?p" {:p 123}])
=> [{:p "{:p 123}"}]

onetom 2025-11-12T10:30:15.113749Z

i tried to make the table name parametric in the FROM clause of a SELECT statement, but that doesn't seem to be supported either

(jdbc/with-transaction [tx "jdbc:sqlite::memory:"]
    (letfn [(! [& sql]
              (-> (jdbc/execute! tx sql)
                  (try (catch Exception ex (ex-message ex)))))]
      (! "create table t(c)")
      (! "insert into t (c) values (?)" 123)
      [(! "select * from t")
       (! "select * from ?" "t")]))
=> [[{:t/c 123}] "[SQLITE_ERROR] SQL error or missing database (near \"?\": syntax error)"]

anything i try today just fails 😢

p-himik 2025-11-12T10:35:40.528819Z

> SQLite allows https://sqlite.org/lang_expr.html#varparam to appear in an SQL statement anywhere that a literal value is allowed. A table name is not a place that allows a literal value.

onetom 2025-11-12T10:47:53.466269Z

well... apparently it's not, though based on the surface syntax it's not that obvious. plus FROM allows subqueries, so i thought it would be intuitive to support params too 🙂 our use-case is that we have included a data-source identifier into the table names and the suffix of the table name is what describes the content of the table. surprisingly claude sonnet / opus has no problem constructing correct queries based on just simple instructions, like

# Get all available sources from source-info tables
sqlite3 xxx.sqlite "SELECT name FROM sqlite_schema WHERE type='table' AND name LIKE '%:source-info';"

# Query specific source metadata (replace {SOURCE} with actual source name)
# Use JSON output for better AI readability
sqlite3 -json xxx.sqlite "SELECT * FROM \"{SOURCE}:source-info\";"

onetom 2025-11-12T10:50:33.298939Z

but it's quite some complication for the "rigid", non-AI 2.0 programming approach, where we have to fight with "bobby tables" 🙂

onetom 2025-11-12T11:04:53.200719Z

googled for: > why sql parameters are not allowed in a from clause of a select statement? and the AI answer is: > SQL parameters are not allowed in the FROM clause of a SELECT statement because the FROM clause defines the table(s) or view(s) from which data is retrieved, and this information is essential for the database management system (DBMS) to compile an execution plan for the query. kinda makes sense...

p-himik 2025-11-12T11:48:20.323439Z

> surprisingly claude sonnet / opus has no problem constructing correct queries based on just simple instructions It'll work up until it doesn't for some random query. Since it's a probabilistic thing, it's not guaranteed to work. Whenever I encounter something in SQL that I'm not that familiar with, I often start with an LLM just to get a better grasp on that something and then proceed to the docs. It's not all that infrequent when generated queries are simply wrong - bad syntax, non-existing functions, clauses from a different SQL dialect. Anyway, for what you need something like HoneySQL is the answer. Or string concatenation/formatting if the table name is guaranteed to not need any escaping.

👍 1

Clojurians Log v2

sql