Re: CallableStatement problem

From:

"ros" <ros060@gmail.com>

Newsgroups:

comp.lang.java.programmer

Date:

2 Apr 2007 05:40:32 -0700

Message-ID:

<1175517632.355959.220560@e65g2000hsc.googlegroups.com>

On Apr 2, 5:15 am, Tom Hawtin <use...@tackline.plus.com> wrote:

ros wrote:

// Create CallableStatement object
CallableStatement genid = conn.prepareCall("{call
GeneratePcId()}");

Do you not want to execute the statement now you have prepared it?

int number = genid.getInt(1);

Looking at the API docs...

The syntax for a result parameter is

"{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}"

Also note

"If used, the result parameter must be registered as an OUT parameter."

But this generates an error. Basically the GeneratePcId() stored

You wouldn't want to quote the error, would you?

PROCEDURE `InsertPrice`(pc_id INT, pc_level INT, price INT, from_date
DATE, to_date DATE)
BEGIN
INSERT INTO price VALUES (pc_id, pc_level, price, from_date, to_date);
END $$

Stored procedure syntax is vendor-specific. You've not told us which
product you are using.

Tom Hawtin

Thanks for the reply Tom. And thanks for pointing out that I forgot
the execute statement. I was thinking that you gave me the solution
until I tried running the code.

The error that I get now is:

init:
deps-jar:
compile-single:
run-single:
Connecting to the database...
java.sql.SQLException: No output parameters registered.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
910)
        at
com.mysql.jdbc.CallableStatement.getOutputParameters(CallableStatement.java:
1426)
        at
com.mysql.jdbc.CallableStatement.getInt(CallableStatement.java:1247)
        at
CallableStatementExercise.query(CallableStatementExercise.java:34)
        at
CallableStatementExercise.main(CallableStatementExercise.java:12)
BUILD SUCCESSFUL (total time: 0 seconds)

And it points to "int number = genid.getInt(1);".

The code is here:
            // Create CallableStatement object
            CallableStatement genid = conn.prepareCall("{call
GeneratePcId()}");
            genid.execute();
            int number = genid.getInt(1);
            CallableStatement cstmt = conn.prepareCall("{call
InsertPrice (?, ?, ?, ?, ?)}");

            // Bind values to the parameters
            cstmt.setInt(1, number );
            cstmt.setInt(2, 8);
            cstmt.setInt(3, 600);
            cstmt.setDate(4, Date.valueOf("2008-01-01"));
            cstmt.setDate(5, Date.valueOf("2008-02-02"));

            // Execute the query
            cstmt.execute();

Thanks again.
ros