Name: krT82822 Date: 06/16/99


When I try to access a MS SQL server through the JDBC-ODBC bridge, I cannot get more than one result set. For example, when I try to get the multiple results from "sp_help TABLENAME", Sun's bridge fails with a "Function sequence error". I am trying to follow the unclear documentation about when and how statement.getUpdateCount(), statement.getResultSet(), and statement.getMoreResults() should be called. I believe I am close to being right, because Microsoft's JVM produces correct results.


java com.epicor.jdbc.test sun.jdbc.odbc.JdbcOdbcDriver "jdbc:odbc:era irvine" tracker er irvine "sp_help fieldnames"

p
Opening db connection
---
        Owner Type Created_datetime
 --
        fieldnames dbo view 1999-03-29 21:46:02.373
---
Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Driver Manage
r] Function sequence error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246)
        at sun.jdbc.odbc.JdbcOdbc.SQLNumResultCols(JdbcOdbc.java:2612)
        at sun.jdbc.odbc.JdbcOdbcStatement.getColumnCount(JdbcOdbcStatement.java
:664)
        at sun.jdbc.odbc.JdbcOdbcStatement.getUpdateCount(JdbcOdbcStatement.java
:300)
        at com.epicor.jdbc.Server.executeQuery(Server.java:79)
        at com.epicor.jdbc.test.main(test.java:31)

</pre>

jview com.epicor.jdbc.test com.ms.jdbc.odbc.JdbcOdbcDriver "jdbc:odbc:era irvine" tracker irvine "sp_help fieldnames"

produces the correct output:
<pre>
Opening db connection
---
        Owner Type Created_datetime
 --
        fieldnames dbo view 1999-03-29 21:46:02.373
---
        Type Computed Length Prec Scale Nullable TrimTrai
lingBlanks FixedLenNullInSource
 --
        fldId int no 4 10 0 yes (n/a) (n/a)
        fieldName varchar no 5 yes yes
no
        fldLabel char no 32 yes yes
yes
        fldClass int no 4 10 0 yes (n/a)
(n/a)
---
        Seed Increment Not For Replication
 --
        No identity column defined. NULL NULL NULL
---

 --
        No rowguidcol column defined.
---
</pre>

---------------------------------------------------
package com.epicor.jdbc;

/**
 * test.java
 *
 *
 * Created: Mon Mar 22 17:28:37 1999
 *
 * @author
 * @version
 */

import java.util.*;

public class test {
    
    public test() {

    }

    public static void main( String[] args ) throws java.sql.SQLException {

Map env = new HashMap( 7 );
env.put( "jdbc.driver", args[0]);
env.put( "jdbc.url", args[1]);
env.put( "jdbc.user", args[2]);
env.put( "jdbc.password", args[3]);

Server s = Server.connect( env );

s.executeQuery( args[4] );
    }
    
} // test
---------------------------------------------------
package com.epicor.jdbc;

/**
 * Server.java
 *
 *
 * Created: Mon Mar 22 15:40:06 1999
 *
 * @author
 * @version
 */

import java.io.*;
import java.util.*;
import java.sql.*;

public class Server{
    
    public static Server connect( Map env ){

String driver = (String)env.get( "jdbc.driver" );
String url = (String)env.get( "jdbc.url" );
String user = (String)env.get( "jdbc.user" );
String password = (String)env.get( "jdbc.password" );

Connection conn = null;

try {

            Class.forName( driver );
            System.out.println("Opening db connection");

            conn = DriverManager.getConnection( url, user, password );
Server s = new Server( conn );
return s;

        }catch (ClassNotFoundException ex) {

            System.err.println("Cannot find the database driver classes.");
            System.err.println(ex);

        } catch (SQLException ex) {

            System.err.println("Cannot connect to this database.");
            System.err.println(ex);

        }
return null;
    }

    Connection connection;
    //PrintWriter out = new PrintWriter( new OutputStreamWriter( System.out ) );
    PrintStream out = System.out;

    protected Server( Connection conn ) {
this.connection = conn;
    }
    

    public List executeQuery( String query ) throws SQLException{
        Statement statement = connection.createStatement();
        boolean results = statement.execute( query );
        
        
        int rsnum = 0;
        int rowsAffected = 0;
        do{
            out.println( "---" );
            //rowsAffected = statement.getUpdateCount();
            //System.err.println("Update count: " + rowsAffected );
            if (results){
                rsnum++;
                ResultSet rs = statement.getResultSet();
                if( rs != null ){
                    saveResults(rs, rsnum);
                    rs.close();
                }
            }else{
                rowsAffected = statement.getUpdateCount();
                if (rowsAffected >= 0) {
                    out.println();
                    out.print( rowsAffected );
                    out.println(" rows Affected.");
                }
            }
            results = statement.getMoreResults();
        }while (results || rowsAffected != -1);

        
        return null;
    }

    public void saveResults( ResultSet rs, int rsnum ) throws SQLException{

        int i;
        
        // Get the ResultSetMetaData. This will be used for the column headings
            
        ResultSetMetaData rsmd = rs.getMetaData ();
            
        // Get the number of columns in the result set
            
        int numCols = rsmd.getColumnCount ();

        // Display column headings

        for (i=2; i<=numCols; i++)
        {
            out.print("\t");
            out.print(rsmd.getColumnLabel(i));
        }
        out.println();
        out.println(" --");

        while( rs.next() ){
            // Loop through each column, getting the column data and displaying
            for (i=1; i<=numCols; i++) {
                out.print("\t");

                String foobar = rs.getString(i);
                if(rs.wasNull()){
                    out.print("NULL");
                }else{
                    out.print(foobar);
                }
            }
            out.println();

            // Fetch the next result set row
        }
//return null;
    }
}

-----------------------------------------
(Review ID: 83420)
======================================================================

Name: krT82822 Date: 10/20/99


The following module works under Version 1.2.1 but after I install Version 1.2.2 I get the following error message:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Function sequence error

If you need more please let me know. I can be reached as
###@###.###

Code:

import java.sql.*;
import javax.swing.*;

public class LoadComboClass extends Thread
{
NotifyClass nc = null;

JComboBox box1 = null;
JComboBox box2 = null;
JComboBox box3 = null;
JComboBox box4 = null;

MainClass mc = null;

EntryClass ec = null;

ResultSet theResults = null;

String theQuery = new String("");
String data = new String("");

public LoadComboClass(JComboBox cardCombo, JComboBox vendorCombo, JComboBox itemCombo, JComboBox checkCombo, MainClass mc, EntryClass ec, NotifyClass nc)
{
box1 = cardCombo;
box2 = vendorCombo;
box3 = itemCombo;
box4 = checkCombo;
this.mc = mc;
this.ec = ec;
this.nc = nc;
}

public void run()
{
if(box1 != null)
{
mc.setStatus("Loading Card Numbers");
nc.put("Loading Card Numbers");

theQuery = "SELECT CardNumber, Bank FROM CardInfo ORDER BY CardNumber";
theResults = ec.getTheData(theQuery);
box1.addItem(" ");
try
{
while (theResults.next())
{
data = theResults.getString("CardNumber");
data = data + "@ " + theResults.getString("Bank");
nc.put(data);
box1.addItem(data); }
}
catch (SQLException e) {}
mc.setStatus("Done Loading Card Numbers");
}

if(box2 != null)
{
mc.setStatus("Loading Vendors");
//EROR OCCURRS HERE AFTER READING ABOUT 30 ROWS......
theQuery = "SELECT DISTINCT Vendor FROM Transactions ORDER BY Vendor";
theResults = ec.getTheData(theQuery);
box2.addItem(" ");
try
{
while(theResults.next())
{
data = theResults.getString("Vendor");
nc.put(data);
box2.addItem(data);
}
}
catch(SQLException e) {}
mc.setStatus("Done Loading Vendors");
}

if(box3 != null)
{
mc.setStatus("Loading Items");

theQuery = "SELECT DISTINCT Item FROM Transactions ORDER BY Item";
theResults = ec.getTheData(theQuery);
box3.addItem(" ");
try
{
while(theResults.next())
{
data = theResults.getString("Item");
nc.put(data);
box3.addItem(data);
}
}
catch(SQLException e) {}
mc.setStatus("Done Loading Items");
}

if(box4 != null)
{
mc.setStatus("Loading Checks");

theQuery = "SELECT DISTINCT CheckNumber FROM Transactions ORDER BY CheckNumber";
theResults = ec.getTheData(theQuery);
box4.addItem(" ");
try
{
while(theResults.next())
{
data = theResults.getString("CheckNumber");
nc.put(data);
box4.addItem(data);
}
}
catch(SQLException e) {}
mc.setStatus("Done Loading Checks");
}

nc.put("Done Loading");

try
{ sleep(500); }
catch(InterruptedException e) {}
}
}
(Review ID: 96801)
======================================================================