You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Ken Katsma <ke...@gmail.com> on 2005/01/15 20:01:19 UTC
Oracle function result sets with iBatis
I think iBatis is a great product, my group was looking to build
something that extracted our sql into xml files anyway, and when I found
iBatis, I was pretty happy to see all that work has already been done.
One thing it can't seem to do right now though is support Oracle result
sets from functions. If there is a way to do so without modifying the
code let me know. I went in and modified SQLExecutor to support the
functionality I need. This is a really bad hack, and it would be great
if we could specify our own SQLExecutor in the xml file to implement
database specific code like this in a more elegant fashion. But this
works, and all you have to do is modify the SQLExecutor like this:
public void executeQueryProcedure(RequestScope request, Connection
conn, String sql, Object[] parameters,
int skipResults, int maxResults,
RowHandlerCallback callback)
throws SQLException {
ErrorContext errorContext = request.getErrorContext();
errorContext.setActivity("executing query procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
ResultSet rs = null;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation
failed).");
cs = conn.prepareCall(sql);
ParameterMap parameterMap = request.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext.setMoreInfo("Check the output parameters (register
output parameters failed).");
registerOutputParameters(cs, mappings);
errorContext.setMoreInfo("Check the parameters (set parameters
failed).");
parameterMap.setParameters(request, cs, parameters);
errorContext.setMoreInfo("Check the statement (update procedure
failed).");
if
(conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
{
boolean b = cs.execute();
errorContext.setMoreInfo("In Oracle query mode.");
errorContext.setMoreInfo("Check the output parameters
(retrieval of output parameters failed).");
retrieveOutputParameters(cs, mappings, parameters);
for (int i=0;i<parameters.length;i++)
{
if (parameters[i] instanceof ResultSet)
{
rs = (ResultSet) parameters[i];
break;
}
}
errorContext.setMoreInfo("Check the results (failed to
retrieve results).");
handleResults(request, rs, skipResults, maxResults, callback);
}
else
{
errorContext.setMoreInfo("In non-Oracle mode.");
rs = cs.executeQuery();
errorContext.setMoreInfo("Check the results (failed to retrieve
results).");
handleResults(request, rs, skipResults, maxResults, callback);
errorContext.setMoreInfo("Check the output parameters (retrieval
of output parameters failed).");
retrieveOutputParameters(cs, mappings, parameters);
}
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(cs);
}
}
If you stick this above the base iBatis libraries, you can now get the
results out.
No big deal in the xml file:
<typeAlias alias="Client" type="com.test.vo.ClientVO" />
<resultMap id="clientResult" class="Client">
<result property="clntSeq" columnIndex="1" />
<result property="clntName" columnIndex="2" />
<result property="clntSubType" columnIndex="3" />
<result property="clntCode" columnIndex="4"/>
<result property="clntMailName" columnIndex="5" />
<result property="clntLOBCode" columnIndex="6" />
<result property="clntCompanyID" columnIndex="7" />
<result property="clntCostCenter" columnIndex="8" />
</resultMap>
<parameterMap id="clientParameters" class="map" >
<parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
<parameter property="maxRows" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
</parameterMap>
<procedure id="getClientListProc" resultMap="clientResult"
parameterMap="clientParameters">
{?= call abc.CLIENT_VIEW_PKG.client_result_list_f(?)}
</procedure>
Hope nobody get's offended by the ugliness of this hack, but we really
need the functionality as all queries are sp's in my project (no choice).
Thanks,
Ken
Re: Oracle function result sets with iBatis
Posted by Clinton Begin <cl...@gmail.com>.
Could you submit this in JIRA so it doesn't get lost?
Thanks
Clinton
On Sun, 16 Jan 2005 00:04:08 -0600, Ken Katsma <ke...@gmail.com> wrote:
> Sure, see my comments through the following....
>
> I just added a check to see if the database was oracle
>
> if
> > (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> > {
>
> And if it is, then don't do a query, do an execute....
>
> > boolean b = cs.execute();
> >
> > errorContext.setMoreInfo("In Oracle query mode.");
> > errorContext.setMoreInfo("Check the output parameters
> > (retrieval of output parameters failed).");
>
> Grab the output parameters, and find the one that's a resultset.
>
> > retrieveOutputParameters(cs, mappings, parameters);
> >
> > for (int i=0;i<parameters.length;i++)
> > {
> > if (parameters[i] instanceof ResultSet)
> > {
> > rs = (ResultSet) parameters[i];
> > break;
> > }
> > }
> >
>
> Then perform handle results...
>
> > errorContext.setMoreInfo("Check the results (failed to
> > retrieve results).");
> > handleResults(request, rs, skipResults, maxResults, callback);
> > }
>
> Otherwise, just do the traditional executeQuery.
>
> > else
> > {
> > errorContext.setMoreInfo("In non-Oracle mode.");
> > rs = cs.executeQuery();
> >
> > errorContext.setMoreInfo("Check the results (failed to retrieve
> > results).");
> > handleResults(request, rs, skipResults, maxResults, callback);
> >
> > errorContext.setMoreInfo("Check the output parameters (retrieval
> > of output parameters failed).");
> > retrieveOutputParameters(cs, mappings, parameters);
> > }
> > } finally {
> > try {
> > closeResultSet(rs);
> > } finally {
> > closeStatement(cs);
> > }
> > }
>
> If you need anything more let me know, thanks.
>
> Clinton Begin wrote:
>
> >Could you explain more clearly what you changed?
> >
> >Clinton
> >
> >
> >
> >
>
Re: Oracle function result sets with iBatis
Posted by Ken Katsma <ke...@gmail.com>.
Sure, see my comments through the following....
I just added a check to see if the database was oracle
if
> (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
And if it is, then don't do a query, do an execute....
> boolean b = cs.execute();
>
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters
> (retrieval of output parameters failed).");
Grab the output parameters, and find the one that's a resultset.
> retrieveOutputParameters(cs, mappings, parameters);
>
> for (int i=0;i<parameters.length;i++)
> {
> if (parameters[i] instanceof ResultSet)
> {
> rs = (ResultSet) parameters[i];
> break;
> }
> }
>
Then perform handle results...
> errorContext.setMoreInfo("Check the results (failed to
> retrieve results).");
> handleResults(request, rs, skipResults, maxResults, callback);
> }
Otherwise, just do the traditional executeQuery.
> else
> {
> errorContext.setMoreInfo("In non-Oracle mode.");
> rs = cs.executeQuery();
>
> errorContext.setMoreInfo("Check the results (failed to retrieve
> results).");
> handleResults(request, rs, skipResults, maxResults, callback);
>
> errorContext.setMoreInfo("Check the output parameters (retrieval
> of output parameters failed).");
> retrieveOutputParameters(cs, mappings, parameters);
> }
> } finally {
> try {
> closeResultSet(rs);
> } finally {
> closeStatement(cs);
> }
> }
If you need anything more let me know, thanks.
Clinton Begin wrote:
>Could you explain more clearly what you changed?
>
>Clinton
>
>
>
>
Re: Oracle function result sets with iBatis
Posted by Clinton Begin <cl...@gmail.com>.
Could you explain more clearly what you changed?
Clinton
On Sat, 15 Jan 2005 13:01:19 -0600, Ken Katsma <ke...@gmail.com> wrote:
> I think iBatis is a great product, my group was looking to build
> something that extracted our sql into xml files anyway, and when I found
> iBatis, I was pretty happy to see all that work has already been done.
>
> One thing it can't seem to do right now though is support Oracle result
> sets from functions. If there is a way to do so without modifying the
> code let me know. I went in and modified SQLExecutor to support the
> functionality I need. This is a really bad hack, and it would be great
> if we could specify our own SQLExecutor in the xml file to implement
> database specific code like this in a more elegant fashion. But this
> works, and all you have to do is modify the SQLExecutor like this:
>
> public void executeQueryProcedure(RequestScope request, Connection
> conn, String sql, Object[] parameters,
> int skipResults, int maxResults,
> RowHandlerCallback callback)
> throws SQLException {
> ErrorContext errorContext = request.getErrorContext();
> errorContext.setActivity("executing query procedure");
> errorContext.setObjectId(sql);
>
> CallableStatement cs = null;
> ResultSet rs = null;
>
> try {
> errorContext.setMoreInfo("Check the SQL Statement (preparation
> failed).");
> cs = conn.prepareCall(sql);
>
> ParameterMap parameterMap = request.getParameterMap();
>
> ParameterMapping[] mappings = parameterMap.getParameterMappings();
>
> errorContext.setMoreInfo("Check the output parameters (register
> output parameters failed).");
> registerOutputParameters(cs, mappings);
>
> errorContext.setMoreInfo("Check the parameters (set parameters
> failed).");
> parameterMap.setParameters(request, cs, parameters);
>
> errorContext.setMoreInfo("Check the statement (update procedure
> failed).");
>
> if
> (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> boolean b = cs.execute();
>
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters
> (retrieval of output parameters failed).");
> retrieveOutputParameters(cs, mappings, parameters);
>
> for (int i=0;i<parameters.length;i++)
> {
> if (parameters[i] instanceof ResultSet)
> {
> rs = (ResultSet) parameters[i];
> break;
> }
> }
>
> errorContext.setMoreInfo("Check the results (failed to
> retrieve results).");
> handleResults(request, rs, skipResults, maxResults, callback);
> }
> else
> {
> errorContext.setMoreInfo("In non-Oracle mode.");
> rs = cs.executeQuery();
>
> errorContext.setMoreInfo("Check the results (failed to retrieve
> results).");
> handleResults(request, rs, skipResults, maxResults, callback);
>
> errorContext.setMoreInfo("Check the output parameters (retrieval
> of output parameters failed).");
> retrieveOutputParameters(cs, mappings, parameters);
> }
> } finally {
> try {
> closeResultSet(rs);
> } finally {
> closeStatement(cs);
> }
> }
>
> If you stick this above the base iBatis libraries, you can now get the
> results out.
> No big deal in the xml file:
>
> <typeAlias alias="Client" type="com.test.vo.ClientVO" />
>
> <resultMap id="clientResult" class="Client">
> <result property="clntSeq" columnIndex="1" />
> <result property="clntName" columnIndex="2" />
> <result property="clntSubType" columnIndex="3" />
> <result property="clntCode" columnIndex="4"/>
> <result property="clntMailName" columnIndex="5" />
> <result property="clntLOBCode" columnIndex="6" />
> <result property="clntCompanyID" columnIndex="7" />
> <result property="clntCostCenter" columnIndex="8" />
> </resultMap>
>
> <parameterMap id="clientParameters" class="map" >
> <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
> <parameter property="maxRows" jdbcType="VARCHAR"
> javaType="java.lang.String" mode="IN"/>
> </parameterMap>
>
> <procedure id="getClientListProc" resultMap="clientResult"
> parameterMap="clientParameters">
> {?= call abc.CLIENT_VIEW_PKG.client_result_list_f(?)}
> </procedure>
>
> Hope nobody get's offended by the ugliness of this hack, but we really
> need the functionality as all queries are sp's in my project (no choice).
>
> Thanks,
>
> Ken
>
>
>