JDBC Source Field Syntax

The JDBC Connector detects the syntax (JDBC or Native) automatically. When you use JDBC syntax, do not specify the semicolon; use JDBCMultiStatementSeparator instead. If you want to use the native dialect of the target database, simply code as you would in other database clients.

Note: Some database clients such as the PostgreSQL client psql have functionality that is not present in the procedural language of the database. For example, psql can interact with STDIN and STDOUT whereas plpgsql as specified in the source cannot.

JDBCMultiStatementSeparator

If JDBCMultiStatementSeparator is set to #:

select * from customers#select * from partners

This will print the result of the two queries.

Note: Be careful when choosing a statement separator, especially on the Job Server side. The character should be used only to separate statements. If you use the character for something else in your query, the process will reach status Error.

JDBCOracleDBMSOutput

To write output to the output file, set JDBCOracleDBMSOutput to true.

The following example requires JDBCOracleDBMSOutput to be set to true, JDBCBindMode to be set to OracleNamed, an Out Parameter named DESCRIPTION, and an In Parameter named JOB_ID with a default value of =jobId. It will write a description of the Job to the output file and store it in the OUTPUT Parameter.

select J.A_DESCRIPTION into OUTPUT from JCS_JOB0 J where J.A_JOBID = JOB_ID;
dbms_output.put_line(OUTPUT);

JDBCOracleDBMSOutputHeader and JDBCOracleDBMSOutputFooter

To use headers and footers, set JDBCOracleDBMSOutputHeader to <h3>Description</h3><pre> and JDBCOracleDBMSOutputFooter to </pre>. The other required parameters are specified in the previous example.

select J.A_DESCRIPTION into OUTPUT from JCS_JOB0 J where J.A_JOBID = JOB_ID;
dbms_output.put_line(OUTPUT);

The output file stdout.log will contain the following (assuming that The description of this process is the description of the current process):

<h3>Description</h3><pre>
The description of this process
</pre>

Parameter Binding

To use Parameter binding, set JDBCBindMode to Ordinal (the default value). Assume the following:

• PARAMETER1 is set to 12345
• PARAMETER2 is set to Northwind

select * from customers where id = ? and name = ?

Here is another example using an additional Out Parameter.

• PARAMETER1 has a direction of Out
• PARAMETER2 is set to 12345
• PARAMETER3 is set to Northwind

select address into ? from customers where id = ? and name = ?

For this example, assume JDBCBindMode is set to OracleNamed (Oracle only).

• L_ID is set to 12345
• L_NAME is set to Northwind

select * from customers where id = l_id and name = l_name

Note that Oracle defaults to uppercase names. Although you can specify mixed case names in double quotes ( " ), such as "MyParameter", Redwood recommends using uppercase Parameter names.

Assume JDBCBindMode is set to OracleNamed (Oracle only).

• milliSeconds is set to 1000

begin
  dbms_lock.sleep("milliSeconds" / 1000);
end;

The following syntax can be used on all supported databases to bind Parameters by name.

select ?{Columns} from ?{Table}

Preprocessing will declare Parameters and set them for you. The actual Parameter name will be specified in the source, not its value.

Parameter Substitution

A Job with Parameter JDBCEnableParameterSubstitution set to Yes and two more Parameters named Table and Columns would allow the following syntax:

select &{Columns} from &{Table}

Note that this potentially exposes you to SQL injection. For example, an attacker on Oracle could set Table to the value myTable;\ngrant dba to scott with admin option;.

Suppressing or Overriding Headers

select address " ", tel_num " " from customers where id = ? and name = ?

select address "Your Address", tel_num "Your Phone Number" from customers where id = ? and name = ?

Table Queries

You can code a simple select statement as follows:

select * from customers

If you specify Parameters named PARAMETER1 and PARAMETER1 on the Job Definition, you can code the following:

select name,product from customers where product = ? and country = ?

On Oracle, you can also use the :<n> syntax:

select name,product from customers where product = :1 and country = :2

Stored Procedures and Functions

If you want to use a stored procedure, leave the Source field blank and specify the full procedure name, with schema, in the JDBCProcedureName Parameter. For example, on Oracle, you might set JDBCProcedureName to SCOTT.MY_PROCEDURE.

If JDBCProcedureName contains the name of a function, you use JDBCReturnValue to store the output of the function.

Consider the following stored procedure:

CREATE PROCEDURE scott.rm_emp (e_ID NUMBER) AS
   num_e NUMBER;
   BEGIN
      DELETE FROM emp
      WHERE emp.e_id = rm_emp.e_id;
   num_e := num_e - 1;
   END;
/

For a procedure like this, you could define PARAMETER1 as In or In/Out with Type Number, and define JDBCProcedureName as In or In/Out with Type String and the default value scott.rm_emp or SCOTT.RM_EMP.

To use it, run the Job Definition and specify the ID of the employee to remove.

Oracle PL/SQL

Standard PL/SQL Job Definition Parameters are accessed as follows:

begin
:1 := 3;
dbms_output.put_line(:2);
end;

Note that in this example, the Job contains two Parameters named PARAMETER1 and PARAMETER2. These Parameters are accessed via :1 and :2, respectively.

TSQL (MS SQL Server and SAP/Sybase ASE)

This example requires Parameters Product, Date, and JDBCEnableParameterSubstitution, with JDBCEnableParameterSubstitution set to Yes.

use Sales;
go
if (select count(*) from customers where product = '?{Product}' and paid is null and reminder is null) > 0
BEGIN
exec send_reminders('?{Product}');
update customers set reminder = '?{Date}' where product = '?{Product}' and paid is null and reminder is null
END;
go
Print 'updated unpaid ?{Product} purchases'
go

PostgreSQL

PostgreSQL supports several procedural languages which, once installed and created, can be used with RunMyJobs just like PL/pgSQL (the default).

This PL/pgSQL example requires a Parameter named Message with the message to log and JDBCEnableParameterSubstitution set to Yes.

 DO language plpgsql $$
 BEGIN
   RAISE NOTICE '?{Message}';
 END
 $$;

PL/Perl

The language in this example requires additional libraries installed on your PostgreSQL server. See the PostgreSQL documentation for more information.

CREATE LANGUAGE plperl;

CREATE OR REPLACE function returns_array()
RETURNS text[][] AS $$
    return [['a"b','c,d'],['e\\f','g']];
$$ LANGUAGE plperl;

select returns_array();