No schemata were loaded... for SQL Server 2012

[gax...@gmail.com]

We are getting the following when trying to generate classes from a SQL Server 2012 database.

I have tested the same gradle task agains a MySQL DB and it works, However, on sqlserver it can't find the schema.

[sts] -----------------------------------------------------

[sts] Starting Gradle build for the following tasks: 

[sts]      generateJooqSqlTypes

[sts] -----------------------------------------------------

test conn, member state is: LA

No schemata were loaded  : Please check your connection settings, and whether your database (and your database version!) is really supported by jOOQ. Also, check the case-sensitivity in your configured <inputSchema/> elements : [LAB_Sandbox]

:generateJooqSqlTypes UP-TO-DATE

BUILD SUCCESSFUL

Total time: 22.15 secs

[sts] -----------------------------------------------------

[sts] Build finished succesfully!

[sts] Time taken: 0 min, 22 sec

[sts] -----------------------------------------------------

build.gradle:

buildscript {

    ext {

generatedSourcesDir = file("src/main/generated")

generatedSourcesPackage = 'com.hsa.paretoetl.domain'

hibernateJpaApiVersion = '1.0.0.Final'

        springBootVersion = '1.2.6.RELEASE'

jodaTimeVersion = '2.8.2'

jadiraVersion = '3.2.0.GA'

jtdsVersion = '1.3.1'

yamlVersion = '1.14'

    }

    repositories {

        mavenCentral()

mavenLocal()

    }

    dependencies {

        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}") 

        classpath 'io.spring.gradle:dependency-management-plugin:0.5.2.RELEASE'

classpath "org.yaml:snakeyaml:${yamlVersion}"

classpath "net.sourceforge.jtds:jtds:${jtdsVersion}"

classpath fileTree(dir: 'libs/JOOQ/JOOQ-lib', include: '*.jar', exclude: 'jooq-scala-*.jar')

    }

}

apply plugin: 'java'

apply plugin: 'groovy'

apply plugin: 'eclipse'

apply plugin: 'idea'

apply plugin: 'spring-boot' 

apply plugin: 'io.spring.dependency-management' 

springBoot {

mainClass = "com.hsa.paretoetl.Application"

}

/*

 * Make java files visible only to the groovy compiler plugin

 */

sourceSets {

main {

java {

srcDirs = []

}

groovy {

srcDirs = ['src/main/java', 'src/main/groovy', generatedSourcesDir]

}

}

test {

java {

srcDirs = []

}

groovy {

srcDirs = ['src/test/java', 'src/test/groovy']

}

}

}

jar {

    baseName = 'pareto-etl'

    version = '0.1'

}

sourceCompatibility = 1.8

targetCompatibility = 1.8

repositories {

    mavenCentral()

}

// Import Spring Boot's bom, spring-boot-dependencies

dependencyManagement {

imports {

mavenBom "org.springframework.boot:spring-boot-dependencies:${springBootVersion}"

}

}

ext['spring-data-releasetrain.version'] = 'Gosling-RELEASE'

dependencies {

    compile 'org.springframework.boot:spring-boot-starter-data-jpa'

    compile 'org.springframework.boot:spring-boot-starter-jdbc'

    compile "org.springframework.boot:spring-boot-configuration-processor"

    compile 'org.codehaus.groovy:groovy-all'

compile 'org.apache.commons:commons-lang3:3.4'

compile 'org.bgee.log4jdbc-log4j2:log4jdbc-log4j2-jdbc4.1:1.16'

compile "org.jadira.usertype:usertype.core:${jadiraVersion}"

compile "joda-time:joda-time:${jodaTimeVersion}"

compile 'joda-time:joda-time-hibernate:1.4'

compile 'javax.inject:javax.inject:1'

compile 'javax.el:el-api:2.2'

compile 'org.hibernate:hibernate-validator:5.2.1.Final'

compile fileTree(dir: 'libs/JOOQ/JOOQ-lib', include: 'jooq-*.jar', exclude: 'jooq-scala-*.jar')

runtime "net.sourceforge.jtds:jtds:${jtdsVersion}"

    testCompile 'org.springframework.boot:spring-boot-starter-test'

}

eclipse {

    classpath {

         containers.remove('org.eclipse.jdt.launching.JRE_CONTAINER')

         containers 'org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8'

    }

}

task generateJooqSqlTypes(group: 'build', description: 'Generate JOOQ SQL types') {

// Use your favourite XML builder to construct the code generation configuration file

// ----------------------------------------------------------------------------------

def writer = new StringWriter()

def xml = new groovy.xml.MarkupBuilder(writer)

.configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.7.0.xsd') {

   generator() {

       database() {

name('org.jooq.util.sqlserver.SQLServerDatabase')

includes('.*')

inputSchema('LAB_Sandbox')

       }

       generate() {

       }

       target() {

           packageName(generatedSourcesPackage)

           directory(generatedSourcesDir)

       }

   }

}

// Run the code generator

// ----------------------

    

    // org.jooq.util.GenerationTool.generate(

        // javax.xml.bind.JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class)

    // )

    // the following complains about SSPI failing to authenticate even though ntlmauth.dll is in the jdk jre /bin folder.

    // use the work-around below to get a successful connection and pass to JOOQ.

def props = file('src/main/resources/application.yml')

def config = new org.yaml.snakeyaml.Yaml().load(props.newReader())

def driver = config.spring.datasource.driver // net.sourceforge.jtds.jdbc.Driver

def url = config.spring.datasource.url     // jdbc:jtds:sqlserver://xxxxx:1433/LAB_Sandbox;domain=true;instance=MyInstance

def sql = groovy.sql.Sql.newInstance(url, driver)

def configuration = javax.xml.bind.JAXB.unmarshal(

new StringReader(writer.toString()), 

org.jooq.util.jaxb.Configuration.class)

// test sql connection

def query = 'select top 1 * Member_Info'

sql.eachRow(query) { println "test conn, member state is: ${it.mbrState}" }

new org.jooq.util.GenerationTool().with {

setConnection(sql.connection)

run(configuration)

}

}

task wrapper(type: Wrapper) {

gradleVersion = '2.7'

}

compileGroovy.dependsOn(processResources)

[Lukas Eder]

Hello,

Thank you very much for your enquiry.

On a quick glance, this looks like (almost) the same question as posted here:

https://groups.google.com/forum/#!topic/jooq-user/LvOLT4HzJjI

Before I look at them both, can you please confirm whether this question is a duplicate?

Best Regards,

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hmm, in fact - no. This question here also contains the message, unlike the other one. I'll reply here, then :)

[Ergels Gaxhaj]

if it was posted by me I think i posted twice by accident. I removed it

Also, I forgot to mention that we are using version 3.7.0.

[Lukas Eder]

There might be a misunderstanding about what jOOQ (or SQL Server / MySQL) considers a schema. I can see the following:

inputSchema('LAB_Sandbox')

...

def url = config.spring.datasource.url     // jdbc:jtds:sqlserver://xxxxx:1433/LAB_Sandbox;domain=true;instance=MyInstance

In MySQL, a database and a schema are essentially the same thing. They're both databases.

In SQL Server (as in most RDBMS), a database is something that can contain several schemas. In your case, LAB_Sandbox seems to be the database, because you've put it in your JDBC URL. That's where SQL Server / jTds expects the database name. However, within a database, there are several schemas. If you didn't explicitly specify the schema, you've probably used the default schema "dbo", which you should put as an inputSchema. Or, you can leave the inputSchema away, which will lead to jOOQ generating all the schemas in the LAB_Sandbox database.

Hope this helps. Let me know if you have any further questions, or if the above didn't do the trick.

Best Regards,

Lukas

[Ergels Gaxhaj]

Man I think i just figured it out based on your reply. I tried the following and it is working now.

I tried 'dbo yesterday when I was working on this and it didn't work. Weird. Anyway, it is generating classes now. Thanks Lukas

inputSchema('dbo')

[Lukas Eder]

Glad it worked, and thanks for the feedback.

Cheers,

Lukas

[Ergels Gaxhaj]

Lukas,

I don't know if this is a bug yet or not but. I noticed that when using Windows Authentication with ntmlauth.dll in the jre /bin the following method of generating code fails.

However, the solution I have on my task works. Might be worth looking into.

...

// Run the code generator

// ----------------------

// does not work with windows authentication. 

org.jooq.util.GenerationTool.generate(

    javax.xml.bind.JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class)

)

// work-around for jtds drivers and  windows authentication with ntlmauth.dll in JRE_HOME/bin

new org.jooq.util.GenerationTool().with {

setConnection(sql.connection)

run(configuration)

}

[Lukas Eder]

Hello,

The windows authentication method can be a pain, indeed. With maven, what I do is I set the relevant library path to the MAVEN_OPTS environment variable:

SET MAVEN_OPTS=-Djava.library.path=C:\sqljdbc_4.0\enu\auth\x64

But that might not be the same issue that you're talking about. What exactly is the problem?

Cheers,

Lukas