Multiple SQL statements in a single Database job

Is it possible to run multiple SQL statements in a single Database Job?

I tried creating a Database Job with several statements, separated by ';', but the job failed. The debug trace shows a syntax error at the semi-colon.

Also, is there a way to get a more informative error in the standard job log when a database job fails? All that shows up in the job log for the above failure is "Job completed with exit code 1."

Yes, it is possible in many databases. As long as your database can understand the syntax. 24x7 simply sends the SQL to the database as is and watches for exceptions and errors. For example in Oracle you would do it like this

BEGIN
INSERT INTO my_stage_table SELECT * FROM my_bus.table;
UPDATE my_stage_table SET col1 = 5;
…
COMMIT;
END;

In SQL Server you can use semicolons but you don't really need them. In DB2 and MySQL you would need to create a stored procedure because they don't support such methods.

Is there a way to get the exception error string to show up in the GUI or Web management interface? If the execution fails, I'd like to know why?

When error occurs, the text of that error should be available in the job log and a result it will appear in both GUI and web console.

Last edited by SysOp on Mon Oct 13, 2008 2:49 pm; edited 1 time in total

Is there a way I can extend the database job in Java so that I can feed MySQL the commands one at a type and report better errors?

	SysOp wrote:
	When error occurs, the text of that error should be available in the job log and a result it will appear in both GUI and web console.

The Job Log only shows the error:

	Code:
	Job completed with exit code 1. This exit code does not satisfy job exit code condition. Job failed.

However, the JDBC exception is much more informative, saying:

	Code:
	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...

I'd like to see the latter in the job log in the GUI.

That should be in the log too. If I' getting it correctly, the job is set to run detached as a separate system process and that's why you see "Job completed with exit code 1". If you refresh the job log view in the web console (or GUI), it will pickup the previous message too. Both messages should in the job log schedule.log file.

	Code:
	Is there a way I can extend the database job in Java so that I can feed MySQL the commands one at a type and report better errors?

I'm not sure how you can "extend" it. In theory, you can create a JavaScript job, that takes commands from some file, or has them hard-coded as a string array defined directly in the script executes them one by one using some other job modified and run dynamically. That other job could be a regular database job but without schedule, and in this scenario it is going to be used as an auxiliary job, as a command and connection placeholder. Check out 24x7 JavaScript Reference and in particular, jobModify and jobRun commands. Let us know if you need some example.

	SysOp wrote:
	That should be in the log too. If I' getting it correctly, the job is set to run detached as a separate system process and that's why you see "Job completed with exit code 1". If you refresh the job log view in the web console (or GUI), it will pickup the previous message too. Both messages should in the job log schedule.log file.

The message is in the scheduler.log file but not displayed on the GUI, at least not anywhere I have looked. I only see the "Job completed with exit code 1" message in the GUI. I see later runs of the same job, so I don't think the view is stale, but I don't know how to refresh the view in the GUI either.