Losing Faith!

Discussion of open issues, suggestions and bugs regarding Virtual Data Access Components for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Losing Faith!

Post by Aggie85 » Wed 31 May 2017 03:29

Howdy All!

I am trying to replace 15+ year old TxQuery with TVirtualQuery and I keep hitting a brick wall.

The first attempt SQL statement I tried against a memory table returned the the following results (TxQuery version works):

Is TVirtualQuery a beta beta component @ this point?

Error: Error: Access violation at address 02B9B1A2 in module 'TheVirtualTicketer.exe'. Read of address FFFFFFFC

SQL: SELECT PeriodYear,AccountNumber,AccountDescription,
SUM( CASE PeriodMonth
WHEN 1 THEN Amount
ELSE 0.0
END
) AS PeriodAmount01,
SUM( CASE PeriodMonth
WHEN 2 THEN Amount
ELSE 0.0
END
) AS PeriodAmount02,
SUM( CASE PeriodMonth
WHEN 3 THEN Amount
ELSE 0.0
END
) AS PeriodAmount03,
SUM( CASE PeriodMonth
WHEN 4 THEN Amount
ELSE 0.0
END
) AS PeriodAmount04,
SUM( CASE PeriodMonth
WHEN 5 THEN Amount
ELSE 0.0
END
) AS PeriodAmount05,
SUM( CASE PeriodMonth
WHEN 6 THEN Amount
ELSE 0.0
END
) AS PeriodAmount06,
SUM( CASE PeriodMonth
WHEN 7 THEN Amount
ELSE 0.0
END
) AS PeriodAmount07,
SUM( CASE PeriodMonth
WHEN 8 THEN Amount
ELSE 0.0
END
) AS PeriodAmount08,
SUM( CASE PeriodMonth
WHEN 9 THEN Amount
ELSE 0.0
END
) AS PeriodAmount09,
SUM( CASE PeriodMonth
WHEN 10 THEN Amount
ELSE 0.0
END
) AS PeriodAmount10,
SUM( CASE PeriodMonth
WHEN 11 THEN Amount
ELSE 0.0
END
) AS PeriodAmount11,
SUM( CASE PeriodMonth
WHEN 12 THEN Amount
ELSE 0.0
END
) AS PeriodAmount12,
SUM(Amount) AS TotalAmount
FROM ReportRevenuesLedger
GROUP BY PeriodYear,AccountNumber,AccountDescription
ORDER BY PeriodYear,AccountNumber,AccountDescription


SQL: SELECT PeriodYear,AccountNumber,AccountDescription,
SUM( CASE PeriodMonth
WHEN 1 THEN Amount
ELSE 0.0
END
) AS PeriodAmount01,
SUM( CASE PeriodMonth
WHEN 2 THEN Amount
ELSE 0.0
END
) AS PeriodAmount02,
SUM( CASE PeriodMonth
WHEN 3 THEN Amount
ELSE 0.0
END
) AS PeriodAmount03,
SUM( CASE PeriodMonth
WHEN 4 THEN Amount
ELSE 0.0
END
) AS PeriodAmount04,
SUM( CASE PeriodMonth
WHEN 5 THEN Amount
ELSE 0.0
END
) AS PeriodAmount05,
SUM( CASE PeriodMonth
WHEN 6 THEN Amount
ELSE 0.0
END
) AS PeriodAmount06,
SUM( CASE PeriodMonth
WHEN 7 THEN Amount
ELSE 0.0
END
) AS PeriodAmount07,
SUM( CASE PeriodMonth
WHEN 8 THEN Amount
ELSE 0.0
END
) AS PeriodAmount08,
SUM( CASE PeriodMonth
WHEN 9 THEN Amount
ELSE 0.0
END
) AS PeriodAmount09,
SUM( CASE PeriodMonth
WHEN 10 THEN Amount
ELSE 0.0
END
) AS PeriodAmount10,
SUM( CASE PeriodMonth
WHEN 11 THEN Amount
ELSE 0.0
END
) AS PeriodAmount11,
SUM( CASE PeriodMonth
WHEN 12 THEN Amount
ELSE 0.0
END
) AS PeriodAmount12,
SUM(Amount) AS TotalAmount
FROM ReportRevenuesLedger
GROUP BY PeriodYear,AccountNumber,AccountDescription
ORDER BY PeriodYear,AccountNumber,AccountDescription

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Losing Faith!

Post by AlexP » Wed 31 May 2017 06:31

Hello,

The provided queries are correct, when linking VirtualTable and VirtualQuery the error does not occur. Please compose a small smaple reproducing the error and send it to support*devart*com.
P.S. Also specify the exact IDE and VirtualTable versions.

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Losing Faith!

Post by Aggie85 » Fri 02 Jun 2017 19:59

Howdy!

Can you explain exactly how TVirtualQuery works with NON TVirtualTable based data sets?

All of my memory tables I plan on feeding to TVirtualQuery use TkbmMemTable.

I have too many tables to switch to TVirtualTable and TkbmMemTable does a lot of other functions that I still need in my software.

Thanks,

Aggie85

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Losing Faith!

Post by Aggie85 » Mon 05 Jun 2017 02:17

Howdy!

I found the problem in my implementation!

In TxQuery and TkbmMemSQL, I add all my data sets in my create statement. I then reuse the object as needed issuing the appropriate SQL statement. TxQuery and TkbmMemSQL are smart enough to only use reference the tables needed.

In TVirtualQuery, I need to only set the the SourceDataSets that are used in the SQL statement.

Is this a WAD?

Thanks,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Losing Faith!

Post by MaximG » Thu 08 Jun 2017 07:17

You are absolutely right. The data sources for VirtualQuery should be specified using the SourceDataSets property ( https://www.devart.com/virtualdac/docs/ ... tasets.htm ). Additional information about using VirtualQuery is described in our blog : http://blog.devart.com/how-to-combine-d ... query.html

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Losing Faith!

Post by Aggie85 » Thu 08 Jun 2017 14:58

Howdy Max!

What I mean i that in TxQuery, I can put all my datasets at design time then I can run n# of SQL statements with no issue.

In TVirutalQuery, the SourceDataSets MUST only be for the SQL statement being executed. If I have any additional that are NOT needed, I get errors.

I have adjusted my framework so this is just a little PITA but no big deal!

Thanks,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Losing Faith!

Post by MaximG » Fri 09 Jun 2017 11:29

Please check the described behavior again using UniDAC night build, the link for download of which we have sent you earlier

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Losing Faith!

Post by Aggie85 » Fri 09 Jun 2017 23:21

Latest nightly build fixes this problem.

Best Regards,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Losing Faith!

Post by MaximG » Mon 12 Jun 2017 10:17

We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning VirtualDAC usage.

Post Reply