Board index » delphi » Can not delete using TQuery.Delete

Can not delete using TQuery.Delete

Why if I call Delete, it raises EDBEngineError: Table is read-only???

I don't use updatesql because I don't need cache update and the code
below is a background process to delete specific records. So there is no
datasource

procedure TForm1.FormShow(Sender: TObject);
begin
  With QueryMsg do
  Begin
    DatabaseName := 'Server';
    SQL.Add('SELECT (Count(*)) As Num FROM MESSAGES WHERE ToUIN =
:UIN');
    ParamByName('UIN').AsSmallInt := User;
    Open;
    NumOfMsg := FieldByName('Num').AsInteger;
    ShowMessage(IntToStr(NumOfMsg));
    Close;
    Unprepare;
    SQL.Clear;
    SQL.Add('SELECT FROMUIN, TIME, DATA FROM MESSAGES WHERE TOUIN =
:UIN');
    ParamByName('UIN').AsSmallInt := User;
    Prepare;
    RequestLive:= True;
    Open;
  End;
end;

procedure TForm1.BtDeleteAllClick(Sender: TObject);
begin
  with QueryMsg do
  Begin
    First;
    While NumOfMsg > 0 do
    Begin
      Delete;
      Dec(NumOfMsg);
    End;
  End;
end;

Thanks in advance.

Regards,
Wasis

 

Re:Can not delete using TQuery.Delete


Wasis,

Before I make a suggestion about deletes, let me say that you can
safely delete all references to prepare and unprepare here. They are
not helping you. You are running an SQL statement just one time. In
this case, let Delphi do the prepare and unprepare automatically.
Prepare and unprepare are useful only if you run the query several
times and don't change the SQL.

About deletes.
When you make a loop to run from the first to the last record, you
must be sure to change the current record in the database. You start
OK by using the First method. But you also must use the Next method to
move to a new record. Also, it's better to use the EOF method instead
of a counter. So a loop through the dataset might look like this.

MyQUery.First;
while not MyQuery.EOF do
        begin
        {do something to the record}
        Next;  {this is what you missed}
        end;

But this is not a good way to do deletes because, as you delete, the
record count changes.

A better way to do deletes is with SQL, and you don't have to use
parameters. For example:

MyQuery.Close;
MyQuery.SQL.Add('DELETE FROM MESSAGES WHERE TOUIN = '
        + IntToStr(user));
MyQuery.ExecSQL;

And it's done.

This is the kind of code you should put in the BtDeleteAllClick event.

Note that you use ExecSQL here and not Open. Open is for SELECT.
ExecSQL is for everything else. But note that you must have the query
closed before you call ExecSQL.

Quote
Wasis Sugiono <wasi...@rad.net.id> wrote:
>Why if I call Delete, it raises EDBEngineError: Table is read-only???

>I don't use updatesql because I don't need cache update and the code
>below is a background process to delete specific records. So there is no
>datasource

>procedure TForm1.FormShow(Sender: TObject);
>begin
>  With QueryMsg do
>  Begin
>    DatabaseName := 'Server';
>    SQL.Add('SELECT (Count(*)) As Num FROM MESSAGES WHERE ToUIN =
>:UIN');
>    ParamByName('UIN').AsSmallInt := User;
>    Open;
>    NumOfMsg := FieldByName('Num').AsInteger;
>    ShowMessage(IntToStr(NumOfMsg));
>    Close;
>    Unprepare;
>    SQL.Clear;
>    SQL.Add('SELECT FROMUIN, TIME, DATA FROM MESSAGES WHERE TOUIN =
>:UIN');
>    ParamByName('UIN').AsSmallInt := User;
>    Prepare;
>    RequestLive:= True;
>    Open;
>  End;
>end;

>procedure TForm1.BtDeleteAllClick(Sender: TObject);
>begin
>  with QueryMsg do
>  Begin
>    First;
>    While NumOfMsg > 0 do
>    Begin
>      Delete;
>      Dec(NumOfMsg);
>    End;
>  End;
>end;

>Thanks in advance.

>Regards,
>Wasis

--

Re:Can not delete using TQuery.Delete


Quote
>Why if I call Delete, it raises EDBEngineError: Table is read-only???

Are you sure request Live is working?  Have you checked the CanModify property?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Can not delete using TQuery.Delete


Deleting a record will be best done if you send an explicit DELETE SQL
statement to the database (using the rec primary key).
But now, you can still use the Delete method of Tquery since the latter is
set Live. So, there is perhaps a problem with your query, as the error
message suggests it.
Which database are you using ?

--
Olivier Dahan
oda...@{*word*104}cable.fr
Delphi 32b C/S certified engineer
Wasis Sugiono <wasi...@rad.net.id> a crit dans le message :
3741E6FF.E8906...@rad.net.id...

Quote
> Why if I call Delete, it raises EDBEngineError: Table is read-only???

> I don't use updatesql because I don't need cache update and the code
> below is a background process to delete specific records. So there is no
> datasource

> procedure TForm1.FormShow(Sender: TObject);
> begin
>   With QueryMsg do
>   Begin
>     DatabaseName := 'Server';
>     SQL.Add('SELECT (Count(*)) As Num FROM MESSAGES WHERE ToUIN =
> :UIN');
>     ParamByName('UIN').AsSmallInt := User;
>     Open;
>     NumOfMsg := FieldByName('Num').AsInteger;
>     ShowMessage(IntToStr(NumOfMsg));
>     Close;
>     Unprepare;
>     SQL.Clear;
>     SQL.Add('SELECT FROMUIN, TIME, DATA FROM MESSAGES WHERE TOUIN =
> :UIN');
>     ParamByName('UIN').AsSmallInt := User;
>     Prepare;
>     RequestLive:= True;
>     Open;
>   End;
> end;

> procedure TForm1.BtDeleteAllClick(Sender: TObject);
> begin
>   with QueryMsg do
>   Begin
>     First;
>     While NumOfMsg > 0 do
>     Begin
>       Delete;
>       Dec(NumOfMsg);
>     End;
>   End;
> end;

> Thanks in advance.

> Regards,
> Wasis

Re:Can not delete using TQuery.Delete


Quote
> But now, you can still use the Delete method of Tquery since the latter is
> set Live. So, there is perhaps a problem with your query, as the error
> message suggests it.

CanModify := False

Quote
> Which database are you using ?

Local Interbase

Thanks in advance.

Regards,
Wasis

Re:Can not delete using TQuery.Delete


Quote
> Are you sure request Live is working?

Not sure, just Set RequestLive to True

Quote
>  Have you checked the CanModify property?

always False

Re:Can not delete using TQuery.Delete


Quote
> Before I make a suggestion about deletes, let me say that you can
> safely delete all references to prepare and unprepare here. They are
> not helping you. You are running an SQL statement just one time. In
> this case, let Delphi do the prepare and unprepare automatically.
> Prepare and unprepare are useful only if you run the query several
> times and don't change the SQL.

2 times: 2 open and 2 close. First get the number of records and then get all
specific records.

Quote
> About deletes.
> When you make a loop to run from the first to the last record, you
> must be sure to change the current record in the database. You start
> OK by using the First method. But you also must use the Next method to
> move to a new record. Also, it's better to use the EOF method instead
> of a counter. So a loop through the dataset might look like this.

> MyQUery.First;
> while not MyQuery.EOF do
>         begin
>         {do something to the record}
>         Next;  {this is what you missed}
>         end;
>From the Delphi Help:

Delete: Deletes the current record and positions the cursor on the next
record.

So, do I need to call Next?

Quote
> But this is not a good way to do deletes because, as you delete, the
> record count changes.

I use Select because I want to get all fields of the current record and send
them through TServerSocket, before delete the current record. After that go
to the next record, get, send, delete and so on.

Quote
> MyQuery.Close;
> MyQuery.SQL.Add('DELETE FROM MESSAGES WHERE TOUIN = '
>         + IntToStr(user));
> MyQuery.ExecSQL;

In this case, I have to use another TQuery, don't I?

Thanks in advance.

Regards,
Wasis

Re:Can not delete using TQuery.Delete


Quote
Wasis Sugiono <wasi...@rad.net.id> wrote:

>> Before I make a suggestion about deletes, let me say that you can
>> safely delete all references to prepare and unprepare here. They are
>> not helping you. You are running an SQL statement just one time. In
>> this case, let Delphi do the prepare and unprepare automatically.
>> Prepare and unprepare are useful only if you run the query several
>> times and don't change the SQL.

>2 times: 2 open and 2 close. First get the number of records and then get all
>specific records.

Yes, but you change the SQL. Whenever you change the SQL, the query
has to do the prepare step again. I think you are confusing "query"
with "TQuery". A database query is not a TQuery component. A database
query is the SQL. The TQuery component is just the thing that handles
a database query.

If you ran the same query (that is, not changing SQL) then you don't
need the prepare every time and you can increase performance by doing
the prepare manually.  

Quote

>> About deletes.
>> When you make a loop to run from the first to the last record, you
>> must be sure to change the current record in the database. You start
>> OK by using the First method. But you also must use the Next method to
>> move to a new record. Also, it's better to use the EOF method instead
>> of a counter. So a loop through the dataset might look like this.

>> MyQUery.First;
>> while not MyQuery.EOF do
>>         begin
>>         {do something to the record}
>>         Next;  {this is what you missed}
>>         end;

>>From the Delphi Help:
>Delete: Deletes the current record and positions the cursor on the next
>record.

>So, do I need to call Next?

I'm not sure. You would have to test that by looking at things like
RecordCount and BookMark.

Quote

>> But this is not a good way to do deletes because, as you delete, the
>> record count changes.

>I use Select because I want to get all fields of the current record and send
>them through TServerSocket, before delete the current record. After that go
>to the next record, get, send, delete and so on.

>> MyQuery.Close;
>> MyQuery.SQL.Add('DELETE FROM MESSAGES WHERE TOUIN = '
>>         + IntToStr(user));
>> MyQuery.ExecSQL;

>In this case, I have to use another TQuery, don't I?

No. The choice is yours. You can use a TQuery over and over again for
as many different queries or SQL statements as you like.

My apps use a lot of TQuery components. Usually that's because I set
the SQL at design time and don't want to change it in the code. So I
have one TQuery that looks up orders, say, and another one that looks
up customers.

But I usually have a general query that I use for all my code-based
queries, like you are doing. One TQuery, many uses. Since I am always
changing the SQL in this one TQuery, I never do an explicit prepare or
unprepare. I let Delphi do it for me since I don't reuse the SQL.

Quote

Phil Cain
--

Re:Can not delete using TQuery.Delete


Quote
> But I usually have a general query that I use for all my code-based
> queries, like you are doing. One TQuery, many uses. Since I am always
> changing the SQL in this one TQuery, I never do an explicit prepare or
> unprepare. I let Delphi do it for me since I don't reuse the SQL.

What do you mean by 'reuse'? Like this:
1. Use SQL1
2. Use SQL2
3. Reuse SQL1 again.

So if I use a new SQL, I don't have to prepare, but if I use an SQL which I have
used it before, I have to do prepare?

But does it affect the component so that CanModify is always False;

Thanks in advance.

Regards,
Wasis

Re:Can not delete using TQuery.Delete


Quote
Wasis Sugiono <wasi...@rad.net.id> wrote:
>> But I usually have a general query that I use for all my code-based
>> queries, like you are doing. One TQuery, many uses. Since I am always
>> changing the SQL in this one TQuery, I never do an explicit prepare or
>> unprepare. I let Delphi do it for me since I don't reuse the SQL.

>What do you mean by 'reuse'? Like this:
>1. Use SQL1
>2. Use SQL2
>3. Reuse SQL1 again.

>So if I use a new SQL, I don't have to prepare, but if I use an SQL which I have
>used it before, I have to do prepare?

>But does it affect the component so that CanModify is always False;

Wasis,

I "reuse" a TQuery by changing the SQL. Every time I do this:

MyQuery.SQL.Clear

or

MyQuery.SQL.Add('My SQL statement')

then I change the value in the SQL property of the TQuery. When I
change the value of the SQL property, I've made a new query. It
doesn't matter if you reuse SQL1 again or use SQL3 or SQL4. Each time
you change the SQL property, the TQuery component starts over again.

So when I make up SQL in my code, I usually use only one TQuery and
change the SQL property instead of having more than one TQuery.

Now suppose you have two SQLs: SQL1 and SQL2. If you start with SQL1,
then go to SQL2, the TQuery doesn't know anything about SQL1 any more.
If you go back to SQL1 again, it's like doing it the first time.

When you change the SQL property like this, it doesn't make any sense
to prepare and unprepare manually. You can if you like to. It doesn't
hurt. You're just doing what TQuery will do automatically.

CanModify is set by TQuery depending on 1) the SQL you use 2) the
value in RequestLive and 3) rules established through the BDE or other
middleware. So the value of CanModify can change as you change SQL.
This process is not entirely under your control.

You have a certain amount of control. For example, you can ensure that
CanModify is always False if you set RequestLive to False manually.
This turns off all ability to change the data when you use a SELECT
statement. However, I don't think that RequestLive or CanModify apply
when you do not use a SELECT statement. For example, when you use  a
DELETE sql statement.

Also, CanModify doesn't tell the whole story. If CanModify is true,
maybe you still can't modify because you have rules in the database
which prohibit modification, rules that TQuery doesn't know about. In
that case, the database will give an error if you try to modify
something you shouldn't.

I have never yet used CanModify and I don't think it is used much.
When I want to change the dataset that comes from a SELECT statement,
I do one of two things.

First, I set RequestLive to True. This means I must have a one-table
select - no joins or TQuery will turn RequestLive to False in most
cases. Or the second way is to use cached updates and do the
modification with TUpdateSQL.

HTH

Phil Cain

--

Re:Can not delete using TQuery.Delete


Quote
>What's the easyest way to change the Level from 7 to 5 of an existing table?

No easy what that I know of.  The best you can do is create a new level 5 table
and add the contents of the old table to it.

Check the CanModify property to see if you actually got a live query
Brian

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Can not delete using TQuery.Delete


Hi,

Quote
> >So, do I need to call Next?

> I'm not sure. You would have to test that by looking at things like
> RecordCount and BookMark.

In this case, I'm right. Suppose I had 4 records, if I delete and call next, only
2 records would be deleted. So, don't call next after delete

Quote
> But I usually have a general query that I use for all my code-based
> queries, like you are doing. One TQuery, many uses. Since I am always
> changing the SQL in this one TQuery, I never do an explicit prepare or
> unprepare. I let Delphi do it for me since I don't reuse the SQL.

I know what's the problem.
I set RequestLive to True AFTER calling Open. If I set it before opening the
dataset, it works. However, my sql makes an error:
Invalid use of keyword.
Token: TIME

This is my SQL (the second sql):
SQL.Add('SELECT MESSAGES.FROMUIN, MESSAGES.TOUIN, MESSAGES.TIME, MESSAGES.DATA
FROM MESSAGES WHERE MESSAGES.TOUIN = :UIN');

Do you still remember the first one:
SQL.Add('SELECT (Count(*)) As Num FROM MESSAGES WHERE ToUIN = :UIN');

Well, I try to set RequestLive to True at design time. And when it try to open
using the first sql, it raises an error: Capability not supported.

These two errors are confusing, aren't they? I have another form which contains a
TQuery. I set the RequestLive to True at design time. These are its SQLs:

SELECT USERS.NNAME, USERS.FNAME || ' ' || USERS.LNAME As FullName,
MESSAGES.FromUIN, Messages.ToUIN, MESSAGES.TIME, MESSAGES.DATA
FROM USERS, MESSAGES
WHERE USERS.UIN = MESSAGES.FromUIN and MESSAGES.ToUIN = 100
ORDER BY MESSAGES.TIME ASC

and

SELECT USERS.NNAME, USERS.FNAME || ' ' || USERS.LNAME As FullName,
MESSAGES.FromUIN, Messages.ToUIN, MESSAGES.TIME, MESSAGES.DATA
FROM USERS, MESSAGES
WHERE USERS.UIN = MESSAGES.ToUIN and MESSAGES.FromUIN = 100
ORDER BY MESSAGES.TIME ASC

It works without any errors. No "invalid use of keyword" error though it uses TIME
and no "capability not supported" error.

Re:Can not delete using TQuery.Delete


Quote
Wasis Sugiono <wasi...@rad.net.id> wrote:
>These two errors are confusing, aren't they? I have another form which contains a
>TQuery. I set the RequestLive to True at design time. These are its SQLs:

You are right to say that you have to set RequestLive before you start
the query. TQuery needs that information for the prepare step and that
happens before data is fetched.

RequestLive is a request to be able to change the data in the dataset.
Your error messages are not confusing if you remember that the dataset
you get is not necessarily in the same shape as the data in the
database.

In order to allow you to use the dataset to update the database,
TQuery must be able to map each row/column value in your dataset to a
row/column in the database. If you say SELECT * FROM MYTABLE, then the
dataset is an exact mirror of your database and TQuery knows what
comes from what. This association is called "unambiguous."

But if you say SELECT COUNT(*) FROM MYTABLE, you get one row in your
dataset and that row doesn't match any row in your database. This
association is called "ambiguous." The TQuery has no idea what part of
the database to put changes in. RequestLive is not allowed here.

Further, if the SQL statement is not a SELECT statement, then
RequestLive is meaningless. I haven't tested this, but I hope TQuery
would ignore RequestLive if the statement is not a SELECT.

I don't know exactly why you are getting an error on TIME in one case
and not the other. However, I suspect that TIME is a keyword in the
database engine and when you use it for a column name, it confuses the
database sometimes. You don't get the error when TIME is a part of the
Order By clause. When you do that, I think the database has enough
information to figure out that you are talking about your column and
not the TIME keyword. To fix this problem, change the name of the
column to something that is not a keyword.

Phil Cain
--

Other Threads