An SDO client can supply the JDBC Data Mediator Service (DMS) with a SELECT statement to replace the statement that is generated from the DMS metadata.
When the SDO client instantiates a DMS, the DMS uses the defining metadata to generate a basic SELECT statement. Substituting that query gives you the ability to specify parameter markers; therefore you have more control over the client data that populates a dataGraph. Use a standard SQL SELECT string for a client-supplied query.
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.
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.
DataObject parameters = mediator.getParameterDataObject(); parameter.setString(0, "NY"); parameter.setInt(1, 12345); DataObject graph = mediator.getGraph(parameters);
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.
The JDBC DMS generated 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.