store procedure to return a result set [* Resolved *]

[steve_rm]

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

[CornedBee]

What exactly is the problem?

What database system?

[steve_rm]

Hello,

Thanks for your reply, the problem is that it doesn't work.

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

I think the above code has a problem.

I tried using a callable object but an error message came back saying callable objects are not supported.

Thnaks,

Steve

[CornedBee]

Thanks for your reply, the problem is that it doesn't work.

*banghead*

In what way does it not work? Does it throw an exception? Return incorrect results? Do nothing at all? Do something incorrect?

Be specific, man, we're not psychic here!

[steve_rm]

Hello Cornedbee,

Sorry for not been more specific. I am a .Net programmer and been busy with some other projects. I am new to Java so just need some help with this problem.

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

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

I did try using a callable object but got a error message saying callable objects not supported.

The database I am using is mySQL 5 and SQLyog for the interface. I written my stored procedures and they run within mySQL without errors, So I think it could be a java coding problem.

Thanks for helping,

Steve

[CornedBee]

Use executeUpdate() instead of executeQuery(). executeQuery() can only be used with queries that return a result, such as SELECT queries or procedures with a return value.

Or upgrade your Connector/J and use CallableStatement. I believe the newest Connector/J (you might need to download a development version) supports it when connected to a MySQL 5.x server.


Edit: Hmm ... apparently your procedure does a select query. Let me look something up ...

Edit2: OK, this is rather confusing. The MySQL docs claim that procedures can't return result sets as such, and that you have to use OUT parameters for that. A user comment in the documentation [1] suggests that you can. It might be, of course, that Connector/J cannot handle this apparently undocumented feature.
Try upgrading Connector/J. It seems the overall safest bet.

[1] http://dev.mysql.com/doc/refman/5.0/...rocedures.html

[steve_rm]

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

[CornedBee]

JDBC, the Java Database Connectivity (I think), is the standard API for communicating with databases from Java. Java database drivers such as Connector/J, more commonly called JDBC drivers, implement this API.
The standard allows you to, SQL dialect issues aside, communicate with all databases in the same manner.