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

[Lars Fosdal 1790]

Not from the top of my head.

I would definitively check the monitor to see if there is something enlightening in there?

[Jasonjac2 0]

Looks like you can't "savetofile" for a unidirectional query.  As I can't hook up to a grid, I was hoping to just saveToFile <filename>, JSON to keep it simple.

[Lars Fosdal 1790]

I don't see any such limitation mentioned?

[Lajos Juhász 293]

You cannot hook up DB controls to an unidirectional query but that should not stop you from inspecting and saving it. 

 

I am using FireDAC with ODBC driver from the first version (to connect to Informix database). The data is never corrupted. The only problem I am aware is when writing to database FireDAC ignores the database locale and uses the default Windows locale for non-unicode language to write data. 

[Lars Fosdal 1790]

2 minutes ago, Lajos Juhász said:

You cannot hook up DB controls to an unidirectional query

Good point. I forgot about that since we don't use DB controls but feed regular grids from our query data.

[Jasonjac2 0]

Unidirectional = same. As @Lajos Juhász says, you can't hook up grid OR in fact issue a fdQuery.savetofile it would appear.  I had to cobble together a copy to clipboard for my test.  I can confirm that D11Upd1 32 bit on Sage V28 using unidirectional still exhibits the same behaviour.   And on the example where I select 2 x rows using an OR, the actual value in the column that is wrong is actually the value in the very last entry in the whole table.  i.e. it shouldn't even be part of the resultset.

 

I will try once with the logging turned on and see what it gives me.  Will it give me all the data being pulled back from down the pipe?

 

Are there any good / great articles / white papers / books on FD that are better than Google + docwiki?

[Frickler 11]

Have you tried ADO ("dbGo") instead of FireDAC?

[Lajos Juhász 293]

Now I see that TFDDataSet.SaveToStorage indeed is calling CheckFetchedAll - now I would like to know the reason why is that required.

[programmerdelphi2k 237]

as said on Help:

Quote

isUniDirectional: do not support filters, bookmarks, lookup fields, or any other features that require an internal record buffer. You can't use a unidirectional dataset as the source to a data-aware grid.

and, as we can change the "directional" mode, then is necessary check it.

[Jasonjac2 0]

@Lars Fosdal starting to loose the will :-(.  But I have enabled FD logs and also enabled ODBC logs.  I don't think ODBC logs gives me data being returned, so I can't have a smoking gun for Sage being the culpret. I will post each of the tests below as separate posts for clarity I think.

 

I have pulled out bits from the logs, I am happy to supply you with the logs if they make any more sense from you.

There will be a conclusion at the end.

 

Post1 - single row

 

SageExportTest20230303_11_1_32Bit 08_1507.wasexe

 

20230308_1537 Demo 11U1 32 Bit.txt

08/03/2023 15:38 List ODBC

08/03/2023 15:38 Connect

08/03/2023 15:38 Select

select invoice_number, invoice_type, ITEMS_NET, ITEMS_TAX, ITEMS_GROSS

 from Invoice where invoice_number = 1

 

Copy to Clipboard

INVOICE_NUMBER        INVOICE_TYPE        ITEMS_NET        ITEMS_TAX        ITEMS_GROSS

1        Product Invoice        1123.04        191.63        1314.67

 

Correct ITEMS_GROSS

 

499603908269 15:39:20.543         >> Fetched [Row=0]

4499604064212 15:39:20.559              . Column [N=1, Type=SLONG, Size=10, Len=4, Data=1]

4499604221005 15:39:20.574              . Column [N=2, Type=CHAR, Size=60, Len=15, Data='Product Invoice']

4499604377054 15:39:20.590              . Column [N=3, Type=DOUBLE, Size=15, Len=8, Data=1123.04]

4499604533425 15:39:20.606              . Column [N=4, Type=DOUBLE, Size=15, Len=8, Data=191.63]

4499604688718 15:39:20.621              . Column [N=5, Type=DOUBLE, Size=15, Len=8, Data=1314.67]

4499604688873 15:39:20.621         << Fetched [Row=0]

4499604845731 15:39:20.637          . ENTER SQLFetch

HSTMT               0x06D7CD48

 

From Logs: 4499602502605 - > 4499605626273

[Jasonjac2 0]

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

[Jasonjac2 0]

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

[Jasonjac2 0]

1 hour ago, Frickler said:

Have you tried ADO ("dbGo") instead of FireDAC?

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.

[weirdo12 19]

Have you tried this setting? It's just a shot in the dark.

 

Quote

Note: Some ODBC drivers (such as Oracle ODBC driver v 11) have a bug that leads to an Access Violation on data fetching. To workaround this bug, set FetchOptions.RowsetSize to 1.

 

Edited March 8, 2023 by weirdo12
Clarification

[programmerdelphi2k 237]

a test using a XLS file Excell 2003 by FireDAC ODCB 32bits on MSWin 10 ... nothing more!!!

• I not use (not installed) MSOffice in my system

 

 

Quote

• file_example_XLS_10.xls is a Microsoft Excel worksheet sheet (97–2003)
  •   https://file-examples.com/index.php/sample-documents-download/sample-xls-download/
•   MyDSNexcell.dsn is a DSN created on MSWindows 10 using ODBC 32bits tool
•   Nothing was installed on Delphi or MSWindows, just default by default (including in components)

 

ODBC_Excell_sheet.7z

Edited March 8, 2023 by programmerdelphi2k

[Lars Fosdal 1790]

This is indeed weird.

Have you called CoInitialize(nil);  CoUnInitialize; at start/end of app/threads?

[Jasonjac2 0]

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

 

[Lars Fosdal 1790]

I thought ckForwardOnly was implicit when setting Unidirectional to true.

When you look at the Monitor log - are all field types what you expect them to be?

 

Personally, I always use CoInitialize(nil);  CoUnInitialize; for projects that use a DB driver. Also when there is only a single main thread.

[Jasonjac2 0]

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 1790]

CoInit/CoUnInit -  Prior to starting DB components, and after the last disconnect

I do this because I have zero control over what a DB driver does with regards to COM.

[Tom Chamberlain 47]

4 hours ago, Lars Fosdal said:

CoInit/CoUnInit -  Prior to starting DB components, and after the last disconnect

I do this because I have zero control over what a DB driver does with regards to COM.

Interesting, we only call CoInit/CoUnInit in new threads before/after new connections are created/freed, never in the main (GUI) thread which has DB queries and data-aware components (to many, but 20+yr old code) but only one shared DB connection for the GUI in a data module.  But every thread inside services get CoInit/CoUnInit's.  We have only ever used M$/SQL, used the BDE for the first 5yrs and switched to ADO in 2003 or 2004 which has be sufficient for our needs until changes to M$/SQL date field types have forced us to make the switch to FireDac and the latest ODBC SQL drivers.  We have had some learning pain's with FireDac but nothing like theses issues. 

 

 

[Jasonjac2 0]

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

 

 

[Lars Fosdal 1790]

@Jasonjac2 I haven't read Cary's FD books, so I don't know if there has been any amendments?

http://www.jensendatasystems.com/aboutcj.html

[Jasonjac2 0]

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.

[Sriram 4]

On 3/8/2023 at 8:19 AM, Jasonjac2 said:

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

Jason,

Here's a wild guess. I have no knowhow of Sage, and FD+ODBC. Please discard if it does not make any sense.

 

#1. The column with the bad value (multi-row select) seems to be always the last column in your samples. Do you get the right values if the column is mentioned earlier in the list?

 

#2. What is the definition of the column ITEMS_GROSS in the INVOICE table? Is it by chance a computed by value using any Window Function, perhaps LAST_VALUE()? The last value in a partitioned result set is returned for all the rows in the result set. That would explain the 214.32 value from row 84 coming for all rows.

Perhaps the optimizer in Sage does not use an index when you mention invoice_number =1 or 2, and use all the rows (including row 84); and the Window Function is perhaps applied on all the rows (incorrectly) before the OR condition is applied.