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:
The connection automatically closes when the map finishes execution.
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.
The executeSQL() method sends the actual query to the relationship database for execution. This section covers execution of the following kinds of SQL queries:
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.
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");
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.
To access the returned data, perform the following steps:
Table 115 shows the methods in the DtpConnection class that provide access to the rows of returned query data.
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.
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()); }
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.
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)");
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.
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.
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.
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 |
As Table 117 shows, the choice of which method to use to call a stored procedure depends on:
An OUT parameter is a parameter through which the stored procedure returns a value to the calling code. If the stored procedure uses an OUT parameter, you must use execStoredProcedure() to call the stored procedure.
The execStoredProcedure() method precompiles the stored procedure. Therefore, if you call the same stored procedure more than once (for example, in a loop), use of execStoredProcedure() can be faster than executeSQL() because the relationship database can reuse the precompiled version.
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)");
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.
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:
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.
Parameter information | UserStoredProcedureParam method |
---|---|
Parameter name | getParamName(), setParamName() |
Parameter value | getParamValue(), setParamValue() |
Parameter data type: | |
getParamDataTypeJavaObj(), setParamDataTypeJavaObj() | |
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:
Use the UserStoredProcedureParam() constructor to create a new UserStoredProcedureParam object. To this constructor, pass the following parameter information to initialize the object:
Use the addElement() method of the Vector class to add the UserStoredProcedureParam object.
The execStoredProcedure() method sends the stored procedure and its parameters to the relationship database for execution.
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:
Therefore, you must initialize its associated UserStoredProcedureParam object with an in/out type of "IN", as well as with the appropriate data type, name, and the value to send into the stored procedure. Because emp_id is declared as the SQL NUMBER type (which holds an integer value), the parameter's data type and value must be of a Java Object that holds integer values: Integer.
For this parameter, create an empty UserStoredProcedureParam object to send into the stored procedure. You initialize this object with an in/out type of "OUT" as well as with the appropriate data type and name. However, you provide a dummy value for this parameter. Once the stored procedure completes execution, you can obtain the returned value from this OUT parameter with the getParamValue() method.
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(); }
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.
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.
Therefore, every UserStoredProcedureParam object contains two representations of its data type, as Table 121 shows.
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"
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.