Announcement Announcement Module
Collapse
No announcement yet.
Informix stored procedure & spring Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Informix stored procedure & spring

    Hello everybody...

    This is a very painfull issue for me...it's been unresolved for a few days now and i could use some help... Here is my code:


    RowMapper:

    public class CustomRowMapper implements RowMapper {


    public Object mapRow(ResultSet arg0, int arg1) throws SQLException {

    Map data = new HashMap(1);
    Object str = "";
    while (arg0.next()) {
    str = arg0.getObject(1);
    }
    data.put("id",str);
    return data;

    }

    }



    Stored proc. code and testing code:

    public class TestStoredProcedure {
    public static void main(String[] args) {
    TestStoredProcedure t = new TestStoredProcedure();
    t.test();
    System.out.println("Done!");
    }

    void test() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName("com.informix.jdbc.IfxDriver ");
    ds.setUrl("jdbc:informix-sqli://server:1526/db:informixserver=serverInstance");
    ds.setUsername("user");
    ds.setPassword("pass");
    MyStoredProcedure sproc = new MyStoredProcedure(ds);
    Map res = sproc.execute();
    printMap(res);
    }

    private class MyStoredProcedure extends StoredProcedure {
    public static final String SQL = "testiranje";

    public MyStoredProcedure(DataSource ds) {
    setDataSource(ds);
    setFunction(false);
    setSql(SQL);
    declareParameter(new SqlParameter("name", Types.VARCHAR));
    SqlReturnResultSet set = new SqlReturnResultSet("id", new CustomRowMapper());

    declareParameter(set);
    compile();
    }

    public Map execute() {
    Map params = new HashMap();
    params.put("name", "someName");
    Map out = execute(params);

    return out;
    }
    }

    private static void printMap(Map r) {
    Iterator i = r.entrySet().iterator();
    while (i.hasNext()) {
    System.out.println((String)i.next().toString());
    }
    }
    }

    procedure code:

    CREATE PROCEDURE testiranje
    (p_test VARCHAR(50))
    RETURNING VARCHAR(50);
    DEFINE p_return VARCHAR(50);
    LET p_return = p_test;
    RETURN p_return;
    END PROCEDURE;

    When i run the java code, i get:
    "WARNING: ResultSet returned from stored procedure but a corresponding SqlReturnResultSet parameter was not declared"


    First I tried to call this procedure with
    declareParameter(new SqlOutParameter("returned", Types.VARCHAR));

    instead of SqlReturnResultSet, but the test code crashed with "the routine testiranje cannot be resolved" ->this happened because informix returns a result set not a bunch of param values..

    Help please..I'm stuck..

  • #2
    a RowMapper gets called once for each row in the resultset and calls ResultSet.next() itself. So because are doing it in the rowmapper, it confuses the code.

    To return a List of several HashMaps you would:
    Map data = new HashMap(1);
    String str = arg0.getString(1);
    data.put("id", str);
    return data;

    If you wanted to put all of the values in one HashMap, and use your own while(arg0.next() loop, you would need to use a ResultSetExtractor

    Comment


    • #3
      didn't do the trick..still the same thing....when i try a simple jdbc procedure call, it returns a resultset with a single string - as it should..

      Comment


      • #4
        Have you tried treating your procedure like a function? setFunction(true); and declaring an outparameter before the in parameter? Like this:

        Code:
        public MyStoredProcedure(DataSource ds) {
          setDataSource(ds);
          setFunction(true);
          setSql(SQL);
          declareParameter(new SqlOutParameter("id", Types.VARCHAR));
          declareParameter(new SqlParameter("name", Types.VARCHAR));
          compile();
        }

        Comment


        • #5
          Tried it,didn't work,all i get is a different error....This is the code now:
          public MyStoredProcedure(DataSource ds) {
          setDataSource(ds);
          setFunction(true);
          setSql(SQL);
          declareParameter(new SqlOutParameter("id", Types.VARCHAR ));
          declareParameter(new SqlParameter("naziv", Types.VARCHAR));

          compile();
          }

          And the procedure:
          CREATE PROCEDURE testiranje
          (p_test VARCHAR(50))
          RETURNING VARCHAR(50);
          DEFINE p_return VARCHAR(50);
          LET p_return = p_test;
          RETURN p_return;
          END PROCEDURE;


          even tried
          CREATE FUNCTION testiranje
          (p_test VARCHAR(50))
          RETURNING VARCHAR(50);
          DEFINE p_return VARCHAR(50);
          LET p_return = p_test;
          RETURN p_return;
          END FUNCTION;

          but it resulted in the same thing:
          org.springframework.jdbc.UncategorizedSQLException : (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call testiranje1(?)}]: params=[{naziv=JovanDrljaca}]]): encountered SQLException [Function doesn't have an output parameter or the out parameter isn't returned.]; nested exception is java.sql.SQLException: Function doesn't have an output parameter or the out parameter isn't returned.
          java.sql.SQLException: Function doesn't have an output parameter or the out parameter isn't returned.

          By the way, thanks for your ideas

          Comment


          • #6
            Hello everyone...mannaged to work it out...:
            declareParameter(new SqlReturnResultSet("p_return", new RowMapper()
            {

            public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
            Integer i = new Integer(999);

            Map map = new HashMap(10);

            i = new Integer(arg0.getInt(1));

            map.put("1",i);


            return map;
            }

            }));

            this has to be before the declaration of in parameters...
            works fine now

            Comment


            • #7
              Was with the setFunction as true or false?

              Comment


              • #8
                false...i use a informix function, but i have to treat it as a procedure.

                Comment


                • #9
                  I have also tried your code and it appears to work but I receive an additional exception saying "java.sql.SQLException: Method can be called only once."

                  I know its working because I print what is being inserted into the Map inside mapRow.

                  I am using an Informix function that returns a single result, is this code expecting a result set?

                  Thanks

                  Comment

                  Working...
                  X