Recent

Author Topic: IBX: Opening a TIBTable tries to delete a record  (Read 1640 times)

emilt

  • New Member
  • *
  • Posts: 26
IBX: Opening a TIBTable tries to delete a record
« on: June 29, 2020, 04:01:39 pm »
The test case is pretty simple and is in the attached files. TIBDatabase, TIBTransaction and a TIBTable component. On button click, the table is opened, a value is read from the record, and the table is closed.

On second attempt to do the same, there is an EIBInterBaseError :
Quote
no permission for DELETE access to TABLE SYSOPT When executing: delete form "SYSOPT" where RDB$DB_KEY = :IBX_INTERNAL_DBKEY

What the message says is true, the user has no rights to delete from the table, and the table obviously doesn't have a primary key. But all this happens on a simple
Code: [Select]
IBTable1.Active := True; line.  What am I doing wrong?

Lazarus 2.0.8 on Win10 x64, Firebird 3.0.5 x64.

PS. Use a non-SYSDBA firebird user!

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #1 on: June 29, 2020, 04:49:54 pm »
I didn't test it... but this could be because TIBTable tries to create an INSERT, UPDATE and DELETE command on opening the table and those statements are prepared. I think the error comes from the prepare (InternalPrepare in TIBTable.GenerateUpdateSQL).

BTW Why are you using TIBTable? I've always thought it was highly inefficient.

emilt

  • New Member
  • *
  • Posts: 26
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #2 on: June 29, 2020, 05:55:55 pm »
Yes, it makes sense that the error is coming from Prepare and it's not really trying to delete anything. But still, it's strange.

I used a TIBTable because it looked the simplest option (maybe too simple :)), and the table anyway has always only one record.

I will probably rework this using the IBX IAttachment interface directly.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #3 on: June 29, 2020, 06:00:41 pm »
I will probably rework this using the IBX IAttachment interface directly.
The easiest would be TIBQuery, I think.
Simple conversion from TIBTable and you keep everything from TDataset (and can use it with TDatasource etc).

Or if you only need one value you can create a separate procedure which fetches this value with a dynamically created (and destroyed) TIBQuery.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #4 on: June 30, 2020, 10:19:41 am »
With TIBTable if you do not want it to generate and prepare Insert/Update/Delete SQL then you have to set the ReadOnly property to true before the TIBTable is opened (e.g. at design time). TIBTable does not itself check to see if you have read/write rights to the underlying dataset.

If you want better control than this then you either use TIBQuery for read only selects or TIBDataSet. TIBDataSet is useful when you know (e.g.) that you have Select/Update privilege on a table or view but not Insert/Delete. In such a case, you can provide Select, Refresh and Modify SQL, but leave the Insert and Delete SQL empty. At prepare time, it would then prepare the Select, Refresh and Modify queries while skipping over the empty delete and insert queries.

TIBTable is really there to provide a simple "get you going" access to an individual table in a database. As you are you "get serious" you will move on the TIBQuery and TIBDataset.

emilt

  • New Member
  • *
  • Posts: 26
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #5 on: June 30, 2020, 12:34:04 pm »
Yes, now I understand that TIBTable was the "too simple" choice for my case  :) But the error message was somewhat frightening  :o

While at  this subject, is there any special difference between a TIBQuery with an Update object, and a TIBDataset? AFAIU both can provide read/write access, why would one be preferred over the other?

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #6 on: June 30, 2020, 12:51:40 pm »
Yes, now I understand that TIBTable was the "too simple" choice for my case  :) But the error message was somewhat frightening  :o

While at  this subject, is there any special difference between a TIBQuery with an Update object, and a TIBDataset? AFAIU both can provide read/write access, why would one be preferred over the other?
As I understand it, and someone correct me if I'm wrong... you have (at least for Delphi, links to Delphi sites).

TIBSQL - This is the most bare component for just executing SQL. No interface to data-aware components. Very simple and ideal for just executing SQL.

TIBQuery - The next thing. On itself a read-only dataset, which can be expanded with TIBUpdateSQL to make it updatable. Highly flexible. SQL can be anything you want (including joins etc). Can be used with data-aware components. (I use it most of the time with a dynamic function to create TIBUpdateSQL from the given SQL.)

TIBDataSet - Somewhat more heavy. Includes UPDATE, INSERT and DELETE statements. Buffers the result set.

TIBTable - The heavy one. You provide a table name and it just does a SELECT on it. No joins etc. Can also read a lot of data/records (including ALL fields) so you might want to avoid this one in client/server production programs.

IBX for Lazarus might differ slightly but I think the core functionality remained the same.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #7 on: June 30, 2020, 02:48:05 pm »
TIBSQL - This is the most bare component for just executing SQL. No interface to data-aware components. Very simple and ideal for just executing SQL.
TIBSQL is really just a wrapper around the underlying Firebird API.

TIBQuery - The next thing. On itself a read-only dataset, which can be expanded with TIBUpdateSQL to make it updatable. Highly flexible. SQL can be anything you want (including joins etc). Can be used with data-aware components. (I use it most of the time with a dynamic function to create TIBUpdateSQL from the given SQL.)

TIBDataSet - Somewhat more heavy. Includes UPDATE, INSERT and DELETE statements. Buffers the result set.
To be honest, there is not much difference between TIBQuery/TIBUpdateSQL and TIBDataset. Both TIBQuery and TIBDataset descend from TIBCustomDataset and neither adds much in the way of extra functionality. TIBDataset does little more than publish selected TIBCustomDataset properties, while TIBQuery adds SQL parameter property management allowing parameter values to be given at design time (for what it's worth). Both buffer the result set unless you set the Unidirectional property to true - buffering is avoided at the cost of losing bidirectional navigation.

TIBUpdateSQL allows you to add Modify/Insert/Delete/Refresh SQL to an existing TIBQuery (select query). You can thus flexibly extend an existing read only dataset and make it read/write without having to replace it with a TIBDataset. However, the logic for buffering the dataset and transferring data to and from the database is all in TIBCustomDataset and is the same regardless of which approach you take. If you know that you are going to want a read/write dataset when you first write a program then TIBDataset has the merit of holding all the SQL queries in the same object rather than splitting them across two objects. At run time, the path lengths are slightly longer when using TIBQuery/TIBUpdateSQL compared with TIBDataset, but not in a significant way.

A potentially interesting use of TIBUpdateSQL at run time is with TIBDataset (or even TIBTable). These also have a public UpdateObject property (this is published in TIBTable). If you assign a TIBUpdateSQL object to a TIBDataset.UpdateObject property then its Modify/Insert/Delete/Refresh SQL overrides the Modify/Insert/Delete/Refresh SQL in the TIBDataset properties. Similarly, if you use at TUpdateSQL with a TIBTable then you can thus modify the way it updates the underlying table using custom SQL instead of the automatically generated SQL.



TIBTable - The heavy one. You provide a table name and it just does a SELECT on it. No joins etc. Can also read a lot of data/records (including ALL fields) so you might want to avoid this one in client/server production programs.

TIBTable is also a TIBCustomDataset descendent and functionally is the same as TIBDataset except that it generates the SQL queries for you rather than you having to provide them. The downside is that it is restricted to a single table (no joins) and that all fields are read/written rather than only those that you are interested in. It should only be of interest for simple applications and for programmers that do not know SQL and do not want to know about SQL. Everyone else should use TIBDataset or TIBQuery/TIBUpdateSQL.


IBX for Lazarus might differ slightly but I think the core functionality remained the same.
That's true - warts and all!
« Last Edit: June 30, 2020, 02:52:49 pm by tonyw »

emilt

  • New Member
  • *
  • Posts: 26
Re: IBX: Opening a TIBTable tries to delete a record
« Reply #8 on: June 30, 2020, 06:32:21 pm »
Thanks, Tony, for this detailed explanation.

Quote
while TIBQuery adds SQL parameter property management allowing parameter values to be given at design time (for what it's worth).

For me that's an important point, as it allows to pass parameters in a more generic (read: not IBX specific as TParams is part of the FPC DB unit) way to queries. It seems to be the way to go if porting from TSQLQuery.

 

TinyPortal © 2005-2018