0

Why it is recommended to process a sql-query in Delphi in the following way?:

dataset.close();     //  ?????
dataset.sql.clear(); // old sql-query gets deleted

dataset.sql.add('your sql-query'); // here a query-String is added to your sql-object

dataset.open();  // here your sql-query starts to work

Can it be that closing of a sql-Object defines everytime a default state by which the former dataset resulting from the former sql-query is deleted?

user3133542
  • 1,695
  • 4
  • 21
  • 42
  • 1
    > *"Why it is recommended ..?"* > - It's not. That won't even compile. The sql property is a string list, it won't open. – Sertac Akyuz Aug 08 '14 at 01:10
  • sorry for the error, it is fixed! – user3133542 Aug 08 '14 at 01:52
  • Thanks. The effect is closing the dataset puts it in a state that it can't work with data. Also, in this state it is possible to make changes that otherwise you can't do. – Sertac Akyuz Aug 08 '14 at 02:21
  • 1
    The exact effect is to close the dataset (making it unavailable for use), just as the name suggests. Until the dataset is opened again (via `Open` or setting `Active` to true, there is not dataset to use for any purpose. – Ken White Aug 08 '14 at 02:46
  • 1
    *"Why to use Close ?"* Because if you want to open the door, they must be closed before you can do so. You cannot open already opened door. About that `Dataset.SQL.Clear; Dataset.SQL.Add('Single line SQL query');` construct (for single line SQL queries). You don't have to do it and I'm against using that (it's worse for me to read). It's enough to use `Dataset.SQL.Text := 'Single line SQL query';` instead of those two lines. – TLama Aug 08 '14 at 04:33
  • `TDataSet.Close` puts the dataset into the state as it was before `TDataSet.Open`. Not more not less. – Sir Rufo Aug 08 '14 at 04:41
  • 1
    @TLama: what is worse the most of dataset descendants hang on Sql.OnChange event and perform SQL parsing... – pf1957 Aug 08 '14 at 11:05
  • @pf1957, thanks for pointing this! I thought (or was hoping, since I do not assign queries by lines) that queries are parsed (or passed to the underlying provider object) when they are prepared, but it really happens when the `SQL` string list changes (checked at least popular `TADOQuery` and `TIBQuery`). I can't remember if I ever saw an example that would be adding lines to `SQL` and would be enclosed by the `BeginUpdate`, `EndUpdate` block. It looks like another *code snippet virus* launched probably again by delphi.about.com. – TLama Aug 12 '14 at 20:00

1 Answers1

2

When you open a dataset, it enables an active connection, assuming your database/drivers support this. Some database connections are keep-alive, others aren't. For the ones which are, the Open procedure enables an open connection with the database, in which case you can dynamically edit that data using the dataset. So when you Close a dataset, this is typically closing what was opened. Keep in mind that the TDataset is inherited into other more specific types, and the Open / Close procedures are required for many of those.

Closing a dataset also invalidates the data contained within it, therefore is no longer providing that data as well. Your query will stay there, but the data will no longer be available to read/write.

Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
  • 1
    Closing a dataset does not close the connection, typically. The connection and the dataset are separate, which is why there are separate classes for TSQLConnection and TSQLQuery, or TADOConnection and TADOQuery. Some datasets will automatically create a connection if you don't provide one, but stating that closing a dataset *typically* closes the connection isn't quite correct. – Ken White Aug 08 '14 at 02:44
  • @KenWhite By "closing the connection" I mean closing the dataset functionality off from the connection. – Jerry Dodge Aug 08 '14 at 03:38
  • But it doesn't typically affect *the connection* at all. You can have multiple datasets sharing the same connection. Your statement that the connection is "typically" closed would mean that all datasets would lose their connectivity, and that isn't the case at all. – Ken White Aug 08 '14 at 13:30
  • @KenWhite That wasn't what I intended though, edited to clarify. – Jerry Dodge Aug 08 '14 at 16:49
  • I hate to quibble, but you're still referring to the *connection*. There doesn't have to be a connection involved at all. For instance, `TClientDataSet.Open` has no "connection" that becomes active that would then be closed; if I use the FieldDefs to define the fields at runtime, use CreateDataSet to actually create it, use Open or Active to open it, insert/edit/delete/etc., and then simply Close it (using it as a temporary local dataset), there is zero "connection" involvement. – Ken White Aug 08 '14 at 20:36