Executing queries in the relationship database

As you use relationships, you may need to obtain information about a relationship definition. The relationship information is stored in special tables in the relationship database. To obtain information about a relationship, you can query its relationship tables. A query is a request, usually in the form of an SQL (Structured Query Language) statement, that you send to the database for execution.

To execute queries in the relationship database:

  1. Open a connection to the relationship database to obtain a DtpConnection object.
  2. Through the DtpConnection object, execute queries and manage transactions in the relationship database.

The connection automatically closes when the map finishes execution.

Important:
Using the DtpConnection class and its methods to establish a connection to a relationship database is supported for backward compatibility only. These deprecated methods will not generate errors, but you should avoid using them and migrate existing code to the new methods. The deprecated methods might be removed in a future release. In new map development, use the CwDBStoredProcedureParam class and its methods to obtain a database connection and execute SQL queries. For more information, see Executing database queries.

Opening a connection

To be able to query the relationship database, you must first open a connection to this database with the getRelConnection() method of the BaseDLM class. To identify the relationship database to open, specify the name of the relationship definition you want to query. The repository keeps track of the location of the relationship tables for each relationship definition. For more information, see Advanced settings for relationship definitions.

Example: The following call to getRelConnection() opens the relationship database that contains the relationship tables for the SapCust relationship:

DtpConnection connection = getRelConnection("SapCust");

This call returns a DtpConnection object in the connection variable, which you can then use to access the relationship database.

Executing the query

The executeSQL() method sends the actual query to the relationship database for execution. This section covers execution of the following kinds of SQL queries:

Queries that return data (SELECT)

The SQL statement SELECT queries one or more tables for data. To send a SELECT statement to the relationship database for execution, specify a string representation of the SELECT as an argument to the executeSQL() method.

Note:
The DtpConnection.executeSQL() methods is supported for backward compatibility only. Do not use this method in the development of new code; instead, use the executeSQL() method of the CwDBConnection class.

Example: The following call to executeSQL() sends a SELECT of one column value from the RelRT_T table:

connection.executeSQL(
   "select data from RelRT_T where INSTANCEID = 2");

Note:
In the preceding code, the connection variable is a DtpConnection object obtained from a previous call to the getRelConnection() method.

You can also send a SELECT statement that has parameters in it by using the second form of the executeSQL() method.

Example: The following call to executeSQL() performs the same task as the previous example except that it passes the instance ID as a parameter to the SELECT statement:

Vector argValues = new Vector();

String instance_id = "2";
argValues.addElement( instance_id );
connection.executeSQL(
   "select data from RelRT_T where INSTANCEID = ?", argValues);

The SELECT statement returns data from the relationship tables as rows. Each row is one row from the specified relationship table that matches the conditions in the SELECT. Each row contains the values for the columns that the SELECT statement specified. You can visualize the returned data as a two-dimensional array of these rows and columns.

Tip:
The syntax of the SELECT statement must be valid to the particular relationship database you are accessing. Consult your database documentation for the exact syntax of the SELECT statement.

To access the returned data, perform the following steps:

  1. Obtain one row of data.
  2. Obtain column values, one by one.

Table 115 shows the methods in the DtpConnection class that provide access to the rows of returned query data.

Table 115. DtpConnection methods for row access
Row-Access task DtpConnection method
Check for existence of a row. hasMoreRows()
Obtain one row of data. nextRow()

Control the loop through the returned rows with the hasMoreRows() method. End the row loop when hasMoreRows() returns false. To obtain one row of data, use the nextRow() method. This method returns the selected column values as elements in a Java Vector object. You can then use the Enumeration class to access the column values individually. Both the Vector and Enumeration classes are in the java.util package. See Table 71 for the Java methods for accessing the columns of a returned query row.

Note:
The mechanism for accessing rows from the query result is the same for the deprecated DtpConnection class as for its replacement, the CwDBConnection class. For more information, see Executing static queries that return data (SELECT).

Example: The following code sample gets an instance of the DtpConnection class, which is a connection to the relationship database that stores the sampleRelationshipName relationship definition. It then executes a SELECT statement that returns only one column with a single string value of "CrossWorlds":

Vector theRow = null;
Enumeration theRowEnum = null;
String theColumn1 = null;
DtpConnection connectn = null;

try 
   {
   connectn = getRelConnection("sampleRelationshipName");
   }

catch(DtpConnectionException e) 
   {
   System.out.println(e.getMessage());
   }

// Test for a resulting single column, single row, result set
// specified condition
try 
   {
   connectn.executeSQL(
      "select data from RelRT_T where INSTANCEID = 2");

   // Loop through each row
   while(connectn.hasMoreRows())
      {
      theRow = connectn.nextRow();
      int length = 0;
      if ((length = theRow.size())!= 1)
         {
         return "Expected result set size = 1," + 
            " Actual result state size = " + length;
         }

      // Get each column
      theRowEnum = theRow.elements();
      if(theRowEnum.hasMoreElements())
         {
         // Get the value
         theColumn1 = (String)theRowEnum.nextElement();
         if(theColumn1.equals("CrossWorlds")==false)
            {
            return "Expected result = CrossWorlds,"
               + " Resulting result = " + theColumn1;
            }
         }
      }
   }

catch(DtpConnectionException e)
   {
   System.out.println(e.getMessage());
   }

Note:
The SELECT statement does not modify the contents of the relationship database. Therefore, you do not usually need to perform transaction management for SELECT statements.

Queries that modify the relationship tables

SQL statements that modify a relationship table include the following:

To send one of these statements to the relationship database for execution, specify a string representation of the statement as an argument to the executeSQL() method.

Note:
The DtpConnection.executeSQL() methods is supported for backward compatibility only. Do not use this method in the development of new code; instead, use the executeSQL() method of the CwDBConnection class.

Example: The following call to executeSQL() sends an INSERT of one row into the RelRT_T table:

connection.executeSQL("insert into RelRT_T values 
(1, 3, 6)");

Note:
In the preceding code, the connection variable is a DtpConnection object obtained from a previous call to the getRelConnection() method.

For an UPDATE or INSERT statement, you can determine the number of rows in the relationship table that have been modified or added with the getUpdateCount() method.

Because the INSERT, UPDATE, and DELETE statements modify the contents of the relationship database, you must perform transaction management for these statements. A transaction is a set of operational steps that execute as a unit. All SQL statements that execute within a transaction succeed or fail as a unit. Table 116 shows the methods in the DtpConnection class that provide transaction support for SQL queries.

Table 116. DtpConnection methods for transaction management
Transaction-Management task DtpConnection method
Begin a new transaction. beginTran()
End the transaction, committing (saving) all changes made during the transaction to the database. commit()
Determine if a transaction is currently active. inTransaction()
End the transaction, rolling back (backing out) all changes made during the transaction. rollBack()

To mark the beginning of a transaction in the relationship database, use the beginTran() method. Execute all SQL statements that must succeed or fail as a unit between this call to beginTran() and the end of the transaction. You can end the transaction in either of two ways:

You can choose what conditions cause a transaction to fail. Test the condition and call rollBack() if any failure condition is met. Otherwise, call commit() to end the transaction successfully.

DtpConnection connection = getRelConnection("SapCust");

// begin a transaction
connection.beginTran();

// insert a row
connection.executeSQL("insert...");

// commit the transaction
connection.commit();

// release the database connection
releaseRelConnection(true);

To determine whether a transaction is currently active, use the inTransaction() method.

Queries that call stored procedures

A stored procedure is a user-defined procedure that contains SQL statements and conditional logic. Stored procedures are stored in a database. When you create a new relationship, Relationship Designer Express creates a stored procedure to maintain each relationship table.

Table 117 shows the methods in the DtpConnection class that call a stored procedure. These methods are supported for backward compatibility only. Do not use these methods in the development of new code; instead, use the executeSQL() and executeStoredProcedure() methods of the CwDBConnection class.

Table 117. DtpConnection methods for Calling a Stored Procedure
How to call the stored procedure DtpConnection method Use
Send a CALL statement that executes the stored procedure to the relationship database. executeSQL() To call a stored procedure that does not have OUT parameters
Specify the name of the stored procedure and an array of its parameters to create a procedure call, which is sent to the relationship database for execution. execStoredProcedure() To call any stored procedure, including one with OUT parameters
Note:
You can use JDBC methods to execute a stored procedure directly. However, the interface that the Mapping API provides is simpler and it reuses database resources, which can increase the efficiency of execution. You should use the Mapping API to execute stored procedures.

As Table 117 shows, the choice of which method to use to call a stored procedure depends on:

The following sections describe how to use the executeSQL() and execStoredProcedure() methods to call a stored procedure.

Calling stored procedures with executeSQL()

To call a stored procedure with the executeSQL() method, specify as an argument to the executeSQL() method a string representation of the CALL statement that includes the stored procedure and any arguments.

Example: The following call to executeSQL() sends a CALL statement to execute the setOrderCurrDate() stored procedure:

connection.executeSQL("call setOrderCurrDate(345698)");

Note:
In the preceding code, the connection variable is a DtpConnection object obtained from a previous call to the getRelConnection() method.

You can execute the setOrderCurrDate() stored procedure because its single argument is an IN parameter; that is, the value is only sent into the stored procedure. The stored procedure does not have any OUT parameters.

Note:
You can use the form of executeSQL() that accepts a parameter array to pass in parameter values. However, you cannot use executeSQL() to call a stored procedure that uses an OUT parameter. To execute such a stored procedure, you must use execStoredProcedure().

Use an anonymous PL/SQL block if you plan on calling Oracle stored PL/SQL objects via ODBC using the DtpConnection executeSQL method. The following is an acceptable format (the stored procedure name is myproc):

executeSQL("begin myproc(...); end;");
Calling stored procedures with execStoredProcedure()

To call a stored procedure with the execStoredProcedure() method, you:

  1. Specify the name of the stored procedure to execute as a string.
  2. Build a Vector parameter array of UserStoredProcedureParam objects, which provide parameter information (such as the name, type, and value of each parameter).

A parameter is a value you can send into or out of the stored procedure. The parameter's in/out type determines how the stored procedure uses the parameter value:

A UserStoredProcedureParam object describes a single parameter for a stored procedure. Table 118 shows the parameter information that a UserStoredProcedureParam object contains as well as the methods to retrieve and set this parameter information.

Table 118. Parameter information in a UserStoredProcedureParam Object
Parameter information UserStoredProcedureParam method
Parameter name getParamName(), setParamName()
Parameter value getParamValue(), setParamValue()
Parameter data type:
  • As a Java Object
getParamDataTypeJavaObj(), setParamDataTypeJavaObj()
  • As a JDBC data type
getParamDataTypeJDBC(), setParamDataTypeJDBC()
Parameter in/out type getParamIOType(), setParamIOType()
Parameter index position getParamIndex(), setParamIndex()

Steps for passing parameters to a stored procedure: To pass parameters to a stored procedure, perform the following steps:

  1. Create a UserStoredProcedureParam object to hold the parameter information.

    Use the UserStoredProcedureParam() constructor to create a new UserStoredProcedureParam object. To this constructor, pass the following parameter information to initialize the object:

  2. Repeat step 1 for each stored-procedure parameter.
  3. Create a Vector object with enough elements to hold all stored-procedure parameters.
  4. Add the initialized UserStoredProcedureParam object to the parameter Vector object.

    Use the addElement() method of the Vector class to add the UserStoredProcedureParam object.

  5. Once you have created all UserStoredProcedureParam objects and added them to the Vector parameter array, pass this parameter array as the second argument to the execStoredProcedure() method.

    The execStoredProcedure() method sends the stored procedure and its parameters to the relationship database for execution.

Note:
The first argument to execStoredProcedure() is the name of the stored procedure to execute.

Example: Suppose you have the get_empno() stored procedure defined as follows:

create or replace procedure get_empno(emp_id IN number,
      emp_number OUT number) as
   begin
      select emp_no into emp_number
      from emp
      where emp_id = 1;
   end;

This stored procedure has two parameters:

Example: The following example executes the get_empno() stored procedure with the execStoredProcedure() method:

DtpConnection connectn = null;
try 
   {
   // Get database connection
   connectn = getRelConnection("Customer");

   // Create parameter Vector
   Vector paramData = new Vector(2);

   // Construct the procedure name
   String sProcName = "get_empno";

   // Create IN parameter
   UserStoredProcedureParam arg_in = new UserStoredProcedureParam(
      1, "Integer", new Integer(6), "IN", "arg_in");

   // Create dummy argument for OUT parameter
   UserStoredProcedureParam arg_out = new UserStoredProcedureParam(
      2, "Integer", new Integer(0), "OUT", "arg_out");

   // Add these two parameters to the parameter Vector
   paramData.addElement(arg_in);
   paramData.addElement(arg_out);

   // Run get_empno() stored procedure
   connectn.execStoredProcedure(sProcName, paramData);

   // Get the result from the OUT parameter
   arg_out = (UserStoredProcedureParam) paramData.elementAt(1);
   Integer emp_number = (Integer) arg_out.getParamValue();
   }

Tip:
The Vector object is a zero-based array while the UserStoredProcedureParam-objects are indexed as a one-based array. In the preceding code, the OUT parameter is created with an index value of 2 in the UserStoredProcedureParam() constructor because this parameter array is one-based. However, to access the value for this OUT parameter from the Vector parameter array, the elementAt() call specifies an index value of 1 because this Vector array is zero-based.

A stored procedure processes its parameters as SQL data types. Because SQL and Java data types are not identical, the Mapping API must convert a parameter value between these two data types. For an IN parameter, the Mapping API converts the parameter value from a Java Object to its SQL data type. For an OUT parameter, the Mapping API converts the parameter value from its SQL data type to a Java Object. The Mapping API converts a parameter value between these two data types using two layers of data type mapping:

The Mapping API uses the JDBC data type internally to hold the parameter value sent to and from the stored procedure. JDBC defines a set of generic SQL type identifiers in the java.sql.Types class. These types represent the most commonly used SQL types. JDBC also provides standard mapping from JDBC types to Java data types. For example, a JDBC INTEGER is normally mapped to a Java int type.

To map an IN (or INOUT) parameter from a Java object to its JDBC equivalent, the Mapping API uses the mappings in Table 119.

Table 119. Mappings from Java object to JDBC data type equivalent
From Java object To JDBC data type
String CHAR, VARCHAR, or LONGVARCHAR
java.math.BigDecimal NUMERIC
Boolean BIT
Integer INTEGER
Long BIGINT
Float REAL
Double DOUBLE
byte[] BINARY, VARBINARY, or LONGVARBINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
Clob CLOB
Blob BLOB
Array ARRAY
Struct STRUCT
Ref REF

To map an OUT (or INOUT) parameter from a JDBC data type to its Java object equivalent, the Mapping API uses the mappings in Table 120.

Table 120. Mappings from JDBC data type to Java object
From JDBC data type To Java object
CHAR, VARCHAR, LONGVARCHAR String
NUMERIC, DECIMAL java.math.BigDecimal
BIT Boolean
TINYINT Integer
SMALLINT Integer
INTEGER Integer
BIGINT Long
REAL Float
FLOAT, DOUBLE Double
BINARY, VARBINARY, or LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
CLOB Clob
BLOB Blob
ARRAY Array
STRUCT Struct
REF Ref

Therefore, every UserStoredProcedureParam object contains two representations of its data type, as Table 121 shows.

Table 121. Parameter data types
Parameter data type Description UserStoredProcedureParam method
The Java Object Data type that the map-transformation code uses to hold the parameter value getParamDataTypeJavaObj(), setParamDataTypeJavaObj()
The JDBC data type Data type that the Mapping API uses internally to hold the parameter value getParamDataTypeJDBC(), setParamDataTypeJDBC()

You can use the UserStoredProcedureParam methods in Table 121 to access either form of the parameter data type. However, you should use the Java Object data type (such as Integer, String, or Float) for the following reasons:

The emp_id parameter of get_empno() is declared with the SQL data type of NUMBER, which contains an integer value. Therefore, in the code example that starts on ***, the call to UserStoredProcedureParam() for the emp_id parameter (parameter with the index position of 1) sets its value to 6 with its third argument of:

new Integer(6)

This call also sets the parameter type to the same Java Object type with its second argument of:

"Integer"

Closing a connection

The connection to the relationship database is released when the map is finished executing. If the map executes successfully, all transactions are automatically committed if they are not already explicitly committed. If the map execution fails (for instance, if an exception is thrown that is not handled with a catch statement), all transactions are rolled back if they are not already explicitly rolled back.

Copyright IBM Corp. 2004, 2005