You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by GitBox <gi...@apache.org> on 2021/05/28 08:43:41 UTC
[GitHub] [incubator-age] seo-kw opened a new issue #76: Preparestatement supporting issues(?)
seo-kw opened a new issue #76:
URL: https://github.com/apache/incubator-age/issues/76
Hello there, I got some trouble in using JDBC preparestatement. So I want to share about my experiences.
I have been confused rather am I doing something wrong or is it a bug. So, I just write down those phenomena.
1. Querystring
1. PrepareStatement
```java
PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
"select * from cypher('a', $$ "+
"match (a) where id(a) = ? return properties(a)" +
"$$) as (a agtype););"
pstmt.setString(1, "281474976710667"); //character '?' is replaced to 281474976710667
```
2. Prepared Statements Preparation & Execution in "Apache_AGE_Guide.pdf"
```java
PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
"PREPARE cypher_stored_procedure(agtype) AS " +
"select * " +
"from cypher('a', $$ " +
"match (a) " +
"where a.id = $id " +
"return properties(a) " +
"$$, $1) as (a agtype);" +
"EXECUTE cypher_stored_procedure(('{\"id\": \" ? \"}'))");
pstmt.setString(1, "844424930131976"); // character '?' is replaced to 844424930131976
```
2. Error message
Both of them got the same out of index error. because they aren't replaced to other strings which is what we want to replace. (org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.)
3. Suggestion
I asked about these errors to a coworker and understood why those errors were thrown. Because of accurate error showing, you guys did so.
I concluded dollar-quoted strings are not to be replaced now. But JDBC standards compatibility is a kind of important thing about normal users. And AGE is an extension of Postgres. So how about some another route about preparestatement supporting?
4. Conclusion
I am waiting for your opinion. or maybe you want some specific things about this article. then, feel easy to tell me. I would happily append more detailed info. If I got something miss or wrong, just let me know.
Many thanks..
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850412218
>
>
> Are you using this same connection object to execute the match query?
>
> In my asyncpg code, I have to make sure `LOAD 'age';` and `set search_path = a, ag_catalog, \"$user\", public` are executed on the connection object that is used to do actual age queries.
----
Yes. I'm using the same connection object. I have tested many functions in [http://age.incubator.apache.org/docs/Apache_AGE_Guide.pdf](http://age.incubator.apache.org/docs/Apache_AGE_Guide.pdf) through that connection. For example, basic graph creation and delete, update, and so on.
But I got stuck in preparestatement in jdbc. That's the reason that I made this issue.
Anyway, I'm thanks for the continuous reply <3
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850272683
>
>
> I haven't used JDBC myself, but if it is similar to asyncpg, it should work like this:
>
> ```
> PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
> "select * from cypher('a', $$ "+
> "match (a) where id(a) = $id return properties(a)" +
> "$$, ?) as (a agtype););"
> pstmt.setString(1, '{\"id\": 281474976710667}');
> ```
>
> Additional remark: `id(a)` (automatically generated primary key for the vertex a) is not the same as `a.id` (property id of the vertex a).
----
Hi potter. I just closed this issue by mistake. Sorry for the confusion. And thanks for your reply.
But I didn't solve the previous problem you suggested.
I just applied your solution below.
```
PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
"select * from cypher('a', $$ "+
"match (a) where a.id = $id return properties(a)" +
"$$, ?) as (a agtype););");
pstmt.setString(1, "{\"id\": 281474976710667}");
```
I check this syntax in AGE_Guide.pdf "Prepared Statements" paragraph. and maybe I know what you meant to.
but I got errors below.
`
org.postgresql.util.PSQLException: ERROR: function cypher(unknown, unknown, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
`
Maybe character '?' is recognized to character varying and it's not accepted in function.
Anyway, I appreciate your feedback.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw edited a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw edited a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850412218
>
>
> Are you using this same connection object to execute the match query?
>
> In my asyncpg code, I have to make sure `LOAD 'age';` and `set search_path = a, ag_catalog, \"$user\", public` are executed on the connection object that is used to do actual age queries.
----
Yes. I'm using the same connection object. I have tested many functions in [http://age.incubator.apache.org/docs/Apache_AGE_Guide.pdf](http://age.incubator.apache.org/docs/Apache_AGE_Guide.pdf) through that connection. For example, basic graph creation and delete, update, and so on.
But I got stuck in preparestatement in jdbc. That's the reason that I made this issue.
Anyway, I'm thanks for the your continuous reply <3
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850390215
>
>
> Did you execute `LOAD 'age';` and `SET search_path = ag_catalog, "$user", public;` on your connection before executing the query above?
>
> Probably something like this (again, I haven't tried this and I am not familiar with JDBC):
>
> ```
> Connection conn = connectionHelper.createConnection();
>
> Statement loadStatement = conn.createStatement();
> statement.execute("LOAD 'age'");
> statement.execute("SET search_path = ag_catalog, \"$user\", public;");
>
> PreparedStatement pstmt = conn.prepareStatement(
> "select * from cypher('a', $$ "+
> "match (a) where a.id = $id return properties(a)" +
> "$$, ?) as (a agtype););");
> pstmt.setString(1, "{\"id\": 281474976710667}");
> ResultSet resultset = pstmt.executeQuery();
> conn.close();
> ```
----
Hello again. Wow, thanks for the fast reply! I got dinner so am quite late lol.
Then, sure. It's a kind of basic process to using age.
I did that process in another class. and like this.
```
Statement statement = connection.createStatement();
statement.execute("CREATE EXTENSION IF NOT EXISTS age;");
statement.execute("LOAD 'age';");
statement.execute("set search_path = a, ag_catalog, \"$user\", public");
```
And also created graph using `SELECT create_graph('a')` function.
You're not familiar with jdbc but you've done this for me. Thanks a lot. But I supposed to it's kind of AGE's core architectural algorithm problem.
No offense, If I am something wrong, feel free to tell me then, I would be happy.
It's almost the weekend, have a nice week guys.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw edited a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw edited a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850390215
>
>
> Did you execute `LOAD 'age';` and `SET search_path = ag_catalog, "$user", public;` on your connection before executing the query above?
>
> Probably something like this (again, I haven't tried this and I am not familiar with JDBC):
>
> ```
> Connection conn = connectionHelper.createConnection();
>
> Statement loadStatement = conn.createStatement();
> statement.execute("LOAD 'age'");
> statement.execute("SET search_path = ag_catalog, \"$user\", public;");
>
> PreparedStatement pstmt = conn.prepareStatement(
> "select * from cypher('a', $$ "+
> "match (a) where a.id = $id return properties(a)" +
> "$$, ?) as (a agtype););");
> pstmt.setString(1, "{\"id\": 281474976710667}");
> ResultSet resultset = pstmt.executeQuery();
> conn.close();
> ```
----
Hello again. Wow, thanks for the fast reply! I got dinner so am quite late lol.
Then, sure. It's a kind of basic process to using age.
I did that process in another class. and like this.
```
Statement statement = connection.createStatement();
statement.execute("CREATE EXTENSION IF NOT EXISTS age;");
statement.execute("LOAD 'age';");
statement.execute("set search_path = a, ag_catalog, \"$user\", public");
```
And also created graph using `SELECT create_graph('a')` function.
You're not familiar with jdbc but you've done this for me. Thanks a lot. But I supposed to it's kind of AGE's core architectural algorithm problem.
No offense, If I am something wrong, feel free to tell me then, I would be happy.
It's almost the weekend, have a nice weekend guys.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw removed a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw removed a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850256389
>
>
> I haven't used JDBC myself, but if it is similar to asyncpg, it should work like this:
>
> ```
> PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
> "select * from cypher('a', $$ "+
> "match (a) where id(a) = $id return properties(a)" +
> "$$, ?) as (a agtype););"
> pstmt.setString(1, '{\"id\": 281474976710667}');
> ```
>
> Additional remark: `id(a)` (automatically generated primary key for the vertex a) is not the same as `a.id` (property id of the vertex a).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] JoshInnis commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
JoshInnis commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-857261677
@pdpotter @seo-kw Agtype is an extensible type in Postgres. The standard JDBC driver does not support it. In the driver folder for the repository there is an extension to the JDBC driver that should support using Agtype in Java.
https://github.com/apache/incubator-age/tree/master/drivers/jdbc
@emotionbug The third argument of the Cypher can only be not NULL when used with prepared statements. Otherwise an error will be thrown.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] emotionbug commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
emotionbug commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-856354546
I test some cases for using prepared statement.
**case-1**
```pgsql
-- case#1 with agtype_build_map func
select * from cypher('age', $$ MATCH (n) WHERE n.released = $released RETURN n $$, agtype_build_map('released', '1999')) as (n agtype);
-- ERROR: third argument of cypher function must be a parameter
```
https://github.com/apache/incubator-age/blob/83426d3a92bd5fee06376a38542d35efb1e6397b/src/backend/parser/cypher_analyze.c#L348-L354
it looks like bug.
**case-2**
```pgsql
-- case#2 with none dollar quoted string
select * from cypher('age', concat('MATCH (n) RETURN ', 'n')::cstring) as (n agtype);
-- ERROR: a dollar-quoted string constant is expected
```
https://github.com/apache/incubator-age/blob/83426d3a92bd5fee06376a38542d35efb1e6397b/src/backend/parser/cypher_analyze.c#L319-L340
This seems to exist to accurately mark the syntax problem, and I think it would be better to give the user an option.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] JoshInnis commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
JoshInnis commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-859957803
@emotionbug I'm not sure what you're asking. You can use AGE in Java without using Prepared Statements. However, the JDBC driver cannot parse Agtype correctly. You would need the need the extension to the JDBC driver located in this repository. Since prepared statements use Agtype to pass the parameters, you cannot use prepared statements with AGE without the driver extension.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] emotionbug edited a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
emotionbug edited a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-857279217
@JoshInnis Uhm... could you explain why can't use without prepared statements?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850256389
>
>
> I haven't used JDBC myself, but if it is similar to asyncpg, it should work like this:
>
> ```
> PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
> "select * from cypher('a', $$ "+
> "match (a) where id(a) = $id return properties(a)" +
> "$$, ?) as (a agtype););"
> pstmt.setString(1, '{\"id\": 281474976710667}');
> ```
>
> Additional remark: `id(a)` (automatically generated primary key for the vertex a) is not the same as `a.id` (property id of the vertex a).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] emotionbug commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
emotionbug commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-857279217
@JoshInnis Uhm... can you explain why?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw edited a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw edited a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850272683
>
>
> I haven't used JDBC myself, but if it is similar to asyncpg, it should work like this:
>
> ```
> PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
> "select * from cypher('a', $$ "+
> "match (a) where id(a) = $id return properties(a)" +
> "$$, ?) as (a agtype););"
> pstmt.setString(1, '{\"id\": 281474976710667}');
> ```
>
> Additional remark: `id(a)` (automatically generated primary key for the vertex a) is not the same as `a.id` (property id of the vertex a).
----
Hi potter. I just closed this issue by mistake. Sorry for the confusion. And thanks for your reply.
But I didn't solve the previous problem through your solution that you suggested.
I just applied your solution below.
```
PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
"select * from cypher('a', $$ "+
"match (a) where a.id = $id return properties(a)" +
"$$, ?) as (a agtype););");
pstmt.setString(1, "{\"id\": 281474976710667}");
```
I check this syntax in AGE_Guide.pdf "Prepared Statements" paragraph. and maybe I know what you meant to.
but I got errors below.
`
org.postgresql.util.PSQLException: ERROR: function cypher(unknown, unknown, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
`
Maybe character '?' is recognized to character varying and it's not accepted in function.
Anyway, I appreciate your feedback.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] emotionbug commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
emotionbug commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-860228285
@JoshInnis
I mean, I wonder why can't use the parameter value if don't use PreparedStatement.
```
An optional map of parameters used for stored procedure. Default is NULL. See Stored Procedures for details
```
and, read the document, but I think there is a way to support it even if it is not functionally stored procedures, and I hope this function is supported for general situation, not PreparedStatement.
Because, I think can make a more readable query using this function.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] emotionbug edited a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
emotionbug edited a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-860228285
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] pdpotter commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
pdpotter commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850282617
Did you execute `LOAD 'age';` and `SET search_path = ag_catalog, "$user", public;` on your connection before executing the query above?
Probably something like this (again, I haven't tried this and I am not familiar with JDBC):
```
Connection conn = connectionHelper.createConnection();
Statement loadStatement = conn.createStatement();
statement.execute("LOAD 'age'");
statement.execute("SET search_path = ag_catalog, \"$user\", public;");
PreparedStatement pstmt = conn.prepareStatement(
"select * from cypher('a', $$ "+
"match (a) where a.id = $id return properties(a)" +
"$$, ?) as (a agtype););");
pstmt.setString(1, "{\"id\": 281474976710667}");
ResultSet resultset = pstmt.executeQuery();
conn.close();
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] pdpotter commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
pdpotter commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850207557
I haven't used JDBC myself, but if it is similar to asyncpg, it should work like this:
```
PreparedStatement pstmt = connectionHelper.createConnection().prepareStatement(
"select * from cypher('a', $$ "+
"match (a) where id(a) = $id return properties(a)" +
"$$, ?) as (a agtype););"
pstmt.setString(1, '{\"id\": 281474976710667}');
```
Additional remark: `id(a)` (automatically generated primary key for the vertex a) is not the same as `a.id` (property id of the vertex a).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] pdpotter commented on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
pdpotter commented on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-850392760
Are you using this same connection object to execute the match query?
In my asyncpg code, I have to make sure `LOAD 'age';` and `set search_path = a, ag_catalog, \"$user\", public` are executed on the connection object that is used to do actual age queries.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] seo-kw closed issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
seo-kw closed issue #76:
URL: https://github.com/apache/incubator-age/issues/76
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-age] emotionbug edited a comment on issue #76: Preparestatement supporting issues(?)
Posted by GitBox <gi...@apache.org>.
emotionbug edited a comment on issue #76:
URL: https://github.com/apache/incubator-age/issues/76#issuecomment-857279217
@JoshInnis Uhm... colud you explain why can't use without prepared statements?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org