some PROCEDURE issues and one request regarding the Objects tree/panel

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

some PROCEDURE issues and one request regarding the Objects tree/panel

martin van dinther
Dear SQuirreL developers,

I have found some PROCEDURE issues and one request regarding the Objects
tree/panel:

Issue-1:
Based on the boolean return value of call
DatabaseMetaData.supportsStoredProcedures() SQuirreL decides whether the
node PROCEDURE should be shown in the Objects tree pane.
See:
https://sourceforge.net/p/squirrel-sql/git/ci/4864dc6f625ac1d0fb3cd25f0d689d9d4070f945/tree/sql12/core/src/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaData.java
and its ISQLDatabaseMetaData.java

However the JDBC method DatabaseMetaData.supportsStoredProcedures()
retrieves and returns whether this database supports stored procedure
calls THAT USE THE stored procedure ESCAPE SYNTAX. Not whether it
supports creation and calling of stored procedures!
See:
https://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#supportsStoredProcedures()

So a JDBC driver (and RDBMS) could support stored procedures but NOT
support the stored procedure escape syntax. It will return false for the
method DatabaseMetaData.supportsStoredProcedures().
In that case you should still show the PROCEDURE node in the Objects
tree pane.
Could you please improve this in SQuirreL, by removing the test on
squirrel_sql.fw.sql.SQLDatabaseMetaData.supportsStoredProcedures() value
when deciding whether the PROCEDURE node needs to be shown in the
Objects tree or not?

Also correct the incorrect text in interface
net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData for boolean
supportsStoredProcedures():
     * Retrieves whether this DBMS supports stored procedures. Cached on
first
     * call.
     *
     * @return  <TT>true</TT> if DBMS supports stored procedures.
and improve class
net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData by not
overriding the values of Postgress and Netezza. They both probably
support stored procedures, but not the stored procedure escape syntax,
hence they correctly return false.


Issue-2:
Currently the procedures shown in the PROCEDURE node in the Objects tree
pane do not show the overloaded procedures, i.e. which have the same
name but different arguments. Only one entry is shown in the tree for an
overloaded procedure name. This should be corrected by showing all (as
returned by getProcedures()) procedure names. This will lead to
duplicate name entries in the tree-view. You can and should make this
unique by appending the value of the column SPECIFIC_NAME to the entry
name in the tree view or show a subtree of the overloaded variants
(using SPECIFIC_NAME values as entry name) for each procedure if it has
more than one overloaded procedure.
>From JDBC doc: SPECIFIC_NAME String => The name which uniquely
identifies this procedure within its schema.
See:
https://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getProcedures(java.lang.String,%20java.lang.String,%20java.lang.String)

Issue-3:
To view (in the Columns tab) the arguments of a selected procedure, you
will currently get all arguments of all overloaded procedures with the
same procedure name. This is the case in MonetDB which allows and stores
overloaded procedures (and functions) separately in the data dictiornary.
To show only the arguments/columns of a specific selected overloaded
procedure you will need to also filter the rows returned by
getProcedureColumns() to match the value of column SPECIFIC_NAME with
that of the value of SPECIFIC_NAME returned by getProcedures(), besides
filtering on the same catalog, schema, procedure name.
>From JDBC doc: SPECIFIC_NAME String => the name which uniquely
identifies this procedure within its schema.
See:
https://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getProcedureColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

FYI: In Latest MonetDB JDBC driver monetdb-jdbc-2.23.jar
(https://www.monetdb.org/downloads/Java/Jun2016-SP1/) this has been
implemented. The SPECIFIC_NAME columns now return the internal id of the
procedure such that any generic JDBC client programs can match the
correct arguments to a specific overloaded procedure as described by
JDBC API.
Note that in MonetDB the SPECIFIC_NAME contains only a unique id number,
not the name of the procedure or its complete signature. So it is wise
to show in the Objects tree both the PROCEDURE_NAME and the
SPECIFIC_NAME in the tree view entries. Structurally the SPECIFIC_NAME
is a subtree of the PROCEDURE_NAME entry, so if that could be
implemented it would be even better (more user friendly) for overloaded
procedures.


Request:
Besides SQL stored procedures, RDBMS also support SQL stored functions,
also called User Defined Functions (UDF).
These can be retrieved via methods DatabaseMetaData.getFunctions() and
DatabaseMetaData.getFunctionColumns() which have been introduced in Java
1.6.
See:
https://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getFunctions(java.lang.String,%20java.lang.String,%20java.lang.String)

For drivers which comply to JDBC 3.0 or higher (so getJDBCMajorVersion()
>= 3), could you add a FUNCTION node in the Objects tree pane and call
these methods, similar as done for Procedures and ProcedureColumns?
When implementing this new functionality please make sure overloaded
functions are correctly shown and accessable by including the value of
the SPECIFIC_NAME to avoid similar issues as explained above in issue-2
and issue-3.
>From JDBC doc: SPECIFIC_NAME String => the name which uniquely
identifies this function within its schema. This is a user specified, or
DBMS generated, name that may be different then the FUNCTION_NAME for
example with overload functions.


Note: as of Java 1.6 there is a new method
DatabaseMetaData.supportsStoredFunctionsUsingCallSyntax().
This returns whether this database supports invoking user-defined or
vendor functions using the stored procedure escape syntax.
See:
https://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#supportsStoredFunctionsUsingCallSyntax()
Similar to DatabaseMetaData.supportsStoredProcedures() this should NOT
be used to determine whether a JDBC driver and RDBMS supports Stored
Functions or UDFs at all, only to retrieve whether the escape syntax {fn
<function call> } is supported.


Hope this helps in improving SQuirreL.

Kind Regards,
Martin van Dinther


------------------------------------------------------------------------------
_______________________________________________
Squirrel-sql-develop mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Loading...