Devart Forums

My situation:
(newbie on this part)
I am building a webservices app.
My query is on a tdsServermodule.
The function builds and runs a query, returns tDataset :
function GetCustomer(CustUID : string=''; withCustom : boolean = false) : tDataSet;

the query that is run :
Select
a.RecUID
, a.UID
, a.SalesUID
, a.Created
, a.Name
, a.AccountNumber
, a.Division
, a.DivisionUID
, a.SMPSystem
, a.SMPSystemUID
, a.WarrantyExp
, a.Address1
, a.Address2
, a.City
, a.St
, a.Zip
, a.Contact
, a.Phone
, a.Fax
, a.Cell
, a.Pager
, a.Email
, a.fPhone
, a.fFax
, a.fCell
, a.fPager
, a.ContactUID
, a.Country
, a.Zone
, a.ZoneUID
, a.PhoneLbl
, a.FaxLbl
, a.CellLbl
, a.PagerLbl
, a.BillName
, a.BillAddress1
, a.BillAddress2
, a.BillCity
, a.BillZip
, a.BillContact
, a.BillPhone
, a.BillSt
, a.BillFax
, a.BillingEmail
, a.fBillPhone
, a.fBillFax
, a.BillContactUID
, a.BillCountry
, a.BillingUID
, a.BillPhoneLbl
, a.BillFaxLbl
, a.Installer
, a.InstallerUID
, a.SalesPerson
, a.SalesPersonUID
, a.BusinessType
, a.BusinessTypeUID
, a.TaxID
, a.TaxGroup
, a.TaxGroupUID
, a.Terms
, a.TermUID
, a.DateMoved
, a.PONumRequired
, a.URL
, a.LDCarrier
, a.LDCarrierUID
, a.Tech1
, a.Tech1UID
, a.Tech2
, a.Tech2UID
, a.CreditCardType
, a.CCTypeUID
, a.CreditCardName
, a.Comments
, a.Current30
, a.Net30
, a.Net60
, a.Net90
, a.YTD
, a.Balance
, a.Status
, a.DepositOnAccount
, a.LastService
, a.UseCustRates
, a.TripCharge
, a.LaborRate
, a.CreditLimit
, a.MaintCost
, a.MaintExp
, a.Premium
, a.MatTaxRate
, a.LaborTaxRate
, a.OrderTaxRate
, a.ServiceTaxRate
, a.ExciseTaxRate
, a.FinanceCharge
, a.MatPriceLvl
, a.MailName
, a.MailAddress1
, a.MailAddress2
, a.MailCity
, a.MailSt
, a.MailZip
, a.MailContact
, a.MailPhone
, a.MailingEmail
, a.fMailPhone
, a.MailContactUID
, a.MailCountry
, a.MailPhoneLbl
, a.MailAddInvoice
, a.MailAddLabel
, a.LastAction
, a.IsActive
, a.MsgMaint
, a.MsgWarr
, a.MsgPastDue
, a.IsBillable
, a.CustID
, a.QBModified
, a.IAccountID
, a.IAccountName
, a.PAccountID
, a.PAccountName
, a.DAccountID
, a.DAccountName
, a.ParentID
, a.QBTaxID
, a.QBExport
, a.FullName
, a.MinService
, a.MinHours
, a.MinServiceUID
, a.ServiceMsg
, a.MinHoursUID
, a.HoursMsg
, a.AddExciseTax
, a.QBNonTaxed
, a.ClassID
, a.ClassUID
, a.Class
, a.PriceBookUID
, a.PriceBook
, a.LocationUID
, a.Location
, a.ConvertDate
, a.DocDir
, a.CertCodeUID
, a.CertCode
, a.HasLead
, a.FormCode
, a.WhoEdit
, a.EditDateTime
, a.EditCount
, a.EditModule
, a.LocationID
, a.EditUID
From Cust a
Where a.UID = 'CM022610154188244058'

The issue:
if I use a tUniQuery, I get a list index out of bounds (158) error sometime after the end of the function.
If I use a tSQLQuery, I have no issues.
Any ideas where to start looking?
(btw, I am using the trial version of unidac and Delphi XE Enterprise, with a MSSQL database)

Hello,

I could not reproduce the problem.
Please provide a text of your function or send a complete small sample to alexp*devart*com to demonstrate it.

sent.

Hello,

I have received your project, but it's working correctly.
Please send me a script to create and fill a table.

I had included a smallish database backup in the project (itcomputing.bak)

Hello,

This problem is connected with the SQL Server peculiarity and incorrect behaviuor of the TDBXDataSetTable.GetColumns method. The point is that if a table contains a timestamp field, SQLServer always returns it, even if it isn't included in the SQL query (in this case it is marked as hidden). For hidden FieldDefs TDataSet doesn't create the Fields objects, so the real number of fields does not match to the number of FieldDefs, and the TDBXDataSetTable.GetColumns method (in the DBXDBReaders unit) raises the error.
To resolve the problem, you can add the [EditTimeStamp] timestamp field to your SQL statement.

When I add the timestamp to my field list, neither will work, due to 'cannot convert dbx type tdbxTypes.Bytes into a JSON value'.

When I don't include the time stamp, the DBX works, the Uni does not.
So, I'm guessing that you are saying that UNI will not work for this project.

Does SDAC have the same issue? (I'm guessing it will).

You can solve this problem by setting the UniQuery.ReadOnly property to True. In this case SQL Server doesn't return timestamp fields that weren't included in a query.