Recent

Author Topic: How to replace the field type and value in dbgrid?  (Read 10566 times)

Vodnik

  • Full Member
  • ***
  • Posts: 210
How to replace the field type and value in dbgrid?
« on: March 27, 2018, 11:06:51 pm »
My SQL query:

 
Code: XML  [Select][+][-]
  1. DBForm.DBquery.SQL.text:='select callstart, callend, origin, destination, direction from callrecord;

termtype are integer values 0...3, instead I would like to show in dbgrid column fields: None, Incoming, Outgoing or Internal, accordingly. These text values are not stored in any DB table to be joined with; it is also not allowed to add smth to this DB (in my case).
Is there a way how to do this "on fly", when the dbgrid obtains the values from the DB? 

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: How to replace the field type and value in dbgrid?
« Reply #1 on: March 28, 2018, 12:50:11 am »
No. You have two choices
1)write an ongettext/onsetetxt event handler for the field you are interested in return the appropriate text to be sown on the grid.
2)add a lookup field on the sqlquery that does not exists in the database, connect its dataset property to the TBufDataset its keyfields property to your database field and its lookupKeys and resultfield to the appropriate bufdataset fields. the lookupkey will be the field that has the same value as your KeyField and resultfield is a text/string field that will show what ever your want for that value.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: How to replace the field type and value in dbgrid?
« Reply #2 on: March 28, 2018, 07:19:47 am »
@Voldink, try:

Code: Pascal  [Select][+][-]
  1. ...
  2. DBForm.DBquery.SQL.text := 'select termtype, callstart, callend, origin, destination, direction from callrecord';
  3. ...
  4.  
  5. procedure TForm1.DBQuery1TERMTYPEGetText(Sender: TField; var aText: string;
  6.   DisplayText: Boolean);
  7. begin
  8.   if (DisplayText) then
  9.   begin
  10.     case Sender.Value of
  11.       0: aText := 'None';
  12.       1: aText := 'Incoming';
  13.       2: aText := 'Outgoing';
  14.       3: aText := 'Internal';
  15.     else
  16.       aText := 'Unknown2';
  17.     end;
  18.   end
  19.   else
  20.     aText := 'Unknown1';
  21. end;
  22.  
  23. procedure TForm1.SQLQuery1TERMTYPESetText(Sender: TField; const aText: string);
  24. begin
  25.   if (aText = 'None') then
  26.     Sender.Value := 0;
  27.   if (aText = 'Incoming') then
  28.     Sender.Value := 1;
  29.   if (aText = 'Outgoing') then
  30.     Sender.Value := 2;
  31.   if (aText = 'Internal') then
  32.     Sender.Value := 3;
  33. end;

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #4 on: March 28, 2018, 10:24:04 am »
No. You have two choices
1)write an ongettext/onsetetxt event handler for the field you are interested in return the appropriate text to be sown on the grid.
2)add a lookup field on the sqlquery that does not exists in the database, connect its dataset property to the TBufDataset its keyfields property to your database field and its lookupKeys and resultfield to the appropriate bufdataset fields. the lookupkey will be the field that has the same value as your KeyField and resultfield is a text/string field that will show what ever your want for that value.
Thank's, taazz! Not sure I have understand everything, but will try to discover.

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #5 on: March 28, 2018, 10:31:32 am »
@Voldink, try:

Code: Pascal  [Select][+][-]
  1. ...
  2. DBForm.DBquery.SQL.text := 'select termtype, callstart, callend, origin, destination, direction from callrecord';
  3. ...
  4.  
  5. procedure TForm1.DBQuery1TERMTYPEGetText(Sender: TField; var aText: string;
  6.   DisplayText: Boolean);
  7. begin
  8.   if (DisplayText) then
  9.   begin
  10.     case Sender.Value of
  11.       0: aText := 'None';
  12.       1: aText := 'Incoming';
  13.       2: aText := 'Outgoing';
  14.       3: aText := 'Internal';
  15.     else
  16.       aText := 'Unknown2';
  17.     end;
  18.   end
  19.   else
  20.     aText := 'Unknown1';
  21. end;
  22.  
  23. procedure TForm1.SQLQuery1TERMTYPESetText(Sender: TField; const aText: string);
  24. begin
  25.   if (aText = 'None') then
  26.     Sender.Value := 0;
  27.   if (aText = 'Incoming') then
  28.     Sender.Value := 1;
  29.   if (aText = 'Outgoing') then
  30.     Sender.Value := 2;
  31.   if (aText = 'Internal') then
  32.     Sender.Value := 3;
  33. end;

valdir.marcos, these procedures are for taazz's variant 1?


No, I'm not working with asterisk. My theme is OpenScape Contact Center from Unify.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: How to replace the field type and value in dbgrid?
« Reply #6 on: March 28, 2018, 10:15:21 pm »
valdir.marcos, these procedures are for taazz's variant 1?

Yes.

Follow a simplified tutorial on how you can get there (one helping image is attached):
1. Set the connection information;
Depending on the DBMS or RDBMS you are using, you may need to set more properties than just DatabaseName.
Try to activate the connection component to guarantee it is correctly set up.

2. Set the transaction information;
3. Set the query information;
4. Set the query command;
Try to activate the query component to guarantee is correctly set up.
If the connection, transaction and query components are correctly set, steps 5 through 8 should now work as a breeze.

5. Right click on the SQLQuery component to access the field editor;
6. Right click on the field editor blank area to add one, some or all fields;
7. Click on Create button;
8. Click on the chosen field, then on Events tab, then double click on OnGetText and OnSetText.
« Last Edit: April 01, 2018, 09:31:28 pm by valdir.marcos »

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #7 on: April 01, 2018, 07:40:24 pm »
valdir.marcos, from step 5 I start discovering completely new for me part of Lazarus.
Up to now I was successfully reading data from database without setting fields in TSQLQuery.
Thank you for short tutorial, I found pictures a bit different in Lazarus 1.8.0.
I didn't find any more detailed tutorial about how to use this stuff.
Following it without understanding cause an error "Field not found" from TSQLQuery.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: How to replace the field type and value in dbgrid?
« Reply #8 on: April 01, 2018, 09:28:18 pm »
On step 1, depending on the DBMS or RDBMS you are using, you may need to set more properties than just DatabaseName.
After step 1, try to activate the connection component to guarantee it is correctly set up.
After step 4, try to activate the query component to guarantee is correctly set up.

If the connection, transaction and query components are correctly set, steps 5 through 8 should now work as a breeze.
Just in case you step onto more warning or errors, please come back here.
« Last Edit: April 01, 2018, 09:32:15 pm by valdir.marcos »

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #9 on: April 06, 2018, 05:25:16 pm »
valdir.marcos, I have followed your brilliant tutorial on a live TODBCConnection to Informix DB. All steps work fine, and now I start understanding how cool Lazarus is...
So, at step 5 I got a list of fields and have selected one of them (waitresolution). I assigned a procedure for OnGetText event. When I start application, I get a correctly filled DBGrid according to SQL string set in TSQLQuery, and waitresolution field was converted correctly!
But after that my application sets a new query in TSQLQuery.SQL.Text and call TSQLQUery.Open. This produce an EDatabaseError exeption with text:
 DBquery: Field not found: "waitresolution"
This I do not understand: first query was OK, second can't find the field...
Query set in TSQLQuery is SELECT FIRST 10 * FROM CALLRECORD,
second query:
select min(callstart + sitetzoffset units minute), max(callstart + sitetzoffset units minute), count(all callid), count(distinct callid) from callrecord; 
This (and all subsequent) queries were working fine before I have done steps 5-8
What can be wrong?
« Last Edit: April 06, 2018, 06:09:44 pm by Vodnik »

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #10 on: April 06, 2018, 10:00:04 pm »
I was too tired when wrote my previous post, please ignore it.  %)
Of course, there would be error in a query that provide no "waitresolution" field in its result.
Problem now is to send different queries in such case:
e.g. one query provides all the fields, another only counts number of records in a table.
But this is another theme, the root topic is SOLVED.
« Last Edit: April 07, 2018, 11:14:05 am by Vodnik »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: How to replace the field type and value in dbgrid?
« Reply #11 on: April 08, 2018, 02:21:00 am »
I suggest you to divide your SQLQuery components on the form in two strategies:
- one group of permanent information (following my previous simplified small tutorial using Fields Editor);
- only one SQLQuery component for all dynamically changing queries (without using Fields Editor).

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #12 on: April 08, 2018, 06:33:39 pm »
Yes, two instances of TSQLQuery have solved the problem.
Thank you, valdir.marcos!

vrull

  • Full Member
  • ***
  • Posts: 118
Re: How to replace the field type and value in dbgrid?
« Reply #13 on: April 12, 2018, 02:00:36 am »
Just a suggestion - if you slightly change your SQL query to the below, and display [termtypetext] instead, you can avoid all that complicated Pascal code.
Code: Pascal  [Select][+][-]
  1. select termtype,
  2.   case termtype
  3.     when 0 then 'None'
  4.         when 1 then 'Incoming'
  5.         when 2 then 'Outgoing'
  6.         when 3 then 'Internal'
  7.         else 'Unknown2' end as termtypetext,
  8.   callstart, callend, origin, destination, direction
  9. from callrecord
  10.  

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: How to replace the field type and value in dbgrid?
« Reply #14 on: April 12, 2018, 09:35:23 pm »
Thanks, vrull, I know this SQL construction and thought about using it.
But in my case:
- several fields have to be processed in such way
- some fields have about 50 variants of meaning
- I have to reduce the time while DB table is locked while reading.
I do not know what work faster: SQL or Pascal.
No idea how query is processed through ODBC.
This was just a guess to give more work to Pascal, maybe wrong.

 

TinyPortal © 2005-2018