Calling Stored Procedures from a database ( MySQL )

Thank you for the fast replies, I have seen these examples before and tried them, this metod is still crashing out when it hit's the execute() command. If you open a new project, insert a button, called button1, insert 2 text boxes called, txt1 & txt2, open up the java and insert this code in the relevent spots. To see where it fails, include the returnedID lines i've included in the comment lines.

The stored procedure is simple, it just seraches a string and returns a single integer.

DELIMITER $$;

DROP PROCEDURE IF EXISTS `corrovu`.`sp3`$$

CREATE PROCEDURE `sp3`()

BEGIN

SELECT diam FROM details WHERE PID = '0050003';

END$$

DELIMITER ;$$

diam is '4' where PID is '0050003' so the txt1 should display 4 but it doesn't, this is really driving me crazy, I've probably spent more than 50 hours trying to get the stored proc working, I don't know what more to do. I've looked at hundreds of expamples and can't get any of them working.

package spca;

import com.sun.rave.web.ui.appbase.AbstractPageBean;

import com.sun.rave.web.ui.component.Body;

import com.sun.rave.web.ui.component.Form;

import com.sun.rave.web.ui.component.Head;

import com.sun.rave.web.ui.component.Html;

import com.sun.rave.web.ui.component.Link;

import com.sun.rave.web.ui.component.Page;

import javax.faces.FacesException;

import com.sun.rave.web.ui.component.Button;

import com.sun.rave.web.ui.component.StaticText;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.Types;

public class Page1 extends AbstractPageBean {

static int outputValue = 69;

public static String sp01() {

String returnedID2="21345";

String returnedID="1";

try {

//The following line loads the driver (it work's to see include - returnedID="2";)

Class.forName("com.mysql.jdbc.Driver").newInstance();

//This line connects to the database (it work's )- returnedID="3";

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/corrovu?user=root&password=Glancino");

//This line prepares the call (apparantly work's, If i put a non-existant stored proc, this line fails)- returnedID="4";

CallableStatement cStmt = conn.prepareCall("{?= call sp3()}");

//This line registers the out paramaters (it apperntly works, If I insert incorrect values, it fails)- returnedID="5";

cStmt.registerOutParameter(1, Types.INTEGER);

//This line exit's the Try, I changed it as per the example, it still fails. I don't know what I'm doing wrong.- returnedID="6";

boolean hadResults = cStmt.execute();

//The rest of the code doesn't get parsed - returnedID="7";

while (hadResults) {

ResultSet rs = cStmt.getResultSet();

hadResults = cStmt.getMoreResults();

}

outputValue = cStmt.getInt(1); // index-based

if (returnedID == null){

return null;

} else {

return returnedID;

}

} catch(Exception e){

}

//I had to insert this following line to make this method work, this was not in the example as per these forums?

return returnedID;

}

public String button1_action() {

txt1.setText(sp01());

txt2.setText(""+outputValue);

return null;

}

}

Cheers, I hope someone can help me.

3Pc at 2007-7-6 14:33:00 >