1
0
-1

Hello Experts,

I am trying to use a Database Update Tool plugin in order to update a row in one Table and then insert a row in another table in a single Tool activity.

Here are my 2 queries:

a) Usage Number 1 (No semi-colon at the end of queries)

INSERT INTO APP_FD_ROUTE_NOTE(id, datecreated, datemodified, c_request_id1, c_status, c_name, c_userid, c_Role, c_notes)
VALUES(Lower(substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)),TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), '#form.aas_routes.id#', 'Route deassigned from User', 'System Activity', 'System', 'System Tool', 'No Action on Route since last 24 hrs')


UPDATE APP_FD_AAS_ROUTES
SET c_currentUser='', c_status='SA_Submitted'
WHERE id='#form.aas_routes.id#'

 

I get an error when I use the above queries in the logs:

ERROR 19 Jun 2015 21:19:29 org.joget.apps.app.lib.DatabaseUpdateTool - Error executing plugin
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059

 

a) Usage Number 2 (semi-colon at the end of each query)

INSERT INTO APP_FD_ROUTE_NOTE(id, datecreated, datemodified, c_request_id1, c_status, c_name, c_userid, c_Role, c_notes) 
VALUES(Lower(substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)),TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), '#form.aas_routes.id#', 'Route deassigned from User', 'System Activity', 'System', 'System Tool', 'No Action on Route since last 24 hrs');


UPDATE APP_FD_AAS_ROUTES
SET c_currentUser='', c_status='SA_Submitted'
WHERE id='#form.aas_routes.id#';

 

I get an error when I use the above queries in the logs:

ERROR 19 Jun 2015 21:12:29 org.joget.apps.app.lib.DatabaseUpdateTool - Error executing plugin
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)

 

I can use both the queries successfully in 2 separate instances of Database Update Tool activity.


  1. Hugo

    Hey there, Thanks for your observation, we will try it out and let you know on our observation too. Cheers.

CommentAdd your comment...

1 answer

  1.  
    1
    0
    -1

    It probably depends on your JDBC connection setting whether or not multiple statements are allowed http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement

    If not, then you'll need to run them in separate tools.

      CommentAdd your comment...