Derby
  1. Derby
  2. DERBY-925

Implement new JDBC 4 metadata API getFunctionParameters()

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: JDBC
    • Labels:
      None
    • Environment:
      JDK 1.6

      Description

      I am currently implementing this to return an empty result set so at least we're compliant, but we should be able to provide real metadata here.

      1. TypePrinter.java
        5 kB
        Rick Hillegas
      2. derby-925.v1.diff
        61 kB
        Dyre Tjeldvoll
      3. derby-925.v1.stat
        3 kB
        Dyre Tjeldvoll
      4. derbyall_report.txt
        4 kB
        Dyre Tjeldvoll

        Issue Links

          Activity

          Hide
          Dyre Tjeldvoll added a comment -

          As far as I can tell it is not possible to obtain all the necessary information from Derby's existing system tables. The getProcedureColumns() method needs much of the same information, but solves the problem by joining SYSALIASES and SYSSCHEMAS with a the VTI GetProcedureColumns.java.

          I think it will be difficult to extend it so that it works for getFunctionParameters() as well, but I think should be relatively easy to implement a new VTI along the same lines (with some new columns, and a row for the return value).

          Show
          Dyre Tjeldvoll added a comment - As far as I can tell it is not possible to obtain all the necessary information from Derby's existing system tables. The getProcedureColumns() method needs much of the same information, but solves the problem by joining SYSALIASES and SYSSCHEMAS with a the VTI GetProcedureColumns.java. I think it will be difficult to extend it so that it works for getFunctionParameters() as well, but I think should be relatively easy to implement a new VTI along the same lines (with some new columns, and a row for the return value).
          Hide
          Dyre Tjeldvoll added a comment -

          I have some questions about the columns in the resultset that is returned from this method.

          Abbreviations:
          GFP=DatabaseMetaData.getFunctionParameters()
          GPC=DatabaseMetaData.getProcedureColumns()

          Column (3, PARAMETER_NAME): Will this be NULL in the return value row?

          Column (8, PRECISION): GPC returns non-NULL for all SQL types. Should it return NULL for some types?

          Column (9, LENGTH): GPC uses TypeDescriptor.getMaximumWidthInBytes(). When running in ij, this returns 256 for VARCHAR(128), and it looks like it will return 256 for CLOB and BLOB. That does not seem right to me...

          Column (11, RADIX): GPC returns 10 for all types, except REAL, FLOAT and DOUBLE. Should it not be NULL for some types, e.g. VARCHAR, CLOB?

          Column (12, NULLABLE):
          a) GPC always returns procedureNullable. Should GFP return functionNullable for all types? (TypeDescriptor.isNullable() exists but is not used)
          b) Should this column be NULL for the return value row?

          Column (14, CHAR_OCTET_LENGTH) a) How can we identify the char/binary types in Derby? Can we use DataTypeDescriptor.isBinary() and isCharacter()?
          b) How to get the true max for the type? (Don't think getMaximumWidthInBytes() will work as it returns maximumWidth for these types.

          Column (16, IS_NULLABLE a) Is this a String equivalent of column 12?
          b) Does YES in quotes mean String s = "YES" or String s = "'YES'"?

          Show
          Dyre Tjeldvoll added a comment - I have some questions about the columns in the resultset that is returned from this method. Abbreviations: GFP=DatabaseMetaData.getFunctionParameters() GPC=DatabaseMetaData.getProcedureColumns() Column (3, PARAMETER_NAME): Will this be NULL in the return value row? Column (8, PRECISION): GPC returns non-NULL for all SQL types. Should it return NULL for some types? Column (9, LENGTH): GPC uses TypeDescriptor.getMaximumWidthInBytes(). When running in ij, this returns 256 for VARCHAR(128), and it looks like it will return 256 for CLOB and BLOB. That does not seem right to me... Column (11, RADIX): GPC returns 10 for all types, except REAL, FLOAT and DOUBLE. Should it not be NULL for some types, e.g. VARCHAR, CLOB? Column (12, NULLABLE): a) GPC always returns procedureNullable. Should GFP return functionNullable for all types? (TypeDescriptor.isNullable() exists but is not used) b) Should this column be NULL for the return value row? Column (14, CHAR_OCTET_LENGTH) a) How can we identify the char/binary types in Derby? Can we use DataTypeDescriptor.isBinary() and isCharacter()? b) How to get the true max for the type? (Don't think getMaximumWidthInBytes() will work as it returns maximumWidth for these types. Column (16, IS_NULLABLE a) Is this a String equivalent of column 12? b) Does YES in quotes mean String s = "YES" or String s = "'YES'"?
          Hide
          Rick Hillegas added a comment -

          Hi Dyre,

          As far as CHAR_OCTET_LENGTH is concerned, I think that DataTypeDescriptor.getTypeId().isStringTypeId() and DataTypeDescriptor.getTypeId().isBitTypeId() will give you what you need. See the attached program.

          Show
          Rick Hillegas added a comment - Hi Dyre, As far as CHAR_OCTET_LENGTH is concerned, I think that DataTypeDescriptor.getTypeId().isStringTypeId() and DataTypeDescriptor.getTypeId().isBitTypeId() will give you what you need. See the attached program.
          Hide
          Dyre Tjeldvoll added a comment -

          I just realized that DatabaseMetaData.getProcedureColumns() has been changed in JDBC 4.0. The result set it returns now conatains a number of new columns and is, in fact, a super set of the columns returned by getFunctionParameters. For JDBC 4.0 it will likely be necessary to extend the existing GetProcedureColumns.java VTI with much of the same information that I was thinking about putting into the new VTI. Perhaps both methods can be implemented with queries against a single VTI? We will probably need a separate getProcedureColumns40 query in metadata.properties to maintain backward compatibility?

          Show
          Dyre Tjeldvoll added a comment - I just realized that DatabaseMetaData.getProcedureColumns() has been changed in JDBC 4.0. The result set it returns now conatains a number of new columns and is, in fact, a super set of the columns returned by getFunctionParameters. For JDBC 4.0 it will likely be necessary to extend the existing GetProcedureColumns.java VTI with much of the same information that I was thinking about putting into the new VTI. Perhaps both methods can be implemented with queries against a single VTI? We will probably need a separate getProcedureColumns40 query in metadata.properties to maintain backward compatibility?
          Hide
          Knut Anders Hatlen added a comment -

          Dyre wrote:

          > I just realized that DatabaseMetaData.getProcedureColumns() has been
          > changed in JDBC 4.0. The result set it returns now conatains a
          > number of new columns and is, in fact, a super set of the columns
          > returned by getFunctionParameters. For JDBC 4.0 it will likely be
          > necessary to extend the existing GetProcedureColumns.java VTI with
          > much of the same information that I was thinking about putting into
          > the new VTI. Perhaps both methods can be implemented with queries
          > against a single VTI? We will probably need a separate
          > getProcedureColumns40 query in metadata.properties to maintain
          > backward compatibility?

          I don't think it is necessary have a separate JDBC4
          implementation. The 13 columns specified by JDBC3 have not changed in
          JDBC4, so I would say that you could just add the seven columns that
          are new in JDBC4 and return the same result regardless of JDBC
          version. I can't see why any application should rely on
          getProcedureColumns() returning a result set with exactly 13
          columns. The current Derby implementation of getProcedureColumns()
          returns 15 columns, so if returning 20 columns would break it, it's
          probably already broken.

          Show
          Knut Anders Hatlen added a comment - Dyre wrote: > I just realized that DatabaseMetaData.getProcedureColumns() has been > changed in JDBC 4.0. The result set it returns now conatains a > number of new columns and is, in fact, a super set of the columns > returned by getFunctionParameters. For JDBC 4.0 it will likely be > necessary to extend the existing GetProcedureColumns.java VTI with > much of the same information that I was thinking about putting into > the new VTI. Perhaps both methods can be implemented with queries > against a single VTI? We will probably need a separate > getProcedureColumns40 query in metadata.properties to maintain > backward compatibility? I don't think it is necessary have a separate JDBC4 implementation. The 13 columns specified by JDBC3 have not changed in JDBC4, so I would say that you could just add the seven columns that are new in JDBC4 and return the same result regardless of JDBC version. I can't see why any application should rely on getProcedureColumns() returning a result set with exactly 13 columns. The current Derby implementation of getProcedureColumns() returns 15 columns, so if returning 20 columns would break it, it's probably already broken.
          Hide
          Dyre Tjeldvoll added a comment -

          I agree, no separate JDBC4 implementation should be necessary. But I was thinking that we could have multiple queries in metadata.properties, one that returns the old result set and another that returns the new one with more columns. That way EmbedDatabaseMetaData40 could use the new query, while the existing implementation could use the old query... maybe this would let us keep most of the master files as they are?

          Show
          Dyre Tjeldvoll added a comment - I agree, no separate JDBC4 implementation should be necessary. But I was thinking that we could have multiple queries in metadata.properties, one that returns the old result set and another that returns the new one with more columns. That way EmbedDatabaseMetaData40 could use the new query, while the existing implementation could use the old query... maybe this would let us keep most of the master files as they are?
          Hide
          Knut Anders Hatlen added a comment -

          Keeping the master files unmodified is not very important. If the output is different with the JDBC 3.0 driver and the JDBC 4.0 driver, you don't have to modify the master files, but you'll have to duplicate them to make derbyall succeed under JVM 1.6.

          Show
          Knut Anders Hatlen added a comment - Keeping the master files unmodified is not very important. If the output is different with the JDBC 3.0 driver and the JDBC 4.0 driver, you don't have to modify the master files, but you'll have to duplicate them to make derbyall succeed under JVM 1.6.
          Hide
          Daniel John Debrunner added a comment -

          Single implementation for 3.0 and 4.0 seems ok to me, changing master files or not should not be a decision factor for implementing somthing.

          Show
          Daniel John Debrunner added a comment - Single implementation for 3.0 and 4.0 seems ok to me, changing master files or not should not be a decision factor for implementing somthing.
          Hide
          Kathey Marsden added a comment -

          I'm interested to know the behaviour

          • if Server is at 10.1 and Client is at 10.2
          • If server and client are at different jvm versions.

          This I guess is a general question about JDBC 4.0.
          What combinations should work and what errors are users going to see if it doesn't work?

          It sounds like the final conclusion was that metadata.properties won't change. If it does in the end need to change, I am curious the behaviour for soft upgrade and downgrade and soft upgrade again. This was handled in old Cloudscape versions by dropping and recreating the SPS's on every version change, but I think that is no longer done in Derby.

          Show
          Kathey Marsden added a comment - I'm interested to know the behaviour if Server is at 10.1 and Client is at 10.2 If server and client are at different jvm versions. This I guess is a general question about JDBC 4.0. What combinations should work and what errors are users going to see if it doesn't work? It sounds like the final conclusion was that metadata.properties won't change. If it does in the end need to change, I am curious the behaviour for soft upgrade and downgrade and soft upgrade again. This was handled in old Cloudscape versions by dropping and recreating the SPS's on every version change, but I think that is no longer done in Derby.
          Hide
          Dyre Tjeldvoll added a comment -

          First of all, metadata.properties WILL have to change for JDBC 4.0, and it already has. David has already added new queries for getFunctions and getFunctionParameters (and others I think), but they only return empty resultsets. For DERBY-924 I changed the query for getFunctions to return the correct values, but I have not created a system procedure that invokes this method so that it can be used in client server mode.

          I guess that means that a JDBC 4.0 client that tries to invoke getFunctions will get a "no such PROCEDURE" error or something, but I have not verified this. I guess this depends on the implementation of DatabaseMetaData in the client?

          I don't know how the jvm version affects this. Presumably it would still be possible to run an old version of Derby in java 1.6, but then one would get a "no such method" exception when attempting to invoke the new methods?

          For getProcdures and getProcedureColumns there should not be any upgrade issues, since they already exist in JDBC 3.0. Note however, that metadata.properties will have to change for these as well. A JDBC 4.0 client should be able to execute these methods against a JDBC 3.0 server without any problems as long as it doesn't attempt to access any of the new columns in the returned result set. If it does, I expect that the conventional "no such column" exception will be thrown (can be avoided by using ResultSetMetaData defensively, I think).

          Show
          Dyre Tjeldvoll added a comment - First of all, metadata.properties WILL have to change for JDBC 4.0, and it already has. David has already added new queries for getFunctions and getFunctionParameters (and others I think), but they only return empty resultsets. For DERBY-924 I changed the query for getFunctions to return the correct values, but I have not created a system procedure that invokes this method so that it can be used in client server mode. I guess that means that a JDBC 4.0 client that tries to invoke getFunctions will get a "no such PROCEDURE" error or something, but I have not verified this. I guess this depends on the implementation of DatabaseMetaData in the client? I don't know how the jvm version affects this. Presumably it would still be possible to run an old version of Derby in java 1.6, but then one would get a "no such method" exception when attempting to invoke the new methods? For getProcdures and getProcedureColumns there should not be any upgrade issues, since they already exist in JDBC 3.0. Note however, that metadata.properties will have to change for these as well. A JDBC 4.0 client should be able to execute these methods against a JDBC 3.0 server without any problems as long as it doesn't attempt to access any of the new columns in the returned result set. If it does, I expect that the conventional "no such column" exception will be thrown (can be avoided by using ResultSetMetaData defensively, I think).
          Hide
          David Van Couvering added a comment -

          The network client currently returns a "Not Implemented" SQLException for these new metadata functions, but that's logged as a bug (DERBY-970).

          Show
          David Van Couvering added a comment - The network client currently returns a "Not Implemented" SQLException for these new metadata functions, but that's logged as a bug ( DERBY-970 ).
          Hide
          Dyre Tjeldvoll added a comment -

          Knut Anders Hatlen wrote in a message to derby-dev:

          getFunctionParameters() and getFunctions() are not implemented (not
          even to throw "Not Implemented") on the client, so you will get an
          AbstractMethodError.

          Show
          Dyre Tjeldvoll added a comment - Knut Anders Hatlen wrote in a message to derby-dev: getFunctionParameters() and getFunctions() are not implemented (not even to throw "Not Implemented") on the client, so you will get an AbstractMethodError.
          Hide
          Dyre Tjeldvoll added a comment -

          Attaching a patch for review. Thanks.

          Show
          Dyre Tjeldvoll added a comment - Attaching a patch for review. Thanks.
          Hide
          Rick Hillegas added a comment -

          I will take a look at this patch and run the tests. Cheers-Rick

          Show
          Rick Hillegas added a comment - I will take a look at this patch and run the tests. Cheers-Rick
          Hide
          Dyre Tjeldvoll added a comment -

          Patch is available.

          Some comments about the patch:
          A lot of the earlier comments on this issue turned out not to be relevant. The query for getFunctionParameters is mostly based on the generated ODBC getProcedureColumns query, which already has all the new columns. I did however have to extend the GetProcedureColumns VTI so that the return value (parameter) is included for functions, and that the new functionParameter* constants are used for functions.

          Show
          Dyre Tjeldvoll added a comment - Patch is available. Some comments about the patch: A lot of the earlier comments on this issue turned out not to be relevant. The query for getFunctionParameters is mostly based on the generated ODBC getProcedureColumns query, which already has all the new columns. I did however have to extend the GetProcedureColumns VTI so that the return value (parameter) is included for functions, and that the new functionParameter* constants are used for functions.
          Hide
          Rick Hillegas added a comment -

          Looks solid thanks to the thoughtful discussion which preceded this patch. JDBC4 tests pass cleanly. Derbyall passes modulo wisconsin noise. Committed at subversion revision 395414.

          Show
          Rick Hillegas added a comment - Looks solid thanks to the thoughtful discussion which preceded this patch. JDBC4 tests pass cleanly. Derbyall passes modulo wisconsin noise. Committed at subversion revision 395414.

            People

            • Assignee:
              Dyre Tjeldvoll
              Reporter:
              David Van Couvering
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development