D11 Update 1 + FireDAC + ODBC to Sage returning wrong data!

[Jasonjac2 0]

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

 

 

 

Edited March 7, 2023 by Jasonjac2

[programmerdelphi2k 237]

hi @Jasonjac2

 

if your problem is about use ODBC in FireDAC, you can do it as usual in any other case, for example:

Excell file access:

1. with ODBC installed in your PC, 32 or 64bits edition
2. in MSWin use ODBC tools (see in Control Panel or any other shortcut in MSwin) and create a "DNS" file with all setup necessary to access your EXCELL file.
3. open this file and see what is necessary to config your FDConnection FireDAC
4. in Delphi, in FireDAC you'll needs this components:
5. a FDxxxWait, FDxxPhysODBC driver, FDxxxConnect, FDxxQuery, basically.
6. now, just config your FDConnection you can use ADVANCED config to put all DSN file config (the string connection). it's simple way.
7. now connect your FDQuery on FDConnection and fill your SQL
8. to easy usage, use "PARAMS"  in FDQuery to change the values on "where" clause, for example.

fdqry.close;

fdqry.Param.clear;

fdqry.SQL.Text:= 'select .... from .... where YourField =  :YourParamName';

fdqry.Param.ParamByName( 'YourParamName' ).AsInteger := 11111;

fdqry.Open;

....

 

when you need change the value on Param, just use this:

fdqry.Param.ParamByName( 'YourParamName' ).AsInteger := 22222;

fdqry.Refresh;

 

 

https://intercom.help/ssgpenelope/en/articles/5189639-how-to-create-an-odbc-data-connection-in-excel

 

https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Connect_to_ODBC_Data_Source_(FireDAC)

Edited March 7, 2023 by programmerdelphi2k

[Jasonjac2 0]

very basic source.  This was built to 32 bit release. It should hook up to any 32 bit ODBC DSN, but I'm especially interested in Sage V29 to see if it is just me.

 

If you are familiar with FD & ODBC, then I would love for you to look at the app, in case I am doing something silly (like a boolean BreakNumbers = true!!)

SageExportTest20230303.zip

[Jasonjac2 0]

10 minutes ago, programmerdelphi2k said:

hi @Jasonjac2

 

if your problem is about use ODBC in FireDAC, you can do it as usual in any other case, for example:

Excell file access:

1.  

Thanks for the input.  I am getting data, just the wrong data, i.e. not what is in the column, so I think I am way past connection strings etc.  This is about a select that returns different field values depending on the number of rows returned, i.e. highly looks like a bug in D + FD+ ODBC.

[Jasonjac2 0]

I have now built in D11 Upd1 and D11.3 32 bit and both exhibit the same issue.  As we only have one Sage install, I could do with seeing if anyone can reproduce.

[programmerdelphi2k 237]

why do you need  ClientDataset? some special?

fdQuery can save in disk too!!! did you know?

Edited March 7, 2023 by programmerdelphi2k

[programmerdelphi2k 237]

first, you need test your connection string, if ok...

2nd, fdconnected, now you need just a fdquery as usual.

3rd, in fdquery, the sql xpression is the same than any other table access, same fields declaration, and others filters.

nothing different than others data sources. you see?

[Jasonjac2 0]

It all connects OK and I get data. Did you look at the youtube clip I recorded above?

TClientDataset - ignore that, it is a not relevant now I have got it down to the minimum set up to reproduce.

[programmerdelphi2k 237]

im on cell now, then the point is:

1. connections string ok... yeah db opened
2. fdquery sql open the records (all records without any "where") and show my fields choiced? yeah table opened
3. then just now use your filter, like "where" above

did you try this?

Edited March 7, 2023 by programmerdelphi2k

[programmerdelphi2k 237]

SELECT * FROM tableX where

invoice = 99 OR invoice = 98

----

invoice in (98,99)  ... invoice between 98 to 99

Edited March 7, 2023 by programmerdelphi2k

[Jasonjac2 0]

Sorry, @programmerdelphi2kI know you are trying to be helpful, but it isn't the selection of the rows.  Somehow the TField.data is becoming corrupt. 

For Invoice_number 99 you get 2 different values for FOREIGN_INVOICE_NET

FOREIGN_INVOICE_NET="1300" (Actually -5.83 if selected via excel & ODBC or using the specific Where clause)

 

select Invoice_number, FOREIGN_INVOICE_NET where invoice_number = 99

returns different data for Invoice Number 99 than

select Invoice_number, FOREIGN_INVOICE_NET where invoice_number <=99

 

i.e. if you goto the invoice_number = 99 row, the field FOREIGN_INVOICE_NET has a different value.

[Brian Evans 105]

Odd that each value is off by a factor of around -222. The common factor seems to suggest a currency conversion but the flipped sign doesn't.

 

 

Edited March 7, 2023 by Brian Evans

[programmerdelphi2k 237]

I understand! 

maybe some necessity of clean old read... like clear cache on memory...

each read needs a transaction roolback or commit ... did try?

Firedac create 1 transaction internally for when not explicitly created.

Edited March 7, 2023 by programmerdelphi2k

[Jasonjac2 0]

@Brian Evans actually I am pretty sure the value gets "stuck", i.e. the data for that column for ALL rows is then the same, but not for all columns. I mean, that value is actually found in another row.  The string columns and a lot of other columns all seem to have the correct data, but there are a bunch of them that break. 

 

Initially I thought I was breaking some kind of record for 125 columns or whatever Sage returns, but reducing it to a simple 4 column and 4 rows is enough to break it.

 

I just need to see if it is reproducable on anyone else's set up and then try and report it to EMBT.  Actually I hope it is something I am doing wrong, maybe in the connection settings, i.e. just setting the Driver to ODBC, setting DSN, username and password is about all I do.  I attached the code in my second post ...zip if you want to take a look.

[Brian Evans 105]

Might want to clear out the DataSetProvider and ClientDataSet in case they are interfering somehow - they are still hooked up to the FDQuery in the file attached to your second message,  

[Stano 143]

I don't think that's your problem. But we're gonna write it here anyway.
I got the contents of the tables (Paradox) in *.xls. I copied them to *.csv. Then imported them into Firebird. I had errors of type String cannot be converted to integer even for date. I selected the entire column(s) in Excel and formatted. I repeated the whole procedure. Everything worked.

[Jasonjac2 0]

@brian

9 hours ago, Brian Evans said:

Might want to clear out the DataSetProvider and ClientDataSet in case they are interfering somehow - they are still hooked up to the FDQuery in the file attached to your second message,  

Thank - I didn't realise they were hooked up.  They really shouldn't make any difference until the TClientDataset has opened, but I totally get your point and will do another test.

[Jasonjac2 0]

18 minutes ago, Stano said:

I don't think that's your problem. But we're gonna write it here anyway.
I got the contents of the tables (Paradox) in *.xls. I copied them to *.csv. Then imported them into Firebird. I had errors of type String cannot be converted to integer even for date. I selected the entire column(s) in Excel and formatted. I repeated the whole procedure. Everything worked.

Thank for the additional info, as you say, it won't be applicable here as I am doing the select from the ODBC source to Sage, not via Excel and it isn't a format issue.

[Lars Fosdal 1792]

@Jasonjac2 Do you get the same problems with the 64-bit ODBC and a 64-bit Delphi app?

[Jasonjac2 0]

4 minutes ago, Lars Fosdal said:

@Jasonjac2 Do you get the same problems with the 64-bit ODBC and a 64-bit Delphi app?

That is my next check - we don't use any 64 bit apps onsite, so hadn't even thoughts about it, so just built a 64 bit version and going to create a 64 bit DSN now.  Thanks for the thought.  In terms of reporting to EMBT - which is bottom of my priority list prior to getting the data I need, I think it will be tricky as the current use case is Sage specific as I only have a sage example.

[Lars Fosdal 1792]

I was thinking along the lines of eliminating the way the 32-bit ODBC is used as the core problem for the Delphi app.

The next thing I'd look at, would be encoding, language, locale issues.

 

[Jasonjac2 0]

18 minutes ago, Lars Fosdal said:

I was thinking along the lines of eliminating the way the 32-bit ODBC is used as the core problem for the Delphi app.

The next thing I'd look at, would be encoding, language, locale issues.

 

64 bit exhibits the same issue.

Do you think that encoding / language / locale issues are likely.  Just to reiterate the problem in its simplest form:

select one invoice by invoice number and the numbers look right.

Select multiple invoices (2) and some of the fields get incorrect values (same column same value) and it is repeatable.

 

[Lars Fosdal 1792]

FDQuery.FetchOptions - Does changing Unidirectional to True have any effect?

FDQuery.FormatOptions have a TON of settings, including map rules - which may solve encoding issues if the ODBC doesn't provide all the info that FireDAC wants.

I.e. you can explicity specify the type of value that the db field should be translated to.

From the Delphi Tools menu:

FireDAC Explorer can help with experimenting with Format/Fetch options.

FireDAC Monitor can also give good insight into the low level goings on when your application is running.

 

Edit: I also wrote this a few years back:

.

 

[Jasonjac2 0]

10.4.2 32 bit exhibits the same on the Demo Data for Sage v29.  Next to check Lars, setting unidirectional.

 

Also, any others to try?

In my tiny example, I let the fields be created dynamically due to user provided SQL.

Do you think anything about mappings etc could be the cause?

 

Thanks for the article.  I think right now, it is probably a bit deep for me, but I will read as I am going to have to become a FD expert at some pioint. 

I am about to have to break off and work out how to get the data a different way and process it, e.g. -> excel and process it using Delphi to get the user what they want.

 

I need to make it reproducible on different DB's with difference V of Delphi, raise a QP (my colleague says 0 point raising with Sage as they will say "it works with Excel and we don't support Delphi").

45 minutes ago, Lars Fosdal said:

FDQuery.FetchOptions - Does changing Unidirectional to True have any effect?

FDQuery.FormatOptions have a TON of settings, including map rules - which may solve encoding issues if the ODBC doesn't provide all the info that FireDAC wants.

I.e. you can explicity specify the type of value that the db field should be translated to.

From the Delphi Tools menu:

FireDAC Explorer can help with experimenting with Format/Fetch options.

FireDAC Monitor can also give good insight into the low level goings on when your application is running.

 

Edit: I also wrote this a few years back:

.

 

I have a colleague who is going to test on V28 & V29 using my code - recompiled in D11.2

 

[Jasonjac2 0]

Repeatable in Save V28 demo data,  About to test with Unidirectional as suggested by @Lars Fosdal.  Any other tests Lars?