vTable constructor failed

Discussion of open issues, suggestions and bugs regarding Virtual Data Access Components for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
kneighbour
Posts: 56
Joined: Wed 08 Oct 2008 04:55

vTable constructor failed

Post by kneighbour » Thu 14 Dec 2017 05:19

I am trying to build a query that reads a bunch of records from a MSSQL database. I use a UniQuery to setup this - and the query is listed below.

I can run the query - ie UniQuery.Open, and I can read the records in and I can process them. Everything with this query is fine. I want to add a single column to this query from a local Firebird database, which is why I am looking at VirtualQuery. Simply to merge in this local data.

I add a VirtualQuery and setup a sourcedatasource to point to the UniQuery previously mentioned. At designtime I make the UniQuery active. I open VirtualQuery Editor, set up the query

Code: Select all

SELECT * FROM iConnect
where 'iConnect' is the tablename of the sourcedataset and then try to execute. I get an error "vTable constructor failed: iConnect".

I can setup a Firebird UniQuery and sourcedataset, and that works ok. But this one to the MSSQL database I cannot.

This is in Delphi XE7, with the latest UniDac (7.1?) installed.

Code: Select all

SELECT
  dbo.icTransactionIndex.AccountId AS "Cust ID",
  dbo.icTransaction.TxTradingDate AS "Trading Day",
  dbo.icTransaction.TxEnd AS "Transaction Time",
  dbo.dbVenues.Name as Venue,
  'unknown' as "Location class",
  dbo.icTransaction.TxLocationName AS "Location Name",
  dbo.icTransaction.TxTillName AS "Terminal Name",
  dbo.icTransaction.TxId AS "Transaction No",
  ROW_NUMBER() OVER (PARTITION BY dbo.icTransaction.TxId Order by dbo.icTransaction.TxId) AS "Transaction line",
  dbo.icTransactionDetailProduct.ProductId AS "Item Number",
  dbo.dbProducts.FullName AS "Item Desc",
  dbo.dbProducts.Size,
  (dbo.icTransactionDetail.DetailQty *-1) AS Qty,
  dbo.icTransactionDetailProduct.ProductCost AS Cost,
  dbo.icTransactionDetailProduct.ProductSell AS "Selling Price",
  (dbo.icTransactionDetail.DetailValue *-1) AS "Sale Value",
  dbo.icTransactionDetailProduct.ProductAPNs AS "Item Barcode",
  dbo.icTransactionDetailProduct.Barcode AS "Barcode Scanned",
  dbo.icTransactionDetailGroups.GroupValue AS "Sub Group",
  icTransactionDetailGroups1.GroupValue AS Dept,
  dbo.icTransactionDetailPrice.PriceName AS "Promo Name",
  dbo.icTransactionDetailPrice.PriceFormula AS "Price Formula",
  dbo.icTransactionDetailPrice.PriceSource AS "Price Source",
  dbo.icTransactionDetailPrice.PriceSender AS "Method of Activation"
FROM
   dbo.icTransaction
  LEFT OUTER JOIN dbo.icTransactionIndex ON (dbo.icTransaction.TxID = dbo.icTransactionIndex.TxID)
  INNER JOIN dbo.dbCustomers ON (dbo.icTransactionIndex.AccountId = dbo.dbCustomers.Id)
  INNER JOIN dbo.icTransactionDetail ON (dbo.icTransactionDetail.TxId = dbo.icTransaction.TxId)
  INNER JOIN dbo.icTransactionDetailProduct ON (dbo.icTransactionDetail.TxId = dbo.icTransactionDetailProduct.TxId)
  AND (dbo.icTransactionDetailProduct.PartId = dbo.icTransactionDetail.PartId)
  AND (dbo.icTransactionDetailProduct.DetailId = dbo.icTransactionDetail.DetailId)
  INNER JOIN dbo.icTransactionDetailGroups ON (dbo.icTransactionDetailProduct.TxId = dbo.icTransactionDetailGroups.TxId)
  AND (dbo.icTransactionDetailProduct.PartId = dbo.icTransactionDetailGroups.PartId)
  AND (dbo.icTransactionDetailProduct.DetailId = dbo.icTransactionDetailGroups.DetailId)
  INNER JOIN dbo.icTransactionDetailGroups icTransactionDetailGroups1 ON (dbo.icTransactionDetailProduct.TxId = icTransactionDetailGroups1.TxId)
  AND (dbo.icTransactionDetailProduct.PartId = icTransactionDetailGroups1.PartId)
  AND (dbo.icTransactionDetailProduct.DetailId = icTransactionDetailGroups1.DetailId)
  LEFT OUTER JOIN dbo.icTransactionDetailPrice ON (dbo.icTransactionDetailProduct.DetailId = dbo.icTransactionDetailPrice.DetailId)
  AND (dbo.icTransactionDetailProduct.TxId = dbo.icTransactionDetailPrice.TxId)
  AND (dbo.icTransactionDetailProduct.PartId = dbo.icTransactionDetailPrice.PartId)
  INNER JOIN dbo.dbProducts ON (dbo.icTransactionDetailProduct.ProductId = dbo.dbProducts.Id)
  AND (dbo.icTransactionDetailProduct.ProductId = dbo.dbProducts.Id)
  LEFT OUTER JOIN dbo.dbLocations ON (dbo.icTransaction.TxLocation = dbo.dbLocations.Id)
  LEFT OUTER JOIN dbo.dbVenues ON (dbo.dbLocations.VenueId = dbo.dbVenues.Id)
WHERE
  dbo.icTransaction.TxTradingDate BETWEEN '2015-07-16' AND '2017-12-14'
  AND (dbo.icTransactionDetail.DetailType = 'ttSale') OR (dbo.icTransactionDetail.DetailType = 'ttReturn') 
  AND dbo.icTransactionDetailGroups.GroupName = 'SUBDEPT' AND icTransactionDetailGroups1.GroupName = 'DEPT' 
ORDER BY
  [Trading Day],
  [Transaction Time],
  [Cust ID],
  [Transaction No]

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

Re: vTable constructor failed

Post by MaximG » Thu 14 Dec 2017 09:42

To investigate the issue, please provide DDL script to create the objects used in your query (icTransaction, icTransactionIndex, etc.)

kneighbour
Posts: 56
Joined: Wed 08 Oct 2008 04:55

Re: vTable constructor failed

Post by kneighbour » Fri 15 Dec 2017 03:24

I have narrowed it down a bit. There might be other issues - but when I add the icTransactionIndex table, VirtualQuery does not load it. Note : this query works with any other UniQuery component - so there is nothing wrong with the query itself.

Does not load

Code: Select all

SELECT
  icTransaction.TxTradingDate As [Trading Day],
  icTransaction.TxEnd As [Transaction Time]
From
  icTransaction
  LEFT OUTER JOIN icTransactionIndex ON (icTransaction.TxId = icTransactionIndex.TxId)
Where
  dbo.icTransaction.TxTradingDate BETWEEN '2015-07-17' AND '2017-12-15'
  ORDER BY
  [Trading Day]
Does load

Code: Select all

SELECT
  icTransaction.TxTradingDate As [Trading Day],
  icTransaction.TxEnd As [Transaction Time]
From
  icTransaction
Where
  dbo.icTransaction.TxTradingDate BETWEEN '2015-07-17' AND '2017-12-15'
  ORDER BY
  [Trading Day]
this is the icTransaction table

Code: Select all

CREATE TABLE dbo.icTransaction (
  TxId            varchar(9),
  TxType          varchar(15) NOT NULL CONSTRAINT DF_icTransaction_TxType DEFAULT 'ttNormal',
  TxClass         varchar(15) NOT NULL,
  TxVersion       varchar(50),
  TxFormat        numeric,
  TxTill          int NOT NULL,
  TxTillName      varchar(80),
  TxLocation      int NOT NULL,
  TxLocationName  varchar(80),
  TxCashier       varchar(20),
  TxStart         datetime,
  TxEnd           datetime,
  TxProduct       money,
  TxMoney         money,
  TxStock         money,
  TxLoyalty       money,
  TxTaxation      money,
  TxCost          money,
  TxQuantity      int,
  TxTradingDate   AS CONVERT([date],dateadd(hour,(-5),[TxEnd])) PERSISTED,
  TxModernFormat  bit NOT NULL CONSTRAINT DF_icTransaction_TxModernFormat DEFAULT 0,
  /* Keys */
  CONSTRAINT PK_icTransaction PRIMARY KEY (TxId)
)
and this is the "problem" table

Code: Select all

CREATE TABLE dbo.icTransactionIndex (
  TxId          varchar(9),
  [Start]       datetime,
  LastUsed      datetime,
  TillId        int,
  TillName      varchar(80),
  LocationId    int,
  LocationName  varchar(80),
  CashierId     varchar(20),
  CashierName   varchar(200),
  TableId       int,
  TableName     varchar(200),
  AccountId     varchar(90),
  Tag           varchar(50),
  TxTotal       money,
  TxOwed        money,
  TxLimit       money,
  Payments      varchar(200),
  /* Keys */
  CONSTRAINT PK_icTransactionIndex PRIMARY KEY (TxId)
)

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

Re: vTable constructor failed

Post by MaximG » Fri 15 Dec 2017 10:51

Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next VirtualDAC build. We are planning VirtualDAC release in the shortest time

kneighbour
Posts: 56
Joined: Wed 08 Oct 2008 04:55

Re: vTable constructor failed

Post by kneighbour » Mon 18 Dec 2017 01:15

Good news! Thanks for the effort.

Post Reply