Devart Forums

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

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.

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

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

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

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

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

Latest nightly build fixes this problem.

Best Regards,

Aggie85

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