2

I'm trying to query records (between certain period) from SQL DB using APOC library. When I pass the from & to dates as parameters I'm getting an error.

Code:

String queryString ="CALL apoc.load.jdbc('mssql',\"select * from table as tb where tb.from_Date >= {fromDate} AND tb.to_Date <= {endDate} \") YIELD row";

String fromDate = "2018-11-12 00:00:00";
String endDate = "2018-11-13 00:00:00";
Map<String, Object> parameters = new HashMap<>();
parameters.put("fromDate", fromDate);
parameters.put("endDate", endDate);

StatementResult result = tx.run(queryString, parameters);

I get below errors:

If I use syntax {}

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure 
`apoc.load.jdbc`: Caused by: 
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '{'.

If I use syntax $

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure 
`apoc.load.jdbc`: Caused by: 
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid pseudocolumn "$fromDate".
stdob--
  • 28,222
  • 5
  • 58
  • 73
Manoj Chikkala
  • 23
  • 4

1 Answers1

0

In your version, the parameters placeholders are just part of the query string and they value are not passed.

So use the apoc.load.jdbcParams procedure like this:

String query = "CALL apoc.load.jdbc('mssql', {sql}, [{fromDate}, {endDate}]) YIELD row return row";
String sql = "select * from table as tb where tb.from_Date >= ? AND tb.to_Date <= ?";
String fromDate = "2018-11-12 00:00:00";
String endDate = "2018-11-13 00:00:00";

Map<String, Object> parameters = new HashMap<>();
parameters.put("sql", sql);
parameters.put("fromDate", fromDate);
parameters.put("endDate", endDate);

StatementResult result = tx.run(query, parameters);
stdob--
  • 28,222
  • 5
  • 58
  • 73