Is it possible to run SQL against a table that you have created in CPS?

[Former Member]

Is it possible to run SQL against a table that you have created in CPS?

I would like to be able to run an SQL LEFT join for null ids

for a table created in CPS called Master_Schedule which has job names that should be scheduled, and the job table for any job that has a status scheduled, wait, running, etc.

Any null values found would indicate that a job that should be scheduled has been cancelled or killed and needs to be manually re-scheduled.

I'll use this list of missing jobs to send an email alert to the job monitoring team to reschedule the missing job(s).

Any pointers to a solution greatly appreciated.

Guy

[gmblom]

Hi Guy,

Of course that is possible: you can do something like this (let's assume you have put the job names in the Key field of your table):

select tv.Key from TableValue tv, Table t where tv.Table = t.UniqueId and t.Name = 'Master_Schedule' where not exists (select 1 from Job j, JobDefinition jd where jd.Name = tv.Key and j.JobDefinition = jd.UniqueId and j.Status in ('R','W','S','Q','B','q','2','4','6'))

Regards Gerben

[Former Member]

That's great news, but I'm having trouble parsing the SQL.  I was having trouble with the SQL until I replaced the second where with AND.

All good.  Cheers

Guy

[Former Member]

Sorry, Gerben.  I've been struggling to iterate over the results of the SQL.  I can create a report easily enough, but I'd like to go through the results within the RedwoodScript, and generate an email alert if any missing scheduled jobs are returned.

    String query = "select tv.Key as Job_Name from TableValue tv, Table t where tv.Table = t.UniqueId and t.Name = 'Master_Schedule' and not exists (select 1 from Job j, JobDefinition jd where jd.Name = tv.Key and j.JobDefinition = jd.UniqueId and j.Status in ('R','W','S','Q','B','q','2','4','6'))";

    jcsErr.println(query);

I've tried without success to use executeObjectQuery and executeQuery, and I believe my problem (apart from being rubbish at Java!) is that the data being returned is simple string Job_Name and the executeObjectQuery is for querying objects (like I've done successfully in other scripts).

Could you possibly give an example of how I can execute and iterate over the sql results for the above query?

Regards

Guy