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

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 on the central Redwood Server. Accepts both PL/SQL calls and JDBC queries. Numerous parameter mapping options are available (see below).
SQLPLUS Definition Type - requires a platform agent with 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 Redwood Server to be installed in an Oracle database, the output is stored on the central Redwood Server. Accepts a single anonymous PL/SQL block per process definition. Parameters are bound using OracleNamed bind mode.

Library

If the JDBC driver (jar file) is not on the CLASSPATH of the application server (AS), you need to specify the library where you uploaded the jar file. If the JDBC driver is on the CLASSPATH, set the library to None. See JDBC Drivers for more information.

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 the database vendor in question. Array parameters are supported by the definition type, they will be passed on as such to the target database. Arrays must have unique elements.

Parameter	Description
JDBCBindMode	The parameter bind mode. If this parameter is set to OracleNamed (Oracle only), parameters are bound by name and a single PL/SQL block is expected. Otherwise they will be either bound by position (with `PARAMETER<n>`, where `<n>` is the order) or by name using Redwood Server syntax (`?{name}`).
JDBCEnableParameterSubstitution	Enables Redwood Server substitution variables when set to Yes. A process parameter named `<parameter>` referenced in the process source as `&{<parameter>}` will be replaced with the value of the process parameter. This syntax is deprecated in favor of the ?{} syntax as the &{} syntax is vulnerable to code injection.
JDBCOutputFormat	The output format to use, valid values are HTMLText, XMLText and CSV.
JDBCMultiStatementSeparator	A regular expression used to delimit multiple queries in the process source; for example`;\n`. This cannot be used in conjunction with JDBCBindMode set to OracleNamed. You set this to `*;`. and can enter comments in the source after the queries terminated with a ``;, provided each query is on its own line.
JDBCOracleDBMSOutput	(Oracle only) Boolean parameter that enables/disables Oracle `dbms_output`. Equivalent to `SET SERVEROUTPUT ON/OFF` (default is false).
JDBCOracleDBMSOutputDestination	(Oracle only) `stderr.log` or `stdout.log`. Defaults to `stdout.log` for XML and HTML, and `stderr.log` for all others.
JDBCOracleDBMSOutputHeader	(Oracle only)Header to print before the dbms output. Defaults to `<h3>Output</h3><hr /><pre>` for HTML, `<output>` for XML and nothing for all others.
JDBCOracleDBMSOutputFooter	(Oracle only) Footer to print after the dbms output. Defaults to `</pre>` for HTML, `</output>` for XML and nothing for all others.
PARAMETER`<n>`	Parameter(s) for the stored procedure, the name will be used as bind position; these are also made available as variables in the source. Note that the name is case-sensitive.
JDBCProcedureName	Name of a stored procedure or function to execute, if this parameter is set, the process source will be ignored.
JDBCReturnValue	Parameter used to store the return value of the function specified in JDBCProcedureName; the parameter is ignored for procedures.
JCS_USER	Database user to use for the connection.
JCS_ENDPOINT	Database identifier to use for the connection.
JCS_PASSWORD	Password of the database user to use for the connection (not recommended, use credentials instead).
JDBCStatementFeedbackFormat	Verbosity of the SQL statement feedback in JDBC jobs. Possible options are: Simple, Normal, or Extended. For example, when you create a table named MyTable in your process, Simple results in Statement processed, Normal in Table created, and Extended in Table MyTable created.
JDBCStatementTimingFormat	Should the duration of SQL statements in JDBC jobs be printed and if so, in milliseconds or in pretty format. Possible options are: None, MilliSeconds, Pretty (Default)

Depending on the bind mode, parameters can be specified 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, as 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:

Run As User field	Consequence
{forcejobowner}@PS_JDBC_ORCL	If there is no credential with the username of the process submitter the process fails
{tryjobowner}	If there is no credential with the username of the process submitter the value of process server parameter DefaultRunAsUser is used. The process server where the process runs specifies the Endpoint.
{tryjobowner}@PS_JDBC_ORCL:SYSJCS	If there is no credential with the username of the process submitter, the SYSJCS credential is used

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();

Product Documentation

Using the JDBC Definition Type