List index out of bounds error

ccmcbride · Post by **ccmcbride** » Fri 06 May 2011 14:07

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)

AlexP · Post by **AlexP** » Fri 06 May 2011 14:38

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.

ccmcbride · Post by **ccmcbride** » Fri 06 May 2011 15:17

sent.

AlexP · Post by **AlexP** » Tue 10 May 2011 12:07

Hello,

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

ccmcbride · Post by **ccmcbride** » Tue 10 May 2011 15:33

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

AlexP · Post by **AlexP** » Wed 11 May 2011 14:54

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.

ccmcbride · Post by **ccmcbride** » Wed 11 May 2011 15:35

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

AndreyZ · Post by **AndreyZ** » Thu 12 May 2011 14:14

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.