Preparestatement supporting issues(?)

[seo-kw]

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

      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"

      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..

[pdpotter]

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).

[seo-kw]

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.

[pdpotter]

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();

[seo-kw]

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.

[pdpotter]

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.

[seo-kw]

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 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

[emotionbug]

I test some cases for using prepared statement.

case-1

-- 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

-- 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.

[JoshInnis]

@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.

[emotionbug]

@JoshInnis Uhm... could you explain why can't use without prepared statements?