You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Ken Katsma (JIRA)" <ib...@incubator.apache.org> on 2005/01/19 02:31:17 UTC
[jira] Created: (IBATIS-53) Support for oracle cursors as resultsets
Support for oracle cursors as resultsets
----------------------------------------
Key: IBATIS-53
URL: http://issues.apache.org/jira/browse/IBATIS-53
Project: iBatis for Java
Type: New Feature
Components: SQL Maps
Reporter: Ken Katsma
Priority: Minor
iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
The code change is in SQLExecutor.executeQueryProcedure (see comments):
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).");
// ****************************************
// Code changes below
// ****************************************
if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
{
// If in oracle then execute instead of executeQuery
boolean b = cs.execute();
errorContext.setMoreInfo("In Oracle query mode.");
errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
// Get the output parameters first, instead of last
retrieveOutputParameters(cs, mappings, parameters);
// Then find the resultset and handle it
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);
}
//****************************************
// Non-oracle..original code
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);
}
}
An example mapping looks like:
<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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by Ken Katsma <ke...@gmail.com>.
Thanks for the notice Clinton. As long as I can say that the
functionality will be added in a future release, I think people will
accept my work-around for the interim. Again, thanks for communicating.
Ken
Clinton Begin wrote:
>Once we change to using execute() vs. executeQuery() (and
>executeUpdate() for consistency), it should become easy to use a
>custom type handler to deal with ref cursors.
>
>Unfortuntately, there's no ETA on this. Larry said he'd start on it
>soon, but he's currently in Brazil (maybe speaking to the Brazillian
>JUG as I type this!).
>
>I'm sorry to anyone who has to deal with Oracle ref cursors.
>
>IIRC there are other ways to handle this. I seem to recall others who
>have wrapped their procs in an outer procedure that returns a result
>set. Maybe even a single proc that can wrap and execute any other
>proc.
>
>Cheers,
>Clinton
>
>
>
>
>On Thu, 24 Mar 2005 13:28:27 +0100 (CET), Fabio Grassi (JIRA)
><ib...@incubator.apache.org> wrote:
>
>
>> [ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_61482 ]
>>
>>Fabio Grassi commented on IBATIS-53:
>>------------------------------------
>>
>>This issue is critical for the adoption of SqlMaps in my organization (Assicurazioni Generali). We access the RDBMS (Oracle) exclusively through stored procedures. Support for Oracle ref cursors both as function result and as out parameter is fundamental. Thanks in advance, Fabio.
>>
>>
>>
>>>Support for oracle cursors as resultsets
>>>----------------------------------------
>>>
>>> Key: IBATIS-53
>>> URL: http://issues.apache.org/jira/browse/IBATIS-53
>>> Project: iBatis for Java
>>> Type: New Feature
>>> Components: SQL Maps
>>> Reporter: Ken Katsma
>>> Priority: Minor
>>>
>>>
>>>iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
>>>The code change is in SQLExecutor.executeQueryProcedure (see comments):
>>> 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).");
>>> // ****************************************
>>> // Code changes below
>>> // ****************************************
>>> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
>>> {
>>> // If in oracle then execute instead of executeQuery
>>> boolean b = cs.execute();
>>> errorContext.setMoreInfo("In Oracle query mode.");
>>> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
>>> // Get the output parameters first, instead of last
>>> retrieveOutputParameters(cs, mappings, parameters);
>>> // Then find the resultset and handle it
>>> 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);
>>> }
>>> //****************************************
>>> // Non-oracle..original code
>>> 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);
>>> }
>>> }
>>>An example mapping looks like:
>>> <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>
>>>
>>>
>>--
>>This message is automatically generated by JIRA.
>>-
>>If you think it was sent incorrectly contact one of the administrators:
>> http://issues.apache.org/jira/secure/Administrators.jspa
>>-
>>If you want more information on JIRA, or have a bug to report see:
>> http://www.atlassian.com/software/jira
>>
>>
>>
>>
>
>
>
Re: [jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by Clinton Begin <cl...@gmail.com>.
Once we change to using execute() vs. executeQuery() (and
executeUpdate() for consistency), it should become easy to use a
custom type handler to deal with ref cursors.
Unfortuntately, there's no ETA on this. Larry said he'd start on it
soon, but he's currently in Brazil (maybe speaking to the Brazillian
JUG as I type this!).
I'm sorry to anyone who has to deal with Oracle ref cursors.
IIRC there are other ways to handle this. I seem to recall others who
have wrapped their procs in an outer procedure that returns a result
set. Maybe even a single proc that can wrap and execute any other
proc.
Cheers,
Clinton
On Thu, 24 Mar 2005 13:28:27 +0100 (CET), Fabio Grassi (JIRA)
<ib...@incubator.apache.org> wrote:
> [ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_61482 ]
>
> Fabio Grassi commented on IBATIS-53:
> ------------------------------------
>
> This issue is critical for the adoption of SqlMaps in my organization (Assicurazioni Generali). We access the RDBMS (Oracle) exclusively through stored procedures. Support for Oracle ref cursors both as function result and as out parameter is fundamental. Thanks in advance, Fabio.
>
> > Support for oracle cursors as resultsets
> > ----------------------------------------
> >
> > Key: IBATIS-53
> > URL: http://issues.apache.org/jira/browse/IBATIS-53
> > Project: iBatis for Java
> > Type: New Feature
> > Components: SQL Maps
> > Reporter: Ken Katsma
> > Priority: Minor
>
> >
> > iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> > The code change is in SQLExecutor.executeQueryProcedure (see comments):
> > 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).");
> > // ****************************************
> > // Code changes below
> > // ****************************************
> > if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> > {
> > // If in oracle then execute instead of executeQuery
> > boolean b = cs.execute();
> > errorContext.setMoreInfo("In Oracle query mode.");
> > errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> > // Get the output parameters first, instead of last
> > retrieveOutputParameters(cs, mappings, parameters);
> > // Then find the resultset and handle it
> > 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);
> > }
> > //****************************************
> > // Non-oracle..original code
> > 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);
> > }
> > }
> > An example mapping looks like:
> > <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>
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
> http://issues.apache.org/jira/secure/Administrators.jspa
> -
> If you want more information on JIRA, or have a bug to report see:
> http://www.atlassian.com/software/jira
>
>
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Sven Boden (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=all ]
Sven Boden updated IBATIS-53:
-----------------------------
Attachment: showcase.txt
First a remark on IBATIS-53. Are ResultSets really required for Ibatis, I thought the whole idea of iBatis was to be a mapping layer, hiding some JDBC stuff... Returning a ResultSet is not going to help in that.
Then again the attached example is a showcase that returns a nested Oracle cursor from iBatis. I don't know whether this is the best solution, but it works in iBatis 2.1.0 for me. Does this mean IBATIS-53 can be closed?
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Brandon Goodin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=all ]
Brandon Goodin closed IBATIS-53:
--------------------------------
Fix Version: 2.1.0
Resolution: Fixed
I believe this is all resolved now. We have not had any comments since we changed from executeQuery to execute.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Fix For: 2.1.0
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Sven Boden (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=all ]
Sven Boden updated IBATIS-53:
-----------------------------
Attachment: showcase_storedprocedure1.txt
A new showscase for ResultSets attached to IBATIS-53 (after an example of Graham) now showing how to retrieve ResultSets as OUT parameters, as in:
<parameterMap id="parameters" class="map" >
<parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
<parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>
<parameter property="output2" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>
</parameterMap>
<procedure id="GetEmpRs" parameterMap="parameters">
{ call scott.example.GetEmpRS(?, ?, ?) }
</procedure>
I think this now covers all IBATIS-53 requests. So far (as attached showcases):
- return ResultSet as return value from sql
- return ResultSet as return value from stored procedure
- return different ResultSet as OUT parameters.
So is there any reason to keep IBATIS-53 open with iBatis 2.1.0 using custom type handlers to retrieve ResultSets? Maybe a ref cursor type handler could be included in a next version of iBatis, like the clob handler.
Regards,
Sven
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as
resultsets
Posted by "Michael Fagan (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_12365453 ]
Michael Fagan commented on IBATIS-53:
-------------------------------------
A sample solution for this issue can be find at: http://opensource2.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions
The modified IBATIS jars are listed as attachments.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Fix For: 2.1.0
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "GrahamCruickshanks (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_58687 ]
GrahamCruickshanks commented on IBATIS-53:
------------------------------------------
This is fix is key for using IBatis in our organization.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Michael Fagan (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=history ]
Michael Fagan updated IBATIS-53:
--------------------------------
Attachment: SqlExecutor.java
Here is my version of the ref cursor fix.
This fix has been running stable since 1/20/05.
NOTE it does not support more than one resultset from a proc/function
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Fabio Grassi (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=history ]
Fabio Grassi updated IBATIS-53:
-------------------------------
Attachment: SqlExecutor.java
I tested and slightly modified my modification proposal. Now it allows to use the first out parameter of type ref cursor as a normal result set (remapped by the usual resultMap).
Although not very elegant, I think it covers 80% of everyday needs. Let me know what you think.
Ciao, Fabio.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Michael Fagan (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_57838 ]
Michael Fagan commented on IBATIS-53:
-------------------------------------
Ken, Thanks for posting that fix for handling refcursors. I will implement that fix in my version now with one change:
I am replacing the check for an oracle driver with a check for an REF output parameter.
This will allow the original logic to be called for 'standard' procs.
This modification will hold me over until this feature makes it back in the baseline.
Mike Fagan
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "David Winterbottom (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_12313497 ]
David Winterbottom commented on IBATIS-53:
------------------------------------------
Running the suplied code (number 6) with log4j-1.2.9 on the class path gives me a NullPointerException.
Am I doing something wrong ?
(see IBATIS-152)
DEBUG [main] - Created connection 3235124.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement: { ? = call mis_extract.mgr_case_temp.FindCases }
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - Returned connection 3235124 to pool.
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/kinnect/mis/datamodel/sql/CaseToExtract.xml.
--- The error occurred while applying a parameter map.
--- Check the output.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:184)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:100)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:97)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:69)
at com.kinnect.mis.MISExtract.readCasesToExtract(MISExtract.java:233)
at com.kinnect.mis.MISExtract.main(MISExtract.java:64)
Caused by:
java.lang.NullPointerException
at java.lang.reflect.Method.invoke(Native Method)
at com.ibatis.common.jdbc.logging.ResultSetLogProxy.invoke(ResultSetLogProxy.java:47)
at $Proxy2.close(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.closeResultSet(SqlExecutor.java:392)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:298)
at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:169)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:100)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:97)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:69)
at com.kinnect.mis.MISExtract.readCasesToExtract(MISExtract.java:233)
at com.kinnect.mis.MISExtract.main(MISExtract.java:64)
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Fix For: 2.1.0
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Fabio Grassi (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_61482 ]
Fabio Grassi commented on IBATIS-53:
------------------------------------
This issue is critical for the adoption of SqlMaps in my organization (Assicurazioni Generali). We access the RDBMS (Oracle) exclusively through stored procedures. Support for Oracle ref cursors both as function result and as out parameter is fundamental. Thanks in advance, Fabio.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Sven Boden (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=all ]
Sven Boden updated IBATIS-53:
-----------------------------
Attachment: showcase_storedprocedure.txt
Ken,
New showcase attached to IBATIS-53, this time using a stored procedure combined with an unchanged iBatis 2.1.0 ... works in the same way as the previous example:
In the example I use
<procedure id="getdeptemp" parameterMap="output">
{ ? = call scott.example.my_procedure }
</procedure>
where my_procedure is a stored procedure returning a ref cursor. You can retrieve the ResultSet from the first parameter (outgoing).
Sven
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "GrahamCruickshanks (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_60716 ]
GrahamCruickshanks commented on IBATIS-53:
------------------------------------------
It's correct that the OUT parameter returns the ResultsSet and this is returned via cs.execute.
That patch list above causes a null point though if you are returning a OUT parameter that is not a ResultSet.
My Hack that means a MAP put into IBATIS will have the OUT parameter added to it.
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).");
// ****************************************
// Code changes below
// ****************************************
if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
{
// If in oracle then execute instead of executeQuery
boolean b = cs.execute();
errorContext.setMoreInfo("In Oracle query mode.");
errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
// Get the output parameters first, instead of last
retrieveOutputParameters(cs, mappings, parameters);
// Then find the resultset and handle it
for (int i=0;i<parameters.length;i++)
{
if (parameters[i] instanceof ResultSet)
{
rs = (ResultSet) parameters[i];
break;
}
}
if(rs != null ) {
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
handleResults(request, rs, skipResults, maxResults, callback);
}
}
//****************************************
// Non-oracle..original code
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);
}
}
}
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Paul Mathai Mathew (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_62710 ]
Paul Mathai Mathew commented on IBATIS-53:
------------------------------------------
Clinton / Brandon,
I've been evaluating iBatis for use in my organization.
The one major issue stopping my organization from accepting iBatis is the lack of support for Oracle REF CURSORs.
At the same time, my organization is willing to put in resources to get this issue fixed and contribute the same back to ASF.
Could you please guide me on how I could get started? (Access to SVN, etc.)
Regards,
Paul.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "GrahamCruickshanks (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_64163 ]
GrahamCruickshanks commented on IBATIS-53:
------------------------------------------
Is there any News on when this issue is going to be address?
Sorry to bother the development team about this. But it would be good to know when.
Thanks
Graham
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as
resultsets
Posted by "Mohamed Arif (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_12365398 ]
Mohamed Arif commented on IBATIS-53:
------------------------------------
Hi,
I am new to Ibatis and trying Ibatis SQL Map for replacing Spring DAO.
Can any one let me know, is the Bug "IBATIS-53" resolved in any other version of Ibatis ? If yes, pls let me know the version no and where it can be downloaded.
I am trying Ibatis SQL using spring, and
As suggested by Ken, I made the code change in SQLExecutor.executeQueryProcedure, but still getting the following exception,
--- The error occurred in Account.xml.
--- The error occurred while applying a parameter map.
--- Check the Account.accountSPInput.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.lang.NullPointerException; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in Account.xml.
--- The error occurred while applying a parameter map.
--- Check the Account.accountSPInput.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.lang.NullPointerException], value class [java.lang.String], SQL type 12
[DEBUG] 2006-02-07 11:29:46,899 jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
Below are the configuration files and other related files,
Spring Configuration XML :
<bean id="AccountDAO" class="com.rsa.rcas.common.dao.ibatisImpl.IbatisAccountDAOImpl">
<property name="dataSource"><ref local="RCASDataSource"/></property>
<property name="sqlMapClient"><ref local="sqlMapClient"/></property>
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>ibatisConfig.xml</value>
</property>
</bean>
<!--Defines the Transaction Manager-->
<bean id="TransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="RCASDataSource"></ref>
</property>
</bean>
<!--Data Source-->
<bean id="RCASDataSource" class="org.springframework.jndi.JndiObjectFactoryBean" lazy-init="true">
<property name="jndiName">
<value>RCASDev</value>
</property>
</bean>
Ibatis Configuration XML:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<sqlMap resource="Account.xml"/>
</sqlMapConfig>
Account.xml :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="Account">
<typeAlias alias="account" type="com.rsa.rcas.common.dto.Account"/>
<typeAlias alias="accountDeviceDetail" type="com.rsa.rcas.common.dto.AccountDeviceDetail"/>
<resultMap id="accountResult" class="accountDeviceDetail">
<result property="accountId" column="ACC_ID" />
<result property="status" column="ACC_STA" />
<result property="deviceID" column="D_ID" />
<result property="deviceStatus" column="D_STA" />
<result property="createdDate" column="CRT_DAT" />
<result property="nickName" column="NK_NA" />
<result property="authenticatorId" column="AUT_ID" />
<result property="authenticatorStatus" column="AUT_STA" />
<result property="expiryDate" column="EXP_DATE" />
<result property="last4DigitsSerNum" column="LTFR_DIGIT_SN" />
<result property="privateData" column="PT_DATA" />
<result property="serialNumber" column="SL_NUM" />
</resultMap>
<parameterMap id="accountSPInput" class="map" >
<parameter property="PA_IN_ACCOUNTID" jdbcType="long" javaType="java.lang.Long" mode="IN"/>
<parameter property="PA_IN_SITEID" jdbcType="long" javaType="java.lang.Long" mode="IN"/>
<parameter property="PA_OUT_RETCURSOR" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>
</parameterMap>
<procedure id="getAccountDetail" parameterMap="accountSPInput" resultMap="accountResult">
{ call PR_RS_GET_ACC_DETAILS (?, ?, ?) }
</procedure>
</sqlMap>
My Stored Procedure :
CREATE OR REPLACE PROCEDURE PR_RS_GET_ACC_DETAILS (
PA_IN_ACCOUNTID IN NUMBER,
PA_IN_SITEID IN NUMBER,
PA_OUT_RETCURSOR OUT RS_GENERAL.REFCURSOR
)
IS
/BEGIN
OPEN PA_OUT_RETCURSOR FOR
SELECT ACC.ACCOUNT_ID AS ACCOUNT_ID,
ACC.STATUS AS ACC_STA,
DEV.D_ID AS D_ID,
DEV.STA AS D_STA,
DEV.CRT_DATE AS CRT_DATE,
DEV.NK_NA AS NK_NA,
AUT.AUT_ID AS AUT_ID,
AUT.STA AS AUT_STA,
AUT.EXP_DATE AS EXP_DATE,
AUT.LT_FRDIGIT_SN AS LTFR_DIGIT_SN,
AUT.PT_DATA AS PT_DATA,
AUT.SL_NUM
FROM RS_ANT_MST AC,
RS_DE_MST DEV,
RS_AUT_MST AUT
WHERE ACC.ACC_ID = DEV.ACC_ID
AND AUT.AUT_ID = DEV.AUT_ID
AND DEV.ACC_ID = PA_IN_ACCOUNTID
AND ACC.SITE_ID = PA_IN_SITEID;
END PR_RS_GET_ACC_DETAILS;
/
Kindly, just me what changes to be done and if any latest Ibatis jar to be used.
Currently, i am using "iBATIS_DBL-2.1.7.597.zip" downloaded from ibatis site.
Regards,
RF
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Fix For: 2.1.0
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Brandon Goodin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_60696 ]
Brandon Goodin commented on IBATIS-53:
--------------------------------------
Let me see if i understand this correctly...
A cursor must be retrieved using a CallableStatement/Stored Procedure. The Callable Statement returns a ResultSet as an OUT parameter that you want to map to objects as normal with ibatis. Your current issue is that you need to cs.execute instead of cs.executeQuery because you do no expect a resultset directly back.
Is that correct?
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Michael Fagan (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=history ]
Michael Fagan updated IBATIS-53:
--------------------------------
Attachment: SqlExecutor.java
Here is my version of the ref cursor fix.
This fix has been running stable since 1/20/05.
NOTE it does not support more than one resultset from a proc/function
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Michael Fagan (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_62380 ]
Michael Fagan commented on IBATIS-53:
-------------------------------------
I would agree your solution is better and more maintainable. Probably not a edit I would feel comfortable making due to my limited exposure to the code base. I would be willing to test such a the patch against my baseline.
Wrapping procs with function(s) is possible if you have control of the schema. Using a generic function to wrap multiple procs will not work until the DTD allows procedures to have the remapResults attribute. See issue IBATIS-101.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Paul Brooks (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_59705 ]
Paul Brooks commented on IBATIS-53:
-----------------------------------
Not sure if this is able to be done within the same issue, but it would be excellent if we were able to specify a result map for specific parameters.
A bit like:
<parameterMap id="params" class="map">
<parameter property="result"
jdbcType="ORACLECURSOR"
mode="OUT"
resultMap="results"
typeHandler="TestTypeHandler"/>
<parameter property="pr_gptn"
jdbcType="STRUCT"
mode="IN"
typeHandler="TestTypeHandler"/>
</parameterMap>
<resultMap id="results" class="ProductConfigNote">
...
</resultMap>
This would enable the returning and automatic mapping of multiple ref cursors as OUT parameters.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Reopened: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Brandon Goodin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=all ]
Brandon Goodin reopened IBATIS-53:
----------------------------------
Since there is still dialog on this issue we will reopen it for future consideration.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Fix For: 2.1.0
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Fabio Grassi (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=history ]
Fabio Grassi updated IBATIS-53:
-------------------------------
Attachment: SqlExecutor.java
Here is my little first contibution to this issue. The patch attached should allow the extraction of the *first* returned result set regardless of the target RDBMS (i.e. no forking of the code base for Oracle needed). I will not have the time to test it for today, I submit it for your comments.
The main problem still remains: even if extracting result sets from output parameter is easy, I think it a new 'parameterMap' attribute should be added to the 'parameter' element (dtd modification?). I still don't have a clear vision of the code base.
Best regards, Fabio.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Ken Katsma (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_61402 ]
Ken Katsma commented on IBATIS-53:
----------------------------------
Brandon,
I'm not sure you received my earlier response on this, but yes, your statement is correct.
Do you think this functionality might be integrated in a near future release? I've got a meeting with 23 development managers and enterprise architects tomorrow and they'll probably be asking me about this feature in iBatis. I was hoping to be able to tell them it'll be in the main dev stream soon. Do you see any roadblocks to its eventual integration?
Thanks,
Ken
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Brandon Goodin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_62376 ]
Brandon Goodin commented on IBATIS-53:
--------------------------------------
Once we change to using execute() vs. executeQuery() (and
executeUpdate() for consistency), it should become easy to use a
custom type handler to deal with ref cursors.
IIRC there are other ways to handle this. I seem to recall others who
have wrapped their procs in an outer procedure that returns a result
set. Maybe even a single proc that can wrap and execute any other
proc.
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Ken Katsma (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_65599 ]
Ken Katsma commented on IBATIS-53:
----------------------------------
Sven,
This issue is regarding stored procedure resultsets. The example you gave has always worked as it is using dynamic sql.
Thanks,
Ken
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
> Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, showcase.txt
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Posted by "Ken Katsma (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_60693 ]
Ken Katsma commented on IBATIS-53:
----------------------------------
Just wondering if this might make it into an upcoming release?
I'm having a really hard time selling this to my organization (JPMorgan) without support for Oracle stored procedure resultsets somewhere in the base code.
I'm still using it in my project (with the hack), but it could get a lot more use if it had this feature.
Long term, if iBatis can't support Oracle ref cursors, it will really limit its adoption.
Thanks,
Ken
> Support for oracle cursors as resultsets
> ----------------------------------------
>
> Key: IBATIS-53
> URL: http://issues.apache.org/jira/browse/IBATIS-53
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Reporter: Ken Katsma
> Priority: Minor
>
> iBatis doesn't currently support result sets from functions in Oracle. A modification to SQLExecutor as detailed below can add the necessary support. However, it requires a hard-coded check for an Oracle driver. A better option would be to supply a factory for alternate SQLExecutor's for different dialects. This would allow for any future database specific customization as well.
> The code change is in SQLExecutor.executeQueryProcedure (see comments):
> 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).");
> // ****************************************
> // Code changes below
> // ****************************************
> if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
> {
> // If in oracle then execute instead of executeQuery
> boolean b = cs.execute();
> errorContext.setMoreInfo("In Oracle query mode.");
> errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
> // Get the output parameters first, instead of last
> retrieveOutputParameters(cs, mappings, parameters);
> // Then find the resultset and handle it
> 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);
> }
> //****************************************
> // Non-oracle..original code
> 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);
> }
> }
> An example mapping looks like:
> <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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira