Clojurians Log v2

If anyone might have an idea why 1st request returns some data and 2nd one fails, please share your thoughts

(next.jdbc/execute-one! t-conn
                        ["SELECT * FROM foo WHERE id = ?" 123])

(next.jdbc/execute-one! t-conn
                        ["COPY(SELECT * FROM foo WHERE id = ?) TO '/tmp/foo.csv' WITH CSV DELIMITER ',' HEADER" 123])
Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2532).
ERROR: there is no parameter $1
  Position: 36

These 2 queries look very similar.. same number of arguments. No idea why in 2nd case it fails to find a parameter

@nfedyashev Just to clarify, since you talk about HoneySQL and a "huge structure", are the two queries above the repro case without all of that complexity? So, the actual queries that work and fail respectively?

(i.e., the failing case is something I could actually add to the next.jdbc test suite for PG and expect it to "work" if I can figure out what the issue is 🙂 )

@seancorfield this should be the shortest code to reproduce the issue(2nd function fails):

(next.jdbc/execute-one! t-conn
                          ["SELECT 1 WHERE 2 = ?" 2])

  (next.jdbc/execute-one! t-conn
                          ["COPY(SELECT 1 WHERE 2 = ?) TO '/tmp/foo.csv' WITH CSV DELIMITER ',' HEADER" 2])

Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2532).
ERROR: there is no parameter $1
  Position: 25

If I execute this copy command in psql, it works fine:

COPY(SELECT 1 WHERE 2 = 2) TO '/tmp/foo.csv' WITH CSV DELIMITER ',' HEADER;
COPY 1

PostgreSQL 11.7 next.jdbc "1.1.547"

@seancorfield thanks for your time Sean! Let me know if you need any additional info. Not sure if I fully understood your clarification question. That looks like next.jdbc level issue, not HoneySQL.

Yup, you clarified. Thanks. I'll create an issue and investigate tomorrow or over the weekend. It's an odd one. But, hey, PostgreSQL is its own kind of special...

https://github.com/seancorfield/next-jdbc/issues/136

@nfedyashev I can repro that in the test suite. Thank you! Now I just have to keep poking at it until I figure out why it breaks 🙂

ERROR in (postgresql-issue-136) (QueryExecutorImpl.java:2510)
Uncaught exception, not in assertion.
expected: nil
  actual: org.postgresql.util.PSQLException: ERROR: there is no parameter $1
  Position: 25
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2510)
    org.postgresql.core.v3.QueryExecutorImpl.processResults (QueryExecutorImpl.java:2245)
    org.postgresql.core.v3.QueryExecutorImpl.execute (QueryExecutorImpl.java:311)
    org.postgresql.jdbc.PgStatement.executeInternal (PgStatement.java:447)
    org.postgresql.jdbc.PgStatement.execute (PgStatement.java:368)
    org.postgresql.jdbc.PgPreparedStatement.executeWithFlags (PgPreparedStatement.java:159)
    org.postgresql.jdbc.PgPreparedStatement.execute (PgPreparedStatement.java:148)
    next.jdbc.result_set$stmt__GT_result_set.invokeStatic (result_set.clj:631)

@nfedyashev After doing some research on this, it seems that the COPY command cannot have parameters (I found a node.js repo for PG that supports COPY and has closed out several issues asking for parameterized copy queries, saying that PG simply doesn't support it. I wondered if you could do it via EXECUTE format(...) USING ... but that doesn't seem to support the COPY command (only basic CRUD operations). So I'm not sure that this is actually possible.

Do you have links to documentation indicating that it should be possible via a PreparedStatement?

yes, I was just trying to post it in the issue - https://www.postgresql.org/docs/9.3/sql-prepare.html

"Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement."

I'll try to transform it into a regular string

Right, but COPY is not a SELECT, INSERT, UPDATE, DELETE

yep

So... close the issue out as "not possible"?

yep. Thank you

As my PostgreSQL friend explained, same issue is with TRUNCATE, GRANT functions and some others. They function more like a utility rather than other regular commands so it's kind of hard to transform them into a prepared statement

hi, anyone has experience in hugsql? I need to convert this query into their syntax.

SELECT id
FROM (VALUES(4),(5),(6)) V(id)
EXCEPT
SELECT id 
FROM images;

select id from (values (:v*:my-arguments)) v(id) except select id from images;

@iagwanderson Take a look at the Tuple List parameter type: https://www.hugsql.org/#param-tuple-list

@iagwanderson Also, there's a #hugsql channel.

thanks @seancorfield I will check there too next time.

Curtis is often pretty attentive here but you'll find more HugSQL users in that channel, in case he's not around 🙂

(my experience of HugSQL extends solely to getting next.jdbc working with it, per the "Getting Started with HugSQL" section of the next.jdbc docs)