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 <[email protected]> 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/
