How to concatenate fields via SQL for use in TCRDBGrid?

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

How to concatenate fields via SQL for use in TCRDBGrid?

Post by Steven » Sat 14 Dec 2013 17:28

I've got a contact database and I need to extract the contacts First & Last name as a single entity for use in a DBGrid column.

I've done this with other database formats but can't get it to work in SQLite using the TLiteQuery component.

For example the following code fails:

Code: Select all

sSQL := 'Select Vendors.*, (Contacts.First + '' '' + Contacts.Last) as Contact ' +
        ' from Vendors, Contacts' +
        ' WHERE Contacts.Cont_ID = Vendors.Cont_ID';
I am using the trial version of LiteDAC if that makes any difference.
Last edited by Steven on Mon 16 Dec 2013 18:14, edited 1 time in total.

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

Re: How to concatenate fields via SQL for use in DBGrid?

Post by AlexP » Mon 16 Dec 2013 08:14

Hello,

For string concatenation in SQLite, the "||" symbol is used instead of "+". You can find more detailed information in the SQLite documentation.

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

Re: How to concatenate fields via SQL for use in DBGrid?

Post by Steven » Mon 16 Dec 2013 10:11

Code: Select all

sSQL := 'Select Vendors.*, Contacts.First || '' '' || Contacts.Last as Contact ' +
        ' from Vendors, Contacts' +
        ' WHERE Contacts.Cont_ID = Vendors.Cont_ID'
The fields being concatenated are VarChar fields 65 characters in length.
In TCRDBGrid the contents of the Contact field show as (MEMO)
If you click on the cell a panel opens and displays the concatenated contact name.
Is there a way to have the text e.g. 'Susan Someone' show in the cell?
You can find more detailed information in the SQLite documentation.
I looked and sorry but that information is Not in the LiteDAC help that installs in the Delphi IDE.

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

Re: How to concatenate fields via SQL for use in DBGrid?

Post by AlexP » Mon 16 Dec 2013 11:21

Hello,

If you are using concatenation, aggregating functions (MAX, AVG, ...), etc., SQLite returns the Unknown type for such fields. For correct data display, you should use DataTypeMapping http://www.devart.com/litedac/docs/data ... apping.htm .
The SQLite documentation is available on the developer's website, our product distribution includes only our product documentation.

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

Re: How to concatenate fields via SQL for use in DBGrid?

Post by Steven » Mon 16 Dec 2013 18:13

Hi Alex,

Appreciate the help, thanks for the link to the documentation.
While the documentation was somewhat useful - I found the applicable section of "Rules for a particular field" to be too case specific and too cryptic to be an answer in itself.

Providing an example would have clarified what the documentation was trying to say, would have helped resolve my issue and saved some frustration.

I finally figured it by hunting through the forum for users having similar issues.
Here'e the solution in case anyone else runs into similar issues.

In the code example below Contact is the name of my concatenated field.

Code: Select all

LiteQuery1.SQL.Text := sSQLtextString;
LiteQuery1.DataTypeMap.AddFieldNameRule('Contact', ftString);
LiteQuery1.Open;

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

Re: How to concatenate fields via SQL for use in TCRDBGrid?

Post by AlexP » Tue 17 Dec 2013 11:44

Hello,

If you haven't found description of any class, property, etc., or it seems to you that description is not clear or full enough, please let us know - and we will correct the documentation.

Post Reply