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

[Steven]

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


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]

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]

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]

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]

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]

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

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.

[Steven]

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

> 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

[AlexP]

Hello,

The size is 20 by default

The correct link to this article:
addrule

[Steven]

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]

Hello,

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