db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Hill <Thomas.K.H...@t-online.de>
Subject Re: creating a SQL function in derby
Date Fri, 14 Aug 2009 20:52:12 GMT
Hi Rick,

thanks for your help once again.

I have now created my first procedure and have paid attention to the link you
provided to guide me regarding argument matching. Even after having read the
documentation multiple times and checking my code again and again, I am stuck
again and can't figure out why I am getting an 'No method was found that matched
the method call void derbyPk.Functions.spGetNextID(int[], java.lang.String) 
error.

This is my procedure definition:

CREATE PROCEDURE spGetNextID(OUT iOut integer, IN vcIn character varying(128))
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  MODIFIES SQL DATA
  EXTERNAL NAME 'derbyPk.Functions.spGetNextID'; 

This is my Java method:
package derbyPk;

import java.sql.*;

public class Functions {

   public static int spGetNextID( int iNextVal[], String vcIDName )
    throws SQLException
   {
    Connection          conn = getDefaultConnection();
    int                 column = 1;
    
    PreparedStatement   ps = Utils.prepare
    (
     conn,
     "select \"NextVal\"\n" +
     "from rte.\"IDs\"\n" +
     "where \"IDName\" = ?\n" 
    );
    
    ps.setString( 1, vcIDName );
    
    ResultSet           rs = ps.executeQuery();

    rs.next();

    iNextVal[0] = rs.getInt( column++ ) + 1;

     return iNextVal[0];
}
	/*
	<p>
	Get the default connection, called from inside the database engine.
	</p>
	*/
	
	static Connection getDefaultConnection()
	 throws SQLException
	 {
	return DriverManager.getConnection("jdbc:default:connection");
		}

}

and this is the application code to call the procedure:

public static void main(String[] args)
   throws SQLException {
   // TODO Auto-generated method stub

   String driver = "org.apache.derby.jdbc.EmbeddedDriver";
   try {
        Class.forName(driver); 
	} catch(java.lang.ClassNotFoundException e) {
	}
	String dbName = "pmsdev";
	String connectionURL = "jdbc:derby:" + dbName + ";create=true";
	try {
    
    Connection conn = DriverManager.getConnection(connectionURL);
    System.out.println("... Connection successful");
    
    String vcIDName = "Transaction";
    CallableStatement cstmt = conn.prepareCall("{ 
                        call DERBY.spGetNextID(?, ?)}");
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.setString(2, vcIDName);
   
	cstmt.executeUpdate();
	int retVal = cstmt.getInt(1);
	System.out.println(retVal);
		
	}  catch (Throwable e)  {   
		System.out.println(e);
	}
	

}

}

Seems I need to call the procedure fully qualified as 'DERBY.spGetNextID' as
otherwise I am getting an error that no procedure or funtion was found.

Hope you can easily spot what I am unable to find.....

Regards
Thomas


Mime
View raw message