java version "1.3.0"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0-C)
Java HotSpot(TM) Client VM (build 1.3.0-C, mixed mode)


/* Steps to reproduce:

1) Create one database stored procedure.
It needs to have MORE columns in the 2nd select, than in the first.
(Severity of problem can be witnessed more easily by:
1st select/1st column: bit datatype
2nd select/1st column: int/String (anything bigger than a bit))

2) Setup Connection object
3) Setup CallableStatement object with "{ call test_2_selects }"
4) Execute CallableStatement
5) Print 1st ResultSet -- it will be fine
6) Move to next resultset (cs.getMoreResults)
7) Try printing 2nd resultSet -- error -- [Microsoft][ODBC SQL Server Driver]
Numeric value out of range
// # of columns not updated (1st problem)
// java.sql.Type from ResultSet.getColumnType(i) stays the same in 2nd
as in 1st resultset
// because types are wrong in 2nd resultset, if a bit datatype is in a
different
// position than in the 1st select, we get Numeric value out of range

*/

package com;
import java.util.*;
import java.sql.*;

import com.plurimi.utils.myutils;


public class metaDataBug
{
public metaDataBug() { }
public void testit() {
this.print ("Begin testit");

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (java.lang.ClassNotFoundException cnfe) {
System.err.println (" failed: Class.forName
(sun.jdbc.odbc.JdbcOdbcDriver) " + cnfe.getMessage());
}


try {
// 2) Setup Connection object
Connection con = DriverManager.getConnection
("jdbc:odbc:TESTDATABASE",

"foo",

"foo");
// 3) Setup CallableStatement object with "{ call
test_2_selects }"
CallableStatement cs = con.prepareCall ("{ call
test_2_selects }");
// cs.registerOutParameter(1, Types.INTEGER);
// cs.registerOutParameter(2, Types.INTEGER);
/*
CREATE PROCEDURE test_2_selects AS
select * from test;
select myInt, myBit, * from test;
*/

// 4) Execute CallableStatement
cs.execute();

ResultSet rs = cs.getResultSet();

// 5) Print 1st ResultSet -- it will be fine
this.printResultSet(rs, true);

// 6) Move to next resultset (cs.getMoreResults)
while (cs.getMoreResults()) {
rs = cs.getResultSet();
// 7) Try printing 2nd resultSet -- error --
[Microsoft][ODBC SQL Server Driver]Numeric value out of range
this.printResultSet(rs, true);
}
}
catch (java.sql.SQLException se) {
// System.err.println (" sql ex: " +
se.getMessage());
this.ReportjavaSQLExcpetion (se, "testing");
}

this.print ("End testit");
}

public void printOutParam(CallableStatement cs) {
try {
try {

this.print ("p1: " + cs.getInt(1));
this.print ("p2: " + cs.getInt(2));
}
catch (java.sql.SQLException se) {
this.ReportjavaSQLExcpetion (se, "Getting out param");
}
}
catch (java.lang.Exception e) {
this.ReportjavalangException (e, "Getting out param");
}

}

//------------------------------------------------------------------------------
---------
//------------------------------------------------------------------------------
---------
public void printResultSet(ResultSet res, boolean PrintData) {
if (null == res) {
this.print("ResultSet was null, couldn't print it");
return;
}
try {
java.sql.ResultSetMetaData meta = res.getMetaData();

if (null != res) {

System.err.println ("Printing resultset");
String s = new String();
String types = new String();
String typeNames = new String();
String sizes = new String();
String scales = new String();
String precisions = new String();

int i;
int nc = meta.getColumnCount();

for (i = 1; i<= nc; i++) {
s = s
+ "," + meta.getColumnName(i);
types = types + "," +
new Integer(meta.getColumnType(i)).toString();
typeNames = typeNames + "," +
meta.getColumnTypeName(i);
sizes = sizes + "," +
new Integer(meta.getColumnDisplaySize(i)).toString();
scales = scales + "," +
new Integer(meta.getScale(i)).toString();
// precisions = precisions+ "," +
new Integer(localDbcall.getMetaData().getPrecision(i)).toString();

String s2 =
meta.getColumnName(i);
s2 = s2 + "," + new Integer
(meta.getColumnType(i)).toString();
s2 = s2 + "," +
meta.getColumnTypeName(i);
s2 = s2 + "," + new Integer
(meta.getColumnDisplaySize(i)).toString();
s2 = s2 + "," + new Integer
(meta.getScale(i)).toString();
System.err.println (" " + s2);
}
System.err.println (" Columns" + s);
System.err.println (" Types" + types);
System.err.println (" TypeNames" + typeNames);
System.err.println (" Sizes" + sizes);
System.err.println (" Scales" + scales);

if (PrintData) {
System.err.println (" Printing Data now ...");
while (res.next()) {
s = "";

for (i = 1; i<= nc; i++) {
s = s + " :: " + res.getString(i);
}
System.err.println (" " + s);
}
}
}

}catch (java.sql.SQLException ex) {
this.ReportjavaSQLExcpetion (ex, "Printing resultset -- got
exception: ");
// this.print("Printing resultset -- got exception: " +
ex.getMessage());
}

this.print ("End Printing resultset");
}

public void ReportjavaSQLExcpetion (java.sql.SQLException ex, String
sql) {
// return ex and resultset?
java.util.Date curDate = new java.util.Date();
System.err.println("----------------------- " +
this.getClass() + " at " + curDate.toString());
System.err.println(ex.getErrorCode() + ": " +
ex.getMessage());
ex.printStackTrace();
System.err.println("----- End Error Message");
}
public void ReportjavalangException (java.lang.Exception ex, String
sql) {
// return ex and resultset?
java.util.Date curDate = new java.util.Date();
System.err.println("----------------------- " + this.getClass()
+ " at " + curDate.toString());
if ("" != sql) {
System.err.println("+++SQL/Proc: " + sql);
}
System.err.println(ex.getMessage());
ex.printStackTrace();
System.err.println("----- End Error Message");
}

// Speed up getclass and date/time of error messages
public void print (String message) {
java.util.Date curDate = new java.util.Date();

StringBuffer sb = new StringBuffer();
sb.append (curDate.toString()).append (": " + message + "
" + this.getClass());

System.err.println (sb.toString());
System.out.println (sb.toString());
}
}

/*
Here is the problem in English (code example proves it below)
(SQL Server build table, and stored proc below all the code)

When calling a stored procedure, and trying to get MULTIPLE resultsets
returned,
the metaData for each subsequent resultset is not changed from that of
the 1st resultset.

1st select:
ok Columns ,myInt ,myFloat,myBit ,myString
ok Types ,4 ,6 ,-7
,1
ok TypeNames ,int ,float ,bit ,char

only 4 fields instead of 6
2nd select:
ok* Columns ,myInt ,myBit ,myInt ,myFloat
wrong Types ,4 ,6 ,-7 ,1
(Same as first resultset)
ok* TypeNames ,int ,bit ,int ,float

ok* means they were updated, but the # of columns is inaccurate, so it's not
quite right.

When selecting column myInt from 2nd ResultSet, Numeric Value out of range
occurs
because the driver/java.sql.ResultSet.getString(3) "thinks" bit, and when it
finds an
int, it chokes.

--------------------------------------------------------------------------------
---
2nd select WAS SUPPOSED TO BE 6 COLUMNS (one problem)
Columns ,myInt ,myBit ,myInt ,myFloat, myBit
,myString

On this 2nd resultset, the metaData java type (-7) thinks the field is
a BIT,
so when ResultSet.getString or ResultSet.get* functions are called
and the java type is incorrect, we get
[Microsoft][ODBC SQL Server Driver]Numeric value out of range

So, currently I cannot get more than one resultSet from a single stored
procedure call

*/

/*
-- FOR SQL SERVER --

if exists (select * from sysobjects where id = object_id(N'[dbo].
[test_2_selects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_2_selects]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[TEST]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST]
GO

CREATE TABLE [dbo].[TEST] (
[myInt] [int] NULL ,
[myFloat] [float] NULL ,
[myBit] [bit] NOT NULL ,
[myString] [char] (10) NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
GO

CREATE PROCEDURE test_2_selects AS
select * from test;
select myInt, myBit, * from test;
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

-- Now insert 2 rows of data --

*/

/* Actual data returned in Query Analyzer

(4 columns)
myInt myFloat myBit
myString
----------- ----------------------------------------------------- ----- --------
--
23 23.23 1
Testing
45 45.450000000000003 0
Testing2

(2 row(s) affected)


(6 columns)
myInt myBit myInt
myFloat myBit myString
----------- ----- ----------- --------------------------------------------------
--- ----- ----------
23 1 23
23.23 1 Testing
45 0 45
45.450000000000003 0 Testing2

(2 row(s) affected)


 */

/* Results:
1st select works great

Printing resultset
myInt ,4 ,int,11,0
myFloat ,6 ,float,24,0
myBit ,-7 ,bit,1,0
myString ,1 ,char,10,0
Columns,myInt,myFloat,myBit,myString
Types,4,6,-7,1
TypeNames,int,float,bit,char
Sizes,11,24,1,10
Scales,0,0,0,0
Printing Data now ...
:: 23 :: 23.23 :: 1 :: Testing
:: 45 :: 45.450000000000003 :: 0 :: Testing2
End Printing resultset

************************************
For 2nd resultset:

MetaData for 2nd resultSet STILL thinks it only has 4 columns
The first 4 column names ARE CORRECT getColumnName(i);
The first 4 Type Names ARE CORRECT getColumnTypeName(i);
The first 4 Types (Java) ARE INCORRECT getColumnType(i)

This is critical!! The java type is what is used by ResultSet.get* functions

************************************

Printing resultset
myInt ,4 ,int,11,0
myBit ,6 ,bit,1,0
myInt ,-7 ,int,11,0
myFloat ,1 ,float,24,0
Columns,myInt,myBit,myInt,myFloat
Types,4,6,-7,1
TypeNames,int,bit,int,float
Sizes,11,1,11,24
Scales,0,0,0,0

Printing Data now ...
Mon Nov 20 15:01:56 PST 2000: Printing resultset -- got exception: [Microsoft]
[ODBC SQL Server Driver]Numeric value out of range class
com.metaDataBug
End Printing resultset

*/
(Review ID: 112583)
======================================================================