I have tried to run a program that calls a stored procedure on MySQL
server version 5.0.17 by using connector/j 5.0, but it always fails on the
statement: con.preparecall() ,
have looked on the internet and found out that people can all mysql
stored procedure all right in their programs, really dont know what's
wrong with this small peiece of code:
import java.sql.*;
public class TestDB {
/**
// procedure being called is:
CREATE PROCEDURE `dbsaystorm`.`getsite` ()
BEGIN
select name from tblsite;
END
*/
public static void main(String[] args) {
try {
//Class.forName("org.gjt.mm.mysql.Driver");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbname",
"user", "pwd");
/**
* executing SQL statement here gives perfect correct results:
*/
// PreparedStatement ps = con.prepareStatement("select name from tblsite");
// ResultSet rs =ps.executeQuery();
// but in stored procedure way...
//it fails here on this prepare call statement:
CallableStatement proc = con.prepareCall("call getsite()");
ResultSet rs =proc.executeQuery();
if (rs == null) return;
while (rs.next()){
System.out.println("site name is: "+ rs.getString(1));
}
rs.close();
} catch (SQLException e) {e.printStackTrace();}
catch (Exception e) {e.printStackTrace();}
}
}
it always gives this exception:
java.lang.NullPointerException
at com.mysql.jdbc.StringUtils.indexOfIgnoreCaseRespectQuotes(StringUtils.java:959)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1280)
at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3668)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:638)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:453)
at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4365)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4439)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4413)
at saystorm.server.data.database.TestDB.main(TestDB.java:29)
where have I gone wrong?
when I commented out the statement that makes the procedure call and call preparedstatement to execute SQL select statement, it gave perfectly correct result.
it looks like there is no problem with java prog accessing MYSQL server database, but the it seems that it's just java can't call stored procedure stored on the mysql server version 5.
can it or can't it? if it can, how is that accomplished?