Because the SHOW FULL COLUMNS FROM command does not apply to temporary table, a
java program using Connector/J
is not able to use the sun.jdbc.rowset.CachedRowSet to retrieve the data from a
temporary table.
Does anyboby have any suggestions?
Thank you.
Here is the version of mysql server and MySQL Connector/J
mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.15, for Win32 (ia32)
Connection id: 3
Current database: fmms
Current user: [EMAIL PROTECTED]
SSL: Not in use
Using delimiter: ;
Server version: 5.0.15-nt-log
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 29 min 58 sec
Threads: 1 Questions: 97 Slow queries: 0 Opens: 5 Flush tables: 1 Open tabl
es: 12 Queries per second avg: 0.054
--------------
MySQL Connector/J 3.1.8
This is the stored procedure created in the test database.
It creates a temporary table, populates with one record and it returns the
content of the temporary table.
DROP PROCEDURE IF EXISTS mytempsp
//
CREATE PROCEDURE mytempsp()
BEGIN
CREATE TEMPORARY TABLE mytemptable (i INT, a VARCHAR(16));
INSERT INTO mytemptable(i, a) VALUES (10000, "First");
SELECT i, a FROM mytemptable;
END
//
Here is the content of log produced by the java program
051028 12:17:06 16 Connect [EMAIL PROTECTED] on test
16 Query SET NAMES latin1
16 Query SET character_set_results = NULL
16 Query SHOW VARIABLES
16 Query SHOW COLLATION
16 Query SET autocommit=1
16 Query SHOW CREATE PROCEDURE `test`.`mytempsp`
16 Query call mytempsp()
16 Query SHOW CHARACTER SET
16 Query SHOW FULL COLUMNS FROM `test`.`mytemptable`
16 Quit
Here is the system output of the java program:
Obtained connection
Executed query [EMAIL PROTECTED]
SQLException: Table 'test.mytemptable' doesn't exist
SQLState: 42S02
VendorError: 1146
Shutting down..
Here is the java code that invokes the stored procedure
/*
* Created on Oct 27, 2005
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.transcore.dao;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import sun.jdbc.rowset.CachedRowSet;
/**
* @author frondonig
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class TemporaryTable {
private Connection connection = null;
private CallableStatement statement = null;
private ResultSet rs = null;
public TemporaryTable()
{
// Load the driver to allow connection to the db
try {
String driverName = "com.mysql.jdbc.Driver";
Class.forName(driverName);
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=root&password=mypass");
System.out.println("Obtained connection");
} catch (ClassNotFoundException cnfex) {
System.out.println("Failed to load jdbc driver");
cnfex.printStackTrace();
System.exit(1);
} catch (SQLException sqlex) {
// handle SQL errors
System.out.println("SQLException: " +
sqlex.getMessage());
System.out.println("SQLState: " + sqlex.getSQLState());
System.out.println("VendorError: " +
sqlex.getErrorCode());
} catch (Exception ex) {
// handle any other errors
System.out.println("Exception: " + ex.getMessage());
}
}
public void shutDown() {
System.out.println("Shutting down..");
try {
if (connection != null )
connection.close();
} catch (SQLException sqlex) {
// handle SQL errors
System.out.println("Unable to disconnect");
sqlex.printStackTrace();
}
}
public void TempTableViaStoredProcedure() throws Exception {
if ( connection != null ) {
try {
// Prepare the statement
statement = connection.prepareCall("call mytempsp()");
rs = statement.executeQuery();
System.out.println("Executed query " + rs.toString());
CachedRowSet cr = new CachedRowSet();
cr.populate(rs);
} catch (SQLException sqlex) {
System.out.println("SQLException: " +
sqlex.getMessage());
System.out.println("SQLState: " +
sqlex.getSQLState());
System.out.println("VendorError: " +
sqlex.getErrorCode());
throw sqlex;
} catch (Exception ex) {
ex.printStackTrace();
System.out.println("Exception: " + ex.getMessage());
throw ex;
}
}
return;
}
public static void main( String arcs[] ) {
TemporaryTable tt = null;
tt = new TemporaryTable();
try {
tt.TempTableViaStoredProcedure();
} catch (Exception e) {}
tt.shutDown();
System.exit(0);
}
}
Giorgio Frondoni
AVP, Chief of System Development
TransCore
phone: (858) 826-4750
cell: (760) 214-4092
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
