SQL Anywhere database - T-SQL TRIGGER causes crash in .Net, Interactive SQL works...?

Author

T-SQL TRIGGER causes crash in .Net, Interactive SQL works...?

Marc Andre

2005-12-20, 7:23 am

Hi All

I am fairly new to Sybase databases. I have a database with a single table
(AdressenStorage) to that table I added (T-SQL)triggers for INSERT, UPDATE
and DELETE. Everything works fine with InteractiveSQL. But as soon as I
execute the statements from my .Net application, it crashes. (The program
hangs at cmd.ExecuteNonQuery() ).
I removed all triggers and everything worked fine. Right now I have just the
(reduced) INSERT trigger (which still crashes):

CREATE TRIGGER " AdressenStorage_Inse
rt" on DBA.AdressenStorage for insert as
begin
-- Handle RevIsCurrent
update AdressenStorage as m set
RevIsCurrent = 0 from
AdressenStorage as m,inserted as i where
m.Adr_Nr = i.Adr_Nr AND m.RevNr=0
-- Update Revision Info
update AdressenStorage as m set
RevModifiedDate = NOW(*),
RevModifiedUser = sp_GetCurrentFullUse
r(*),
RevModifiedOrgID = sp_GetCurrentOrgID(*
) from
AdressenStorage as m,inserted as i where
i.RevNr = m.RevNr and i.Adr_Nr = m.Adr_Nr
end

The problem mainly is at the first UPDATE statement. If I remove this, it
works fine.
Statement that causes the crash in .Net:
INSERT INTO AdressenStorage (Name, Zusatz, Adr_Nr, RevNr) VALUES ('test1',
'', 20003057, 1)

Additional Infos:
- Sybase ASA Server 9 Build 3228
- Connection and Commands opened the standard way. Parameters to connection:
eng, dbn, uid, pwd=, CommLink=tcpip
- Executing SET TEMPORARY OPTION CONNECTION_AUTHENTIC
ATION=... for OEM
authentification
- SQL-User has full rights, identical user in Interactive SQL and .Net.

Questions:
- Does anybody know the difference from executing the SQL statement in
Interactive SQL and .NET ASA Provider?
- What is wrong with my trigger?
- Is there a possiblity to debug the trigger?
- Is there a possiblity to find out, why execution of this command fails in
..Net, can I get any error/debug reports?
- Is there a advantage to user WATCOM-SQL TRIGGERS?

I hope somebody can help me!
Cheers
Marc

Breck Carter [TeamSybase]

2005-12-20, 9:23 am

Does it "crash" with an error message, or does it just "hang" (stops
doing anything) until you kill it?

If the former, tell us the error message... but I think it is the
latter, and it sounds like the connection is being blocked.

Do you have multiple connections to the database? If one of them
updates a row, another connection trying to update will be blocked
until the first one does a commit.

Does your program open multiple connections to the database?

You can watch for blocked connections with Block Sniffer
http://www.risingroad.com/block_sniffer.html

With WATCOM SQL you can have row-level before triggers which let you
change columns via SET statements rather than updates, as in

inserted.whatever = new_value;

....highly recommended. However, TSQL syntax is probably not your
problem here.

Breck

On 20 Dec 2005 03:06:48 -0800, "Marc Andre" <marc.andre@netline.ch>
wrote:

>Hi All
>
>I am fairly new to Sybase databases. I have a database with a single table
>(AdressenStorage) to that table I added (T-SQL)triggers for INSERT, UPDATE
>and DELETE. Everything works fine with InteractiveSQL. But as soon as I
>execute the statements from my .Net application, it crashes. (The program
>hangs at cmd.ExecuteNonQuery() ).
>I removed all triggers and everything worked fine. Right now I have just the
>(reduced) INSERT trigger (which still crashes):
>
>CREATE TRIGGER " AdressenStorage_Inse
rt" on DBA.AdressenStorage for insert as
>begin
> -- Handle RevIsCurrent
> update AdressenStorage as m set
> RevIsCurrent = 0 from
> AdressenStorage as m,inserted as i where
> m.Adr_Nr = i.Adr_Nr AND m.RevNr=0
> -- Update Revision Info
> update AdressenStorage as m set
> RevModifiedDate = NOW(*),
> RevModifiedUser = sp_GetCurrentFullUse
r(*),
> RevModifiedOrgID = sp_GetCurrentOrgID(*
) from
> AdressenStorage as m,inserted as i where
> i.RevNr = m.RevNr and i.Adr_Nr = m.Adr_Nr
>end
>
>The problem mainly is at the first UPDATE statement. If I remove this, it
>works fine.
>Statement that causes the crash in .Net:
>INSERT INTO AdressenStorage (Name, Zusatz, Adr_Nr, RevNr) VALUES ('test1',
>'', 20003057, 1)
>
>Additional Infos:
>- Sybase ASA Server 9 Build 3228
>- Connection and Commands opened the standard way. Parameters to connection:
>eng, dbn, uid, pwd=, CommLink=tcpip
>- Executing SET TEMPORARY OPTION CONNECTION_AUTHENTIC
ATION=... for OEM
>authentification
>- SQL-User has full rights, identical user in Interactive SQL and .Net.
>
>Questions:
>- Does anybody know the difference from executing the SQL statement in
>Interactive SQL and .NET ASA Provider?
>- What is wrong with my trigger?
>- Is there a possiblity to debug the trigger?
>- Is there a possiblity to find out, why execution of this command fails in
>.Net, can I get any error/debug reports?
>- Is there a advantage to user WATCOM-SQL TRIGGERS?
>
>I hope somebody can help me!
>Cheers
>Marc
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Marc Andre

2005-12-20, 9:23 am

Hi Breck

> Does it "crash" with an error message, or does it just "hang" (stops
> doing anything) until you kill it?

It is just hanging.

> If the former, tell us the error message... but I think it is the
> latter, and it sounds like the connection is being blocked.
>
> Do you have multiple connections to the database? If one of them
> updates a row, another connection trying to update will be blocked
> until the first one does a commit.
>
> Does your program open multiple connections to the database?

No, not yet.

> You can watch for blocked connections with Block Sniffer
> http://www.risingroad.com/block_sniffer.html

I will test that. Is it possible that the trigger blocks itself? The trigger
updates every row in the same table where the new one is being inserted (and
why the trigger was triggerd)
Is there a different behavior for triggers between Interactive SQL and the
..Net Provider?

I will let you know of my results with Block Sniffer

> With WATCOM SQL you can have row-level before triggers which let you
> change columns via SET statements rather than updates, as in
>
> inserted.whatever = new_value;
>
> ...highly recommended. However, TSQL syntax is probably not your
> problem here.
>
> Breck

Thanks
Marc

>
> On 20 Dec 2005 03:06:48 -0800, "Marc Andre" <marc.andre@netline.ch>
> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-12-20, 8:24 pm

On 20 Dec 2005 06:29:14 -0800, "Marc Andre" <marc.andre@netline.ch>
wrote:

>Is it possible that the trigger blocks itself?

No, absolutely not. A connection cannot block itself, only two
different connections can get into conflict.

Another possibility is an infinite trigger loop, but that would
require two update triggers on the same table (which is possible, but
only you would know that... and AFAIK a trigger loop blows up quickly
on an error message).

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Marc Andre

2005-12-20, 8:24 pm

Hi Breck

I see the blocking with BlockSniffer.
Blocking Connection is always Conn # 2. Blocked Connection is the connection
that tries to insert the row. Blocking Lock is "Shared Phantom Anti-phantom
row lock on DBA.AdressenStorage"

I was currios because Conn # 2 had no name. In my app I start every
connection with a name to be able to debug it. When I restart my app, the
connection number of the blocked connection counts up (as expected), but the
blocking connection is always conn # 2.

I finally found out, that the problem was the Interacitve SQL instance, that
was running parallel (for debugging, etc), and that blocked quite every
table. So the problem seams not to lie in my app or the triggers, but in my
debugging... ;-)

Anyway I think I can solve the problem now.

Thank you for your help!
Marc

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:ocsgq1d84pqinrm
ap3ogap96igmg0sp3k9@
4ax.com...
> On 20 Dec 2005 06:29:14 -0800, "Marc Andre" <marc.andre@netline.ch>
> wrote:
>
>
> No, absolutely not. A connection cannot block itself, only two
> different connections can get into conflict.
>
> Another possibility is an infinite trigger loop, but that would
> require two update triggers on the same table (which is possible, but
> only you would know that... and AFAIK a trigger loop blows up quickly
> on an error message).
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links