How to replace the field type and value in dbgrid? lazarus / 16-Databases ---------------------------------------------------------------------- Vodnik · 2018-03-27 21:06 My SQL query:  Code: XML  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2018-03-27 22:50 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. ---------------------------------------------------------------------- valdir.marcos · 2018-03-28 05:19 @Voldink, try: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ...DBForm.DBquery.SQL.text := 'select termtype, callstart, callend, origin, destination, direction from callrecord';... procedure TForm1.DBQuery1TERMTYPEGetText(Sender: TField; var aText: string;  DisplayText: Boolean);begin  if (DisplayText) then  begin    case Sender.Value of      0: aText := 'None';      1: aText := 'Incoming';      2: aText := 'Outgoing';      3: aText := 'Internal';    else      aText := 'Unknown2';    end;  end  else    aText := 'Unknown1';end; procedure TForm1.SQLQuery1TERMTYPESetText(Sender: TField; const aText: string);begin  if (aText = 'None') then    Sender.Value := 0;  if (aText = 'Incoming') then    Sender.Value := 1;  if (aText = 'Outgoing') then    Sender.Value := 2;  if (aText = 'Internal') then    Sender.Value := 3;end; ---------------------------------------------------------------------- valdir.marcos · 2018-03-28 05:27 @Voldink, do you work with Asterisk? https://www.asterisk.org/ https://wiki.asterisk.org/wiki/display/AST/Asterisk+Versions ---------------------------------------------------------------------- Vodnik · 2018-03-28 08:24 Quote from: taazz on March 28, 2018, 12:50:11 amNo. 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 · 2018-03-28 08:31 Quote from: valdir.marcos on March 28, 2018, 07:19:47 am@Voldink, try: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ...DBForm.DBquery.SQL.text := 'select termtype, callstart, callend, origin, destination, direction from callrecord';... procedure TForm1.DBQuery1TERMTYPEGetText(Sender: TField; var aText: string;  DisplayText: Boolean);begin  if (DisplayText) then  begin    case Sender.Value of      0: aText := 'None';      1: aText := 'Incoming';      2: aText := 'Outgoing';      3: aText := 'Internal';    else      aText := 'Unknown2';    end;  end  else    aText := 'Unknown1';end; procedure TForm1.SQLQuery1TERMTYPESetText(Sender: TField; const aText: string);begin  if (aText = 'None') then    Sender.Value := 0;  if (aText = 'Incoming') then    Sender.Value := 1;  if (aText = 'Outgoing') then    Sender.Value := 2;  if (aText = 'Internal') then    Sender.Value := 3;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 · 2018-03-28 20:15 Quote from: Vodnik on March 28, 2018, 10:31:32 amvaldir.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. ---------------------------------------------------------------------- Vodnik · 2018-04-01 17:40 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 · 2018-04-01 19:28 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. ---------------------------------------------------------------------- Vodnik · 2018-04-06 15:25 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? ---------------------------------------------------------------------- Vodnik · 2018-04-06 20:00 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. ---------------------------------------------------------------------- valdir.marcos · 2018-04-08 00:21 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 · 2018-04-08 16:33 Yes, two instances of TSQLQuery have solved the problem. Thank you, valdir.marcos! ---------------------------------------------------------------------- vrull · 2018-04-12 00:00 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i select termtype,  case termtype    when 0 then 'None'        when 1 then 'Incoming'        when 2 then 'Outgoing'        when 3 then 'Internal'        else 'Unknown2' end as termtypetext,  callstart, callend, origin, destination, directionfrom callrecord ---------------------------------------------------------------------- Vodnik · 2018-04-12 19:35 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. ---------------------------------------------------------------------- ezlage · 2018-04-12 20:35 Friend, take a look at the attachment. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.ZQuery1newfieldGetText(Sender: TField; var aText: string;  DisplayText: Boolean);begin  case AnotherField.AsInteger of  //refer to the integer field    0: AText:='Zero';    1: AText:='Um';    2: AText:='Dois';    else AText:='Qualquer coisa';  end;end; ---------------------------------------------------------------------- vrull · 2018-04-13 00:06 @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. ---------------------------------------------------------------------- Vodnik · 2018-04-13 20:54 Quote from: ezlage on April 12, 2018, 10:35:04 pmFriend, take a look at the attachment. ezlage, I'm using this, thanks! ---------------------------------------------------------------------- Vodnik · 2018-04-13 21:15 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. ---------------------------------------------------------------------- vrull · 2018-04-17 04:51 @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. ---------------------------------------------------------------------- Vodnik · 2018-04-17 16:49 @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. ---------------------------------------------------------------------- vrull · 2018-04-17 23:09 @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/ 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). So you TSQLQuery(yourComponent).SQL.Text := ; ; 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.