Problem with having to DataTypeMap all fields when query contains UNION clause

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Problem with having to DataTypeMap all fields when query contains UNION clause

Post by Steven » Tue 14 Jan 2014 13:36

Problem with having to DataTypeMap all fields when query contains UNION.

I need to use a UNION to pull all records in my Contact table and if the record is linked to a company in the Vendor table show which vendor.
If I use either of the subqueries I don't have a problem but once combined with the UNION clause I have to DataTypeMap ALL the fields (except DateTime) or else TCRDBGrid shows their values as (MEMO).

Code: Select all

      'SELECT Contact.*, Contact.First || " " || Contact.Last as Contact, "" AS Vendor, "0" AS Vendor_ID' +
      ' FROM Contact' +
      ' WHERE Contact.Cont_ID NOT IN (SELECT CONT_ID FROM VENDOR)' +
      'UNION ' +
      'SELECT Contact.*, Contact.First || " " || Contact.Last as Contact, Vendor.Company as Vendor, Vendor.Vendor_ID' +
      ' FROM Contact, Vendor' +
      ' WHERE Contact.Cont_ID != 0' +
      '   AND Contact.Cont_ID = Vendor.Cont_ID';
Even more of a problem is that I also have to specify the fieldsize for all these fields Image
or I get 'String value is too long' errors.
Unfortunately getting one of these errors would more often than not lock up Delphi (the error message would appear but neither the Break or Continue buttons would function) requiring me to use Windows Taskmanager to terminate Delphi.
Image

Aside from the above mentioned issues - how would I specify the DataTypeMap field size (such as I was having to create in the component interface) in code?
For example I'm using the following rule to handle the field Contact which is a concatenation of the FirstName & LastName fields:

Code: Select all

ltqryProduct.DataTypeMap.AddFieldNameRule('Contact', ftString);

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

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by AlexP » Tue 14 Jan 2014 14:59

hello,

This is correct behavior, since when using the UNION operator, SQLite doesn't return the field length - and such fields are mapped as Memo.
This error occurs due to the fact, that on an attempt to record data to a string, the data length is greater than the specified field size. To avoid this error, you should either increase the size or set the IgnoreError property. When setting this property, data will be cut to the specified size.
The 'String value is too long' error will occur on every record and field, which data size is greater than the size specified in DataTypeMapping. Therefore, if you have N such fields in the table, the error occurs N times.

You can use several overload methods to set various mapping parameters
http://www.devart.com/litedac/docs/inde ... perule.htm
http://www.devart.com/litedac/docs/inde ... merule.htm
http://www.devart.com/litedac/docs/deva ... ddrule.htm

Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by Steven » Tue 14 Jan 2014 17:52

Thank you for the quick response & information.

In regards to
> the data length is greater than the specified field size.

By specified field size you are referring to the size of the table structure?
In no case where I got the field size error was the string longer than the field size of the table**. In the error screenshot of the previous post the email address string is approximately 30 characters long and the field length was 75 - I had to enter a field length in the datatypemapping (I just matched the field sizes of the table) to be able to avoid throwing errors.

**just to be clear - these errors were being thrown by data already in the database, not by attempts to edit the data or add new data.

Code: Select all

CREATE TABLE [Contact] (
  [Cont_ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT CONSTRAINT [cont_id] UNIQUE, 
  [Last] varchar(65), 
  [First] varchar(65), 
  [Address] varchar(65), 
  [Address2] varchar(65), 
  [City] varchar(65), 
  [State] varchar(2), 
  [Postal_Code] varchar(10), 
  [CountryCode] varchar(2), 
  [Phone] varchar(10), 
  [Email] varchar(75), 
  [Added] DATETIME, 
  [LastUpdate] datetime, 
  [Active] boolean DEFAULT 1, 
  [Notes] VARCHAR(256), 
  [AddressIsVendor] BOOLEAN DEFAULT 1);

Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by Steven » Wed 15 Jan 2014 21:44

Just an additional note.

When LiteDAC throws a "string too long" error (or others) - more often than not the Delphi IDE appears to be hung and I ended up having to terminate Delphi and then restart it.

I just discovered that these errors may not actually hang Delphi - the problem is that the error message box that pops up does not respond to mouse clicks! Yet it does respond to keyboard input, so there is a way to recover! Don't know if this will work with the "Cannot perform this operation on a closed dataset' error that I was getting before I fixed things, but I bet it does.

I don't know if this particular problem is a Delphi Xe2 issue, LiteDAC issue, or what - just reporting it.
It (the not responding to mouse clicks) doesn't happen all the time, but most of time.

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

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by AlexP » Thu 16 Jan 2014 12:26

Hello,

This error occurs when the real data length (not the field size) is greater than the size specified in the DataTypeMapping parameters.
Error message forms (Raise Exception) are generated by Delphi, therefore our components have no concern with their "hanging"

Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by Steven » Thu 16 Jan 2014 14:50

AlexP wrote: This error occurs when the real data length (not the field size) is greater than the size specified in the DataTypeMapping parameters.
As stated in the documentation at:
http://www.devart.com/litedac/docs/inde ... perule.htm
TDAMapRules.AddRule Method wrote: Remarks
One of two parameters requires to be specified: FieldName or DBType. Also, it is required to specify the FieldType parameter. The other parameters are not required, therefore it is allowed to set the rlAny constant for them instead of a specific value. If the rlAny constant is set, then the given rule will be applied for all fields independently on their length and scale.
For example, if it is necessary to set the field length in a database to 20 or more, then DBLengthMin should be set to 20, and DBLengthMax - to rlAny.
If it is necessary to set scale to 5 or less, then DBScaleMin should be set to rlAny, and DBScaleMax - to 5.
So then the real question is - what are the defaults?
There is nothing in the documentation that states this.
This is vital information if I am to prevent errors from occurring with deployed applications.
Last edited by Steven on Thu 16 Jan 2014 15:09, edited 2 times in total.

Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by Steven » Thu 16 Jan 2014 14:58

> http://www.devart.com/litedac/docs/deva ... ddrule.htm
The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.

other links are OK
Last edited by Steven on Thu 16 Jan 2014 15:10, edited 1 time in total.

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

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by AlexP » Thu 16 Jan 2014 15:10

Hello,

The size is 20 by default

The correct link to this article:
addrule

Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by Steven » Thu 16 Jan 2014 15:35

Thank you for the info.
Just trying to get a handle on things, not only to understand the issues that I have run into but to prevent accidentally creating other problems.

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

Re: Problem with having to DataTypeMap all fields when query contains UNION clause

Post by AlexP » Fri 17 Jan 2014 10:44

Hello,

If you have any further questions, feel free to contact us.

Post Reply