Menu
▾
▴
firebird-net-provider
|
From: Mercea P. <pau...@al...> - 2008-04-01 13:30:07
|
Hi
Using FB 1.5.5 SS , FB.Net provider 2.5, VS C#2005.
Database created with FB 1.5.3, ODS 10.1, Dialect 1.
I have a SP which is running fine like "execute procedure
balance(@p1,@p2,@p3);"
The same database, but under FB 2.1 RC2 (17755), running same SP I get this
error:
base {System.Data.Common.DbException} = {"attempted update of read-only
column"}
ErrorCode = 335544359
If I run SP from IBExpert works as expected with both FB SS versions.
SP content looks like :
For select a,b,c from table1 t1
Into :v_a,:v_b, :v_c
do begin
If (exists(select 1 from table2 t2 where t2.id= :v_a and t2.v=:v_b)) then
Begin
Delete from table2 t2 where t2.id=:v_a and t2.v=:v_b;
End
Insert into table2 (t2a,t2b,t2c)
Values (:v_a, ,:v_b, :v_c);
If I delete IF condition and DELETE statement it works without errors.
Regards,
Paul
|
|
From: Jiri C. <di...@ci...> - 2008-04-01 14:06:15
|
First try 2.1 FirebirdClient. The 2.5 has still unfinished work on 2.1
protocol changes. This can cause problems.
> If (exists(select 1 from table2 t2 where t2.id= :v_a and t2.v=:v_b)) then
Isn't this IF redundant? ;)
--
Jiri {x2} Cincura (CTO x2develop.com)
http://blog.vyvojar.cz/jirka/ | http://www.ID3renamer.com
|
|
From: Helen B. <he...@tp...> - 2008-04-01 23:26:48
|
At 11:29 PM 1/04/2008, Mercea Paul wrote:
>Using FB 1.5.5 SS , FB.Net provider 2.5, VS C#2005.
>
>Database created with FB 1.5.3, ODS 10.1, Dialect 1.
>
>I have a SP which is running fine like execute procedure balance(@p1,@p2,@p3);
>
>The same database, but under FB 2.1 RC2 (17755), running same SP I get this error:
>
>base {System.Data.Common.DbException} = {"attempted update of read-only column"}
>
>ErrorCode = 335544359
In Firebird 2 and higher you will get this exception if you have an AFTER trigger that attempts to update a column in the table that owns the trigger.
As to the rest, you have a confusion of cursors there, as well as a confusion of table/set qualifiers. Study the Firebird 2/2.1 release notes to understand why some of your statements might now be "illegal". However, the exception you are seeing is not one of those that you would see as a result of the errors in your stored procedure. I think you will discover its cause in a trigger.
>If I run SP from IBExpert works as expected with both FB SS versions.
Are you actually executing it, or just running it in the IBExpert debugger? If the latter, it is likely that you need to upgrade your IBExpert to a version that supports Firebird 2 and higher. Firebird 1.5 and below were more tolerant to poorly-qualified SQL statements which means that older debugging tools are no longer valid.
Better to test it in isql.
Helen
|
|
From: Mercea P. <pau...@al...> - 2008-04-02 09:20:27
|
Hi Hellen, > > In Firebird 2 and higher you will get this exception if you have an > AFTER trigger that attempts to update a column in the table that owns > the trigger. That table have 5 triggers, 3 for Before Insert, 1 for Before Delete and 1 for Before Update. If I deactivate trigger from Before Delete it works! It's strange because I have 1 trigger for each operation with the same definition (inserting into a log table some values and operation type as "U" for update, "I" for insert,etc),the difference is the type of operation! > > As to the rest, you have a confusion of cursors there, as well as a > confusion of table/set qualifiers. Study the Firebird 2/2.1 release > notes to understand why some of your statements might now be "illegal". > However, the exception you are seeing is not one of those that you > would see as a result of the errors in your stored procedure. I think > you will discover its cause in a trigger. This trigger definition is over my all tables, more than 200 (to log operations maded on each table, if is activated),and works ok for 2 years now!I will debug the SP to see what's happening ! > >If I run SP from IBExpert works as expected with both FB SS versions. > > Are you actually executing it, or just running it in the IBExpert > debugger? If the latter, it is likely that you need to upgrade your > IBExpert to a version that supports Firebird 2 and higher. Firebird > 1.5 and below were more tolerant to poorly-qualified SQL statements > which means that older debugging tools are no longer valid. I'm using IBExpert personal edition, this version does'n have any debugger! I have modified my SP to return values and works ok from IBExpert and isql ! Best regards, Paul |
|
From: Mercea P. <pau...@al...> - 2008-04-02 09:22:11
|
Hi Jiri > First try 2.1 FirebirdClient. The 2.5 has still unfinished work on 2.1 > protocol changes. This can cause problems. Same FB Client (2.5) run ok on FB 1.5.5 server! I thought is a problem from ODS structure ... but isn't! Regards, Paul |
|
From: Jiri C. <di...@ci...> - 2008-04-02 09:27:22
|
On 4/2/08, Mercea Paul <pau...@al...> wrote:
> Same FB Client (2.5) run ok on FB 1.5.5 server!
This is irelevant. If server doesn't support new protocol (<2.1)
provider is using "normal" protocol.
--
Jiri {x2} Cincura (CTO x2develop.com)
http://blog.vyvojar.cz/jirka/ | http://www.ID3renamer.com
|
|
From: Helen B. <he...@tp...> - 2008-04-02 09:41:45
|
At 07:20 PM 2/04/2008, you wrote: >Hi Hellen, > > >> >> In Firebird 2 and higher you will get this exception if you have an >> AFTER trigger that attempts to update a column in the table that owns >> the trigger. > >That table have 5 triggers, 3 for Before Insert, 1 for Before Delete and 1 >for Before Update. >If I deactivate trigger from Before Delete it works! Then your Before Delete trigger probably has a reference to NEW.something. It is invalid in a delete trigger. >It's strange because I have 1 trigger for each operation with the same >definition (inserting into a log table some values and operation type as "U" >for update, "I" for insert,etc),the difference is the type of operation! See above. You can only refer to OLD.something in a delete trigger. Helen |
|
From: Mercea P. <pau...@al...> - 2008-04-02 11:08:23
|
> Then your Before Delete trigger probably has a reference to
> NEW.something. It is invalid in a delete trigger.
Trigger ddl is:
CREATE OR ALTER TRIGGER RPL_doc_invoice_BDEL FOR doc_invoice
ACTIVE BEFORE DELETE POSITION 1000
as
declare variable v_repl char(1);
begin
v_repl='N';
select o.repl from sys_repl o where o.name='doc_invoiceţ into :v_repl;
if (v_repl is null) then
v_repl='N';
/* if (v_repl='I') then old.doc_repl='I'; */
execute procedure insert_log('doc_invoice',old.doc_repl,'D');
end
If I uncomment the "if (repl='I') then old.doc_repl=0;" I get the error!
Best Regards,
Paul
|
|
From: Helen B. <he...@tp...> - 2008-04-02 11:16:25
|
At 09:07 PM 2/04/2008, you wrote:
>> Then your Before Delete trigger probably has a reference to
>> NEW.something. It is invalid in a delete trigger.
>Trigger ddl is:
>
>CREATE OR ALTER TRIGGER RPL_doc_invoice_BDEL FOR doc_invoice
>ACTIVE BEFORE DELETE POSITION 1000
>as
> declare variable v_repl char(1);
> begin
> v_repl='N';
> select o.repl from sys_repl o where o.name='doc_invoiceþ into :v_repl;
> if (v_repl is null) then
> v_repl='N';
> /* if (v_repl='I') then old.doc_repl='I'; */
> execute procedure insert_log('doc_invoice',old.doc_repl,'D');
>
> end
>
>If I uncomment the "if (repl='I') then old.doc_repl=0;" I get the error!
Of course you do! Consider the logic of updating a record that is being deleted! All of these absurdities used to be ignored. From v.2 onward, they are now surfaced as exceptions. Consider it as free education! ;-)
Helen
|
|
From: Mercea P. <pau...@al...> - 2008-04-02 11:28:15
|
> Of course you do! Consider the logic of updating a record that is > being deleted! I thought BEFORE delete is exactly as it say, before deletion, and the record exists and I can use of it and operate as I want! This is possible in 1.5! In this case if I delete a record I can't modify anything on that record! The migration FB 2.X will be very difficult! > All of these absurdities used to be ignored. From v.2 > onward, they are now surfaced as exceptions. Consider it as free > education! ;-) Thanks!!! > Helen |
|
From: Helen B. <he...@tp...> - 2008-04-02 23:14:31
|
At 09:27 PM 2/04/2008, you wrote: > >> Of course you do! Consider the logic of updating a record that is >> being deleted! >I thought BEFORE delete is exactly as it say, before deletion, and the >record exists and I can use of it and operate as I want! Think about it. A trigger belongs to an OPERATION and an EVENT. You cannot convert a DELETE OPERATION into an UPDATE OPERATION. Once you have posted the delete, the only thing that is going to happen to that record is deletion. So, the record itself is read-only: this means that you can READ values from it during a trigger event but you cannot WRITE values to it. >This is possible in 1.5! No, it isn't. It's a no-op in Fb 1.5. The difference now is that the engine *tells* you about it so that you can repair your code. >In this case if I delete a record I can't modify anything on that record! Of course not. A deleted record is, well, DELETED. If you want to modify the record, then use UPDATE, not DELETE! ;-) >The migration FB 2.X will be very difficult! This thread has become totally off topic for the net-provider forum but I must say I'm curious to know what you thought you were achieving when you believed you were able to modify a record during deletion... Also, would you fix your email client/spam filtering so it doesn't mess around with the message Subject, please? If you don't know how, at least edit the Subject before you send. Helen |
|
From: Mercea P. <pau...@al...> - 2008-04-03 06:56:57
|
> Of course not. A deleted record is, well, DELETED. If you want to > modify the record, then use UPDATE, not DELETE! ;-) > > >The migration FB 2.X will be very difficult! > > This thread has become totally off topic for the net-provider forum but > I must say I'm curious to know what you thought you were achieving when > you believed you were able to modify a record during deletion... Well, the logic was this: just before deleting a record I look o.repl and I set doc_repl value to replicate or not the deletion to master database !It looks like is a wrong logic, need to be modified! Thanks for that! > Also, would you fix your email client/spam filtering so it doesn't mess > around with the message Subject, please? If you don't know how, at > least edit the Subject before you send. I have fixed, the smpt mail have a 'stuid' spam filter! Thanks again! Paul |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X