Thanks for these hints. I have tried reproducing your issue without any success. This is how jOOQ fetches generated IDs in SQLite (as can be seen from TRACE output):
1. Run an insert statement 2. Run a call to last_insert_rowid() 3. Fetch the necessary columns from the table inserted to, filtering by _rowid_ = ? In your case, step 3 fails. Now here are a couple of hypotheses: a) Your SQLite database doesn't support _rowid_. Could you check that, please? Some more info can be found here [1] b) Xerial driver has bugs. 3.7.2 seems to be the latest published version, even if 3.7.15-SNAPSHOT-2 is available. A quick run of the jOOQ integration tests shows that the latter seems to have even more bugs (I'll report them to Xerial) c) Using _rowid_ is wrong, in jOOQ. Given, that this line here [2] throws a NullPointerException, I'm pretty sure that b) is the problem here. Cheers Lukas [1]: http://www.sqlite.org/autoinc.html [2]: https://bitbucket.org/xerial/sqlite-jdbc/src/f62882bb858dfe3cb6b151f6188729efb3efe467/src/main/java/org/sqlite/PrepStmt.java?at=sqlite-jdbc-3.7.2#cl-42 2013/4/30 <[email protected]> > > OK, I will have a look at your TRACE log output, later. Could you > provide me with the DDL to produce the PROJECT table? > Sure: create table 'Project' ('ID' integer primary key autoincrement, > 'Name' text, 'Code' text, 'Company' text); > > Thanks a lot & Cheers, Michele > > Am Dienstag, 30. April 2013 12:00:13 UTC+2 schrieb Lukas Eder: >> >> >> >> >> 2013/4/30 <[email protected]> >> >> Hi Lukas >>> >>> Unfortunately I have the exact same problem when using the other jdbc >>> driver. >>> >> >> OK, I will have a look at your TRACE log output, later. Could you provide >> me with the DDL to produce the PROJECT table? >> >> Cheers >> Lukas >> >> >>> > I will add integration tests to the test suite using your driver: >>> > https://github.com/jOOQ/**jOOQ/**issues/2424<https://github.com/jOOQ/jOOQ/issues/2424> >>> Cool :-) >>> >>> Thanks & Cheers, Michele >>> >>> >>> Am Dienstag, 30. April 2013 11:41:45 UTC+2 schrieb Lukas Eder: >>>> >>>> Hi Michele, >>>> >>>> 2013/4/30 <[email protected]> >>>> >>>>> > What JDBC driver are you using? >>>>> <dependency> >>>>> <groupId>org.xerial</groupId> >>>>> <artifactId>sqlite-jdbc</**artif**actId> >>>>> <version>3.7.2</version> >>>>> </dependency> >>>>> >>>> >>>> jOOQ is integration tested with this driver: >>>> https://code.google.com/p/**sqli**tebot/downloads/detail?**name=** >>>> sqlitejdbc-v056.jar<https://code.google.com/p/sqlitebot/downloads/detail?name=sqlitejdbc-v056.jar> >>>> >>>> Could you verify if the problem persists, or if this might be a xerial >>>> driver issue? >>>> >>>> I will add integration tests to the test suite using your driver: >>>> https://github.com/jOOQ/jOOQ/**i**ssues/2424<https://github.com/jOOQ/jOOQ/issues/2424> >>>> >>>> Cheers >>>> Lukas >>>> >>>> >>>>> > What log4j TRACE output do you get when running that query? >>>>> 2013-04-30 11:31:52,261 | main | INFO | >>>>> DriverManagerDataSource | Loaded JDBC driver: org.sqlite.JDBC >>>>> 2013-04-30 11:31:52,780 | main | TRACE | StopWatch >>>>> | Initialising : Total: 0.713ms >>>>> 2013-04-30 11:31:52,920 | main | TRACE | StopWatch >>>>> | Rendering query : Total: 141.044ms, +140.331ms >>>>> 2013-04-30 11:31:52,924 | main | TRACE | StopWatch >>>>> | Query rendered : Total: 145.302ms, +4.257ms >>>>> 2013-04-30 11:31:52,924 | main | DEBUG | LoggerListener >>>>> | Executing query : insert into Project (Name, Code, >>>>> Company) values (?, ?, ?) >>>>> 2013-04-30 11:31:52,924 | main | DEBUG | LoggerListener >>>>> | -> with bind values : insert into Project (Name, Code, >>>>> Company) values ('billyboytest', 'code1234', 'nca') >>>>> 2013-04-30 11:31:52,924 | main | TRACE | StopWatch >>>>> | Preparing statement : Total: 145.719ms, +0.417ms >>>>> 2013-04-30 11:31:52,928 | main | TRACE | StopWatch >>>>> | Statement prepared : Total: 149.406ms, +3.686ms >>>>> 2013-04-30 11:31:52,928 | main | TRACE | StopWatch >>>>> | Binding variables : Total: 149.526ms, +0.119ms >>>>> 2013-04-30 11:31:52,929 | main | TRACE | DefaultBindContext >>>>> | Binding variable 1 : billyboytest (class >>>>> java.lang.String) >>>>> 2013-04-30 11:31:52,930 | main | TRACE | DefaultBindContext >>>>> | Binding variable 2 : code1234 (class java.lang.String) >>>>> 2013-04-30 11:31:52,930 | main | TRACE | DefaultBindContext >>>>> | Binding variable 3 : nca (class java.lang.String) >>>>> 2013-04-30 11:31:52,930 | main | TRACE | StopWatch >>>>> | Variables bound : Total: 151.321ms, +1.795ms >>>>> 2013-04-30 11:31:52,930 | main | TRACE | StopWatch >>>>> | Executing query : Total: 151.419ms, +0.097ms >>>>> 2013-04-30 11:31:52,934 | main | DEBUG | StopWatch >>>>> | Query executed : Total: 155.23ms, +3.81ms >>>>> 2013-04-30 11:31:52,950 | main | TRACE | StopWatch >>>>> | Initialising : Total: 0.005ms >>>>> 2013-04-30 11:31:52,950 | main | TRACE | StopWatch >>>>> | Rendering query : Total: 0.124ms, +0.119ms >>>>> 2013-04-30 11:31:52,952 | main | TRACE | StopWatch >>>>> | Query rendered : Total: 2.488ms, +2.363ms >>>>> 2013-04-30 11:31:52,953 | main | DEBUG | LoggerListener >>>>> | Executing query : select last_insert_rowid() >>>>> 2013-04-30 11:31:52,953 | main | TRACE | StopWatch >>>>> | Preparing statement : Total: 2.673ms, +0.184ms >>>>> 2013-04-30 11:31:52,953 | main | TRACE | StopWatch >>>>> | Statement prepared : Total: 2.873ms, +0.2ms >>>>> 2013-04-30 11:31:52,953 | main | TRACE | StopWatch >>>>> | Binding variables : Total: 2.939ms, +0.066ms >>>>> 2013-04-30 11:31:52,953 | main | TRACE | StopWatch >>>>> | Variables bound : Total: 3.081ms, +0.141ms >>>>> 2013-04-30 11:31:52,953 | main | TRACE | StopWatch >>>>> | Executing query : Total: 3.156ms, +0.074ms >>>>> 2013-04-30 11:31:52,953 | main | DEBUG | StopWatch >>>>> | Query executed : Total: 3.332ms, +0.176ms >>>>> 2013-04-30 11:31:52,957 | main | TRACE | StopWatch >>>>> | Fetching results : Total: 7.078ms, +3.745ms >>>>> 2013-04-30 11:31:52,959 | main | TRACE | StopWatch >>>>> | Fetching result : Total: 9.114ms, +2.036ms >>>>> 2013-04-30 11:31:52,960 | main | TRACE | StopWatch >>>>> | Fetching record : Total: 10.028ms, +0.914ms >>>>> 2013-04-30 11:31:52,961 | main | TRACE | StopWatch >>>>> | Record fetched : Total: 10.876ms, +0.847ms >>>>> 2013-04-30 11:31:52,962 | main | TRACE | LoggerListener >>>>> | Record fetched : +-------------------+ >>>>> 2013-04-30 11:31:52,962 | main | TRACE | LoggerListener >>>>> | : |last_insert_rowid()| >>>>> 2013-04-30 11:31:52,962 | main | TRACE | LoggerListener >>>>> | : +-------------------+ >>>>> 2013-04-30 11:31:52,962 | main | TRACE | LoggerListener >>>>> | : | 1235| >>>>> 2013-04-30 11:31:52,962 | main | TRACE | LoggerListener >>>>> | : +-------------------+ >>>>> 2013-04-30 11:31:52,962 | main | TRACE | StopWatch >>>>> | Result fetched : Total: 12.351ms, +1.475ms >>>>> 2013-04-30 11:31:52,963 | main | DEBUG | LoggerListener >>>>> | Fetched result : +-------------------+ >>>>> 2013-04-30 11:31:52,963 | main | DEBUG | LoggerListener >>>>> | : |last_insert_rowid()| >>>>> 2013-04-30 11:31:52,963 | main | DEBUG | LoggerListener >>>>> | : +-------------------+ >>>>> 2013-04-30 11:31:52,963 | main | DEBUG | LoggerListener >>>>> | : | 1235| >>>>> 2013-04-30 11:31:52,963 | main | DEBUG | LoggerListener >>>>> | : +-------------------+ >>>>> 2013-04-30 11:31:52,964 | main | TRACE | StopWatch >>>>> | Results fetched : Total: 13.619ms, +1.267ms >>>>> 2013-04-30 11:31:52,966 | main | DEBUG | StopWatch >>>>> | Finishing : Total: 15.957ms, +2.338ms >>>>> 2013-04-30 11:31:52,969 | main | TRACE | StopWatch >>>>> | Initialising : Total: 0.01ms >>>>> 2013-04-30 11:31:52,969 | main | TRACE | StopWatch >>>>> | Rendering query : Total: 0.177ms, +0.166ms >>>>> 2013-04-30 11:31:52,969 | main | TRACE | StopWatch >>>>> | Query rendered : Total: 0.52ms, +0.343ms >>>>> 2013-04-30 11:31:52,969 | main | DEBUG | LoggerListener >>>>> | Executing query : select Project.ID from Project >>>>> where _rowid_ = ? >>>>> 2013-04-30 11:31:52,970 | main | DEBUG | LoggerListener >>>>> | -> with bind values : select Project.ID from Project >>>>> where _rowid_ = 1235 >>>>> 2013-04-30 11:31:52,970 | main | TRACE | StopWatch >>>>> | Preparing statement : Total: 1.423ms, +0.902ms >>>>> 2013-04-30 11:31:52,970 | main | DEBUG | StopWatch >>>>> | Finishing : Total: 1.591ms, +0.168ms >>>>> 2013-04-30 11:31:52,971 | main | DEBUG | StopWatch >>>>> | Finishing : Total: 192.164ms, +36.934ms >>>>> 2013-04-30 11:31:52,979 | Thread-0 | INFO | >>>>> GenericApplicationContext | Closing org.springframework.context.* >>>>> *su**pport.**GenericApplicationContex**t@**2e8923f5: startup date >>>>> [Tue Apr 30 11:31:51 CEST 2013]; root of context hierarchy >>>>> >>>>> >>>>> >>>>> Am Dienstag, 30. April 2013 11:03:11 UTC+2 schrieb Lukas Eder: >>>>>> >>>>>> Hi Michele, >>>>>> >>>>>> >>>>>> 2013/4/30 <[email protected]> >>>>>> >>>>>> Hi all >>>>>>> >>>>>>> I have problems inserting a record into a SQLite database. This >>>>>>> works: >>>>>>> >>>>>>> create().insertInto(PROJECT) >>>>>>> .set(PROJECT.CODE, obj.getCode()) >>>>>>> .set(PROJECT.COMPANY, obj.getCompany()) >>>>>>> .set(PROJECT.NAME, obj.getName()) >>>>>>> .execute(); >>>>>>> >>>>>>> However, If I do something like the following, I get a null pointer >>>>>>> exception (I get the same exception also for other queries...): >>>>>>> >>>>>>> ProjectRecord record = create().newRecord(PROJECT); >>>>>>> record.setCode(obj.getCode()); >>>>>>> record.setCompany(obj.**getCompa****ny()); >>>>>>> record.setName(obj.getName()); >>>>>>> record.insert(); >>>>>>> >>>>>>> java.lang.NullPointerException >>>>>>> at org.sqlite.PrepStmt.<init>(**Pre****pStmt.java:42) >>>>>>> at org.sqlite.Conn.**prepareStateme****nt(Conn.java:**404) >>>>>>> at org.sqlite.Conn.**prepareStateme****nt(Conn.java:**399) >>>>>>> at org.sqlite.Conn.**prepareStateme****nt(Conn.java:**383) >>>>>>> at org.jooq.impl.**ProviderEnabledC****onnection.** >>>>>>> prepareStatement(**Pro****viderEnabledConnection.**java:**10**3) >>>>>>> at org.jooq.impl.**SettingsEnabledC****onnection.** >>>>>>> prepareStatement(**Set****tingsEnabledConnection.**java:**71**) >>>>>>> at org.jooq.impl.**ProviderEnabledC****onnection.**prepareStatement( >>>>>>> **Pro****viderEnabledConnection.**java:**10**3) >>>>>>> at org.jooq.impl.**SettingsEnabledC****onnection.** >>>>>>> prepareStatement(**Set****tingsEnabledConnection.**java:**71**) >>>>>>> at org.jooq.impl.**AbstractResultQu****ery.prepare(** >>>>>>> AbstractResultQuer****y.java:184) >>>>>>> at org.jooq.impl.AbstractQuery.**ex****ecute(AbstractQuery.java:** >>>>>>> 265) >>>>>>> at org.jooq.impl.**AbstractResultQu****ery.fetch(** >>>>>>> AbstractResultQuery.****java:309) >>>>>>> at org.jooq.impl.**AbstractResultQu****ery.fetchInto(** >>>>>>> AbstractResultQu****ery.java:596) >>>>>>> at org.jooq.impl.SelectImpl.**fetch****Into(SelectImpl.java:**1203) >>>>>>> at org.jooq.impl.**AbstractStoreQue****ry.execute(** >>>>>>> AbstractStoreQuery.****java:260) >>>>>>> at org.jooq.impl.AbstractQuery.**ex****ecute(AbstractQuery.java:** >>>>>>> 283) >>>>>>> at org.jooq.impl.**UpdatableRecordI****mpl.**storeInsert(** >>>>>>> UpdatableRecor****dImpl.java:176) >>>>>>> at org.jooq.impl.**UpdatableRecordI****mpl.store(** >>>>>>> UpdatableRecordImpl.****java:139) >>>>>>> at ch.test.service.model.**ProjectD****AOImpl.persist(** >>>>>>> ProjectDAOImpl.****java:46) >>>>>>> at ch.test.service.model.**ProjectD****AOImpl.persist(** >>>>>>> ProjectDAOImpl.****java:1) >>>>>>> at ch.test.service.model.**ProjectD****AOImplTest.** >>>>>>> shouldPersistNewMea****lItem(**ProjectDAOImplTest.java:****43) >>>>>>> at sun.reflect.**NativeMethodAccess****orImpl.**invoke0(Native >>>>>>> Method) >>>>>>> at sun.reflect.**NativeMethodAccess****orImpl.**invoke(** >>>>>>> NativeMethodAcce****ssorImpl.java:**57) >>>>>>> at sun.reflect.**DelegatingMethodAc****cessorImpl.**invoke(** >>>>>>> DelegatingMe****thodAccessorImpl.**java:43) >>>>>>> at java.lang.reflect.Method.**invok****e(Method.java:601) >>>>>>> at org.junit.runners.model.**Framew****orkMethod$1.** >>>>>>> runReflectiveCall(******FrameworkMethod.java:47) >>>>>>> at org.junit.internal.runners.**mod****el.ReflectiveCallable.run(** >>>>>>> Refl****ectiveCallable.java:12) >>>>>>> at org.junit.runners.model.**Framew****orkMethod.** >>>>>>> invokeExplosively(**Fr****ameworkMethod.java:44) >>>>>>> at org.junit.internal.runners.**sta****tements.InvokeMethod.** >>>>>>> evaluate(****InvokeMethod.java:17) >>>>>>> at org.springframework.test.**conte****xt.junit4.statements.** >>>>>>> RunBefore****TestMethodCallbacks.**evaluate(**R**** >>>>>>> unBeforeTestMethodCallbacks.**ja****va:74) >>>>>>> at org.springframework.test.**conte****xt.junit4.statements.** >>>>>>> RunAfterT****estMethodCallbacks.**evaluate(**Ru**** >>>>>>> nAfterTestMethodCallbacks.**java****:83) >>>>>>> at org.springframework.test.**conte****xt.junit4.statements.** >>>>>>> SpringRep****eat.evaluate(**SpringRepeat.**java**:72) >>>>>>> at org.springframework.test.**conte****xt.junit4.** >>>>>>> SpringJUnit4ClassRun****ner.**runChild(**SpringJUnit4Class**** >>>>>>> Runner.java:**231) >>>>>>> at org.springframework.test.**conte****xt.junit4.** >>>>>>> SpringJUnit4ClassRun****ner.**runChild(**SpringJUnit4Class**** >>>>>>> Runner.java:**88) >>>>>>> at org.junit.runners.**ParentRunner****$3.run(** >>>>>>> ParentRunner.java:238) >>>>>>> at org.junit.runners.**ParentRunner****$1.schedule(** >>>>>>> ParentRunner.java:****63) >>>>>>> at org.junit.runners.**ParentRunner****.runChildren(**ParentRunner. >>>>>>> **java**:236) >>>>>>> at org.junit.runners.**ParentRunner****.access$000(** >>>>>>> ParentRunner.java:****53) >>>>>>> at org.junit.runners.**ParentRunner****$2.evaluate(** >>>>>>> ParentRunner.java:****229) >>>>>>> at org.springframework.test.**conte****xt.junit4.statements.** >>>>>>> RunBefore****TestClassCallbacks.**evaluate(**Ru**** >>>>>>> nBeforeTestClassCallbacks.**java****:61) >>>>>>> at org.springframework.test.**conte****xt.junit4.statements.** >>>>>>> RunAfterT****estClassCallbacks.**evaluate(**Run**** >>>>>>> AfterTestClassCallbacks.**java:**7**1) >>>>>>> at org.junit.runners.**ParentRunner****.run(ParentRunner.** >>>>>>> java:309) >>>>>>> at org.springframework.test.**conte****xt.junit4.** >>>>>>> SpringJUnit4ClassRun****ner.run(**SpringJUnit4ClassRunne****r.java:* >>>>>>> *174) >>>>>>> at org.eclipse.jdt.internal.**junit****4.runner.** >>>>>>> JUnit4TestReference.**r**un(**JUnit4TestReference.java:**50**) >>>>>>> at org.eclipse.jdt.internal.**junit****.runner.TestExecution.**run( >>>>>>> **Test**Execution.java:38) >>>>>>> at org.eclipse.jdt.internal.**junit****.runner.RemoteTestRunner.** >>>>>>> runTe****sts(RemoteTestRunner.**java:467) >>>>>>> at org.eclipse.jdt.internal.**junit****.runner.RemoteTestRunner.** >>>>>>> runTe****sts(RemoteTestRunner.**java:683) >>>>>>> at org.eclipse.jdt.internal.**junit****.runner.RemoteTestRunner.** >>>>>>> run(**R**emoteTestRunner.java:390) >>>>>>> at org.eclipse.jdt.internal.**junit****.runner.RemoteTestRunner.** >>>>>>> main(****RemoteTestRunner.java:**197) >>>>>>> >>>>>>> Any ideas what the problem could be? Thanks a lot for any help! >>>>>>> Regards, Michele >>>>>>> >>>>>> >>>>>> That looks quite surprising. >>>>>> >>>>>> - What JDBC driver are you using? >>>>>> - What log4j TRACE output do you get when running that query? Info >>>>>> about logging can be found here [1]. A sample log4j.xml file is here [2] >>>>>> >>>>>> Cheers >>>>>> Lukas >>>>>> >>>>>> [1]: http://blog.jooq.org/**2011****/10/20/debug-logging-sql-**with-* >>>>>> ***jooq <http://blog.jooq.org/2011/10/20/debug-logging-sql-with-jooq> >>>>>> [2]: https://github.com/jOOQ/**j****OOQ/blob/master/jOOQ-test/**src/* >>>>>> ***log4j.xml<https://github.com/jOOQ/jOOQ/blob/master/jOOQ-test/src/log4j.xml> >>>>>> >>>>> -- >>>>> 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/**grou**ps/opt_out<https://groups.google.com/groups/opt_out> >>>>> . >>>>> >>>>> >>>>> >>>> >>>> -- >>> 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/**groups/opt_out<https://groups.google.com/groups/opt_out> >>> . >>> >>> >>> >> >> -- > 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/groups/opt_out. > > > -- 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/groups/opt_out.
