ForEach in Batch Scripts - Nodejs

Dear Team,
I am working on below scenario where I need to create two edges based on input coming from API payload followed by vertex. The edges are connecting to vertex.

Steps
Create a vertex A
Create a edges between A and number of vertex ID coming in the request payload. Its an Array.

Environment
Node.js 10.1
OrientJs
Oriend DB - 3.0.24

Batch Script
var dateFrom = dateFormat(new Date(inputData.schedule_details.date_from));
var dateTo = dateFormat(new Date(inputData.schedule_details.date_to));

                                Db = await skewlDatabase.getConnection();
                                Db = await Db.open();
                                var i =0;
                                let batch = "begin;\nLET trip = INSERT INTO Trip_Schedule (ID,DRIVER_ID,DATE_START,DATE_END,DAYS,START_TIME,SESSION,CREATED_TIME,MODIFIED_TIME) values (sequence('tripscheduleid').next(),"+inputData.driver_id+","+"\""+dateFrom+"\""+","+"\""+dateTo+"\""+","+"["+inputData.schedule_details.days+"],"+"\""+inputData.schedule_details.setoff_time+"\""+","+"\""+inputData.schedule_details.session+"\",sysdate('yyyy-MM-dd HH:mm:ss'),sysdate('yyyy-MM-dd HH:mm:ss'));\nFOREACH (data IN ['1001','1002']){\nLET c = Select from Child where ID =data;\nLET d = CREATE EDGE Scheduled_For FROM $trip TO $c;}\nCOMMIT;\nreturn $trip;"
                                var trx = await Db.query(batch,{class: 's'},{params:{childrenArray:inputData.children}}).then(function(res){
                                    console.log("Result"+JSON.stringify(res[0].ID));
                                    scheduleid = res[0].ID;
                                }).catch(function(error) {
                                    dbErrorType=error.type;
                                    dbError = error.name;
                                    dbErrorMessage = error.message;
                                    if(Db) {
                                        Db.close();
                                    }
                                });

Input Payload
{
“acct_id”:12345,
“driver_id”:12345,
“children”:[11,12],
“schedule_details”:{
“date_from”:“01/01/2020”,
“date_to”:“31/03/2020”,
“days”: [ “Mon”,“Tue”,“Wed”,“Thu”,“Fri”],
“start_time”:“7”
session:“Morning”
}
}

Problem
I was trying to use Batch scripts to avoid round trip network calls.

The Script which I was written here below. However, I have been thrown error constantantly

2019-12-20 02:45:03:676 INFO {db=skewl} /192.168.10.1:59265 - Read 474 bytes: ^@^@^@^As^@^@^@^Csql^@^@^A�begin;
LET trip = INSERT INTO Trip_Schedule (ID,DRIVER_ID,DATE_START,DATE_END,DAYS,START_TIME,SESSION,CREATED_TIME,MODIFIED_TIME) values (sequence(‘tripscheduleid’).next(),100000004,“2020-01-01”,“2020-03-31”,[Mon,Tue,Wed,Thu,Fri],“7”,“Morning”,sysdate(‘yyyy-MM-dd HH:mm:ss’),sysdate(‘yyyy-MM-dd HH:mm:ss’));
FOREACH(data IN [:childrenArray]){
LET c = Select from Child where ID =data;
LET d = CREATE EDGE Scheduled_For FROM trip TO c;}
COMMIT;
return trip;^@^@ [OChannelBinaryServer]Exception 067422A1 in storage plocal:/opt/orientdb-3.0.24/databases/skewl: 3.0.24 - Veloce (build 9920dff342aee1351c89e749d3f0a94ce466355b, branch 3.0.x)
com.orientechnologies.orient.core.command.OCommandExecutorNotFoundException: Cannot find a command executor for the command request: sql.FOREACH (data IN [:childrenArray]) {^M
DB name=“skewl”
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.parse(OCommandExecutorSQLDelegate.java:54)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.parse(OCommandExecutorSQLDelegate.java:39)
at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:3920)
at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:68)
at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeCommand(OCommandExecutorScript.java:517)
at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeSQLScript(OCommandExecutorScript.java:382)
at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeSQL(OCommandExecutorScript.java:228)
at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeInContext(OCommandExecutorScript.java:104)
at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.execute(OCommandExecutorScript.java:88)
at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:3988)
at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:3922)
at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:68)
at com.orientechnologies.orient.server.OConnectionBinaryExecutor.executeCommand(OConnectionBinaryExecutor.java:727)
at com.orientechnologies.orient.client.remote.message.OCommandRequest.execute(OCommandRequest.java:103)
at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.sessionRequest(ONetworkProtocolBinary.java:310)
at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.execute(ONetworkProtocolBinary.java:212)
at com.orientechnologies.common.thread.OSoftThread.run(OSoftThread.java:69)

Any help would be appreciated. I have been trying to crack this since 3 days. Could you please help me!

Hi @Jaisundar

are you using the new OrientJS API from 3.0.x?

http://orientdb.com/docs/3.0.x/orientjs/OrientJS.html

This will use the new OrientDB 3.0.x parser/executor which support forEach statement in batch script.

Let me know if this helps

Thanks

Thanks for quick reply. Yes, I do use latest version of orientjs, version 3.0.5. However, I have looked at the code from github and do not see any parser written in orientjs.

Also, the error logs which I sent in my previous message was from DB logs. I have turned on network.binary.debug in JVM level. Can you please help me what am I doing wrong or any example would be more appreciated. I did se example from documentation but no much help. its an illustration rather working version.

Thanks
Jai

To more precise I am trying to use orientDB batch loop capabilities.

Hi @Jaisundar

i mean are you using the API with OrientDBClient ?

if yes check the batch API

http://orientdb.com/docs/3.0.x/orientjs/Session.html#batch-script

Let me know if this helps

Thanks

Thanks. No I am not using OrientDBClient. I am using dB.query…

Can you please share any example for forEach using OrientDBClient.

Thanks

Jai

1557570722146.png

hi @Jaisundar

here it’s a working example

const OrientDBClient = require("orientjs").OrientDBClient;

const test = async () => {
  let client = await OrientDBClient.connect({
    host: "localhost",
    port: 2424,
    pool: {
      max: 10
    }
  });

  let pool = await client.sessions({
    name: "test",
    username: "admin",
    password: "admin",
    pool: {
      max: 100
    }
  });

  let session = await pool.acquire();

  let batch = `
  begin;
  let v1 = create vertex V set id = 1;
  let v2 = create vertex V set id = 2;

  foreach ($i in [1,2]) {
    create edge e from $v1 to (select from v where id = $i );
  }
  commit;
  return $v1;
  `;

  let result = await session.batch(batch).all();

  console.log(result);

  await session.close();
  await pool.close();
  await client.close();
};

test();

let me know if that helps

Thanks

Thanks a ton! I will try this…

Thanks

Jai

1557570722146.png