Wrong kotlin code generated for SQL Server stored procedures

[thomasobrien99]

Expected behavior

Generated Routines for stored procedures compile

Actual behavior

Generated Routines for stored procedures can fail compilation with: Unresolved reference: syntheticReturnParameter

Steps to reproduce the problem

I'm thinking that this is a bug rather than a configuration issue, but if it's not feel free to close/re-direct me to stack overflow.

Running generate*Jooq is creating Routine files with:

...
init {
        syntheticReturnParameter = RETURN_VALUE
        addInParameter(Foobar)
        ...
    }
...

where syntheticReturnParameter is an unresolved reference:

• If the problem relates to code generation, please post your code generation configuration:

This using the gradle jooq plugin: id("nu.studer.jooq") version "7.1.1"

create("ourgeneratedcode") {
      generateSchemaSourceOnCompilation.set(false)

      jooqConfiguration.apply {
        logging = Logging.INFO
        generator.apply {
          name = "org.jooq.codegen.KotlinGenerator"
          strategy.apply {
            name = "org.jooq.codegen.KeepNamesGeneratorStrategy"
          }
          database.apply {
            name = "org.jooq.meta.sqlserver.SQLServerDatabase"
            inputCatalog = "experiment"
            schemata.addAll(
              arrayOf(
                org.jooq.meta.jaxb.SchemaMappingType()
                  .withInputSchema("dbo"),
                org.jooq.meta.jaxb.SchemaMappingType()
                  .withInputSchema("otherSchema"),
              ),
            )
            isIncludeIndexes = false
            properties.add(
              Property().apply {
                key = "locations"
                value = "src/main/resources/ourMigrations"
              },
            )
          generate.apply {
            isDeprecated = false
            isRecords = true
            isPojos = true
            isPojosEqualsAndHashCode = false
          }
          target.apply {
            packageName = "com.ourPackageName"
            directory =
              "src/main/kotlin/generated/ourGeneratedCodeLocation"
          }
        }
      }
    }

Versions

• jOOQ: 3.16.4
• Java: 17
• Database (include vendor): SQL Server
• OS: iOS 12.4
• JDBC Driver (include name if unofficial driver): com.microsoft.sqlserver.jdbc.SQLServerDriver

[lukaseder]

Thanks a lot for your report. To exclude anything specific to this particular stored procedure of yours, can you please post the procedure definition? (The body isn't needed, only the signature)

[thomasobrien99]

I'm not very familiar with stored procedures in SQL Server, lmk if you need more than this:

CREATE PROCEDURE [ourSchema].[GetValues]
  @PlanYear varchar(8),
  @SubscriberID varchar(9),
  @MemberSeq varchar(2),
  @PlanID varchar(20),
  @GroupID	varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @BenefitCode varchar(10)
SET @BenefitCode	= NULL

SELECT	*
...

[lukaseder]

Sorry for the delay in this. I'll look into this right away now.

[lukaseder]

The problem is that syntheticReturnParameter doesn't work like a property as there isn't any getter, only a setter. This should be an easy fix. The generated code should produce:

setSyntheticReturnParameter(RETURN_VALUE)

[lukaseder]

Fixed in jOOQ 3.18.0, 3.17.3 (#13864), and 3.16.9 (#13865)

[lukaseder]

The workaround is to use the JavaGenerator, at least for generated routines.