Hi Steve, Thanks for your enquiry. There's no particular reason why this should only be supported for Oracle. It's just that we haven't had any specific requests for other databases yet.
Indeed, unfortunately, the sys.parameters.has_default_value column is only populated for CLR procedures, not for Transact-SQL procedures, as it seems: - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d967730-2a29-412b-852a-7e5c2cec46f5/hasdefaultvalue-in-sql-server-2005-sysparameters-isnt-set-to-true-if-default-value-is-null-how Of course, it's a good idea to add another vote to this feature here. Maybe we'll see it in a future SQL Server version: - https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=234143 As far as parsing goes, we already do some parsing in other databases where the dictionary views don't suffice, so I suspect that we can live with this hack. I've registered a feature request for this: https://github.com/jOOQ/jOOQ/issues/3686 If you don't mind, I'd like to go through this together with you, as you probably have a couple of syntax corner cases in your database that we won't think of. The procedure definition syntax is documented here: - http://msdn.microsoft.com/en-us/library/ms186755.aspx (CREATE FUNCTION) - http://msdn.microsoft.com/en-us/library/ms187926.aspx (CREATE PROCEDURE) So, we have something like this syntax: CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS ... And... CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS A couple of remarks: - Even if the syntax for procedures omits parentheses to wrap parameters, they are allowed (and thus optional) - Parameters can be clearly identified by their leading @ signs - Defaulted parameters can be identified by an equal sign - The last parameter is followed by: o a closing parenthesis in functions followed by the RETURNS keyword. Data types can also have parentheses, though o any of these keywords: WITH, FOR, AS possibly preceded by a closing parenthesis. o since "default" can be a VARCHAR value, we must probably parse the values to ensure that we don't capture closing parenthesis or terminating keywords that are contained inside of those default values Do you see any edge cases that fall outside of the remarks mentioned above? 2014-10-13 17:19 GMT+02:00 <[email protected]>: > In the meantime - I'm not entirely sure why AbstractRoutine#addInParameter > defaults the values to null. Maybe it could default to something that you > could detect later and chose to just omit that parameter so that the > database would use its default? > The problem here is that JDBC's support for named parameters is not very sophisticated. While it is possible to specify named parameters on a CallableStatement, from how I see it, it's not possible to declare such named parameters in the SQL string, neither using the JDBC escape syntax { call my_procedure(?, ?, ?) }, nor if generating Transact-SQL. With indexed parameters, defaults are not really possible, because all bind variables need to be bound to a value, and unfortunately there is no "DEFAULT" binding as far as I can tell...? Defaulting to using named parameters with generated Transact-SQL (e.g. "exec my_procedure @non_default = 1") might not be a good idea either. We've had customers in the past who were relying on the fact that indexed parameters were used, i.e. they were free to tweak parameter names... -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
