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)
======================================================================