Jasonjac2

I have to periodically push sync (Make destination the same as source) between 2 databases (Eventually FB to SQLite, but for now sqlite to sqlite). I have sync'd datasets a number of times over my programming career and have some specific to the use case and some more generic solutions. As the datasets are relatively small I decided to try using TFDBatchmove in the hope it might not be super efficient, but would be a case of hooking up some components and pressing the Go button :-). A day later and I still haven't got it working. I intended building from a very trivial example. I also had a look at the samples for D12.1, which in a classic sample fashion wasn't super clear to read the code. So Q1) is it suitable to us use the batchmove components in this way? Current test: 2 identical tables in sqlite, run it once, it fills the table (that actually works) Modify the destination table (e.g. blank a few field values) Rerun and initially I get an exception "Not Implemented" By trial and error I add in an event handler for OnFindDestRecord - which I thought I wouldn't need as I am using TFDTable, the sqlite table has a primary key and the field structures are identical. Now I have hand coded the locate from source to destination it gives me: Project FDBatchMoveScratch.exe raised exception class EFDDBEngineException with message '[FireDAC][Phys][SQLite]-312. Exact update affected [0] rows, while [1] was requested'. Project FDBatchMoveScratch.exe raised exception class EFDException with message '[FireDAC][DApt]-400. Update command updated [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user'. Adding in event handlers for all of the batch move events, to try and work out what is going on - it is trying to update the 1st row, eventhough the row is identical. So I am clearly missing something (or a lot of things). For info - I have 2 FDConnections, an FDBatchMove, in fact to avoid ambiguity, I've pasted the objects below . Q2) Can anyone point me to a decent walk through of setting up one way sync and resync. I could have hand coded a compare, update, insert, delete routine by hand in this time :-(. As always, thank you for your time with this. object FDBatchMove1: TFDBatchMove Reader = FDBatchMoveDataSetReader1 Writer = FDBatchMoveDataSetWriter1 Mode = dmAppendUpdate Options = [poIdentityInsert, poSkipUnmatchedDestFields, poUseTransactions] Mappings = <> LogFileAction = laAppend LogFileName = 'Data.log' OnProgress = FDBatchMove1Progress OnFindDestRecord = FDBatchMove1FindDestRecord OnWriteValue = FDBatchMove1WriteValue OnWriteRecord = FDBatchMove1WriteRecord Left = 384 Top = 216 end object FDBatchMoveDataSetReader1: TFDBatchMoveDataSetReader DataSet = tblsrcWebUsers Left = 520 Top = 120 end object FDBatchMoveDataSetWriter1: TFDBatchMoveDataSetWriter Direct = True DataSet = tbldestWebUsers Left = 512 Top = 296 end object tbldestWebUsers: TFDTable IndexFieldNames = 'ID' Connection = FDConnectionSQLiteDest ResourceOptions.AssignedValues = [rvEscapeExpand] TableName = 'WEB_USERS' Left = 376 Top = 288 object tbldestWebUsersID: TIntegerField FieldName = 'ID' Origin = 'ID' ProviderFlags = [pfInUpdate, pfInWhere, pfInKey] Required = True end object tbldestWebUsersUSERNAME: TStringField FieldName = 'USERNAME' Origin = 'USERNAME' Required = True Size = 50 end object tbldestWebUsersROLE: TStringField FieldName = 'ROLE' Origin = 'ROLE' Size = 50 end object tbldestWebUsersIS_ACTIVE: TIntegerField FieldName = 'IS_ACTIVE' Origin = 'IS_ACTIVE' end object tbldestWebUsersCOMPANY_NAME: TStringField FieldName = 'COMPANY_NAME' Origin = 'COMPANY_NAME' Size = 50 end end

Yes, I can see that, as well as transforming from one format to another (It seems to be used a lot to TDataset -> JSON etc). I thought when I saw update and delete it would be a more general purpose component. Thanks for the offer on the trigger, but I don't think I would use it at the moment. I have written replication engines for firebird and happy with triggers. I was hoping for a simple (to develop) engine, but I can see me moving away from the batchmove component, especially as the records grow - the Find Matching Record is so poor in terms of me not being able to .next the target dataset, that when I get to thousands of records , it is going to be horribly slow. I guess when I have done this working prototype, I'll go back and make my "zipper / ladder" algorithm a bit more componentised and use that instead. It's a shame as the component does a lot.

Thanks for replying, I started to think I was the only Batchmover :-). I am not that familiar with SQLite to know if you can use triggers between 2 separate databases. Regardless, in my actually use case the SQLite <> SQLite was a stepping stone to the actual source, which would be firebird (an older version of FB). Things are moving on. I wish I hadn't started with BatchMove as using something i have used before would have required a lot less head scratching, but it has been a good learning curve for TFDBatchMove. Other Gotchas.... If you don't assign a sourceFieldName or SourceExpression, then in FDBatchMoveWebUsersWriteValue for that field, you get whatever the previous value was,, e.g. I need to put a value in for a field - Address, where address is going to be built in FDBatchMoveWebUsersWriteValue. There is no sourcefield to base it on, so the Mapping just has a destination field. When the FDBatchMoveWebUsersWriteValue gets invoked for the address, AValue actually has the phone number in it , which was the previous call to the event handler - a bit messy think.

...We make more progress forward and some steps back... Using Calculated Fields in your Source Dataset - YOU CAN'T It appears you can't use calculated fields in the source table. The reason I want to is: There is a field that is a constants and I didn't want to have to include it in the query. TFDBatchMove.mappings[?].SourceExpression I thought you would be able to pull fields from the source dataset, but you can't do that, unless they are actually cited in the mappings. So I have Line2, Line2...Postcode and need to convert that into Address (those items concatenated with ~s. I thought I could this with Line1 etc in the source query, but as it doesn't appear in the mappings, you get a filed not found (Line1||"~"||Line2).

Following on from the linked question. I have put some debug code in to try and optimize the locate - see below. procedure TfrmMain.FDBatchMove1FindDestRecord(ASender: TObject; var AFound: Boolean); begin mmInfo.lines.Add('Find: src.ID: '+tblsrcWebUsersid.AsString+' current dest.id: '+tbldestWebUsersid.AsString); afound:=(tblsrcWebUsersID.value = tbldestWebUsersID.value); if afound then mmInfo.lines.Add('Found: ID: '+tblsrcWebUsersid.AsString+' over correct record') else begin tbldestWebUsers.next; //this command does nothing, i.e. it doesn't move the cursor afound:=(tblsrcWebUsersID.value = tbldestWebUsersID.value); if afound then mmInfo.lines.Add('Found: ID: '+tblsrcWebUsersid.AsString+' was next record') else begin afound:=tbldestWebUsers.locate('ID',tblsrcWebUsersid.value,[]); if afound then mmInfo.lines.Add('Found: ID: '+tblsrcWebUsersid.AsString+' using locate') else mmInfo.lines.Add('Not Found: ID: '+tblsrcWebUsersid.AsString+' using locate'); end; end; end; Just to be clear - it works without the code above (2 x FDTables looking at the same table structure is 2 different SQlite DBS) basically as the execute steps through the source table the destination is always (if both have the same PK's and rows) one record behind after the first row. So I thought I could do the code above to try and do a next rather than a locate in nearly all cases. The .next does nothing though. I have traced the code to and in here it, I am not sure what is going on, but the result is that the cursor doesn't move. Does anyone know why? Who are the Batchmove gurus? D12.1 TIA JAC

OK, things move on. I needed to set the FDBatchMoveDataSetWriter1.direct to false and it worked. "FDBatchMoveDataSetWriter1.direct to false. Looking at the help on TFDBatchMoveDataSetWriter.direct I can't see why it wouldn't work with sqlite. Now I have it working for this use case, I stripped out some of the random changes I made to try and get it working (removed the FDBatchMove1FindDestRecord), removed the persistent fields etc. I took another look at the sample and was definitely none the wiser about merging data. So I inch further on, still asking for any decent blogs / walkthroughs / gems of knowledge. Things I know that are coming my way if I continue down this path: - It doesn't help me decide if there are any records that need removing. Normally I use a "ladder" approach, organising the source and destination in such a way that if all rows are in both, then you can just .next on each dataset, but if there is a row in dest where the calculated PK is < the current calculated source PK, then it needs to be deleted. -- anyone dealt with this in the TFDBatchMove world? - Currently this is all sqlite to sqlite with identical field structures just to build the example, moving on, I it will need to cater for pulling a joined SQL from the src and pushing into a denormalised table in sqlite. TIA Regards, Jason

Hi All, I have an old system: 2 x EXEs (D7) 330K combined lines of code BDE FibPlus IBX JVCL JCL TMS SMExport (very small amount) Custom Components (very small part) CNWizards – logging – (very small part) I want to end up with on DAC (FireDAC unless anyone talks me out of it) and D10.4 I have invested in "The Delphi Parser". The author is being very helpful, but ultimately for FIBPlus, I am going to get my hands dirty and modify the script to teach it what to convert into what. I was wondering if anyone else has travelled this path or is about to. Things I was interested in. How you did it, what gotchas you got etc. I like the idea of a "brighter than Grep" parser that understands the structure of a Delphi App.

Exactly what I was after. I m not working on it at the moment. If anyone wanted to work on it at the same time with a similar project, I would be up for that, based on it being more likely to get done, but these hints are great and I'll put them on the list when I warm up the project.

Interesting about the todo's - I wrote a script to add this to every file in the project many years ago when I made an architectural change. I was speaking generally - i.e. gotchas, considerations, progress etc. TIA - JAC.

I hadn't looked at this, It would be very interesting.

I gave Marco from GDK a heads up about this thread as I think I said GDK are in the market of upgrading systems. I am on another large tight deadline project for the same client as the large system (I also need to move from a very of version of Firebird and move from Dialect 1 -> 3). My hope would be able to do this over a period of time, set up a test database, then try and migrate parts of the system that I can then compile (e.g. just include Datamodule units so I am not worrying about UI to begin with). Our system has about 700units (50 /50 data modules / forms and frames). Ultimately, once I get a compile and testing, I am likely to look at formalising a few of the things I have learnt over the years, so I don't carry forward dozens of ways of doing the same task. If you want I am really happy to have a chat about ways of doing this and sharing ideas / scripts. What does your system look like (size, DFM's or coded creation of components, standard CRUD mechanisms etc). Working with someone else is much more likely that I will put the time in, rather than just continue with D7 forever or until something really breaks. It is a problem when the change the customer will see if very small once initially migrated, but the effort is VERY high and risks are introduced that need a lot of testing.... and they say "why do we need to do this now".

@corneliusdavid potentially collaborate on this? FIBPLus is a bit more complicated than the BDE IMO as there are some advanced properties and also I think the developer had less defaults or whatever it is that ensures the DFM doesn't bloat with a gazilion properties that are just default values. How far are you through?

"necroposting" love it! No I didn't. I didn't get the the tool I was looking at to work had to put the project down. It is back on the agenda, but now moving from 7 -> 12.x! Are you doing the same and how are you getting on? Another entry into the marketplace is GDK who advertise specialising in such migrations. I had a quick chat with them, but haven't progressed it further. I could really do with moving to a later version as there are lots of things I want our app to do that would leverage modern libraries / components that use modern language features.

I have lot of marketing e-mails about the Delphi Parser product https://delphiparser.com/ that mention AI powered. I'm not bashing the product, I just wanted to know what is "AI Powered" about it. I e-mailed, but didn't get a response, so thought I would aske here. Regards, Jason

Oh dear, I didn't mean to start a flame 🙂 Just get to the bottom of it. Actually, has anyone used the product and is it actually any good (AI or No AI)?

I'll check out the about... thanks. Using templates and substitution, even with elaborate conditionals and context info doesn't make it AI to my mind and that is what it used to use to re-write code. "70B model with only Delphi code?" - I could defo google this but would love to hear what you are talking about from you.

Hi, I was looking at the excellent https://github.com/Fr0sT-Brutal/awesome-pascal/ and it reminded me of a discussion I was having a while back about RTC/RealThinClient. I thought one of the examples was remote control / file transfer between 2 PC's, or am I confusing it with another component set? I was looking at https://github.com/teppicom/RealThinClient-SDK/ and couldn't see a suitable example. Anyone remember? TIA Jason

Hi, I had an old D7 + BDE + ODBC -> Sage app that did a simple extract from Sage, did a bit of magic and produced a report. Excellent. We upgraded Sage to V29 and the app wouldn't connect, with a not very useful error message. Excel -> ODBC -> Sage was fine. So I thought I would try with D11 Update 1 + FireDAC. After a few hours of fun (there aren't a lot of ODBC FireDAC examples and I don't have the Sage ODBC driver on my dev box), I got a proof of concept that could connect and I could run an SQL against Sage and get data. To cut a long story short, I built the bare minimum to get the data, save it to XML (Using TClientDataset), load it back up and process the invoice & Invoice_Line tables. The actual bits I want look fine, but then I noticed that some of the data in columns appeared to be the same for the whole column. Many hours later, tracing back through, I go back to my fist example AND "select * from invoice" and then finding invoice 99 Doesn't have the same data as "select * from invoice where invoice_number = 99" FOREIGN_INVOICE_NET="1300" (Actually -5.83 if selected via excel & ODBC or using the specific Where clause) FOREIGN_INVOICE_TAX="260" (Actually -1.17) FOREIGN_INVOICE_GROSS="1560" (Actually -7) INVOICE_NET="1300" (Actually -5.83) INVOICE_TAX="260" (Actually -1.17) INVOICE_GROSS="1560" (Actually -7) I'm a couple of days in now and properly pulling my hair out as they found the problem last week and they need the calculation for the monthly ledger entries to close the month off (read a lot of pressure). Does this ring any bells with anyone, any ideas? My next steps are: - get someone to compile my simple app on the latest D11.3 and see if it has the same issue. - get the data out via Excel and use that as the input to my calcs (a temporary solution and a chunk of work). Thanks for your time and thoughts, Jason Added a short video to demonstrate

https://quality.embarcadero.com/browse/RSP-41225 not my finest bug report as there are too many bits I am not sure of and it related to a Sage install.

@Lars FosdalI will need to take another look at the logs. From my quick look and lack of knowledge about Sage DataTypes, I am not sure I know whether they are correct mappings or not. Back to the best sources of info on FD, short of reading the source and dedicating the next year to it :-). Can you rate Cary Jenson's book and do you know if there are any amendments since it was published 2017? I have tried reaching out to him on linkedIn. I can see I need to start thinking the FD way and so want a good book, series of articles. I will return to this in a few weeks as I am really behind on other work. Cheers, JAC.

Hi @Lars Fosdal so in a simple GUI, single threaded app, when do you call CoInitialize(nil)&  CoUnInitialize? The only boilerplate stuff I have in all my apps is: SetThreadLocale(LOCALE_USER_DEFAULT); GetFormatSettings; Which is somethrowback to problems with Dates under Windows 7. I'll take a look at the monitoring log again to check the data types, but of course, I am not that familiar with the data types expected from Sage. This was a hack app from years ago and my only real interaction with Sage. I see Cary Jenson has a book on FD, but I am not sure it will cover such things. Do you know who the maintainer is?

@Lars Fosdal no I haven't. There are no additional threads - very simple. So I think a member of the ukDevGroup has got to the bottom of it. TDQuery.FetchOptions.CursorKind:=ckForwardOnly This sorts out the incorrect repeating values. There is still something strange going on with other fields. invoice.invoice_type invoice.record_deleted, used to be 0 for false, now is 1792! So I would put the combo as "at risk" and I think I need to come up with best practices for the FD + ODBC + Sage.

No, I haven't. I thought FD was the way to go. I am about 18 hours into trying to sort this now. About to tell the user that I don't have a working solution. So @Lars Fosdal any suggestions now? Any way of seeing what is actually coming back over the ODBC connection, i.e. to remove FD from the issue - clearly Excel and FD don't access the data in the same way as excel has the correct answers.

Post 3 2 rows returned, ITEMS_GROSS wrong for both rows 08/03/2023 15:41 select invoice_number, invoice_type, ITEMS_NET, ITEMS_TAX, ITEMS_GROSS from Invoice where invoice_number = 1 or invoice_number = 2 INVOICE_NUMBER INVOICE_TYPE ITEMS_NET ITEMS_TAX ITEMS_GROSS 1 Product Invoice 1123.04 191.63 214.32 2 Product Invoice 2192.64 374.12 214.32 The actual last line is Invoice 84 84 Product Invoice (from SOP) 182.4 31.92 214.32 182.4+31.92=214.32 - which if the FDQuery is only getting the results back from the ODBC would imply that it is the ODBC connection (Sage that is giving the wrong answer as the FDQuery isn't getting the last row back).

Post 2 - 2 rows incorrect ITEMS_GROSS for row 1, correct for row 2 (the last row returned). 08/03/2023 15:40 select select invoice_number, invoice_type, ITEMS_NET, ITEMS_TAX, ITEMS_GROSS from Invoice where invoice_number <= 2 Copy to clipboard INVOICE_NUMBER INVOICE_TYPE ITEMS_NET ITEMS_TAX ITEMS_GROSS 1 Product Invoice 1123.04 191.63 2566.76 2 Product Invoice 2192.64 374.12 2566.76 4500312661677 15:40:31.418 >> TFDCustomCommand.Fetch [Command="select invoice_number, invoice_type, ITEMS_NET, ITEMS_TAX, ITEMS_GROSS from Invoice where invoice_number <= 2 ", AAll=False, ABlocked=True] 4500312814243 15:40:31.434 >> Fetch [ATable="Invoice", Command="select invoice_number, invoice_type, ITEMS_NET, ITEMS_TAX, ITEMS_GROSS from Invoice where invoice_number <= 2 "] 4500313126584 15:40:31.465 . ENTER SQLFetch HSTMT 0x06D7CD48 4500313283156 15:40:31.481 . EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x06D7CD48 4500313283416 15:40:31.481 >> Fetched [Row=0] 4500313438611 15:40:31.496 . Column [N=1, Type=SLONG, Size=10, Len=4, Data=1] 4500313596585 15:40:31.512 . Column [N=2, Type=CHAR, Size=60, Len=15, Data='Product Invoice'] 4500313751854 15:40:31.528 . Column [N=3, Type=DOUBLE, Size=15, Len=8, Data=1123.04] 4500313907727 15:40:31.543 . Column [N=4, Type=DOUBLE, Size=15, Len=8, Data=191.63] 4500313907801 15:40:31.543 . Column [N=5, Type=DOUBLE, Size=15, Len=8, Data=2566.76] 4500313907856 15:40:31.543 << Fetched [Row=0] 4500313909035 15:40:31.543 . ENTER SQLFetch HSTMT 0x06D7CD48 4500314064373 15:40:31.559 >> Fetched [Row=0] 4500314064134 15:40:31.559 . EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x06D7CD48 4500314220045 15:40:31.574 . Column [N=1, Type=SLONG, Size=10, Len=4, Data=2] 4500314376458 15:40:31.590 . Column [N=2, Type=CHAR, Size=60, Len=15, Data='Product Invoice'] 4500314532680 15:40:31.606 . Column [N=3, Type=DOUBLE, Size=15, Len=8, Data=2192.64] 4500314689069 15:40:31.621 . Column [N=4, Type=DOUBLE, Size=15, Len=8, Data=374.12] 4500314845359 15:40:31.637 . Column [N=5, Type=DOUBLE, Size=15, Len=8, Data=2566.76] 4500315001246 15:40:31.653 << Fetched [Row=0] 4500315157831 15:40:31.668 . ENTER SQLFetch HSTMT 0x06D7CD48