Re: How to get Script of a stored procedure

[Rick Hillegas]

On 11/22/11 11:21 PM, gopi krishna wrote:
Hi
  This is Krishna , i need to edit an existing stored procedure from 
one of the client. So i need to get script of that stored 
procudure.How can i get that.
Please can you help me here

Thanks
Krishna
Hi Krishna,

Derby stored procedures are just public static methods in user-supplied 
classes. The following code will show you what static method is bound to 
the procedure name you're interested in. Here's how you run this program:

java z $connectionURL $schemaName $procedureName

e.g.:

java z jdbc:derby:db APP P

Here is the program:

import java.sql.*;

import org.apache.derby.catalog.types.MethodAliasInfo;

public  class   z
{
    public  static  void    main( String[] args ) throws Exception
    {
        String  connectionURL = args[ 0 ];
        String  schemaName = args[ 1 ];
        String  procedureName = args[ 2 ];

        Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
        Class.forName( "org.apache.derby.jdbc.ClientDriver" );
        Connection  conn = DriverManager.getConnection( connectionURL );

        PreparedStatement ps = conn.prepareStatement
            (
             "select javaclassname, aliasinfo\n" +
             "from sys.sysaliases a, sys.sysschemas s\n" +
             "where s.schemaid = a.schemaid\n" +
             "and s.schemaname = ?\n" +
             "and a.alias = ?\n"
             );
        ps.setString( 1, schemaName );
        ps.setString( 2, procedureName );
        ResultSet   rs = ps.executeQuery();

        rs.next();
        String  className = rs.getString( 1 );
        String  methodName = ((MethodAliasInfo) rs.getObject( 2 
)).getMethodName();

        System.out.println( className + "." + methodName );
    }
}

Hope this helps,
-Rick