Name: krT82822 Date: 03/06/2000


6 Mar 2000 eval1127@eng -- there are multiple issues cited here, but the primary one is featured in the synopsis.

orig synopsis: "Multiple bugs with the ODBC bridge, streams, GUIDs"
(see also 4234318, re. metadata problems;
4307662, where a GUID apepears to have been read successfully)

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

1)
- create a MS Accsess 2000 database with a table Authors that contains
an auto-increment/GUID field as primary key named "gGuid"
and a text field name "sName", map this database as a system ODBC
data source named "biblio". enter at least 1 row of data.
- run the program below.

2)
import java.io.*;
import java.sql.*;
public class test {
    public static void main(String args[]) throws Exception {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection co = DriverManager.getConnection("jdbc:odbc:biblio");
        Statement stmt = co.createStatement();
        InputStream is;
        for(int i = 1; i <= 6; i++) {
            ResultSet rs = stmt.executeQuery(
                "SELECT sName, gGuid FROM Authors ORDER BY sName");
            ResultSetMetaData rsmd = rs.getMetaData();
            switch(i) {
            case 1:
                System.out.println("====== test 1: getString()");
                break;
            case 2:
                System.out.println("\n====== test 2: getBigDecimal()");
                break;
            case 3:
                System.out.println("\n====== test 3: getObject()");
                break;
            case 4:
                System.out.println("\n====== test 4: getBytes()");
                break;
            case 5:
                System.out.println("\n====== test 5: getBinaryStream()/read");
                break;
            case 6:
                System.out.println(
                   "\n====== test 6: getBinaryStream()/available");
                break;
            }
            System.out.println("Col 1: "+rsmd.getColumnTypeName(1)+
                " ("+rsmd.getColumnType(1)+")");
            System.out.println("Col 2: "+rsmd.getColumnTypeName(2)+
                " ("+rsmd.getColumnType(2)+")");
            rs.next();
            try {
                switch(i) {
                case 1:
                    System.out.println("Text: " + rs.getString(1));
                    System.out.println("GUID: " + rs.getString(2));
                    break;
                case 2:
                    System.out.println("GUID: " + rs.getBigDecimal(2));
                    break;
                case 3:
                    System.out.println("Text: " + rs.getObject(1));
                    System.out.println("GUID: " + rs.getObject(2));
                    break;
                case 4:
                    System.out.println("Text: " +
                        byteArrayToString(rs.getBytes(1)));
                    System.out.println("GUID: " +
                        byteArrayToString(rs.getBytes(2)));
                    break;
                case 5:
                    is = rs.getBinaryStream(1);
                    System.out.println("Text: " + inputStreamToString(is));
                    is = rs.getBinaryStream(2);
                    System.out.println("GUID: " + inputStreamToString(is));
                    break;
                case 6:
                    is = rs.getBinaryStream(1);
                    System.out.println("Text/length: " + is.available());
                    break;
                }
            } catch(Exception ex) {
                ex.printStackTrace();
            }
        }
        stmt.close();
        co.close();
    }
    public static String byteArrayToString(byte[] bytes) throws IOException {
        StringBuffer sb = new StringBuffer();
        int c;
        for(int i = 0; i < bytes.length; i++) {
            addByte(sb, bytes[i]);
        }
        return sb.toString();
    }
    public static String inputStreamToString(InputStream is) throws IOException
{
        StringBuffer sb = new StringBuffer();
        int c;
        int n = 0;
        while((c = is.read()) >= 0) {
            addByte(sb, c);
            if(++n > 32) {
                sb.append(" !!!! ERROR: >32 bytes");
                break;
            }
        }
        return sb.toString();
    }
    public static void addByte(StringBuffer sb, int i) {
        if(i < 32 || i >= 127) {
            sb.append("\\x");
            sb.append("0123456789ABCDEF".charAt((i>>4) & 0xF));
            sb.append("0123456789ABCDEF".charAt(i & 0xF));
        } else {
            sb.append((char)i);
        }
    }
}


3-4)
Col 1: VARCHAR (12)
Col 2: GUID (1111)
Text: A.C.F.
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 2 (gGuid)
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getString(Unknown Source)
        at to.berger.bibsvr.test.main(test.java:53)


====== test 2: getBigDecimal()
Col 1: VARCHAR (12)
Col 2: GUID (1111)
java.lang.NumberFormatException: 4B-95AE-11D0-8A1F-00A02466848A
        at java.lang.Integer.parseInt(Unknown Source)
        at java.lang.Integer.parseInt(Unknown Source)
        at java.math.BigDecimal.<init>(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getBigDecimal(Unknown Source)
        at to.berger.bibsvr.test.main(test.java:56)


====== test 3: getObject()
Col 1: VARCHAR (12)
Col 2: GUID (1111)
Text: A.C.F.
GUID: null


====== test 4: getBytes()
Col 1: VARCHAR (12)
Col 2: GUID (1111)
Text: A.C.F.
GUID: Kn\x00w\xAE\x95\xD0\x11\x8A\x1F\x00\xA0$f\x84\x8A


====== test 5: getBinaryStream()/read
Col 1: VARCHAR (12)
Col 2: GUID (1111)
Text: A.C.F.
GUID: Kn\x00w\xAE\x95\xD0\x11\x8A\x1F\x00\xA0$f\x84\x8AKn\x00w\xAE\x95\xD0\x11\x
8A\x1F\x00\xA0$f\x84\x8AK !!!! ERROR: >32 bytes


====== test 6: getBinaryStream()/available
Col 1: VARCHAR (12)
Col 2: GUID (1111)
java.io.IOException
        at sun.jdbc.odbc.JdbcOdbcInputStream.available(Unknown Source)
        at to.berger.bibsvr.test.main(test.java:74)


************
step 1 of the program shows that it's impossible to read a GUID as a string
this follows the documentation which states that data type 1111 (OTHER) must
be read with getObject

step 2 show that it is also impossible to read the object with getBigDecimal,
which also follows the specs but is rather infortunate.

step 3 shows that getObject return null, which is a bug (see Types.OTHER doc)
getObject should return a BugDecimal with the value of the GUID.

step 4 shows that fortunatly, getBytes works with the GUID.

step 5 shows that getBinaryStream get the correct data, but return an
infine stream.... it's another bug

step 6 shows that the available() function of the binary stream crashes with
an IOException even for the simple text field, wich is the third bug.
(Review ID: 101116)
======================================================================

Further test of this problem against MS Access 2000 using a the ODBC-Access
driver indicates this problems has been fixed as follows...

The extension of the ODBC type definitions that can be handled
has assisted is allowuing the GUID types to be processed as 'int' types.
See: 4379373 for more details.

jonathan.bruce@eng 2001-07-19