[IBATIS-441] Multiple result sets support broken

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 2.3.0
• Fix Version/s: 2.3.1
• Component/s: Core
• Environment: Mysql Server version: 5.0.32-Debian_7etch1-log; mysql-connector-java-5.0.6-bin.jar; ibatis-2.3.0.677.jar
• Assignee: Clinton Begin
• Reporter: Arnaud Lemaitre
• Created: 21/Jun/07 11:03
• Updated: 23/Mar/08 05:09
• Resolved: 23/Mar/08 05:09

Description

the following iBATIS code can not retrieve the 2 expected result sets. plain JDBC equivalent is provided and is working properly.

platform specific note :
multiple queries (allowMultiQueries=true on JDBC url) is not working for Mysql server 4.0.24 (tested). apparently requires higher version > 4.1
(following tests have been done using Mysql 5.0.32)

Here is my Code (for test purpose) :

...
List results = null;

SqlMapClient sqlMap = IbatisUtil.getSqlMapInstance();

try

{ results = sqlMap.queryForList( "test" ); }

catch( SQLException sqle )

{ throw CoreException.wrap( sqle ); }

final Logger logger = Logger.getLogger( MyCLass.class );

logger.info ( "results.size() = " + results.size() );
...

mySqlMap.xml :

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace = "myNamespace">
<select id = "test" resultClass = "int, int">
<![CDATA[
select 1+1; select 2+2
]]>
</select>
</sqlMap>

My iBATIS Config :
( i've set allowMultiQueries to true, when set to false (default) i've Got sql syntax errors...

<sqlMapConfig>
<settings
cacheModelsEnabled = "true"
enhancementEnabled = "true"
lazyLoadingEnabled = "true"
maxRequests = "32"
maxSessions = "10"
maxTransactions = "5"
useStatementNamespaces = "false"/>
<transactionManager type = "JDBC">
<dataSource type = "SIMPLE">
<property name = "JDBC.Driver" value = "com.mysql.jdbc.Driver"/>
<property name = "JDBC.ConnectionURL" value = "jdbc:mysql://dev:3306/myDatabase?autoReconnect=true&allowMultiQueries=true&characterEncoding=UTF-8&characterSetResults=UTF-8"/>
<property name = "JDBC.Username" value = "myUsername"/>
<property name = "JDBC.Password" value = " myPassword"/>
</dataSource>
</transactionManager>
<sqlMap resource = "com/x/y/z/mySqlMap.xml"/> </sqlMapConfig>

Log4j output :

DEBUG TP-Processor3 java.sql.Connection -

{conn-100000} Connection
DEBUG TP-Processor3 java.sql.Connection - {conn-100000}

Preparing Statement: select 1+1; select 2+2
DEBUG TP-Processor3 java.sql.PreparedStatement -

{pstm-100001} Executing Statement: select 1+1; select 2+2
DEBUG TP-Processor3 java.sql.PreparedStatement - {pstm-100001}

Parameters: [] DEBUG TP-Processor3 java.sql.PreparedStatement -

{pstm-100001}

Types: [] DEBUG TP-Processor3 java.sql.ResultSet -

{rset-100002} ResultSet DEBUG TP-Processor3 java.sql.ResultSet - {rset-100002}

Header: [1+1] DEBUG TP-Processor3 java.sql.ResultSet -

{rset-100002}

Result: [2] DEBUG TP-Processor3 com.ibatis.common.jdbc.SimpleDataSource - Returned connection 1511627065 to pool.
INFO TP-Processor3 com.x.y.MyCLass - results.size() = 1

Here is the test :

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class test {

public static void main( String[] args )

{ new test( args ); }

public void testJDBCConnection() throws Exception
{
Class.forName( "com.mysql.jdbc.Driver" );

String url = "jdbc:mysql://myHost:3306/myDatabase?autoReconnect=true&allowMultiQueries=true&characterEncoding=UTF-8&characterSetResults=UTF-8";

Connection conn = DriverManager.getConnection( url, "myDatabase", "myPassword" );

try
{
String sqlString = "SELECT 1+1; SELECT 2+2; SELECT 3+3";
System.out.println("Before executing");

Statement stmt = conn.createStatement();
stmt.execute( sqlString );
for (; {
int updateCount = stmt.getUpdateCount();
if (updateCount >= 0)

{ // report rows affected... }

else

{ ResultSet rs = stmt.getResultSet(); if (rs == null) break; if ( rs.next() ) System.out.println( "result :" + rs.getInt(1) ); // process resultset .... }

stmt.getMoreResults();
}

System.out.println("After executing");
}
catch (Exception e)

{ System.out.println("Exception: " + e); }

}

public test( String[] args ) {
try

{ testJDBCConnection(); }

catch (Exception e)

{ System.out.println("Exception: " + e); }

}

}

And the output :

Before executing
result :2
result :4
result :6
After executing