Finally, jOOQ does have a parser (since jOOQ 3.9) and it is now sophisticated enough to easily handle parsing T-SQL procedure / function signatures. This means that the feature will be implemented in jOOQ 3.11: https://github.com/jOOQ/jOOQ/issues/3686
Cheers, Lukas Am Dienstag, 14. Oktober 2014 11:34:29 UTC+2 schrieb Lukas Eder: > > 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.
