stored procedure to return a resultset 3

[steve1rm]

Hello,

I have stored procedure the same as the one below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `flightdb`.`spGetAirportCodes`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spGetAirportCodes`()
BEGIN
SELECT AirportCode FROM Airports;
END$$

DELIMITER ;


The code I am using in my java program is listed below:

ResultSet airportCodes = null;

try
{
PreparedStatement ps = conn.prepareStatement( "call spGetAirportCodes" );

airportCodes = ps.executeQuery();

if ( airportCodes.next() )
{
System.out.println("Airport codes have been returned, Well done!" );
}
}
catch( SQLException sqle )
{
System.err.println( "\nSQLException: \n" );
System.err.println( "SQL State: " + sqle.getSQLState() );
System.err.println( "Message: " + sqle.getMessage() );
}

I think the store procedure is correct, I think my code is not working in the java program. I have tried using callable object, but when l run, I get a messsage saying that callable objects are not supported.

Is the java code for calling a stored procedure and return a resultset correct?

Many thanks in advance,

Steve

 

[obadare]

I think CallableStatement is the appropriate object to use rather than PreparedStatement

 

[steve1rm]

Hello Obadare,

I have used this code CallableStatement cs = conn.prepareCall( "spGetAirportCodes");

When I run the program I get the message Callable objects not supported.

I am using Jdk.1.4.2

Thanks,

Steve

 

[sedj]

Did you try :

PreparedStatement ps = conn.prepareStatement( "call spGetAirportCodes()" );

What is the actual error you get ?

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[obadare]

String sql = "{call getTestData(?, ?)}";
maybe inserting the curly braces may make a difference

 

[obadare]

Does the RDMS support stored procedures?
try
DatabaseMetaData dbmd=connection.getMetaData();
System.out.println("Supports stored procedure: " + dbmd.supportsStoredProcedures());
I tried callable statement
prepareCall("{ <storedprocedure>}"); worked
prepareCall("<storedprocedure>") also worked

 

[steve1rm]

Hello Obadare and Sedi,

If i use the code below I get this error message:

ResultSet airportCodes = null;

try
{
PreparedStatement ps = conn.prepareStatement( "{call spGetAirportCodes()}" );
airportCodes = ps.executeQuery();
}
catch( SQLException sqle )
{
System.err.println( "\nSQLException: \n" );
System.err.println( "SQL State: " + sqle.getSQLState() );
System.err.println( "Message: " + sqle.getMessage() );
}
SQL State: 42000
Message: Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{call spGetAirportCodes()}' at line 1"

When I use the code below, the only change is taking the curly braces out. e.g.

PreparedStatement ps = conn.prepareStatement( "call spGetAirportCodes()" );
airportCodes = ps.executeQuery();

I get this error message:
SQL State: 0A000
Message: PROCEDURE flightdb.spGetAirportCodes can't return a result set in the given context


Thanks for you help in helping,

Steve

 

[sedj]

I think you possibly need to open a CURSOR in the stored procedure, then select into that, and have the cursor as an out parameter (and bind it java side) ...

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[steve1rm]

Hello,

I run that code you gave me.

False was the response. So I think i cannot use stored procedures.

My database I am using is mySQL 5 which has stored procedures. I have used stored procedures in other parts of my program.
Code:
try
{
PreparedStatement cs = conn.prepareStatement("call spInsertNewAirport(?,?,?)");

cs.setString(1, airportCode);
cs.setString(2, airportName);
cs.setString(3, country);

cs.executeUpdate();
}
catch (SQLException sqle)
{
System.err.println("\nSQLException:\n");
System.err.println("SQLState: "+sqle.getSQLState());
System.err.println("Message: "+ sqle.getMessage());
}

This code uses a stored procedure to insert a new record into the database.

Any ideas about this.

Thanks for all your help. I am grateful.

Steve

 

[manirana007]

You have to specify the OUT parameter in procedure. For example in oracle, you would call the procedure as follows:

try
{
stmt = conn.prepareCall("{call spGetAirportCodes(?)}");

stmt.registerOutParameter(1, OracleTypes.CURSOR); //REF CUR
stmt.execute();
rs = ((OracleCallableStatement)stmt).getCursor(1); }
Hope this helps.

 

[steve1rm]

Hello,

Problem resolved.

I updated to connector/j version 5.
CallableStatement cs = conn.prepareCall( "call spGetAirportCodes()" );
airportCodes = cs.executeQuery();

this also works with the curly braces
CallableStatement cs = conn.prepareCall( "{call spGetAirportCodes()}" );
airportCodes = cs.executeQuery();

Just another quick question.

The driver is for connecting to java to mySql. What is the JDBC for? is that the driver that connects to the mysql driver?

Thanks in advance for all your help,

Steve

 

[sedj]

The driver is a JDBC driver ...

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk