CODEWITHSUNDEEP
javasql-serversql-insertjooq
recklessGreed
recklessGreed

Reputation: 139

JOOQ store() does not return ResultSet

Following this I try to create a new Student Record, assign it some Values and store it to the Database.

DSLContext ctx = ...

StudentRecord s = ctx.newRecord(Student.STUDENT);
    s.setFirstname("Nicolas");
    s.setLastname("Nox");
    s.setGender("M");
    s.setYearOfBirth((short) 1990);
    s.store(); <-- ERROR

EDIT 1:
The same goes for insert()
End of Edit 1
EDIT 2:
The Student Table contains nothing more than the four values shown above. The Model is autogenerated and select worked so far.
End of Edit 2

Edit 3: As asked here the Table Definition for Student and the dependency of the JDBC Driver.

create table [LECTURE_DB].[dbo].[STUDENT](
[ID] int identity(1, 1) not null,
[FIRSTNAME] nvarchar(20) not null,
[LASTNAME] nvarchar(20) not null,
[YEAR_OF_BIRTH] smallint null,
[GENDER] nvarchar(1) null,
constraint [PK__STUDENT__3214EC277F60ED59]
  primary key ([ID]),
  constraint [STUDENT_NAME_IDX]
  unique (
    [LASTNAME], 
    [FIRSTNAME]
  )
)

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.0.0.jre8</version>
</dependency>

End of Edit 3

This does insert a Value into the Database but the Record is not correctly updated. The following Error is thrown:

Exception in thread "main" java.lang.ExceptionInInitializerError
Caused by: org.jooq.exception.DataAccessException: SQL [declare @result table ([ID] int); insert 
into [LECTURE_DB].[dbo].[STUDENT] ([FIRSTNAME], [LASTNAME], [YEAR_OF_BIRTH], [GENDER]) output 
[inserted].[ID] into @result values (?, ?, ?, ?); select [r].[ID] from @result [r];]; The statement did not return a result set.
at org.jooq_3.12.1.SQLSERVER2014.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2717)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:383)
at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:206)
at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:177)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:130)
at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:173)
at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:196)
at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:136)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:130)
at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:132)
at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:124)
at de.esteam.lecturedb.jooq.LectureDBSetup.insertInitialData(LectureDBSetup.java:49)
at de.esteam.lecturedb.jooq.LectureDBAnalysis.<init>(LectureDBAnalysis.java:77)
at de.esteam.lecturedb.jooq.LectureDBAnalysis.<clinit>(LectureDBAnalysis.java:44)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:464)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:405)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:208)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:183)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:317)
at org.jooq.tools.jdbc.DefaultPreparedStatement.executeQuery(DefaultPreparedStatement.java:94)
at org.jooq.impl.AbstractDMLQuery.executeReturningQuery(AbstractDMLQuery.java:1137)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:935)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:369)
... 12 more

stating the missing ResultSet from the Database to update the ID in my Application.

I kinda need the Records and the IDs since I need to insert many values with foreign keys.

Upvotes: 1

Views: 823

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220842

jOOQ using SQL Server OUTPUT to fetch result data from DML statements is a new feature from jOOQ 3.12: #4498. It has a few known issues in version 3.12.2, including:

Yours was not known yet. I'll update my answer once I know more about it.

A workaround could be to turn off generating the OUTPUT clause in SQL Server, which should still work for single-row DML statements like yours. Set your Settings.renderOutputForSQLServerReturningClause flag to false

Upvotes: 1

Related Questions