Skip to Main Content

Java Programming

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to call stored procedures from java program?

807607Oct 21 2006 — edited Oct 24 2006
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 21 2006
Added on Oct 21 2006
5 comments
480 views