db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: jdbc metadata getProcedures does not return newly created proc/funcs
Date Fri, 07 Sep 2007 15:59:50 GMT
Hi Mike,

Try passing in null as the catalog and schema arguments. The javadoc for 
DatabaseMetaData.getProcedures() is a little vague about this.

Hope this helps,
-Rick

Mike Norman wrote:
> latest Derby:
>   Apache Derby
>   10.3.1.4 - (561794)
>   Apache Derby Network Client JDBC Driver
>   10.1.2.1
>
> I created 2 very simple stored proc/funcs:
>
> package test;
>
> import java.sql.SQLException;
>
> public class SPHelper {
>
>     public static void ProcNoArgs() throws SQLException {
>     }
>    
>     public static String FuncNoArgs() throws SQLException {
>         return "gidday";
>     }
> } 
>
> CREATE PROCEDURE PROC_NO_ARGS() 
> LANGUAGE JAVA 
> EXTERNAL NAME 'test.SPHelper.ProcNoArgs' 
> PARAMETER STYLE JAVA
> NO SQL;
> CREATE FUNCTION FUNC_NO_ARGS() RETURNS VARCHAR(100)
> LANGUAGE JAVA
> EXTERNAL NAME 'test.SPHelper.FuncNoArgs'
> PARAMETER STYLE JAVA
> NO SQL
> RETURNS NULL ON NULL INPUT;
>
> Now, when I try to get the metadata for them, nothing is returned:
>
> String catalogPattern = "";
> String schemaPattern = "%";
> String procedurePattern = "%NO_ARGS";
> List<DbStoredProcedure> tmpProcs
> try {
>     DatabaseMetaData databaseMetaData = getDatabaseMetaData(connection);
>     procsInfo = databaseMetaData.getProcedures(catalogPattern,
> schemaPattern, procedurePattern);
>     // did we get a hit?
>     if (procsInfo != null) {
> 	tmpProcs = new ArrayList<DbStoredProcedure>();
> 	while (procsInfo.next()) {
> 	    String actualCatalogName = procsInfo.getString(PROCS_INFO_CATALOG); //
> index 1
> 	    String actualSchemaName = procsInfo.getString(PROCS_INFO_SCHEMA); //
> index 2
> 	    String actualProcedureName = procsInfo.getString(PROCS_INFO_NAME); //
> index 3
> 	    short procedureType = procsInfo.getShort(PROCS_INFO_TYPE); // index 8
> 	    DbStoredProcedure dbStoredProcedure;
> 	    if (procedureType == java.sql.DatabaseMetaData.procedureReturnsResult)
> {
> 		dbStoredProcedure = new DbStoredFunction(actualProcedureName);
> 	    }
> 	    else {
> 		dbStoredProcedure = new DbStoredProcedure(actualProcedureName);
> 	    }
> 	    if (actualCatalogName != null && actualCatalogName.length() > 0) {
> 		dbStoredProcedure.setCatalog(actualCatalogName);
> 	    }
> 	    if (actualSchemaName != null && actualSchemaName.length() > 0) {
> 		dbStoredProcedure.setSchema(actualSchemaName);
> 	    }
> 	    tmpProcs.add(dbStoredProcedure);
> 	}
> 	procsInfo.close();
>     }
> catch (SQLException sqlException) {
>     throw new IllegalStateException("failure retrieving Stored Procedure
> metadata",
> 	sqlException);
> }
>
> No exceptions are thrown but the list is empty - an empty result set was
> returned.
>
> Any ideas?
>
> Thanks in advance,
>
> Mike
>   


Mime
View raw message