Lazarus

Programming => Databases => Topic started by: Vodnik on March 27, 2018, 11:06:51 pm

Title: How to replace the field type and value in dbgrid?
Post by: Vodnik 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? 
Title: Re: How to replace the field type and value in dbgrid?
Post by: taazz 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.
Title: Re: How to replace the field type and value in dbgrid?
Post by: valdir.marcos 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;
Title: Re: How to replace the field type and value in dbgrid?
Post by: valdir.marcos on March 28, 2018, 07:27:11 am
@Voldink, do you work with Asterisk?
https://www.asterisk.org/
https://wiki.asterisk.org/wiki/display/AST/Asterisk+Versions
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik on March 28, 2018, 10:24:04 am
Quote from: taazz 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.
Thank's, taazz! Not sure I have understand everything, but will try to discover.
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik on March 28, 2018, 10:31:32 am
Quote from: valdir.marcos 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, these procedures are for taazz's variant 1?


No, I'm not working with asterisk. My theme is OpenScape Contact Center from Unify.
Title: Re: How to replace the field type and value in dbgrid?
Post by: valdir.marcos on March 28, 2018, 10:15:21 pm
Quote from: Vodnik on March 28, 2018, 10:31:32 am
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.
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik 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.
Title: Re: How to replace the field type and value in dbgrid?
Post by: valdir.marcos 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.
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik 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?
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik 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.
Title: Re: How to replace the field type and value in dbgrid?
Post by: valdir.marcos 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).
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik on April 08, 2018, 06:33:39 pm
Yes, two instances of TSQLQuery have solved the problem.
Thank you, valdir.marcos!
Title: Re: How to replace the field type and value in dbgrid?
Post by: vrull 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.  
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik 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.
Title: Re: How to replace the field type and value in dbgrid?
Post by: ezlage on April 12, 2018, 10:35:04 pm
Friend, take a look at the attachment.

Code: Pascal  [Select][+][-]
  1. procedure TForm1.ZQuery1newfieldGetText(Sender: TField; var aText: string;
  2.   DisplayText: Boolean);
  3. begin
  4.   case AnotherField.AsInteger of  //refer to the integer field
  5.     0: AText:='Zero';
  6.     1: AText:='Um';
  7.     2: AText:='Dois';
  8.     else AText:='Qualquer coisa';
  9.   end;
  10. end;
Title: Re: How to replace the field type and value in dbgrid?
Post by: vrull on April 13, 2018, 02:06:23 am
@Vodnik,
If you can modify the database, it is easier to make a lookup table to keep all your Ids with user-readable descriptions.
The point is if you work with SQL you can always construct a query which would give you all data you need. You can run that query independently from your program and make sure all your results are correct.
Then you make a program which just reads the result of that query and presents data to the end user; it formats data to make it look nice, but does not modifies anything.
With this approach you separate user interface from data processing, you know where to look if something went wrong. SQL is very powerful language, it can do almost everything with you data.
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik on April 13, 2018, 10:54:49 pm
Quote from: ezlage on April 12, 2018, 10:35:04 pm
Friend, take a look at the attachment.
ezlage, I'm using this, thanks!
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik on April 13, 2018, 11:15:55 pm
Quote from: vrull on April 13, 2018, 02:06:23 am
@Vodnik,
If you can modify the database, it is easier to make a lookup table to keep all your Ids with user-readable descriptions.
The point is if you work with SQL you can always construct a query which would give you all data you need. You can run that query independently from your program and make sure all your results are correct.
Then you make a program which just reads the result of that query and presents data to the end user; it formats data to make it look nice, but does not modifies anything.
With this approach you separate user interface from data processing, you know where to look if something went wrong. SQL is very powerful language, it can do almost everything with you data.
@vrull, I can't. The SW product that is using this DB restricts changing DB. It also restricts querying more than 10000 records at a time. And is very sensible to table locking time. Known practice is to create a replication to another DB and work with it, but I'm not yet good enough to do this. Then IDs can be replaced with text. BUT: More bytes -> more space -> more memory -> more processing time, isn't it?
I'm the beginner both in SQL and Pascal, and still thinking about giving all the work to SQL. As I have already posted, having a CASE statement with 50 variants discourages me. Also some SQL constructions that I have tried are not supported in Lazarus.
Title: Re: How to replace the field type and value in dbgrid?
Post by: vrull on April 17, 2018, 06:51:39 am
@Vodnik. You have not stated what database you are working with. If it is powerful enough, like MS SQL Server, there is lot of ways to achieve desired results. You client application can run a query at start-up to make temporary lookup tables; it may be a case statement with 50 options, but you run it only once and use as long as you connection is active.
Another suggestion is to separate Pascal code from any other languages, be it SQL, HTML, XML or what ever else you program may work with. This makes life much easier.

Seems to be running off-topic.
Title: Re: How to replace the field type and value in dbgrid?
Post by: Vodnik on April 17, 2018, 06:49:10 pm
@vrull, I work with Informix DB via ODBC (because there is no direct connection in Lazarus). It is very powerful!
The things you are talking about I would like to learn, but do not know how - not so much is documented for Lazarus.
Title: Re: How to replace the field type and value in dbgrid?
Post by: vrull on April 18, 2018, 01:09:05 am
@Vodnik,
This is more like the concept rather that specific Lazarus features.
You can have a look at this project which implements this concept to some extend:
https://sourceforge.net/projects/sqlmm/ (https://sourceforge.net/projects/sqlmm/)
The Lazarus code is purely the user interface, It does know anything about data apart from the fact it should connect to an SQLite3 database (in my case). It keeps everything, which is not Pascal code, in a special container (an XML file in that case; you can see it using my XML Tree Editor https://sourceforge.net/projects/xmltreeeditor (https://sourceforge.net/projects/xmltreeeditor)).
So you
TSQLQuery(yourComponent).SQL.Text := <read the whole query from the container>;
<set query parameters from user input or other sources>;
Open or ExecSQL to run.
You can design you query in a "Management Studio", make sure it works as expected and gives you all result you need. And you can apply all the power of SQL Engine to avoid messing up with data in Pascal code.

Certainly, this is my own opinion, you don't have to follow.


TinyPortal © 2005-2018