How to set SQLQuery1 FieldDefs to 0 items by code? lazarus / 16-Databases ---------------------------------------------------------------------- Elmug · 2012-08-06 19:10 Hi every one. Windows 7 Linux 32 bits SQLite3 I start a test  app with FieldDefs set to 0 items. App opens database. SQL = "Select * from TableX" loads grid correctly. However, Query = "Select * from TableY" (changing to another Table also in same database), and sending the query returns error that ColumnX is not in that table (Field not found), which is an unexpected issue, or bug, maybe. ColumnX is still stored in the FieldDefs, of course. So I am trying to first set FieldDefs to zero items with a single generic (not table specific) statement, before sending the query. Have tried  SQLQuery1.FieldDefs := nil; but that gives the SIGSEV error. I hope there is a simple way that someone knows about and would share. Thanks. ---------------------------------------------------------------------- Knipfty · 2012-08-06 20:15 I'll try this at home this evening.  I just want to make sure of something 1st. You have 1 TQuery object connected to a single DBGrid, and you want to load different SQL statements a various times during your program execution. In between execution of the different statements, you set the TQuery object Active:=False and back to True? ---------------------------------------------------------------------- Lacak2 · 2012-08-07 05:34 Changing FieldDefs manualy is IMO not good idea. FieldDefs reflects structure of underlaying database data, so every time you change SQL and reopens dataset FieldDefs are updated as needed. Another situation is with Fields. May be that you have defined persistent Fields ? or there is some DB control with expects FieldName='ColumnX' ? ---------------------------------------------------------------------- Elmug · 2012-08-07 07:51 Quote from: Lacak2 on August 07, 2012, 07:34:49 amChanging FieldDefs manualy is IMO not good idea. FieldDefs reflects structure of underlaying database data, so every time you change SQL and reopens dataset FieldDefs are updated as needed. Another situation is with Fields. May be that you have defined persistent Fields ? or there is some DB control with expects FieldName='ColumnX' ? Yes, I let the Query set the Fields, as usual, in the app. Due to the problem, though, I thought maybe I would try to set Fields to none, or zero fields, before sending the Query. I found that Clear applies. So I suppose that is the answer to my question, but the problem is still there. Another situation is with Fields. May be that you have defined persistent Fields ? No, to this. or there is some DB control with expects FieldName='ColumnX' ? I will check for this. I don't think so, though. I have been troubleshooting this app all day, though, and found that an old app, in which I base this new app INDEED WORKS correctly. So it must be features that the new app has (and all of them work), but somehow something affects that function resulting in the problem explained. So what I will do is check for what you suggest, but will redo a new project with the old project where the problem is not there, and  I will add the new features one by one and test to see if/where/when then problem mentioned is created. Hence, I think I'll have to settle this here as said. I don't think is a Lazarus problem. I will report what I find. Thanks again, and also, thanks Knipfty for trying to help. ---------------------------------------------------------------------- Knipfty · 2012-08-07 13:15 Hi, It's not a Lazarus problem.  Here is what I did without any issues: Code: [Select]  AdsQuery1.Active := false;   AdsQuery1.SQL.Add('select * from investors');   AdsQuery1.Active := true;   AdsQuery1.Active := false;   AdsQuery1.SQL.clear;   AdsQuery1.SQL.Add('select * from testtbl');   AdsQuery1.Active := true; ---------------------------------------------------------------------- Elmug · 2012-08-08 15:45 Quote from: Knipfty on August 07, 2012, 03:15:17 pmHi, It's not a Lazarus problem.  Here is what I did without any issues: Code: [Select]  AdsQuery1.Active := false;   AdsQuery1.SQL.Add('select * from investors');   AdsQuery1.Active := true;   AdsQuery1.Active := false;   AdsQuery1.SQL.clear;   AdsQuery1.SQL.Add('select * from testtbl');   AdsQuery1.Active := true; I wish that were the problem, Knipfty. The problem is rather deep and does relate to how Lazarus works the grid. ---------------------------------------------------------------------- Knipfty · 2012-08-08 15:48 Elmug, I did exactly what you describe as being the problem! I give up! ---------------------------------------------------------------------- Elmug · 2012-08-08 15:58 Hi everyone, The problem is not from the contents of the SQL. The problem is from the fact that I am running code by DBGrid's OnColumnEnter that checks the datatype upon entering. When I have no method OnColumnEnter, I can do selects from other tables, etc, without any problems. With the DBGrid OnColumn enter, applied without changing the code, sometimes it runs one or two times, often I get SIGSEGV. What is happening seems to be related to the DBGrid rows and column and column names, are kind of persistant, even if one clears the Grid. I have also tried empty the grid with a query that brings no data to the grid, and then send a select clause that applies to another table. Sometimes it works, sometimes the SIGSEGV. So, for now, I give up on trying to use DBGrid OnColumnEnter and will resort to buttons for the user to do manually what OnColumnEnter was meant to do. I'm not giving up, and will come later to the idea, specially if anyone has some suggestions that maybe I have not tried yet. Thanks everyone. ---------------------------------------------------------------------- Elmug · 2012-08-08 16:01 Quote from: Knipfty on August 08, 2012, 05:48:46 pmElmug, I did exactly what you describe as being the problem! I give up! Knipfty, don't you think it's a little presumptuous for you to think that my app consists of your 7 or so lines of code. You surely should know that there are other factors that are involved. ---------------------------------------------------------------------- taazz · 2012-08-08 16:08 Quote from: Elmug on August 08, 2012, 06:01:26 pmQuote from: Knipfty on August 08, 2012, 05:48:46 pmElmug, I did exactly what you describe as being the problem! I give up! Knipfty, don't you think it's a little presumptuous for you to think that my app consists of your 7 or so lines of code. You surely should know that there are other factors that are involved. don't you think it's a little presumptuous for you to think that the problem is in the sqldb components and not on your code especially when your assumptions have been wrong so far? If you want to get help please post the code that when commented out it stops raising the error. Anything less is like asking a medium for help with your stock exchange. Regards. ---------------------------------------------------------------------- Elmug · 2012-08-08 16:17 Quote from: taazz on August 08, 2012, 06:08:17 pmQuote from: Elmug on August 08, 2012, 06:01:26 pmQuote from: Knipfty on August 08, 2012, 05:48:46 pmElmug, I did exactly what you describe as being the problem! I give up! Knipfty, don't you think it's a little presumptuous for you to think that my app consists of your 7 or so lines of code. You surely should know that there are other factors that are involved. don't you think it's a little presumptuous for you to think that the problem is in the sqldb components and not on your code especially when your assumptions have been wrong so far? If you want to get help please post the code that when commented out it stops raising the error. Anything less is like asking a medium for help with your stock exchange. Regards. In many of my tests, the debugger pointed to code in Lazarus supplied procedures. It's not a presumption. Do you happen to know which fires first: The DBDGrid OnColumn enter or the usual DBGrid activities? ---------------------------------------------------------------------- taazz · 2012-08-08 16:23 Quote from: Elmug on August 08, 2012, 06:17:54 pm In many of my tests, the debugger pointed to code in Lazarus supplied procedures. It's not a presumption. As I said its an assumption of yours and you haven't provided the requested code yet. Quote from: Elmug on August 08, 2012, 06:17:54 pmDo you happen to know which fires first: The DBDGrid OnColumn enter or the usual DBGrid activities? No personaly I use TVirtualDBGrid which is based on TVirtualTreeView not TDBGrid. ---------------------------------------------------------------------- Elmug · 2012-08-08 16:30 Taazz, Do you happen to know what DBGrid1.Clear does? As part of my tests I put a button that does that, and the grid does clear. But the same code in the app does not seem to solve the problem. Maybe I am not creating the Grid back right, because create via a button does not work as hoped. I want the Grid to be like new (cleared) or empty before I send the new Query, hoping that solves the problem. I also destroyed it and created it and yet the same problem. I loaded the grid with empty rows, before the query, and still the problem. As I said, if I don't use DBGrid OnColEnter, the problem is not there, with the  same queries. The code on DBGrid basically determines the Data type of the column entered and opens a Memo box if memo, and shows the image if Blob. That code all works as long as I don;t send a select clause that calls another table. The select clauses are elementary, one table target. So, I do have reasons to  believe that the code of some supplied Procedures relates to the problem. I don't say that it causes it. ---------------------------------------------------------------------- Elmug · 2012-08-08 16:38 Taazz, this is the code that I use to determine the datatype of the column entered:       if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo'         then GOTO Memo;       if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Blob'         then GOTO Blob; But even this might not be the problem, since as part of my tests, I also set a global as flag that bypasses the whole code of the OnColEnter when a newquery is sent (the closest to there being no OnColEnter procedure). ---------------------------------------------------------------------- taazz · 2012-08-08 17:05 Quote from: Elmug on August 08, 2012, 06:30:12 pmTaazz, Do you happen to know what DBGrid1.Clear does? As part of my tests I put a button that does that, and the grid does not clear. Try closing the TSQLQuery before calling it. Quote from: Elmug on August 08, 2012, 06:30:12 pm I want the Grid to be like new (cleared) or empty before I send the new Query, hoping that solves the problem. I also destroyed it and created it and yet the same problem. I loaded the grid with empty rows, before the query, and still the problem. As I said, if I don't use DBGrid OnColEnter, the problem is not there, with the  same queries. Queries play no role in this, does an empty OnColEnter method raises the error?   Quote from: Elmug on August 08, 2012, 06:30:12 pm The code on DBGrid basically determines the Data type of the column entered and opens a Memo box if memo, and shows the image if Blob. That code all works as long as I don;t send a select clause that calls another table. The select clauses are elementary, one table target. Send the select clause from where under what circumstances. Quote from: Elmug on August 08, 2012, 06:30:12 pmSo, I do have reasons to  believe that the code of some supplied Procedures relates to the problem. I don't say that it causes it. What reasons? Saying that your code is simple is no evidence that has no problems blaming some procedure for your problems provides no information as well. Please keep in mind I do not have a working crystal ball it short circuited a few years back and I couldn't find any spare parts to fix it. Quote from: Elmug on August 08, 2012, 06:38:30 pmTaazz, this is the code that I use to determine the datatype of the column entered:       if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo'         then GOTO Memo;       if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Blob'         then GOTO Blob; But even this might not be the problem, since as part of my tests, I also set a global as flag that bypasses the whole code of the OnColEnter when a newquery is sent (the closest to there being no OnColEnter procedure). protect your access don't assume that the SelectedField has a value, make sure it does eg Code: [Select]   if dbgrid1.SelectedField <> nil then begin      if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo'         then GOTO Memo;       if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Blob'         then GOTO Blob;    end; This should solve some errors when you closing the existing query and opening it with a new command. Regards. ---------------------------------------------------------------------- Elmug · 2012-08-08 17:54 Taazz, These are good points. The problem is that actually, the GRID is built before any column is entered, and, as long as a new Query has been sent, OnColEnter will get confused because the Grid is still trying to use the previous values. So I do believe that the way to do what I aim for can be solved by before sending the Query: DBGrid.Destroy and recreate it right after. I have now a test button that shows that destroy works, but I have not figured how to create again DBGrid1. There would be no problem then setting its properties. Another test button to do after destroy: DBGrid1.Create(Form1) or DBGrid1.Create(Self) crash the app. But maybe my create clause is wrong. So help if you know how to create again the DBGrid1, so it's like when the app starts. Thanks. ---------------------------------------------------------------------- Elmug · 2012-08-08 17:59 I just found this, which maybe will work:  with TDBGrid.Create(Self) do   begin     // Set properties of the grid     DataSource := TempDataSource; // Connect the source to the grid   end; I will try it. Thanks. ---------------------------------------------------------------------- ludob · 2012-08-08 18:20 What about turning off OnColEnter when you close the first dataset, change the sql, open the dataset and then turn OnColEnter again? What I think is happening is that OnColEnter is triggered several times for fields that do not exist (yet) or anymore. An exception is raised which is probably messing up the normal continuation of opening the dataset and you get into all kind of weird situations. If turning off OnColEnter is too difficult then at least call DisableControls and EnableControls when re-opening. Also as Taaz said, don't assume anything on the validity of SelectedField and get rid of the sigsegv's before they cause more problems in sqldb. And forget TDBgrid.Clear. This is a public method of TCustomGrid that should have been made protected for DBGrid. Delphi has done that. You don't clear a DBGrid but close the underlying dataset or delete all rows in the dataset. Destroying the dbgrid and recreating it is going to have the same effect as turning off and on again OnColEnter, but it is much more work. ---------------------------------------------------------------------- Elmug · 2012-08-08 18:23 What about turning off OnColEnter when you close the first dataset, Hey, that sounds like a great idea... I feel it will work, and will try it right away. Thanks a lot! ---------------------------------------------------------------------- Elmug · 2012-08-08 23:31 Quote from: ludob on August 08, 2012, 08:20:38 pmWhat about turning off OnColEnter when you close the first dataset, change the sql, open the dataset and then turn OnColEnter again? What I think is happening is that OnColEnter is triggered several times for fields that do not exist (yet) or anymore. An exception is raised which is probably messing up the normal continuation of opening the dataset and you get into all kind of weird situations. If turning off OnColEnter is too difficult then at least call DisableControls and EnableControls when re-opening. Also as Taaz said, don't assume anything on the validity of SelectedField and get rid of the sigsegv's before they cause more problems in sqldb. And forget TDBgrid.Clear. This is a public method of TCustomGrid that should have been made protected for DBGrid. Delphi has done that. You don't clear a DBGrid but close the underlying dataset or delete all rows in the dataset. Destroying the dbgrid and recreating it is going to have the same effect as turning off and on again OnColEnter, but it is much more work. I managed to disable/enable  the OnColEnter event at run time, at will. But the problem continues. I do need to try to make the DBGrid Empty, I believe. Also, the problem is not empty fields, nor fields with any known problems, since, as said, app  works perfectly as long as I do not Query another table in the same database. The tables are very small with 4 columns or so. And I have only a handful of rows in each. None have empty fields. The application does have a button to delete blob images, and to load and replace them. All that works fine. In fact, I recall that when I did have empty blob cell, the app had no problem. I don't mean to say that the suggestions are bad, either. I will incorporate some. But right now, I believe the problem is that I need to have an empty grid, to work around a solution. Thanks. ---------------------------------------------------------------------- Dick, from the internet · 2012-08-09 00:30 Is this issue related to your other thread "DBGrid1 destroy and create.."? Have you tried SQLQuery1.Close?  As long as you did not set the column fieldname references in the object inspector, this should essentially reset the table for a new query, as long as your query on the new table has the same number of fields as columns in the grid. ---------------------------------------------------------------------- taazz · 2012-08-09 02:46 Please provide some information on what exactly you are doing and the error you are getting. So far you have given no useful information that could help us help you solve the problem. Try to create a small test application that show the error you are getting with as little code as possible and attache it to one of your replies. I'm not going to jump from one topic to an other trying to solve a problem I have no idea what it is. ---------------------------------------------------------------------- Elmug · 2012-08-09 02:46 Quote from: geno on August 09, 2012, 02:30:52 amIs this issue related to your other thread "DBGrid1 destroy and create.."? Have you tried SQLQuery1.Close?  As long as you did not set the column fieldname references in the object inspector, this should essentially reset the table for a new query, as long as your query on the new table has the same number of fields as columns in the grid. Hi Geno, YES, of course, I close SQLQuery1 I have no problem doing QUERIES on the same table or on other tables of the database, as long as I don't have the procedure for DBGrid OnColEnter. Setting that to None clears the issue. Now I have a test button that toggles the OnColEnter procedure on an off at will. When OFF, no problem whatsovever. Without change in code, just toggle the event on and off. The DBGrid OnColEnter is not handled properly, for some reason that so far is beyond me. But the OnColEnter procedure is handled well as long as I am in the same table. I can change queries and they work. If I am on Table1, all is fine. But if I now say select from Table2, then SIGSEGV. Now if I first select from Table2, all is fine. Then I select from Table1, and ditto, SIGSEGV. 3 days now and I can't find the problem. I think I'll have to do without the OnColEnter and do it by manual clicks. Thanks. Thanks. ---------------------------------------------------------------------- Dick, from the internet · 2012-08-09 03:03 OK - fine.  So now, as you have been asked repeatedly, create a small sample application that gives you the errors you describe and drop it here so someone else can actually see what you are trying to do.  It just might be a simple matter of headspace and timing issue; you can't see the solution because you are too close to the problem. ---------------------------------------------------------------------- teos · 2012-08-20 14:57 Elmug, I don't know why the code you posted compiles. And ehm... "GOTO" is jumping to different memory. That is not needed (because you're calling a procedure anyway) and the most unreliable call you can make. But apart from that: TDBGrid refreshes it's columns when you open the dataset you are using to run the SQL statement. There is no need to free and create a new dbgrid instance or to clear the TDBGrid. In Delphi that is called "hacking". *GRIN* I think your mistake is that you use a TFieldType as a string:   if dbgrid1.SelectedField <> nil then begin      if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo'         then caption := 'memo';       if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Blob'         then caption := 'blob';    end; That should be:   if dbgrid1.SelectedField <> nil then begin      if Fieldtypenames[dbgrid1.SelectedField.DataType] = ftMemo         then caption := 'memo';       if Fieldtypenames[dbgrid1.SelectedField.DataType] = ftBlob         then caption := 'blob';    end; Again: I stronly advise against the "GOTO" which is considered as spagetti code but also is not needed to get your code to work. ---------------------------------------------------------------------- Elmug · 2012-08-21 02:08 if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo' Hi  teonieuwlande, NO, this in this function,  the square brackets amount to strings, they are a predeclared array in one of the external units provided,  of datatype names of string type. It compiles OK. The GOTO's I found work well to avoid nested try statements, and all jumps are within a few lines away in short procedures. Thanks for commenting. ---------------------------------------------------------------------- teos · 2012-08-21 23:13 I see the "fieldtypenames" code in db.pas indeed. And learned something new about something really not needed.. because TField.Fieldtype is commonly used for that and fieldtypenames also brings another string comparision which I don't doubt is slower than testing an enumerated type. I would say: forget about the fieldtypenames and test for TField.Datatype. BTW: the DBGrid has columns. I expect if you loop through columns and free them one by one, the grid is cleared. But if you need to free and re-create a DBGrid because you have trouble with the columns, you really have some error somewhere. Like said: the dbrid refreshes its columns on a tdataset.open. It's simple: My code doesn't produce the error you try to work around. In your case, if you would use else if statements, only the one matching would be executed, or if you use begin..end you can use exit to get out of the loop, and if you use the case statement, also only the matching code is executed. I hope you are aware that the event is triggered from somewhere in a unit where there it is expected that the code after the event is also executed. Your goto (if there is no more code than I have seen) does not bring you back to the point where you called it, so the code after the part triggering the event is never executed. Don't be supprised to see very unwanted behaviour. If not in this event, it will be in others. If you don't believe what I say (because I have only 4 days of Lazarus and 15 years of Delphi): http://www.delphibasics.co.uk/RTL.asp?Name=Goto&ExpandCode1=Yes (btw: the link says in red that goto should NEVER be used in modern languages) My 5 cents, the rest is up to you.