PSQLException on SQL query from Neo4j

[ThomasVarias]

Hi All,

I am trying to execute a Postgres SQL query through: 

apoc.load.jdbcParams

and I try to give it an Array as a parameter like this:

WITH "select * from data.table where id = ANY(?)" as sql
CALL apoc.load.jdbcParams("jdbc:postgresql://db/testdb?user=test&password=*****&prepareThreshold=1",
sql, [['f1e245b9-e902-43d4-b3ea-01c5d8c89ac7','e3d5665b-b3b0-4d19-bf78-4c722ae394ae']])
YIELD row

When I run this I get the following error:

Failed to invoke procedure `apoc.load.jdbcParams`: Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

`I know this is not a Neo4j and maybe not even an apoc problem but I was wondering if someone can give me some pointers on how to solve this and how we can pass an Array as a parameter to an SQL apoc call? I tried various things as giving it type hints, but I keep on getting this error unfortunately.

Many thanks in advance,

Thomas

[glilienfield]

From what I read, the argument for 'ANY' is a subquery. Try changing the predicate to 'id IN ?'

[ThomasVarias]

Thank you @glilienfield for your answer!

I tried a few more things and the best I got to at the moment is this:

WITH "select id from data.table where id::text IN (?,?)" as sql
CALL apoc.load.jdbcParams("jdbc:postgresql://db/db?user=test&password=****&prepareThreshold=1", sql, 
['f1e245b9-e902-43d4-b3ea-01c5d8c89ac7','c9b495eb-b90f-4435-8a8e-5b11f10676e6'])
YIELD row
MATCH (n:GenericNode)
WHERE n.id in [row.id]
CALL apoc.path.subgraphNodes(n, {relationshipFilter: ":Parent>",
     minLevel: 0,
     maxLevel: -1}) YIELD node
return node.id;

And we would like to pass in an arbitrary amount of ID's into the query. Would the APOC team be open for patches that would allow this?

Many thanks in advance,

Thomas

[glilienfield]

The 'IN' clause accept a list of values, although Oracle has a limit of 1000 elements per IN clause. Have you tried something like this. Here the first and only element of the parameters array is a list itself. 

WITH "select id from data.table where id::text IN ?" as sql
CALL apoc.load.jdbcParams("jdbc:postgresql://db/db?user=test&password=****&prepareThreshold=1", sql, 
[['f1e245b9-e902-43d4-b3ea-01c5d8c89ac7','c9b495eb-b90f-4435-8a8e-5b11f10676e6']])
YIELD row
MATCH (n:GenericNode)
WHERE n.id in [row.id]
CALL apoc.path.subgraphNodes(n, {relationshipFilter: ":Parent>",
     minLevel: 0,
     maxLevel: -1}) YIELD node
return node.id;

.