Skip to Content

Scheduling Stored Procedure via XS Job Issue

Hello,

I've created the stored procedure below in HANA which I'd like to schedule via an XS Job.

It can also be seen here in the catalog:

And this is the name that I see it in the SQL prompt when creating it:

PROCEDURE "49_LOCALYTICS"."StoredProcedures::LOC_NEW_DEVICE_MOVE" ( ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS etc. etc.

I can manually call the procedure via the SQL prompt and I know it is working.

I have created the xsjob and the xsjs files as shown below:

xsjob:

xsjs:

I've scheduled the job through the XS page but the error I'm getting seems to indicated that I'm not passing the correct path for my stored procedure in the XSJS file:

I've tried several different combinations of what I think should be the correct path, but I keep facing this error.

Am I missing something blatantly obvious, or can someone shed some light on the correct syntax?

Thank you,

Marcelo

1.png (15.1 kB)
1.png (10.2 kB)
1.png (15.0 kB)
1.png (15.4 kB)
1.png (23.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 22 at 04:43 AM

    Hello Marcelo,

    the error is caused by the missing double quotes surrounding the procedure alias in the query string. Also the schema name is not necessary. You can also leave out the curly brackets in the query string.

    ...
    var query = 'CALL "StoredProcedures::LOC_NEW_DEVICE_MOVE"()';
    ...

    Another point is why you are encapsulating the procedure call in a xsjs function if do nothing else than just calling the procedure. You can directly use the procedure in the xsjob file (just use the procedure alias name instead of the xsjs function for the xsjob file action).

    And if you need the xsjs function because of some reasons, please do not use the legacy database api $.db. Please use the newer api $.hdb which gives you more elegant ways to call procedures.

    Regards,
    Florian

    PS: I think you know that, but if you create new stuff please check if you can already do it in XSAdvanced to avaid later migration efforts.

    Add comment
    10|10000 characters needed characters exceeded

    • I figured it out:

      { "description": "Job to move data from Temp to Main table every 15 minutes",

      "action": "StoredProcedures::LOC_NEW_DEVICE_MOVE",

      "schedules": [ { "description": "Move data from Temp to Main table",

      "xscron": "* * * * * 2,17,32,47 0",

      "parameter": {"schema_name":"49_LOCALYTICS"} } ] }

      Also figured out the inelegant XSCRON method of scheduling it as per my requirement. Do you know the "elegant" way of doing it?

      Thank you again for your help!