Name: krT82822 Date: 02/20/2000


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)

Using CachedRowSet from Sun to load a table from MSSQL7.0 database over the
jdbc-odbc bridge. Rowset loads successfully. When accessing rows, however,
get the message "getBoolen[sic] Failed on value (true) in column 3". Column 3
is correct in the database and is accessible with normal jdbc ResultSet
operations. Furthermore, other, non-boolean fields, are accessible from the
CachedRowSet.

-------------------

9 Feb 2000 email back from user:

The source data field is an MSSQL7 bit field of length 1. ODBC normally
returns this as a boolean and this works properly in normal jdbc-odbc
resultsets. I have attached a copy of the source that's having the problem.
The SQLException occurs at line 82:
         boolean willingToRelocate = crs.getBoolean("WillingToRelocate");

Pls. ignore the inelegance of the code -- it's only designed to run
once to clean up an incorrect field in a database table.

Code follows:

import java.awt.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import javax.sql.*;
import javax.swing.*;
import sun.jdbc.rowset.*;
import gnu.regexp.*;

public class ReformatCandidates
{ private static String vendor, dbName, type;
        private static ProgressFrame progress = null;
        private static JProgressBar progressBar = null;
        private static RE rePunctuation, reLeadingPlusses, reControlChars,
                                        reSpaces, reWords;
        public ReformatCandidates(ProgressFrame progress)
        { this.progress = progress;
                this.progressBar = progress.progressBar;
                try
                { rePunctuation = new
         RE("[/,;\\*\\.\\?\\[\\]\\{\\}\\(\\)<>`~!@#\\$%\\^=\\|\\:\"\'\r\n\t\\\\]");
                        reLeadingPlusses = new RE("\\s+\\+");
                        reControlChars = new RE("[\\000-\\037]|[\\200-\\237]|\\177");
                        reSpaces = new RE("\\s+");
                        reWords = new RE(" ([^ ]+)");
                }
                catch(Exception x)
                { Logger.fail(x,"Exception initializing Reformatter"); }
        }
        private static Connection newConnection()
        throws Exception
        { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                return DriverManager.getConnection("jdbc:odbc:"+dbName,"sa",null);
        }
        private static String convertRequirement(String requirement)
        { String result = null;
                String[] tokens = util.split(requirement," ");
                for(int ix=0; ix<tokens.length; ++ix)
                        if(util.isNoise(tokens[ix])) tokens[ix] = "";
                return util.join(tokens," ");
        }
        private static String convertDistrictDesired(boolean willingToRelocate,
                        String resumeText, String districtDesired)
        { String newDistrictDesired = districtDesired;
                TokenExtractor extractor = new TokenExtractor(resumeText);
                if(willingToRelocate)
                { if(newDistrictDesired!=null
                        && newDistrictDesired.indexOf("ALLUS")>=0)
                         newDistrictDesired = null;
                        if(newDistrictDesired==null)
                        { newDistrictDesired = extractor.lineTailTrimmed("Regions:");
                         if(newDistrictDesired==null)
                           newDistrictDesired = extractor.lineTailTrimmed("Region:");
                         if(newDistrictDesired!=null
                         && newDistrictDesired.indexOf("ALLUS")>=0)
                          newDistrictDesired = null;
                        }
                }
                else newDistrictDesired = null;
                return newDistrictDesired;
        }
        private static void execute()
        { try
                { Connection conn = newConnection();
                        int count = 0;
                        ResultSet rs = newConnection().createStatement().executeQuery
                           ("select count(ID) as countOfCandidates from Candidate");
                        if(rs.next()) count = rs.getInt("countOfCandidates");
                        progressBar.setMinimum(0);
                        progressBar.setMaximum(count);
                        int chunkSize = count/20;
                        CachedRowSet crs = new CachedRowSet();
                        int[] key = {1}; crs.setKeyColumns(key);
                        int ixCandidate=0;
                        for(int ixChunk = 0; ixChunk<20; ++ixChunk)
                        { int chunkStart = ixChunk*chunkSize;
                         crs.setCommand(
                                "select ID, ResumeText, WillingToRelocate, DistrictDesired, " +
                                        "Requirement" +
                                                "from Candidate where ID between "+chunkStart+
                                                        " and "+(chunkStart+chunkSize-1));
                         crs.execute(conn);
                         while(crs.next())
                         { int ID = crs.getInt("ID");
                          String resumeText =crs.getString("ResumeText");
                          boolean willingToRelocate = crs.getBoolean("WillingToRelocate");
                          String districtDesired = crs.getString("DistrictDesired");
                          String requirement = crs.getString("Requirement");
crs.updateString("DistrictDesired",convertDistrictDesired(willingToRelocate,
resumeText, districtDesired));
                          crs.updateString("Requirement",convertRequirement(requirement));
                          progressBar.setValue(++ixCandidate);
                         }
                         crs.acceptChanges();
                        }
                        conn.close();
                }
                catch(Exception x){System.out.println(x); x.printStackTrace();}
                System.out.println("Candidate Data reformatted");
        }
        public static void main(String args[])
        throws Exception
        { Object lock = new Object();
                vendor = "sun";
                dbName = args[0];
                try
                { Globals.setProperties(vendor, dbName, "sa", "");
                        progress = new ProgressFrame(lock,"Reformatter");
                        progress.setMessage("Reformating Candidates");
                        Logger.open("stdout");
                        ReformatCandidates reformatter = new ReformatCandidates(progress);
                        reformatter.execute();
                        System.exit(0);
                }
                catch(Exception e)
                { e.printStackTrace(Logger.logWriter);}
        }
}

-----------------

(Review ID: 101017)
======================================================================