sending parameter to a stored procedure

[steve1rm]

Hello,

I want to use a stored procedure to mysql to insert a new airport.
This is the code I am using, it runs ok but I get an error say that the
syntax in the stored procedure is incorrect. I have tested the stored procedure
in mySql and it worked fine. But doesn't work when l call it from my java program.

This is the java 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());
}

I am using Jcreator to develop this program. I was using the callable object but got a message
saying it was not supported. So I have tried using the preparedStatement instead.

For more information this is the stored procedure in mysql
DELIMITER $$

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

CREATE PROCEDURE `flightdb`.`spInsertNewAirport` (_airportCode varchar(10), _airportName varchar(20), _country varchar(20))


BEGIN
INSERT INTO Airport (AirportCode, AirportName, Country)
VALUES (_airportCode, _airportName, _country);
END$$

DELIMITER ;

Many thanks in advance,

Steve

 

[feherke]

Hi

conn.prepareStatement("{call spInsertNewAirport(?,?,?[red])[/red]}");

Is this exact copy & paste ?

Feherke.

http://rootshell.be/~feherke/

 

[steve1rm]

Hello Feherke,

I have re-entered the code:

conn.prepareStatement("{call spInsertNewAirport(?,?,?)}");

Thanks for noticing the bracket that was missing. However, I am still getting the same error message. See below.

Syntax error or access violation message from Server: syntax near '(call spInsertNewAirport('LHR', 'HEAHTROW','ENGLAND'))' at line 1.

Any ideas?

Thanks in advance,

Steve

 

[steve1rm]

Hello,

Problem solved.

The code should be this:

con.prepareStatement("call spInsertNewAirport(?,?,?)");

Thanks,

Steve