How do I handle the 'closed dataset' error ?

[JohnLM 10]

Win7, D11.2, FMX app, using TStringGrid and FireDAC and SQLite

 

In a near-perfect working database, it should never happen, but it could happen elsewhere, and since I have the issue now, I would like to know how to resolve this. 

 

I was doing some what-if scenarios and came upon this issue. I searched around here and elsewhere on the internet but I can not find the answer. 

 

In this scenario, I was adding a new record, which has an auto-increment field in this test database, IDNo -- ('IDNo INTEGER NOT NULL PRIMARY KEY,').  And it generated an error. Of course I understand why this happened, and can later figure out a method to avoid it from happening.  But for now, i need to know how to resolve the "closed dataset" issue because I can't see the data any longer.

 

I've tried closing the Query, and reopening it.  And tried closing the Connection and reopening it too. But they are not working. 

 

When a database or dataset closes, how do I re-open it through code so that I can see the data again in the StringGrid?

 

 

Edited December 5, 2022 by JohnLM

[programmerdelphi2k 235]

have you seen if  your "index" is corrupet or similar? then the table cannot be opened!

if yes, try recreate it using your "SQLite" manager tool

[JohnLM 10]

Please note, this is an FMX app, so live-bindings is utilized.  And the reason I am working this as a Windows app is to speed up these kinds of issues versus if I was to start this in an Android project. 

 

For testing purposes for this db, I was manually writing the script to add the new record while in the IDE.  And when the issue first came about, and to work out a resolution, I needed a way to re-create it so I can learn and fix it.  So, I created a [new record] button and added the code to add the new record, so that every time I run the app and press the [new record] button, it would pop up the error. 

 

Note: [btnPostClick()) is the actual procedure, not [new record]. 

 

It's in this [new record] procedure that I am trying to work out the code to refresh the dataset/stringgrid to show what is current, below. 

 

procedure TForm1.btnPostClick(Sender: TObject); // post or add a new record. // this works. 12/4/sun
begin
  conn.Connected:=true; // to reopen the dataset
  qry.Close;            // most people say to close first then reopen
  qry.Open;             // open dataset, stringgrid should now show

  // original code
  qry.FindLast;
  qry.SQL.Clear;
  qry.SQL.Assign(m1.Lines);
  //  qry.Open; // remmed out for now, to test try/finally below
  // end of original code

  // here, trying catch the error and stop it (but it doesnt)
  try
    qry.Open;
  except on e : exception do
    ShowMessage('Cannot open tblBarcodes, error is: ' + e.message);
  end;
end;

 

 

Edited December 6, 2022 by JohnLM

[programmerdelphi2k 235]

First, any derived StringGrid ("Grids/StringGrid/etc...") in Firemonkey is not data aware like the DBGrid in VCL is!
So, forget about this relationship here at Firemonkey!
FMX uses the LiveBinding framework to make the bridge between the StringGrid/Grid and the data source (table/query/etc...), and the component/class used is the "BindSourceDB" (same function as the Datasource in VCL) ;
you don't need to keep opening and closing the database connection! Unless you need it for some reasonable reason, eg low bandwidth connection etc...

 

you can:

• before or when o form is open:  FDConn.Open;
• after or when o form is closed: FDConn.Close; // this close all datasets using this connection component!
• you close the "query" only when: 
  • dont needs anymore use it
  • needs changes the SQL text
  • needs changes some params (if dont using PARAMetrization correctly)
• when using: "Select" you should use "qry.OPEN"  // return a data-set
• when using: "Insert, Delete, Update" you should use "qry.EXECUTE" // execute dont return any data-set!

said this, then you can try:

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery.Close; // to execute a new statement, or end session! for example
  //
  // you can use this way to new statment, be OPEN or EXECUTE!
  //FDQuery.SQL.Text := '.... new statement ....';
  //
  FDQuery.ExecSQL('your Insert,Delete, Update expression'); // to execute your command
  //
  // to open your query with data refreshed
  FDQuery.Open('your Select... expression'); // to show your data-set result
  //
  FDQuery.Close; // to close your query."OPENED"
end;

 

I think that your "error" should be because your "statement" is wrong!!!!

try see what is the SQL text after "qry.SQL.Assign(m1.Lines);"

       ShowMessage( qry.SQL.Text );

[JohnLM 10]

I created the new record procedure to make it easier to add new UNIQUE records. And I was using that to re-create the issue in order to help resolve the 'closed dataset' issue.

So, yes, the statement is correct. It can enter new records with no problem. 

This is an example script that I am using to post the same info into the query.  It works fine as long as i keep the IDNo unique, so adding a 7 instead of the 6 would work, and so on.

 

I am just duplicating the error to work out solving the 'closed data' issue. 

 

insert into tblBarcodes values (6, '120422', 'Q', 'abc1', '2 120422 Q abc1 desc1');
select * from tblBarcodes;

So, the question remains, how do I reopen the dataset to show its contents through the StringGrid again, without having to close the app, and run it again? 

 

Edited December 6, 2022 by JohnLM

[programmerdelphi2k 235]

if you "SQLite" is using "auto-increment" in ID field, then you can just ignore this field when inserting a new record for example!

-- Insert into TableX(fieldA, FieldB,etc...) values(valueA, ValueB,etc..); // qry.EXECUTE;

-- Select * from TableX;//   qry.OPEN

https://www.sqlitetutorial.net/sqlite-insert/

Edited December 6, 2022 by programmerdelphi2k

[JohnLM 10]

Okay, that may work to prevent it from happening. And I will try that at a later time in this app. But there are other causes that will show 'closed dateset' error, and I will need to at last be able to reopen the dataset and StringGrid.  The user should not have to keep closing down and rerunning an app. At least that is what I want to avoid if possible. 

[Serge_G 85]

Quote

// here, trying catch the error and stop it (but it doesnt)
  try
    qry.Open;
  except on e : exception do
    ShowMessage('Cannot open tblBarcodes, error is: ' + e.message);
  end;

Sure, you can't catch the error if the error is in the upper code !

 

 

I think you use a wrong way.

As I understand you have a FDQuery so one way is to use edit/post/delete and so on is to link it to a FDUpdateSQL (fill clauses with expert or by hand) 

with this method, you can use a "classic"
 

qry.Insert;  // or qry.append
qry.FieldByName('col1').asString:='some string'; 
qry.fieldbyname('col2').as ...
qry.post

In a livebinded grid nothing to code

 

Some remarks :

- when you open a query (or a table) the connection is connected automatically

- use your commands SQL directly on the connection and parameters

  

con.ExecSQL('insert into tblBarcodes values (:i, :p1,:p2,:p3,:p4)',
                                             [6,'120422','Q','abc1','2 120422 Q abc1 desc1']);

- use the INSERT INTO TABLE (<list of column>) VALUES (<list of values>) to avoid auto increment columns 

- except if you want a "physical" ordered table don't worry about APPEND