You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tuscany.apache.org by Rao Zhenlin <ra...@gmail.com> on 2009/04/07 10:16:22 UTC
Suggestion:Make DAS better support SP execution with "Mulit. temp
table Resultset" returned
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
Re: Suggestion:Make DAS better support SP execution with "Mulit. temp
table Resultset" returned
Posted by Luciano Resende <lu...@gmail.com>.
Thanks Rao
Would you mind creating a JIRA and providing a diff patch so I can
try incorporating your changes to the current DAS trunk ?
Please let me know if you need any help creating the diff.
On Tue, Apr 7, 2009 at 1:16 AM, Rao Zhenlin <ra...@gmail.com> wrote:
> 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
>
>
>
>
>
>
>
>
--
Luciano Resende
Apache Tuscany, Apache PhotArk
http://people.apache.org/~lresende
http://lresende.blogspot.com/