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

[andrew_swanton]

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

[nsmith_piano]

Does this work?

where id <> chr(32)

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

[andrew_swanton]

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

[andrew_bowman · 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.

[andrew_swanton]

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