[thelist] i'm going bonkers

[david.landy at somerfield.co.uk]

>("bonkers"?  Isn't that a race track near New York City?)

Must be an english-ism! 

:-))

Here's the JSP:

String sql;
sql = "SELECT processes.process, steps.stepid, steps.step,
Sum(IIf([pass]='early',1,0)) AS Early, " + 
      "Sum(IIf([pass]='late',1,0)) AS Late, Sum(IIf([pass]='late' Or
[pass]='early',1,0)) AS Total, " + 
      "100*Early/Total AS EarlyPct, 100*Late/Total AS LatePct FROM statuses
INNER JOIN " + 
      "(steps INNER JOIN processes ON steps.processid = processes.processid)
ON statuses.status = steps.status " + 
      "WHERE steps.substepid<=0 GROUP BY processes.process, steps.stepid,
steps.step " + 
      "HAVING (((Sum(IIf([pass]='late' Or [pass]='early',1,0)))>0)) " + 
      "ORDER BY 100*Sum(IIf([pass]='early',1,0))/Sum(IIf([pass]='late' Or
[pass]='early',1,0)) DESC";

out.println("sql:" + sql + "<br>");

recordset rs = new recordset();
rs.open(sql);
out.println("countRows():" + rs.countRows() + "<br>");

ResultSetMetaData md = rs.rs.getMetaData();
for (int i = 0; i < md.getColumnCount(); i++) {
    out.println(md.getColumnLabel(i + 1));
}
out.println("<br>");

int rows = 0;
try {
    while (true) {
        rs.rs.next();
        for (int i = 0; i < 2; i++) {
            String t = rs.rs.getString(i + 1);
        }
        rows++;
    }
}
catch (Exception e) {
}
out.println("rows:" + rows + "<br>");


And it uses my recordset class:

public class recordset {
    
    //constants specifying the database and driver to use
    static final String url = "jdbc:odbc:prompt";
    static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    
    private Connection con = null;
    private Statement stmt = null;
    public ResultSet rs = null;
    public ResultSetMetaData md = null;
    
    /** Creates a new instance of rs */
    public void recordset() {
    }
    
    public String open(String sql) {
        //opens the recordset
        String errorMessage = "";
        debug.println("xfiles.recordset.open:sql='" + sql + "'");
        
        try {
            //load class for jdbc/odbc bridge
            Class.forName(driver);
            //open database using jdbc/odbc bridge
            con = DriverManager.getConnection(url, "", "");
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
            //open recordset
            rs = stmt.executeQuery(sql);
            md = rs.getMetaData();
        }
        catch (Exception e) {
            errorMessage = "xfiles.recordset.open:error:" + e.getMessage();
            error.println(errorMessage);
        }
        
        return errorMessage;
    }
    
    public String close() {
        //closes the recordset
        String errorMessage = "";
        if (rs != null) {
            try {
                rs.close();
                stmt.close();
                con.close();
            }
            catch (SQLException e) {
                errorMessage = "xfiles.recordset.close:" + e.getErrorCode()
+ ": " + e.getMessage();
                error.println(errorMessage);
            }
        }
        return errorMessage;
    }
    
    public int countRows() {
        int rows = 0;
        try {
            rs.beforeFirst();
            try {
                while (true) {
                    rs.next();
                    for (int i = 0; i < md.getColumnCount(); i++) {
                        String t = rs.getString(i + 1);
                    }
                    rows++;
                }
            }
            catch (Exception e) {
            }
            rs.beforeFirst();
        }
        return rows;
    }
}

-- 
David Landy, IT Consultant, Business Intelligence
Somerfield/KwikSave Support Centre
Whitchurch, Bristol, UK. Tel: 0117 301 8977
david.landy at somerfield.co.uk <mailto:david.landy at somerfield.co.uk>    
 
This is the true joy of life. The being used for a purpose 
Recognized by yourself as a mighty one. 
George Bernard Shaw


-----Original Message-----
From: Ed McCarroll [mailto:Ed at ComSimplicity.com]
Sent: Tuesday, 25 May 2004 15:58
To: thelist at lists.evolt.org
Subject: RE: [thelist] i'm going bonkers


("bonkers"?  Isn't that a race track near New York City?)

Kidding aside, it might help if we could see more of the code, particularly
the SQL used to open the rs.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Ed McCarroll                             http://www.ComSimplicity.com
(310) 838-4330                      PO Box 654, Culver City, CA 90232
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 
If you are not the intended recipient of this e-mail, please preserve the
confidentiality of it and advise the sender immediately of any error in
transmission. Any disclosure, copying, distribution or action taken, or
omitted to be taken, by an unauthorised recipient in reliance upon the
contents of this e-mail is prohibited. Somerfield cannot accept liability
for any damage which you may sustain as a result of software viruses so
please carry out your own virus checks before opening an attachment. In
replying to this e-mail you are granting the right for that reply to be
forwarded to any other individual within the business and also to be read by
others. Any views expressed by an individual within this message do not
necessarily reflect the views of Somerfield.  Somerfield reserves the right
to intercept, monitor and record communications for lawful business
purposes.