Jump to content
Jasonjac2

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

Recommended Posts

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 by Jasonjac2

Share this post


Link to post

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 by programmerdelphi2k

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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.

Share this post


Link to post

why do you need  ClientDataset? some special?

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

Edited by programmerdelphi2k

Share this post


Link to post

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?

Share this post


Link to post

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.

Share this post


Link to post

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 by programmerdelphi2k

Share this post


Link to post

SELECT * FROM tableX where

invoice = 99 OR invoice = 98

----

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

Edited by programmerdelphi2k

Share this post


Link to post

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.

Share this post


Link to post

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 by Brian Evans

Share this post


Link to post

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 by programmerdelphi2k

Share this post


Link to post

@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.

Share this post


Link to post

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,  

Share this post


Link to post

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.

Share this post


Link to post

@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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post

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.

 

Share this post


Link to post
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.

 

Share this post


Link to post

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:

.

 

  • Like 1

Share this post


Link to post

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

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×