sql 2023-09-29 | Slack Archive

Vinicius Vieira Tozzi09:09:29

Hi everyone, I am having an issue with next.jdbc and sqlite where I try to run the following: (jdbc/execute! ds ["INSERT INTO card_model(uuid, name, image) VALUES('1', '2', '3') ON CONFLICT(uuid) DO UPDATE SET name = EXCLUDED.name"]) , but I get the following error: [SQLITE_ERROR] SQL error or missing database (near "ON": syntax error) . I can run other SQL statements just fine, so assume the connection to the database is working just fine. so it's seems to me it's a SQL syntax issue, but I can run the exact same statement using sqite3 cli and it runs just fine, but somehow when I run trough jdbc I get this error. Anyone have any ideas on what I am doing wrong? Thanks in advance!

igrishaev09:09:19

Do you use the in-memory version of sqlite?

Vinicius Vieira Tozzi09:09:38

No, I am using a local file

Vinicius Vieira Tozzi09:09:12

or at least I think I am, this is my datasource obj:

(jdbc/get-datasource {:dbtype "sqlite"
                        :dbname "resources/db/main.db"})

igrishaev09:09:22

Try to use a persistent connection:

(def conn (jdbc/get-connection ...))

and then pass the connection object to all the query/execute functions

Vinicius Vieira Tozzi09:09:02

I still have the same issue as before:

(def conn (jdbc/get-connection {:dbtype "sqlite"
                                :dbname "resources/db/main.db"}))

(jdbc/execute! conn ["INSERT INTO card_model(uuid, name, image) VALUES(\" 1\", '2', '3') ON CONFLICT(uuid) DO UPDATE SET name = EXCLUDED.name"])

Interesting is that if I remove the part from ON CONFLICT.... it seems to work fine

Vinicius Vieira Tozzi09:09:09

but I don't see anything wrong with the syntax

curtis.summers11:09:20

Is your jdbc sqlite version up to date? https://www.sqlite.org/lang_conflict.html indicates support for insert...on conflict was added in 3.24.0 , and the current lib version is at 3.43.0.0: https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc

curtis.summers11:09:20

More version history: https://www.sqlite.org/lang_upsert.html#history

Vinicius Vieira Tozzi12:09:23

Yeah that was exactly the issue, I upgraded to version 3.43.0.0 and now it works, nice! thank you! I think I just used the version that was on Getting Started page from next.jdbc, even though it's written there to search for the latest version 😅

seancorfield15:09:28

I'll update Getting Started to use newer versions https://github.com/seancorfield/next-jdbc/issues/258

Vinicius Vieira Tozzi15:09:01

nice thanks! 😀

seancorfield16:09:32

It's updated in git. Might be a while before I release a new version for it to get to http://cljdoc.org.

2023-09-29

Channels