Using the JDBC Definition Type

The JDBC standard allows you to connect to third-party databases and perform queries against them. RunMyJobs lets you interface with JDBC. You can query tables, execute stored procedures, and specify database-specific code to be executed.

Note: See Configuring JDBC Support for information on setting up Redwood Server JDBC.

JDBC, OracleJob, and SQLPLUS Definition Types with Oracle Databases

If you need to execute calls against an Oracle database, consider the following options:

• JDBC Definition Type: Requires a Database object. The output is stored in RunMyJobs. Accepts both PL/SQL calls and JDBC queries. Numerous parameter mapping options are available (see below).
• SQLPLUS Definition Type: Requires a Platform Agent with the sqlplus executable. The output is stored on the Platform Agent. Accepts PL/SQL and SQL Plus code. Parameters are accessed and set as substitution variables.
• OracleJob Definition Type: Requires RunMyJobs to be installed in an Oracle database. The output is stored in RunMyJobs. Accepts a single anonymous PL/SQL block per Process Definition. Parameters are bound using OracleNamed bind mode.

Library

If the JDBC driver's JAR file is not in the CLASSPATH of the application server, you must specify the library where you uploaded the JAR file. If the JDBC driver is on the CLASSPATH, set the library to None. For more information, see JDBC Drivers.

Parameters

The following table lists JDBC-specific process parameters which, when set, override Process Server parameters with the same name. Parameter names that contain the name of a database vendor are specific to that database vendor. Array parameters are supported by the Definition Type and will be passed on as such to the target database. Arrays must have unique elements.

Depending on the bind mode, you can specify parameters as follows:

JDBCBindMode set to Ordinal (default):

• ?: Bound by position. The ?'s are counted from left to right, for each ? a parameter PARAMETER<n> must be defined, where <n> is the position of the ?. Note that you cannot re-use a ? when you use multiple SQL statements; each ? will be bound in the order it appears in the source.
• <native_database_syntax>: Bound by position using the native database syntax for positional binding placeholders (for example :<n> on Oracle, where <n> matches <n> from the parameter name). For each placeholder, a parameter PARAMETER<n> must be defined.
• ?{<name>}: Bound by name, where <name> is the name of the parameter.

JDBCEnableParameterSubstitution set to Yes:

• &{<name>}: Substituted by name. The placeholder is replaced with the value of the parameter in the source. This potentially exposes you to SQL injection so it is highly recommended to use parameter constraints to validate input.

JDBCBindMode set to OracleNamed:

• <name>: Bound by name, where <name> is the name of the parameter. It is recommended to use uppercase parameter names, because Oracle defaults to uppercase variable names. If you use lower or mixed-case names, you will have to quote your parameter names with double quotes (") accordingly in the source.

Credentials

The JDBC Definition Type is integrated with the credential system. The Run As User field can be filled with the database user.

If the Run As User field is left empty, the value of the DefaultRunAsUser Process Server parameter will be used.

To use a particular username and password you need to set up the following:

• Create a JDBC credential set for each required login, with the following attributes for each set:
  • Endpoint: Set to the name of JDBC Process Server connected to your database
  • Real User: The database username
  • Password: The database password
  • Virtual User (optional): An alternative name to find the username.

The syntax for specifying a credential in the Run As User field is [{virtual}:]<user>[@<endpoint>]

Using the Process Owner

Use the {forcejobowner} or {tryjobowner} syntax to specify the process owner in the Run As User field. In this case, the process submitter must have access to a corresponding credential with the same username or virtual username; the Redwood Server username of the submitter must match the username or match the virtual username of a valid JDBC credential.

The syntax is as follows:

• [{virtual}:]{forcejobowner}[@<endpoint>]
• [{virtual}:]{tryjobowner}[@<endpoint>]
• [{virtual}:]{tryjobowner}[@<endpoint>]:[{virtual}]<user>[/<password][@<endpoint>]

The following table contains examples for using the process owner and the potential consequences of a missing credential:

Note that {forcejobowner} and {tryjobowner} syntaxes are not supported in the JCS_USER Process Definition parameter.

Example

You have a credential for a virtual user named oracle_user for the database, you specify the virtual user as follows in the Run As User field.

{virtual}:oracle_user

You can also just specify the username as follows:

scott

If you have a valid credential for user scott for the target database, the connection should succeed.

Syntax

The JDBC connector detects the syntax (JDBC or Native) automatically. When you use the JDBC syntax, you do not specify the semi-colon; you use JDBCMultiStatementSeparator instead. When you want to use the native dialect of the target database, you simply code as you would in other database clients. Note that 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.

Examples

JDBCMultiStatementSeparator

JDBCMultiStatementSeparator is set to #:

select * from customers#select * from partners

This will print the result of the two queries.

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

JDBCOracleDBMSOutput

Enable JDBCOracleDBMSOutput (Oracle databases only)

You set JDBCOracleDBMSOutput to true.

This will print the output to the output file. The following example requires JDBCOracleDBMSOutput set to true, JDBCBindMode set to OracleNamed, an Out parameter named DESCRIPTION, an In parameter named JOB_ID with a default value of =jobId. It will print the description of the process 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

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

JDBCBindMode is set to Ordinal (default)

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

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

Second example using an additional Out parameter:

JDBCBindMode is set to Ordinal (default)

• 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 = ?

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", it is recommended to use uppercase parameter names.

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 process 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 code a simple select statement as follows:

select * from customers

You specify parameters named PARAMETER1 and PARAMETER1 on the process and 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 Procedure and Functions

You should not fill anything into the Source field. The name of the stored procedure and its parameters are defined in process parameters.

You specify the full procedure name, with schema, in the JDBCProcedureName parameter.

For example, on Oracle, you set JDBCProcedureName to SCOTT.MY_PROCEDURE.

When JDBCProcedureName contains the name of a fuction, 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;
/

You define PARAMETER1 as In or In/Out and datatype Number in addition to JDBCProcedureName as In or In/Out and datatype String with default value scott.rm_emp or SCOTT.RM_EMP.

You submit the Process Definition and specify the ID of the employee to remove.

Oracle PL/SQL

You use standard PL/SQL. Process Definition parameters are accessed as follows:

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

Note that the process 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; the latter 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 Redwood Server just like PL/pgSQL (the default).

PL/pgSQL - this 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();

See Also

• Connecting to Remote Databases with JDBC
• JDBC Drivers
• Creating JDBC Process Servers Using the Wizard
• Creating JDBC Process Servers
• JDBC Process Definition Wizard