cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.

How can I embed quotes within an SQL statment whilst using apoc.periodic.iterate

Hi,

I have a very large data set I want to into neo4j from a database. However the SQL statement needed to select the data requires the use of single quotes which is causing the load to fail.

I know i could just create a view in the database which includes all my logic, then simply select * from myView. But I wonder if there is an alternative way.

CALL apoc.periodic.iterate("
CALL apoc.load.jdbc('jdbc:oracle:thin:@database:port/service_name',
 'select * 
  from MyTable
 where id <> ' '  //the quotes here are causing the error
 ',
 [],
 {credentials:{user: 'user', password: 'pasword'}}) YIELD row RETURN row
"
,"
CREATE (:entity {id: row.id}
		)
", {batchSize:10000, iterateList:true, parallel:true});

error

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure apoc.periodic.iterate: Caused by: org.neo4j.cypher.internal.v3_5.util.SyntaxException: Invalid input ''': expected whitespace, comment, '.', node labels, '[', "=~", IN, STARTS, ENDS, CONTAINS, IS, '^', '*', '/', '%', '+', '-', '=', '~', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, OR, ',' or ')' (line 5, column 16 (offset: 134))

I have tried escaping the quotes but this didn't work either

CALL apoc.periodic.iterate("
CALL apoc.load.jdbc('jdbc:oracle:thin:@database:port/service_name',
 'select * 
  from MyTable
 where id <> \' \'  //the quotes here are causing the error
 ',
 [],
 {credentials:{user: 'user', password: 'pasword'}}) YIELD row RETURN row
"
,"
CREATE (:entity {id: row.id}
		)
", {batchSize:10000, iterateList:true, parallel:true});
1 ACCEPTED SOLUTION

I hate dealing with nested quoting issues myself. If at all possible, try to create the string earlier, and pass the variable in instead. Something like this:

WITH  'select * 
  from MyTable
 where id <> " "  
 ' as query
CALL apoc.periodic.iterate("
CALL apoc.load.jdbc('jdbc:oracle:thin:@database:port/service_name',
 query,
 [],
 {credentials:{user: 'user', password: 'pasword'}}) YIELD row RETURN row
"
,"
CREATE (:entity {id: row.id}
		)
", {batchSize:10000, iterateList:true, parallel:true, params:{query:query} });

Though if you're going to do this, you may as well parameterize it and pass it in instead of defining it in a WITH clause.

View solution in original post

4 REPLIES 4

Does this work?

where id <> chr(32)

It wouldn't help with other queries, but it might work for your case.

Yes this does work.

However if I wanted to do where id = 'ABC' I would have to do some like where id = cha(65) || chr(66) || chr(67) which quickly become unreadable, and hard to maintain.

I should have made my initial problem a little more complex

I hate dealing with nested quoting issues myself. If at all possible, try to create the string earlier, and pass the variable in instead. Something like this:

WITH  'select * 
  from MyTable
 where id <> " "  
 ' as query
CALL apoc.periodic.iterate("
CALL apoc.load.jdbc('jdbc:oracle:thin:@database:port/service_name',
 query,
 [],
 {credentials:{user: 'user', password: 'pasword'}}) YIELD row RETURN row
"
,"
CREATE (:entity {id: row.id}
		)
", {batchSize:10000, iterateList:true, parallel:true, params:{query:query} });

Though if you're going to do this, you may as well parameterize it and pass it in instead of defining it in a WITH clause.

Thank for this.

I'm new to neo4j and this would have taken ages to figure out. I'll have a go at parameterizing this myself

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

All the sessions of the conference are now available online