Dear Masters,
I found DAS 1.0 beta2 does not support SP execution well. Once the
stored procedure returns multiple resultsets which including temp table
results (on SYBASE 12.5), DAS can not work properly to make those resultset
into SDO.
I have tried to make improvements on that issue
-----------------------------------------------------
Added the executeCallWithMetadataReturned function in class Statement
public GraphBuilderMetadata
executeCallWithMetadataReturned(ParametersExtendedImpl parameters,Config
config)
throws SQLException {
CallableStatement cs = jdbcConnection.prepareCall(queryString);
Iterator inParams = parameters.getInParameters().iterator();
while (inParams.hasNext()) {
ParameterExtendedImpl param = (ParameterExtendedImpl) inParams
.next();
cs.setObject(param.getIndex(), param.getValue());
}
// register out parameters
Iterator outParams = parameters.getOutParameters().iterator();
while (outParams.hasNext()) {
ParameterExtendedImpl param = (ParameterExtendedImpl) outParams
.next();
if (this.logger.isDebugEnabled()) {
this.logger.debug("Registering parameter " + param.getName());
}
cs.registerOutParameter(param.getIndex(), SDODataTypeHelper
.sqlTypeFor(param.getType()));
}
// Using execute because Derby does not currenlty support
// executeQuery
// for SP
cs.executeQuery();
List results = new ArrayList();
CachedRowSetImpl crs = new CachedRowSetImpl();
ResultSet rs = cs.getResultSet();
crs.populate(cs.getResultSet());
results.add(crs);
GraphBuilderMetadata gbmd=new GraphBuilderMetadata(results,config,null);
while (true) {
try {
if (cs.getMoreResults(java.sql.Statement.CLOSE_CURRENT_RESULT)) {
ResultSet rs1 = cs.getResultSet();
try {
CachedRowSetImpl crs3 = new CachedRowSetImpl();
if (!""
.equals(rs1.getMetaData().getTableName(1)
.trim())) {
crs3.populate(rs1);
//results.add(crs3);
gbmd.addMetadata(crs3,config,null);
}
} catch (Exception e) {
// TODO: Exception Handling
}
} else
break;
} catch (SQLException e) {
// TODO: Exception Handling
break;
}
}
Iterator i = parameters.getOutParameters().iterator();
while (i.hasNext()) {
ParameterExtendedImpl param = (ParameterExtendedImpl) i.next();
param.setValue(cs.getObject(param.getIndex()));
}
return gbmd;
}
-----------------------------------------------
Added executeQueryWithConfig(Config config) in SPCommandImpl
public DataObject executeQueryWithConfig(Config config) {
boolean success = false;
try {
GraphBuilderMetadata gbmd =
statement.executeCallWithMetadataReturned(parameters,config);
success = true;
return buildGraph(gbmd);
} catch (SQLException e) {
if (this.logger.isDebugEnabled()) {
this.logger.debug(e);
}
throw new RuntimeException(e);
} finally {
if (success) {
statement.getConnection().cleanUp();
} else {
statement.getConnection().errorCleanUp();
}
}
}
-------------------------------------------------------
Added buildGraph(GraphBuilderMetadata gbmd) in SPCommandImpl
/*
* build Graph with given GraphBuilderMetadata
* Author:Mike *Rao*
* Date:2009-4-2
*/
*protected* DataObject buildGraph(GraphBuilderMetadata gbmd)
*throws*SQLException {
// Create the DataGraph
DataGraph g = SDOUtil.*createDataGraph*();
// Create the root object
g.*createRootObject*(gbmd.getRootType());
SDOUtil.*registerDataGraphTypes*(g, gbmd.getDefinedTypes());
ChangeSummary summary = g.getChangeSummary();
ResultSetProcessor rsp = *new* ResultSetProcessor(g.getRootObject(),
gbmd);
rsp.processResults(getStartRow(), getEndRow());
summary.beginLogging();
*return* g.getRootObject();
}
----------------------------
Added addMetadata(ResultSet rs, Config model, ResultSetShape shape) in
GraphBuilderMetadata
/**
* Author:Mike Rao
* Target:support buildGraph one by one when SP returns results with
TEMP TABLE RESULTSET
* Date:2009-4-2
* */
public void addMetadata(ResultSet rs, Config model, ResultSetShape
shape){
ResultMetadata resultMetadata;
try {
resultMetadata = new ResultMetadata(rs, configWrapper, shape);
resultSets.add(resultMetadata);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
-----------------------------------
Then the DAO class would be like this
public DataObject queryCustomer(DAS das,Config config){
Command cmd=das.getCommand("QRY_CUSTOMER");
DataObject root=cmd.executeQueryWithConfig(config);
return root;
}
-----------------------------------
one constrain:
SP can not put temp table resultset as the last resultset returned, if so ,
users should place SQL like "SELECT 1 AS RESULTCODE" (or something else
looks like) behind the last resultset(temp table)
------------------------------------
The above code would support temp table resultsets during SP calling.
It is just a try on that kind of case,I just wanna share my thoughts; if
there are some better solution, I'd like to know it. And I hope DAS 1.0
final would coming out soon.
:)
Mike Rao
April, 07, 2009
