Incremental Load

[#1]

Hi there, i hope someone will be able to help me.

Currently i'm testing with Incremental Load to lessen the load on the database. Tested quite a few scenario's and i keep getting errors.

Currently i'm using the following script:

==================================================

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO localhost;

Let ThisExecTime = ReloadTime();

CDR:

SQL SELECT ID, start, clid FROM asteriskcdrdb.cdr

WHERE start >= #$(LastExecTime)#;

Concatenate

LOAD ID, start, clid FROM cdr.QVD

WHERE NOT Exists(ID);

If ScriptErrorCount = 0 then

  STORE CDR INTO cdr.QVD;

  Let LastExecTime = ThisExecTime;

End If

==================================================

start contains a date like "2015-09-13 23:32:13"

The error code is as following:

error 1:

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.24]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

CDR:

SQL SELECT ID, start, clid FROM asteriskcdrdb.cdr

WHERE start >= ##

error 2

Cannot open file 'C:\Data\cdr.QVD

WHERE NOT Exists' The filename, directory name, or volume label syntax is incorrect.

Concatenate

LOAD ID, start, clid FROM cdr.QVD

WHERE NOT Exists(ID)

In my test environment i'm using a mysql database.

According to the help file i'm using the following case Case 3: Insert and Update (No Delete)

Thanks in advance!

Regards,

Harry

[maxgro]

what's the value of LastExecTime?

add a trace

also I think you need quote and the LasteExecTime format must be the same as the format of your db

ODBC CONNECT TO localhost;

Let ThisExecTime = ReloadTime();

trace  $(LastExecTime);

CDR:

SQL SELECT ID, start, clid FROM asteriskcdrdb.cdr

WHERE start >= #'$(LastExecTime)'#;

[#3 · Accepted solution]

I've updated your comments and i'm getting the following errors:

Error 1

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.24]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

CDR:

SQL SELECT ID, start, clid FROM asteriskcdrdb.cdr

WHERE start >= #''#

Error 2

Cannot open file 'C:\Data\cdr.qvd

WHERE NOT Exists' The filename, directory name, or volume label syntax is incorrect.

Concatenate

LOAD ID, start, clid FROM cdr.qvd

WHERE NOT Exists(ID)

When i'm using the following script everything gets loaded correct. :

CDR:

LOAD

  * ;

SQL SELECT ID, start, clid

FROM  asteriskcdrdb.cdr

where start >='2015-09-12';

Concatenate

Load

  *

from 'C:\Data\cdr.qvd' (qvd)

where NOT Exists(ID);

Store CDR into 'C:\Data\cdr.qvd';

Connecting to localhost

Connected

CDR << cdr xxxxxx  lines fetched

CDR << cdr (qvd optimized) xxxxx lines fetched

Problem here is every time i reload it add's duplicate records after date 2015-09-12. So it doesnt check for duplicate ID's

Regards,

Harry

[#4]

i've added some code where i load the highest ID. In incremental load code i added a where id > highest id to load new data. thanks for the inspiration maxgro