You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
[PG DRIVER] params are not working for cypher (and probably others languages others than SQL)Â #2119
From my understanding it's because the client is asking the backend to parse and cypher params are not detected as parsable commands, so it never asks to pass arguments.
Unless I misundertood your message, that's how things works with direct API but not how it can work with PG driver.
From my understanding, the PG client (here psycopg) will either tell the backend (here arcadedb's implementation) to either directly execute the query (if there is no param syntax detected in the string) or to parse it before (if there is a param detected in the string).
"SELECT %(select_value)s" gets turned into SELECT $1 ( before being sent to the backend)
Same for sqlscript as it uses the same syntax.
SQLSCRIPT output when using PG syntax :
2025-04-02 12:43:18.838 INFO [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=102)
2025-04-02 12:43:18.880 INFO [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=102)
2025-04-02 12:43:18.881 INFO [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=102)
2025-04-02 12:43:18.881 INFO [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=102)
2025-04-02 12:43:18.881 INFO [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
2025-04-02 12:43:18.881 INFO [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
2025-04-02 12:43:18.882 INFO [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
Received message of type: P
2025-04-02 12:43:18.882 INFO [PostgresNetworkExecutor] PSQL: parse (portal=) -> {sqlscript}SELECT $1 (params=1) (errorInTransaction=false thread=102)
2025-04-02 12:43:18.921 INFO [PostgresNetworkExecutor] PSQL:-> parse complete (1 - 4b) (thread=102)
Received message of type: B
paramValuesCount: 1
Parameter Value: 4
2025-04-02 12:43:18.921 INFO [PostgresNetworkExecutor] PSQL: bind (portal=) -> (query={sqlscript}SELECT $1) (thread=102)
2025-04-02 12:43:18.921 INFO [PostgresNetworkExecutor] PSQL:-> bind complete (2 - 4b) (thread=102)
Received message of type: D
2025-04-02 12:43:18.922 INFO [PostgresNetworkExecutor] PSQL: describe '' type=P (errorInTransaction=false thread=102)
2025-04-02 12:43:18.922 INFO [PostgresNetworkExecutor] PSQL:-> row description (T - 27b) (thread=102)
Received message of type: E
2025-04-02 12:43:18.922 INFO [PostgresNetworkExecutor] PSQL: execute (portal=) (limit=0)-> {sqlscript}SELECT $1 (thread=102)
2025-04-02 12:43:18.922 INFO [PostgresNetworkExecutor] PSQL:-> command complete (C - 5b) (thread=102)
Received message of type: S
2025-04-02 12:43:18.922 INFO [PostgresNetworkExecutor] PSQL: sync (thread=102)
2025-04-02 12:43:18.923 INFO [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
Received message of type: X
Please note the Parse call
Here is the cypher query when using the ArcadeDB (opencypher) syntax :
2025-04-02 12:42:38.485 INFO [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=102)
2025-04-02 12:42:38.532 INFO [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=102)
2025-04-02 12:42:38.533 INFO [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=102)
2025-04-02 12:42:38.533 INFO [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=102)
2025-04-02 12:42:38.534 INFO [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
2025-04-02 12:42:38.534 INFO [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
2025-04-02 12:42:38.534 INFO [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
Received message of type: Q
2025-04-02 12:42:38.536 INFO [PostgresNetworkExecutor] PSQL: query -> Query[language=cypher, query=return $ret_value] javax.script.ScriptException: javax.script.ScriptException: groovy.lang.MissingPropertyException: No such property: ret_value for class: Script1
at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:383)
at java.scripting/javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:231)
at com.arcadedb.gremlin.ArcadeGremlin.executeStatement(ArcadeGremlin.java:236)
at com.arcadedb.gremlin.ArcadeGremlin.executeStatement(ArcadeGremlin.java:213)
at com.arcadedb.gremlin.ArcadeGremlin.execute(ArcadeGremlin.java:73)
at com.arcadedb.cypher.ArcadeCypher.execute(ArcadeCypher.java:66)
at com.arcadedb.cypher.query.CypherQueryEngine.command(CypherQueryEngine.java:77)
at com.arcadedb.cypher.query.CypherQueryEngine.command(CypherQueryEngine.java:159)
at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1334)
at com.arcadedb.server.ServerDatabase.command(ServerDatabase.java:461)
at com.arcadedb.postgres.PostgresNetworkExecutor.queryCommand(PostgresNetworkExecutor.java:391)
at com.arcadedb.postgres.PostgresNetworkExecutor.lambda$run$4(PostgresNetworkExecutor.java:173)
at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1111)
at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:165)
Caused by: javax.script.ScriptException: groovy.lang.MissingPropertyException: No such property: ret_value for class: Script1
at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:702)
at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:381)
... 13 more
Caused by: groovy.lang.MissingPropertyException: No such property: ret_value for class: Script1
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:67)
at org.codehaus.groovy.vmplugin.v8.IndyGuardsFiltersAndSignatures.unwrap(IndyGuardsFiltersAndSignatures.java:163)
at org.codehaus.groovy.vmplugin.v8.IndyInterface.fromCache(IndyInterface.java:321)
at Script1.run(Script1.groovy:1)
at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:679)
... 14 more
2025-04-02 12:42:39.478 INFO [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
Received message of type: X
Please note instead the Q (execute) call instead .
I should have tested with the psycopg like sql and sql script and maybe it could have worked (but i'm out of the office now).
My point is, from my understanding we cannot use the PG driver without rewriting it because it doesn't correctly support params.
It expects it's own syntax instead of the ArcadeDB syntax.
Detecting variables for each languages, SQL with :variable Cyher with $variable
While we can easily do this with cypher using py2neo.cypher doing it with SQL sounds harder because arcade got it's own SQL syntax and replacing each : followed by a word sounds really like a bad idea.
Activity
ExtReMLapin commentedon Apr 2, 2025
From my understanding it's because the client is asking the backend to parse and cypher params are not detected as parsable commands, so it never asks to pass arguments.
robfrank commentedon Apr 2, 2025
"probably" we should give some love to the docs :) That said, the way to user parametrized queries should be (as per https://docs.arcadedb.com/#_10-minute-tutorial) :
ExtReMLapin commentedon Apr 2, 2025
Unless I misundertood your message, that's how things works with direct API but not how it can work with PG driver.
From my understanding, the PG client (here psycopg) will either tell the backend (here arcadedb's implementation) to either directly execute the query (if there is no param syntax detected in the string) or to parse it before (if there is a param detected in the string).
In this code :
"SELECT %(select_value)s"gets turned intoSELECT $1( before being sent to the backend)Same for sqlscript as it uses the same syntax.
SQLSCRIPT output when using PG syntax :
Please note the
Parse callHere is the cypher query when using the ArcadeDB (opencypher) syntax :
Please note instead the Q (execute) call instead .
I should have tested with the
psycopglike sql and sql script and maybe it could have worked (but i'm out of the office now).My point is, from my understanding we cannot use the PG driver without rewriting it because it doesn't correctly support params.
It expects it's own syntax instead of the ArcadeDB syntax.
ExtReMLapin commentedon Apr 9, 2025
I took a look at it this weekend.
IMO it would be better to add a translation layer to https://github.com/extremlapin/pyarcade
Detecting variables for each languages, SQL with
:variableCyher with$variableWhile we can easily do this with cypher using
py2neo.cypherdoing it with SQL sounds harder because arcade got it's own SQL syntax and replacing each:followed by a word sounds really like a bad idea.#2119 test: add parameterized query tests for JdbcQueries and psycopg2
#2119 fix: rename variable for clarity in query execution
#2119 test: add parameterized query tests for JdbcQueries and psycopg2 (
robfrank commentedon Apr 10, 2025
I added a couple of tests:
arcadedb/e2e-python/tests/test_arcadedb.py
Line 202 in c7f4f7e
Tests are green.
reading the doc https://www.psycopg.org/psycopg3/docs/basic/params.html and the one related to Java JDBC preparedStatement, I discovered that the replacement of params is performed on client side.
ExtReMLapin commentedon Apr 10, 2025
Yes thats why the translation layer should be implemented on the client (here pyarcade imo)
robfrank commentedon Apr 11, 2025
Yes, exactly, and every driver has its own syntax and semantic to manage params.
On Python you can us
%swith an array or%(name_of_param)sand a map.In Java/JDBC there is a single way, with question marks for parameters in the query:
And this is the only way for EVERY JDBC driver, not only the PG one.
BTW, I'll add more tests on python and Js e2e with the help of some AI :)
#2119 refactor Cypher ti support parametric queries over JDBC
robfrank commentedon May 13, 2025
#2146 should solve this issue
ExtReMLapin commentedon May 13, 2025
ooray
robfrank commentedon May 13, 2025
no, sorry, this one #2169 (that is still in WIP)
ExtReMLapin commentedon May 13, 2025
:'(
#2119 refactor Cypher to support parametric queries over JDBC (#2169)
robfrank commentedon May 13, 2025
merged, now it works with java JDBC with prepared Statemets
ArcadeData#2119 test: add parameterized query tests for JdbcQueries a…
ArcadeData#2119 refactor Cypher to support parametric queries over JD…
chore(studio-deps-dev)(deps-dev): bump postcss from 8.5.23 to 8.5.24 …
build(deps): bump the go-modules group in /e2e-go with 2 updates [ski…