Incremental reading does not work for SQLite and MySQL

mark

• March 25, 2012 00:00

• Answered

Follow

Hi,
we tried incremental reading with two databases both having the same table and
values. Incremental reading does not work in both cases, however the error we
get is not the same. Is the configuration bad or is there a problem with the
databases or CloverETL? We are using CloverETL Designer Version: 3.3.0.063M1.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Node DB_INPUT_TABLE configuration:

<Node dbConnection="JDBC1" enabled="enabled" guiHeight="67" guiName="Source" guiWidth="128" guiX="131" guiY="294" id="SOURCE" incrementalFile="${DATAIN_DIR}/dbinc.txt" incrementalKey="lastkey=MAX(id)!2" sqlQuery="select $id:=source.id,$value:=source.value from source where id &gt; #lastkey;" type="DB_INPUT_TABLE"/>

Content of incremental file dbinc.txt is:

#Wed Mar 21 16:31:07 CET 2012
lastkey = 2

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQLite

Content of our test database is:

sqlite> select * from source;
id          value
----------  ----------
1           100
2           30
3           40
4           10
5           50

Error message for SQLite version 3.7.6.3

ERROR [WatchDog] - Node SOURCE finished with status: ERROR caused by: java.sql.SQLException: SQLite only supports TYPE_FORWARD_ONLY cursors

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Error message for MySQL Community Server 5.5.16

ERROR [WatchDog] - Node SOURCE finished with status: ERROR caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx
ception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the ri
ght syntax to use near ':=source.id,$value:=source.value from source where 0=1' at line 1

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Instead of the error we would have expected the ids greater than 2 and their corresponding values in the first run.

If we replace the incremental key #lastkey in the query with 2 it works well. However then we have no incremental reading of course.

• 

  mark

  • March 28, 2012 13:16

  0

  Comment actions Permalink

  Hi,
  we now also tried incremental reading with MS SQL Server 2008 R2 and it did not work either.
  We only changed the database connection. All databases have the same table with the same content.
  
  Error message for MS SQL Server 2008 R2
  
  

  ERROR [WatchDog] - Node SOURCE finished with status: ERROR caused by: java.sql.SQLException: Invalid pseudocolumn "$id".

• 

  admin

  • March 29, 2012 08:57

  0

  Comment actions Permalink

  Hi Mark,
  
  it is probably bug, see issue I reported: https://bug.javlin.eu/browse/CL-2239
  
  As workaround please use implicit fields mapping - as described here: http://doc.cloveretl.com/documentation/ ... table.html ("Query Statement without Mapping")

Please sign in to leave a comment.