JDBC mediator supplied query

Although the JDBC Data Mediator Service (DMS) generates a SELECT statement from the metadata provided at the creation of an instance, the DMS also enables the client to provide a specific SELECT statement to be used instead of the generated one. The provided statement is a standard structured query language (SQL) SELECT string and can contain parameter markers. Using supplied queries gives you more control over the data used to populate a DataGraph. With both supplied queries and generated queries, UPDATE, INSERT, and DELETE statements are automatically generated for each DataObject. They are applied when the mediator commits the changes made to the DataGraph back to the database.

 

Parameter DataObjects for supplied queries

Clients can use a parameter DataObject to supply arguments to an SQL SELECT query. A parameter DataObject is a DataObject, but is not part of any DataGraph. It is constructed by the JDBC DMS when requested by the client. The ParameterDataObject for supplied queries is created based on the query given to the mediator. Every parameter in the query is given a name like arg0, arg1, …, argX.

Because a parameter DataObject is a DataObject, one can set its properties using either the property name or an index value. The properties can be referenced by their argX name, or by the number associated with that parameter, 0, 1, … , X. For example, your query is “SELECT CUSTFIRSTNAME WHERE CUSTSTATE = ? AND CUSTZIP = ?” . This supplied query contains two parameters. The first parameter corresponds with CUSTSTATE and can be set using the string “arg0” or the index 0. The second parameter corresponds with CUSTZIP and can be set using the string “arg1” or the index 1. Here is sample code of how they are set. This code assumes that you have already set up the metadata and mediator with the metadata and the aforementioned supplied query. Using the index value method, you code

DataObject parameters = mediator.getParameterDataObject();
parameter.setString(0, "NY");
parameter.setInt(1, 12345);
DataObject graph = mediator.getGraph(parameters);

Using the property name method, you code

DataObject parameters = mediator.getParameterDataObject();
parameters.setString("arg0", "NY");
parameters.setInt("arg1", 12345);
DataObject graph = mediator.getGraph(parameters);

The results are the same for both cases.

 

Limitations

The JDBC DMS supplied SQL SELECT query is not fully supported on Oracle or Informix. This is because the mediator takes advantage of the ResultSetMetaData interface in JDBC 2.0 and requires it to be fully implemented. Oracle, Informix, DB2/390, and older supported versions of Sybase do not implement the ResultSetMetaData interface completely. The supplied select approach can still be used with these databases with one limitation: column names in the Metadata must be unique across all tables. An InvalidMetadataException occurs if the select statement returns a column with a name that appears multiple times in the metadata. For instance, if the Customer and the Order tables both contain a column named “ID”, this would be invalid and cause problems. The way to fix this is to change the name of at least one of the matching columns in the database to better distinguish the two columns from each other. For the Customer table, the column name could be changed to “CUSTID,” as it is in the examples. The Order column name could be changed to “ORDERID”. If you change the Customer column name, you do not have to change the Order column name, but for consistency it may be a good idea.

---

 

---