Skip to content
CA API Gateway - 9.3
Documentation powered by DocOps

Perform JDBC Query Assertion

Last update November 29, 2017

The Perform JDBC Query assertion is used to query an external database and use the query results later. The query results are stored in context variables created by this assertion.

Tip: To support using multivalued context variables in the JDBC Query assertion, you can build up the values of such a variable using the Manipulate Multivalued Variable Assertion.

Before you can perform a JDBC query, be sure a JDBC connection has been configured through Manage JDBC Connections.

WARNING: Do not create connections to the Gateway's MySQL database in general. Any query which writes to this database may render the Gateway inoperable.

The Perform JDBC Query Assertion is able to write to a variety of databases, even during a "Test". Ensure you are aware of the changes you are making as they are irreversible through the Set JDBC Connection Properties.

Unsupported Functionality

Note the following functionality is not currently supported by the Perform JDBC Query assertion:

  • Functions and Procedures are not supported on DB2. 
  • Functions and Procedures are supported on MySQL only when the database name is provided in the JDBC connection URL. Only supported when using the native MySQL driver and not DataDirect. 
  • Functions and Procedures with nested function calls are not supported.
  • PL/SQL blocks are not supported.
  • Calling overloading procedures or functions is not supported. 
  • Calling functions on MySQL Enterprise Edition using the Data Direct MySQL driver is not supported. 
  • Calling functions on Oracle via the native driver is not supported. 
  • Calling functions that return a Boolean with the DataDirect driver is not supported. 
  • Procedures and functions with lowercase names are not supported in Oracle.
  • Functions and procedures in Oracle that return NCLOBs or NBLOBs with values greater than 32KB cannot be called from the CA API Gateway. 
  • The Boolean parameters BOOLEAN and BOOL are not supported for the native MySQL driver. 

Context Variables Created by This Assertion

The Perform JDBC Query assertion sets the following context variables with the query results. The default <prefix> is "jdbcQuery" and can be changed in the assertion properties.

Variable Description
<prefix>.<column_name> Returns the column name specified in the SQL query. This variable is created when one result set is returned.
<prefix>.resultSet1.<column_name> If a stored procedure returns multiple result sets, then "resultSet1...N" will be added to the name of the variable.
<prefix>.queryresult.count Returns the number of records returned by the query (if using a SELECT query) or the number of records affected by the query (if using a non-SELECT query). This variable is always created.
<prefix>.xmlResult Returns the XML results and is created when the Generate XML Results check box is selected in the properties.

<prefix>.multipleResultSet.count

This variable is set when there is more than one result set. This will occur when a called procedure returns more than one result set. This variable is not set if there is only a single result set.

Tip: If OUT parameters are also set, they will be counted as one result set.

<prefix>.multipleResultSet.queryresult.count

This variable is only set when there is more than one result set. If it is set, it contains the number of results in total across all result sets.

Tip: If OUT parameters are also set, these will count as one row.

<prefix>.<out_parameter> Returns the name of an OUT parameter from a procedure.

<prefix>.return

<prefix>.RETURN_VALUE

These variables are set by calling a function, with the name of the variable depending on the DBMS.

During policy consumption, the Gateway will create one multivalued context variable per column name. The number of values in the multivalued context variable corresponds to the number of records returned.

Tip: To use names other than the SQL column names in the context variables, specify a mapping in the assertion properties.

Understanding Result Set Variables and Multiple Result Sets 

The Perform JDBC Query assertion supports multiple result sets. When a SQL query is run (for example, "select * from my_table"), a "result set" is returned. This result set is a logical set of rows, with each row made up of a series of columns. When you (for example) select column_a and column_b from my_table, and there are 10 rows in my_table, the result set will contain 10 rows with each row having 2 columns.

When you call a procedure (for example, "CALL MY_PROC"), it is possible that the procedure may return more than one result set (uncommon but possible). When this happens, the <prefix>.multipleResultSet.count variable will be set to "2" if two result sets were returned. If the result set #1 has 10 rows and result set #2 has 5 rows, then the <prefix>.multipleResultSet.queryresult.count variable will be set to "15".

For example, consider a stored procedure that returns the following result sets: 

group id name value
set1 8 name8 test value8
set1 3 name3 test value3
group id name value
set2 1 extra1 test1
set2 4 extra4 test4
group id name value
set3 6 name6 test value6
set3 5 name5 test value5

These will be the context variables that will be returned (using the default prefix "jdbcQuery"):

${jdbcQuery.resultSet1.group} = set1,set1
${jdbcQuery.resultSet1.id} = 8,3
${jdbcQuery.resultSet1.name} = name8,name3
${jdbcQuery.resultSet1.value = test value8,test value3

${jdbcQuery.resultSet2.group} = set2,set2
${jdbcQuery.resultSet2.field1} = 1,4
${jdbcQuery.resultSet2.field2} = extra1,test1
${jdbcQuery.resultSet2.field3 = extra4,test4

${jdbcQuery.resultSet3.group} = set3,set3
${jdbcQuery.resultSet3.id} = 6,5
${jdbcQuery.resultSet3.name} = name6,name5
${jdbcQuery.resultSet3.value} = test value6,test value5

${jdbcQuery.multipleResultSet.count} = 3

Using the Assertion

  1. Do one of the following:
    • To add the assertion to the Policy Development window, see Adding an Assertion.
    • To change the configuration of an existing assertion, proceed to step 2 below.
  2. When adding the assertion, the JDBC Query Properties automatically appear; when modifying the assertion, right-click Perform JDBC Query in the policy window and select JDBC Query Properties or double-click the assertion in the policy window. The assertion properties are displayed. 

    Tip: Use the split bar between the "SQL Query" and "Context Variables Naming" panels to adjust the relative panels sizes to suit your needs.

  3. Configure the properties as follows:

    Setting Description
    JDBC Connection
    Choose one connection

    Choose the JDBC connection being queried from the drop-down list. If the connection isn't visible in the list, you may type the connection name in the box. You may reference context variables.

    Note: If context variables are specified in the connection name, it will not be possible to test the connection using the [Test] button.

    For information on defining these connections, see Manage JDBC Connections.

    SQL Query (see "SQL Query Tips" below for more information)
    Convert Variables to Strings

    This check box determines how context variables are processed before being sent to the JDBC driver:

    • Select this check box to convert context variable contents into a string. For multivalued variables, their contents are concatenated into a single value, with the values delimited by commas.
    • Clear this check box to add each value of a multivalue or single-value context variable as is. When the policy containing the Perform JDBC Query assertion is executed, the Gateway will construct a statement with the list of parameters per each value of the multivalued context variable. This setting is the default.
    Query Timeout

    Enter the length of time the assertion will wait (in seconds) for a response to the query before timing out.

    Default: 0 (zero, which means use the Gateway-wide timeout, described below)

    Tips: (1) The value entered here overrides the Gateway-wide timeout setting defined by the jdbcqueryManager.maxGatewayStatementTimeout cluster property. The intent is to supply a shorter timeout value than the Gateway default. If a longer timeout value is entered, it will be ignored and the Gateway default is used instead. (2) The JDBC driver may be configured to ignore all calls to set a timeout. If it is so configured, then it is not possible for the Gateway to control the timeout for any queries using that JDBC Connection. For more information, please consult your JDBC administrator. (3) The connection property EnableCancelTimeout set to "true" may be needed when using the DataDirect drivers, to ensure that cancel requests to an unresponsive DBMS do not wait indefinitely.

    SQL query box

    Enter the SQL query to perform. Be cautious about any usages of non-Select DML queries. There is no transaction management within the Perform JDBC Query assertion. Once this assertion executes, the results are permanent in the DBMS, regardless of the policy logic surrounding the assertion. You may replace values in the SQL query with context variables if necessary, but other parts of the query must remain clear text. For example:

    These are valid queries:

       SELECT column_name FROM table WHERE username = 'Bob' AND password = '123password'
       SELECT column_name FROM table WHERE username = ${request.user} AND password = ${request.password}

    These are NOT valid queries:

       SELECT ${columnName} FROM ${tableName} WHERE username = 'Bob' AND password = '123password'
       SELECT ${columnName} FROM ${tableName} WHERE username = ${request.user} AND password = ${request.password}

    Do not enclose context variables within quotes. This applies to both SELECT and INSERT statements.

    The maximum length of the query is 4 KB (4096 characters).

    For more information about SQL queries, refer to "SQL Query Tips" and "Examples Using Procedures and Functions" within this topic.

    [Test]

    Click [Test] to verify whether the SQL query is valid on the chosen JDBC connection. You must confirm that you understand the consequences of the query before proceeding. You will see a message stating whether or not the SQL query is valid.

    Note: Testing is not possible if context variables are used in either the connection name or SQL query.

    Specify Schema

    This check box is available only under the following conditions:

    • A procedure or function call is defined in the SQL query text box.
      AND 
    • The database is Oracle or SQL Server.

    Since the SQL query text box does not accept a schema value as part of the query, select this check box if you need to specify a schema and then enter the name of the schema in the adjacent field (must be a string without spaces or a single-value context variable).This value is passed to the JDBC driver to allow it to obtain the correct metadata from the database.

    If the SQL query requires a schema value for a procedure or function call, select this check box and then enter the name of the schema.You may reference context variables. If an object is contained within a package, then the SQL query itself should reference the package; for example:

    CALL mypackage.myfunction

    Tip: You may need to specify a schema if your query fails with this error message: "The database object either does not exist or the SQL query contains the object's schema".

    Context Variables Naming
    Save results to context variables

    This check box is located above the "Context Variables Naming" table. It is used to quickly enable or disable the saving of SQL results to the context variables specified in the table. Tip: CA recommends leaving this check box enabled. But consider disabling the saving of results if you are experiencing any memory issues.

    • Select this check box to operate the table normally: you can add, edit, or remove context variables and the SQL results will be saved to the variables specified.
    • Clear this check box to disable the saving of SQL results to context variables. This will disable the table and its editing controls. Any variable defined in the table will remain.

    Notes: (1) This check box operates independently of the Generate XML Result check box below. This allows you to populate the ${<prefix>.xmlResult} variable even when opting to not save results to context variables. (2) The following context variables are always created, regardless of the "Save results to context variables" check box: ${<prefix>.queryresult.count}, ${<prefix>.multipleResultSet.count}, and ${<prefix>.multipleResultSet.queryresult.count}. These variables were described under "Context Variables Created by This Assertion".

     table

    This table allows you to map the SQL column headings to different names. This will change the names of the context variables created. For example, you require more descriptive variable names or if you require the names to conform to naming standards at your organization. For more information, see "Context Variables Created by This Assertion".

    The assertion supports multiple result sets. For more information, see the variables under "Context Variables Created by This Assertion" and "Understanding Result Set Variables and Multiple Result Sets" above.

    To add a mapping:

    Click [Add]. The Context Variable Naming dialog appears.

    1. Enter the SQL Column Label. For example: "Column1".
    2. Enter the mapping destination in Variable Name. For example: "Cust_Acct".
    3. Click [OK]. This will create a context variable named ${<prefix>.Cust_Acct} instead of ${<prefix>.Column1}.

    To edit a mapping:

    1. Select a row and click [Edit].
    2. Modify the fields as necessary.
    3. Click [OK].

    To remove a mapping:

    1. Select a row and click [Remove].
    2. Click [Remove] to confirm. The naming will revert to the SQL column name.
    Prefix

    Enter a prefix that will be added to the context variables created by this assertion. This prefix will ensure uniqueness and will prevent the variables from overwriting each other when multiple instances of this assertion appear in a policy.

    You may reference context variables.

    The default prefix is jdbcQuery.

    For more information, see "Context Variables Created by This Assertion".

    Other Settings
    Query Name Optionally enter a name for the query. This name is used only for display purposes in the Policy Manager.
    Maximum records per query Specify the maximum number of records to be returned from the SQL query. The default is 10; this can be changed using the jdbcquery.maxRecords.defaultValue cluster property.
    Fail assertion if no results

    Select this check box if you want the assertion to fail if the SQL query returns no results.

    Clear this check box to prevent an assertion failure on no results. (The assertion can still fail for other reasons—for example, an invalid query.)

    Generate XML Result

    You can place the XML results of the JDBC query in a context variable.

    • Select this check box to store the XML string representation of every variable that was set, in the context variable ${<prefix>.xmlResult}.
    • Clear this check box to not place the XML result in the context variable. Note: If ${<prefix>.xmlResult} was populated previously, that content will remain.

    The following is an illustration of the structure of the XML result:

    <?xml version="1.0" encoding="UTF-8"?>
    <L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result">
       <L7j:row>
          <L7j:col name="COLNAME" type=
          "JAVA_DATA_TYPE">DATA_VALUE</L7j:col>
          ....
       </L7j:row>
       ....
    <L7j:jdbcQueryResult>

    Note: The type shown is the data type after the XML results have been retrieved, not the actual database field type.

  4.  Click [OK] when done.

SQL Query Tips

Keep in mind the following when entering an SQL query:

  • All variables created by the Perform JDBC Query assertion are multivalued. As such, it not possible to use array syntax when a suffix (i.e., selector) is present. Similarly, the variable "${jdbcQuery.return_value.millis}" will not work ("millis" can be any suffix). 
  • For a more detailed description, see "Multivalued Variables and Selectors" in Working with Multivalued Context Variables.
  • If a query requires a null value, use the context variable ${policy.nullvalue}. This built-in variable always resolves to null.

    Tip: For Oracle databases, null values are equivalent to the empty string for VARCHAR types. Thus, another way to pass null values in Oracle is to pass the empty string. For example, both of the following would result in the same, when nullFunction takes a VARCHAR parameter:

    func nullFunction ""
    func nullFunction ${policy.nullvalue}

  • If a query contains SQL functions such as AVG( ), MAX( ), MIN( ), or nested SELECT, you should use the "AS" keyword to alias the returned value; for example:

    SELECT  max(column_name) AS alias_name FROM table;
    SELECT column_name, (SELECT COUNT(*) FROM table1 WHERE conditions) AS alias_name FROM table2 WHERE conditions;
    
  • The SQL query cannot reference a schema. If you need to specify a schema value, select the Specify Schema check box and enter the value there. Note: The ability to specify a schema is available only for Oracle and SQL Server.

    WARNING: The Policy Manager does not prevent you from entering a destructive SQL query. Such a query may corrupt your database irrevocably, even during testing.

  • If you see an error message similar to the following:

    Query testing failed; Data truncation: Incorrect datetime value: <datetime_variable> for column 'date' at row 1

    Check that there are no quotes surrounding the variables being passed on in the query. The Gateway automatically includes quotes as it processes the JDBC query. (By comparison, if you enter values directly into a SQL client, quotes must be added manually where required.)

Converting Variables into Strings

When writing an SQL Query that will reference context variables, you need to decide whether to use the value converted to a string or to use the raw value. When the raw value is used, it will be passed directly to the JDBC driver, which will then convert it as needed, if the value is supported.

If the type of your variable is supported by the JDBC driver but you wish to use its string value while at the same time using the raw value of other variables, then you will need to create a new variable first to convert the raw variable into a string variable. For more information, see the "Convert Variables to String" option in the table above.

Using Functions and Stored Procedures

The query statement may contain calls to functions and stored procedures. The Gateway will determine what parameters a function or procedure require as input, output, or both, by examining the database metadata for it.

To call a stored procedure, use either the CALL OR EXEC keyword followed by the name of the procedure and then the parameters for the procedure. To call a function, use the FUNC keyword.

IMPORTANT: The procedure name must not contain a hyphen ('-'), otherwise errors will occur.

The procedure/function parameters can be supplied as literal values, single or multivalued context variables. These parameters can either be encoded within parentheses (for example, "CALL myproc (param1, param2,.....,paramN)") or without (for example, "CALL myproc param1, param2, ....., paramN").

The only parameters for a procedure that must be supplied are the IN or INOUT parameters. The Gateway will automatically handle correctly registering any OUT or INOUT parameters based on the metadata for the procedure.

Note: There is no method/syntax to bind a context variable in the 'SQL Query' text field with an OUT variable from a procedure (or any other SQL statement).

To call a function, use the FUNC keyword followed by the name of the function and its parameters. The same rules for how to supply parameters apply to functions.

The output of a procedure or function will be set automatically after it has been invoked. The following table lists the names of the output variable for each database when calling a function. 

Database Default variable name
MySQL (only with native driver) return
Oracle RETURN_VALUE
MS SQL Server RETURN_VALUE
DB2 (not supported) n/a

All OUT/INOUT parameters are handled automatically. When calling either a procedure or a function, consider the types of the input parameters. For types such as Date, Timestamp, and BLOB to work correctly, you need to ensure the context variable is off the correct type (Date/Time or byte []). Also ensure that the Perform JDBC Query assertion is configured to not convert variables to Strings. This allows the raw type to be passed the JDBC Driver, which can then provide any conversions it supports.

Note that when calling a nonexistent function or procedure, an exception is triggered only if the package name was also specified. For example, calling the nonexistent "mypackage.myfunction" will trigger an exception, but using "myfunction" will not trigger an exception. 

Note: For Oracle databases, the function and procedure names should be in uppercase and must not contain spaces or special characters. Exception: It is possible to call a function or procedure containing lowercase characters in its name provided that it was not created using quotes around the name.

Messages Returned for Application Users

When a user is connected to the database as an application user (not as the schema owner), the following messages will be returned when a valid stored procedure or function is called with no parameters:

Database Message returned
MySQL "query testing failed: [l7tech][MySQL JDBC Driver][MySQL]No database selected
Oracle "query testing failed: [l7tech][Oracle JDBC Driver][Oracle]ORA-06564: object <object name> does not exist ORA-06512: at "sys.dbms_utility", line 156 ORA-06512: at line 1
MS SQL Server "query testing failed: [l7tech][SQL Server JDBC Driver][SQLServer]Could not find stored procedure 'sp name'."
DB2 n/a (procedures and functions not currently supported in DB2)

Known Issues

Note the following known issues:

  • There is a known issue in MS SQL databases where the OUT parameter is treated as INOUT, which may result in parameters being set improperly. To avoid this, set all the parameters (IN and OUT) explicitly in the query. 
  • Stored procedures that use the OUT/INOUT parameter will always return a value "1" or greater for the variable ${jdbcQuery.queryresult.count}. This is because the parameters are always returned in the results and the assertion will never fail. However, the assertion is configured to fail if there are no results. 
  • Functions and procedures in Oracle that return NCLOBs or NBLOBs with values greater than 32KB cannot be called from the CA API Gateway. 

Examples Using Procedures and Functions

The following are some examples showing how to use keywords to execute functions and procedures. Tip: The name of the output variable from a function is determined by the database; "outParameter" shown below is just an example.

  • Using the FUNC keyword to execute functions:
    FUNC [package].[function]([IN parameters]...)
    Sets jdbcQuery.[<outParameter>]
     
  • Using the EXEC keyword to execute procedures: 
    EXEC [package].[procedure]([IN and INOUT parameters]...) 
  • Using the CALL keyword to execute procedures:
    CALL [package].[procedure]([IN and INOUT parameters]...)
    Sets jdbcQuery.[<outParameter>] etc.
     
  • The 'Specify Schema' text field:

    using multi-valued context variables
    FUNC [package].[function](${vars},${singleVar})

    is equivalent to

     FUNC [package].[function](${vars.1},${vars.2}....,${singleVar})

The following are examples of how to call functions and procedures including:

  • How to access out values from procedures
  • How to access one or more output result sets from a procedure
  • How to supply non primitive types as parameters

The examples below will use the following variables:

  • ${myvars} = "multivalue1", "multivalue2" - This multivalued variable contains two values.
  • ${myvar1} = "singlevalue1" - This variable contains a single value.
  • ${myvar2} = "singlevalue2" - This variable contains a single value.

Calling a function

Example function definition on Oracle, which takes two parameters and returns a varchar2 value:

CREATE or REPLACE FUNCTION MY_FUNC(a IN VARCHAR2, b IN VARCHAR2)RETURN VARCHAR2

After calling this function there will be a single output variable set. The name of the return variable depends on the DBMS.

In this example, if the prefix configured is 'jdbcQuery', then the output variable will be:

jdbcQuery.RETURN_VALUE

Call with a multivalued variable:

FUNC MY_FUNC(${myvars})

Call with single values:

FUNC MY_FUNC(${myvar1}, ${myvar2})

Call without parenthesis:

FUNC MY_FUNC ${myvars}
OR
FUNC MY_FUNC ${myvar1}, ${myvar2}

Call with literal values:

FUNC MY_FUNC "input1", 'input2'
OR
FUNC MY_FUNC ("input1", 'input2')

Calling a procedure

Example procedure definition on Oracle, which takes 3 parameters, two of which are OUT parameters.

CREATE or REPLACE PROCEDURE MY_PROC (a IN VARCHAR2, b INOUT VARCHAR2, c OUT VARCHAR2, d IN VARCHAR2)

After calling this function there will be two output variables set. The default values depend on the name of the OUT variables. If jdbcQuery is the prefix in use the following context variables will be set:

jdbcQuery.b
jdbcQuery.c

Tip: CALL and EXEC are interchangeable. Neither has any specific meaning; they both indicate that your SQL Query will call a procedure equally.

Call with a multivalued variable and a literal value:

CALL MY_PROC (${myvars}, "d value")

Call with a multivalued variable and a single variable:

EXEC MY_PROC (${myvars}, ${myvar1})

After all variables are processed, the number of values must match the number of expected input parameters. The position of the OUT parameters do not matter. After each context variable has been evaluated, the number of values resolved must match the number of input parameters. The values will be applied based on the order they were resolved.

In the above example the procedure would be called with the following runtime values:

multivalue1, multivalue2, singlevalue1

Call with single value variables and a literal value:

CALL MY_PROC (${myvar1}, ${myvar2}, 'd value')

As with functions the parenthesis around the parameters is optional.

Using date type parameters

Example function definition on Oracle:

create or replace FUNCTION DATE_FUNC (param1 IN DATE) RETURN DATE

Invoke the function with a literal string date value:

func DATE_FUNC '2012-12-31 23:55:40.99'

Invoke the function using a Date/Time typed context variable:

Set Context Variable date as Date/Timefunc DATE_FUNC ${date}

Invoke the function sing a String typed context variable:

${dateStr} = "2012-12-31 23:55:40.99"func DATE_FUNC ${dateStr}

If the [Convert Variables to String] check box is selected, then the Date/Time variable will be converted into a string using the default formatting for a Date/Time variable. In order to work with the format required by your DBMS, you may need to explicitly format the Date/Time when referencing it in a function:

Set Context Variable date as Date/Time
func DATE_FUNC ${date.yyyy-MM-dd HH:mm:ss.SS}

In the above example the Date/Time is actually converted into a String when it is resolved at runtime. This is only needed when "Convert Variables to Strings' is being used.

Using numeric types

Example function definition on Oracle:

create or replace FUNCTION NUMBER_FUNC (param1 IN NUMBER) RETURN NUMBER

Invoke the function with a literal numeric value:

func NUMBER_FUNC 1243

Invoke the function using an Integer typed context variable:

Set Context Variable integer as Integer to 5func NUMBER_FUNC ${integer}

Invoke the function using a String typed context variable:

${integerStr} = "12345"func NUMBER_FUNC ${integerStr}

Using Boolean values

Example function definition on SQL Server:

CREATE or ALTER FUNCTION BOOL_FUNC( @a BIT, @b BIT)

Invoke the function with a literal boolean value:

func BOOL_FUNC 'false', 'true'orfunc BOOL_FUNC 0, 1orfunc BOOL_FUNC '0', '1'

Invoke the function with an Integer typed context variable:

Set Context Variable a as Integer to 0Set Context Variable b as Integer to 1func BOOL_FUNC ${a}, ${b}

Invoke the function with a String typed context variable

${falseStr} = "false"${trueStr} = "true"func BOOL_FUNC ${falseStr},${trueStr}or${aStr} = "0"${bStr} = "1"func BOOL_FUNC ${aStr},${bStr}

Using byte[ ] and BLOB values

Example function definition:

create or replace function BLOB_FUNC (a in BLOB) return BLOB

Invoke using a literal hexadecimal string:

func BLOB_FUNC '0123456789abcdef'

Invoke using a String typed context variable:

${hexString} = "0123456789abcdef"func BLOB_FUNC ${hexString}

Invoke using a String value converted into hex using the 'Encode / Decode' assertion:

Base16 Encode ${myvar1} into ${hexString}func BLOB_FUNC ${hexString}

Invoke using a byte[] context variable created via an Encapsulated Assertion:

Output a byte[] variable called ${bytes} from an encapsulated assertionfunc BLOB_FUNC ${bytes}

Using null values

It is possible to supply a null value in a SQL Query via a special built in variable. It is also possible to supply a null value via any existing variable or multivalued variable that may contain a null value.

The predefined variable ${policy.nullvalue}supports passing null values into SQL queries. This variable will only pass a null value into a SQL query when the [Convert Variables to Strings] check box is not selected.

Example function definition

create or replace function NULL_FUNC (a in NUMBER, b in VARCHAR2) return VARCHAR2

Invoke a function using a literal null value:

func NULL_FUNC null 'asdf'

Invoke a function using a context variable with a null value:

func BOOL_FUNC ${policy.nullvalue}, 'b value'

Supported Data Types

These DBMS types are supported via DML statements via the 'SQL Query' text box. When calling procedures or functions they are supported as either input or output values.

DBMS type Context variable type
Char, Varchar, etc String
Numeric (Integer, Long, etc.) Integer or String
BLOB String with hex values or a byte [] (not settable via the set context variable)
CLOB String
Date Date/Time
Timestamp Date/Time
Boolean String or Integer when supported by the DBMS (see "Boolean values" below)

A String value can be used to supply a value for many DBMS types—for example, all character types including CLOB, Numeric types, Date and Timestamps (if formatted correctly) and Blob (via hex strings).

Boolean values

The following are support for Boolean values for each database type:

DBMS Support
Oracle Not supported by either the Data Direct driver or the native driver
MySQL

0 = false, everything else = true

Note: Boolean does not work with MySQL with the native driver.

SQL Server 0 or "false" = false, 1 or "true" = true
DB2 No boolean type

Date values

It is possible to supply the value for a DATE or TIMESTAMP using either literal values, String context variables, or Date/Time context variables (recommended for easiest integration).

The formatting required for a literal string value which represents a date or timestamp is determined by a number of factors, including DBMS settings and connection properties. The following default formats are known to work:

  • Oracle:yyyy-mm-dd hh:mm:ss.fffffffff (for example, '1999-01-31 24:24:24:123456')
  • Other DBMS:yyyy-mm-dd hh:mm:ss (for example, '1999-01-31 24:24:24')

BLOB values

To supply a BLOB value in SQL Query, either a context variable or a literal value may be used. You can also supply binary data as hexidecimal strings either via a String context variable or via a literal hex value (for example, "0123456789abcdef"). 

Known Oracle issues

  • Function and procedure names must be in uppercase
  • Binary_Float, Binary_Double, Binary_Integer, Pls_Integer are returned as strings
  • Functions which return NCLOB values greater than 32kB are not supported

Caching Metadata

This section provides some insight on how the Perform JDBC Query assertion queries the database for metadata via the JDBC driver.

Function and Procedure metadata are cached. Caching can be performed:

  • eagerly, via a background caching task
  • lazily, when metadata is downloaded at message traffic processing time (MTPT)
Notes: (1) When caching is configured and no data is available in the cache, then the metadata will be downloaded by the message processing thread. (2) When a nonexistent function or procedure is referenced, an exception will be issued and cached only if a package name has been specified. For example, calling the nonexistent function "mypackage.myfunction" will trigger an exception. But calling the nonexistent "myfunction" will not trigger an exception.

The caching metadata is enabled by the jdbcQueryManager.cacheMetaData.enable cluster property set to "true" (default). Note that this property does not affect existing cached data and does not prevent background tasks from caching metadata.

For more information on the caching cluster properties, see JDBC Cluster Properties.

Background Caching of Metadata

The Gateway has a background task that downloads metadata eagerly, to ensure that it will be available at message traffic processing time. This eager caching will occur when the Gateway starts or when a policy is saved and activated; it is possible only when no context variables are used for the JDBC connection name or for the schema.

The caching of data is based on tracking a set of unique keys. Each key is defined as:

Connection name + Procedure or Function name + Optional Schema name

Therefore, if two Perform JDBC Query assertions reference the same procedure or function, only a single copy of that metadata will be kept. This background task runs every 10 minutes by default and is enabled or disabled via the jdbcQueryManager.cacheMetaDataTask.enable cluster property. The task interval is controlled by the jdbcQueryManager.cacheRefreshInterval cluster property.

To improve the processing time of this background task, you can increase the number of background processing threads when a large volume of metadata is being managed. The number of processing threads is 10 by default but can be increased up to 200 via the jdbcQueryManager.minCacheConcurrency cluster property. Note: Increase the concurrent threads with caution.

When the background task is not able to obtain the metadata for a unique key, an exception is cached. When a policy containing a Perform JDBC Query assertion for this cache key executes, it will return this cached exception (for example, the assertion may fail) repeatedly until it is cleared out by the cleanup background task, or it is corrected by the background task downloading the metadata.

The cleanup cache task runs every minute by default and is controlled by the jdbcQueryManager.cacheCleanUpInterval cluster property.

Life cycle of a managed key

After the background task starts to manage a unique key, at some point it may need to stop managing it. This can happen when no Perform JDBC Query assertion is referencing the unique key, however this may be difficult to track when context variables are used (for example, the unique key being referenced is unknown until context variables are resolved during runtime).

To resolve this problem, the Gateway can track how often the data from the cache for a particular key is used. If it is over the threshold, then the background task will stop managing that meta data.

The value is defined in the jdbcQueryManager.cacheKeyNoUsageExpiration cluster property. The default is 31 days and the value is configured in seconds.

Automatic Lazy Caching

If the background task is not enabled but caching is allowed, then all metadata downloaded will be cached to avoid it being downloaded a second time. The unique key for this metadata will be added to the list of keys to manage.

Cache Expiration

Any cached item can be configured to expire, to prevent outdated meta from causing the Perform JDBC Query assertion to fail.

The expiration should be longer than the background task refresh interval. It should also be longer than the estimated time it takes the job to complete.

Cache expiration is controlled via jdbcQueryManager.cacheStaleTimeout cluster property. The default is 30 minutes and is configured in seconds.

Manually Populating the Cache

If caching is enabled, you can manually invoke it by clicking the [Test] button in the Perform JDBC Query assertion. This will cause metadata to be downloaded and cached, if not already present in the cache.

Cache Logging

Items which indicate that background tasks are running or working are logged at a FINE level. Items which indicate that the cache is working at message traffic processing time are logged at the FINEST level.

FINE logging is used for:

  • When metadata is downloaded and added to the cache.
  • When metadata could not be downloaded and an exception is added to the cache.
  • When the task to maintain metadata in the background starts and when it finishes.
  • When a unique key representing a procedure to manage metadata for is removed from the cache by the clean up task.

FINEST logging is used for:

  • Metadata cache hit
  • Metadata cache hit but data has expired (it is stale)
  • Metadata cache miss

Was this helpful?

Please log in to post comments.