mORMot Open Source

Wim314

Member

Registered: 2014-12-03

Posts: 13

Problem adding in Batch to TSQLRestServerDB

XE7 UPD1

Am trying to load some legacy data in batch on server startup with CreateMissingTables().
With a previous version of mORMot this worked without hiccup, now we get a SQL syntax error, a ' , ' in excess.

Am using TSQLRestBatch with AutomaticTransactionPerRow set to a number lower then the number of records to import.

Batch := TSQLRestBatch.Create(self, TSQLContact, 30);

Found two ways to bypass this:
1. Set the AutomaticTransactionPerRow parameter in the TSQLRestBatch constructor to a number higher than the number of legacy records to import. (Not really a solution)
2. Or change in procedure TSQLRestServerDB.InternalBatchStop.

      // INSERT Values[] into the DB
      SQL := SQL+','+CSVOfValue('('+CSVOfValue('?',fieldCount)+')',rowCount-1);
      Statement := nil; // make compiler happy

To

      // INSERT Values[] into the DB
      if Pred(rowCount) >0 then
        SQL := SQL+','+CSVOfValue('('+CSVOfValue('?',fieldCount)+')',rowCount-1);
      Statement := nil; // make compiler happy

Or are we overlooking something?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: Problem adding in Batch to TSQLRestServerDB

I do not understand well how you may have rowCount=0.
What is the exact sequence of BatchAdd() you are using?

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: Problem adding in Batch to TSQLRestServerDB

I also have a problem with InternalBatchStop.

Sometimes the SQL generated in the line

SQL := SQL+','+CSVOfValue('('+CSVOfValue('?',fieldCount)+')',rowCount-1);

'insert or ignore into Emails (RowID,Email,First_Name,Last_Name,Recipient_Name,Subscribed,Subscribe_Date,GroupID,Fields) values (?,?,?,?,?,?,?,?,?),'

has an extra unnecessary comma at the end and that leads to exception at Statement^.Prepare(DB.DB,SQL).

As a workaround I use the following code:

   FCSVValues := CSVOfValue('('+CSVOfValue('?',fieldCount)+')',rowCount-1);
      if FCSVValues <> '' then
       SQL := SQL +',' + FCSVValues;

I found the similar problem in this topic http://synopse.info/forum/viewtopic.php?id=2228

I think it should be fixed.

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: Problem adding in Batch to TSQLRestServerDB

No I understand!

Should be fixed by http://synopse.info/fossil/info/04aaa06bea

Thanks for your feedback and patience.