Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    3

    Unanswered: Problem with java store procedure Data Studio DB2

    Hello I have a problema with a Java stored procedure developed with IBM Data Studio 4.1. This stores prcedure work with two tables, Ítems and Products. If the ítem dont exists, the stores procedure check it and will insert the new ítem. After this, insert the new product. The store procedured have a 3 parametres , producto, preio y rubro. My problem with this is that result any insert in none table, Ítems or products.None row is inserted in the tables. This follow is the code in Java:

    package com.Sergio23.Sergio23;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    public class insertProd{
    public static void x_insertProd(java.lang.String producto, double precio,

    java.lang.String rubro, ResultSet[] rs1) throws SQLException, Exception {

    if (producto.trim().length() < 2)

    throw new Exception("Descripción Producto debe ser mayor a 1");



    if (rubro.trim().length() < 2)

    throw new Exception("Descripción Rubro debe ser mayor a 1");




    Connection con = DriverManager.getConnection("jdbc:default:connecti on");

    PreparedStatement stmt = null;

    String sql;

    ResultSet rs = null;

    // El Producto existe?

    sql = "SELECT * FROM SERGIO23.PRODUCTOS WHERE descripcion=?";

    stmt = con.prepareStatement(sql);

    stmt.setString(1, producto);

    rs = stmt.executeQuery();

    if (rs.next())

    throw new Exception("Ya existe: " + producto);

    rs.close();

    // Chequea y asegura rubro

    int idRubro=0;

    sql = "SELECT idRubro FROM SERGIO23.RUBROS WHERE rubro=?";

    stmt = con.prepareStatement(sql);

    stmt.setString(1, rubro);

    rs = stmt.executeQuery();

    if (rs.next()) {

    idRubro = rs.getInt(1);

    rs.close();

    } else {

    rs.close();


    sql = "SELECT idRubro FROM FINAL TABLE (INSERT INTO SERGIO23.RUBROS (idRubro, rubro) VALUES (NEXT VALUE FOR codigoRubro,?))";

    stmt = con.prepareStatement(sql);

    stmt.setString(1, rubro);

    rs = stmt.executeQuery();

    rs.next();

    idRubro=rs.getInt(1);

    rs.close();

    }
    sql = "SELECT * FROM FINAL TABLE (INSERT INTO SERGIO23.PRODUCTOS (idProducto, precioEstandard, toleranciaLote, idrubro,descripcion) VALUES (NEXT VALUE FOR codigoProducto,?,?,NULL,?))";

    stmt = con.prepareStatement(sql);

    stmt.setString(1, producto);

    stmt.setDouble(2, precio);

    stmt.setInt(2, idRubro);

    rs1[0] = stmt.executeQuery();

    }}

    CREATE PROCEDURE insertProd (IN PRODUCTO VARCHAR(50),

    IN PRECIO DOUBLE,

    IN RUBRO VARCHAR(50))
    DYNAMIC RESULT SETS 1
    NOT DETERMINISTIC
    LANGUAGE Java
    EXTERNAL NAME 'DS_20150301045239:com.Sergio23.Sergio23.insertPro d.x_insertProd'
    FENCED
    THREADSAFE
    PARAMETER STYLE JAVA

    And the result is

    Run: SERGIO23.INSERTPROD(VARCHAR(50), DOUBLE, VARCHAR(50))

    {call SERGIO23.INSERTPROD(?,?,?)}

    Run of routine completed successfully.
    Query execution time => 62 ms


    Please help me with this issue thanks¡¡¡¡¡¡

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Your parameter-markers need to match the parameter-numbers when you bind values; also always use an exception handler.
    If you use host-variables and set*AtName() methods for binding, then the code might be easier to follow - see the IBM examples for details.


    Code:
        
        sql = "select * from FINAL TABLE (INSERT INTO user1.PRODUCTOS (idProducto, precioEstandard, toleranciaLote, idrubro, descripcion) VALUES ( NEXT VALUE FOR codigoProducto , ?, NULL, ?, ?))" ;
        stmt = con.prepareStatement(sql);
        stmt.setDouble(1, precio);
        stmt.setInt(2, idRubro);
        stmt.setString(3, producto);  // or some other variable..depends on your intentions
        rs = stmt.executeQuery();

  3. #3
    Join Date
    Mar 2015
    Posts
    3

    Problem with a stored procedure

    Quote Originally Posted by db2mor View Post
    Your parameter-markers need to match the parameter-numbers when you bind values; also always use an exception handler.
    If you use host-variables and set*AtName() methods for binding, then the code might be easier to follow - see the IBM examples for details.


    Code:
        
        sql = "select * from FINAL TABLE (INSERT INTO user1.PRODUCTOS (idProducto, precioEstandard, toleranciaLote, idrubro, descripcion) VALUES ( NEXT VALUE FOR codigoProducto , ?, NULL, ?, ?))" ;
        stmt = con.prepareStatement(sql);
        stmt.setDouble(1, precio);
        stmt.setInt(2, idRubro);
        stmt.setString(3, producto);  // or some other variable..depends on your intentions
        rs = stmt.executeQuery();

    I changed the parameter-marked but the result is the same, the row is not inserted. I drop the procedure and I make again.
    When I save de code java Ibm data studio indicates: " unable to locate suitable method in procedure 'INSERTPROD', method 'x_INSERTPROD'.
    To ensure that DDL is in sync with the java source, select the appropiate method below and click Sync. If you want save your changes as is, click Save Only".

    If click save only, now throws an error

    {call SERGIO23.INSERTPROD(?,?,?)}
    Java stored procedure or user-defined function "SERGIO23.INSERTPROD", specific name "SQL150301025652300" could not call Java method "x_INSERTPROD", signature "(Ljava/lang/String".. SQLCODE=-4306, SQLSTATE=42724, DRIVER=3.68.61
    Run of routine failed.
    - Roll back completed successfully.

    If I click Sync the parameters changes in DDL from VARCHAR(30) to CHAR(1).

    It seems the error is in the signature ,but I no find the way to solve it and make it work.

    Kind regards.






    indicates




    indicates

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •