How to set SQLQuery1 FieldDefs to 0 items by code?

[Elmug]

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]

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]

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]

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' ?

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]

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]

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;


I wish that were the problem, Knipfty.

The problem is rather deep and does relate to how Lazarus works the grid.

[Knipfty]

Elmug,

I did exactly what you describe as being the problem!

I give up!

[Elmug]

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]

Elmug,

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]

Elmug,

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]

Elmug,

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]

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.
 

Do 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]

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]

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]

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 not clear.

Try closing the TSQLQuery before calling it.

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?
 

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.

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.

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.

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).

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.