Board index » delphi » Delphi7->DBExpress->MS SQL200-> Missing database Error.

Delphi7->DBExpress->MS SQL200-> Missing database Error.


2004-05-24 05:08:33 PM
delphi257
Let create such example. We use MS SQL 2000(SP 3a), Delphi 7 (Build 8.1),
dbexpmss.dll (from Delphi 7,MS SQL Driver Update).
Create database tables for our example - I give a script for SQL Query
Analyzer
==============SQL script start
drop TABLE TABLE1
go
CREATE TABLE [TABLE1] (
[TBL1_ID] [int] NOT NULL ,
[TBL1_NOTE] [varchar] (30) COLLATE Cyrillic_General_CS_AS NULL ,
CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
(
[TBL1_ID]
) ON [PRIMARY] ,
) ON [PRIMARY]
GO
CREATE trigger TABLE1_BD on TABLE1
instead of delete
as
begin
declare @TBL1_ID integer
declare @TBL1_STR varchar(255)
select @TBL1_ID=TBL1_ID from deleted
delete from table1 where TBL1_ID=@TBL1_ID
set @TBL1_STR=cast(@TBL1_ID as varchar(255) )
IF @@TRANCOUNT>0 ROLLBACK TRAN
raiserror('Can not delete record with ID=. %s',16,1, @TBL1_STR )
end
go
insert into TABLE1(TBL1_ID,TBL1_NOTE) values(1, 'First')
go
insert into TABLE1(TBL1_ID,TBL1_NOTE) values(2, 'Second')
go
insert into TABLE1(TBL1_ID,TBL1_NOTE) values(3, 'Third')
go
grant all on TABLE1 to public
go
select * from table1
go
==============SQL script end
Ok. Try to delete record from TABLE1 in SQL Query Analyzer
====================
delete from table1 where tbl1_id=1
We can see a error generated by trigger TABLE1_BD
Can not delete record with ID=. 1
Ok. Now we write an simple Example on Delphi using DBExpress. We have an
SQLConnection1 :TSQLConnection object, connected to MS SQL server and
SimpleDataSet1: TSimpleDataSet with such properties
object SimpleDataSet1: TSimpleDataSet
Aggregates = <>
Connection = SQLConnection1
DataSet.CommandText = 'select * from TABLE1'
DataSet.MaxBlobSize = -1
DataSet.Params = <>
Params = <>
Left = 96
Top = 664
end
We also have a button with such code
procedure TForm1.Button5Click(Sender: TObject);
begin
SQLConnection1.Open;
SimpleDataSet1.Open;
SQLConnection1.StartTransaction;
try
SimpleDataSet1.Delete;
SimpleDataSet1.ApplyUpdates( 0 );
SQLConnection1.Commit;
except
SQLConnection1.Rollback;
Application.HandleException( Self );
end;
end;
Run this example and click on Button5. We can see in SQL Profiler this log
declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, NULL, N'select * from TABLE1'
select @P1
go
set implicit_transactions on
go
SET FMTONLY ON select "TBL1_ID","TBL1_NOTE" from "TABLE1" SET FMTONLY OFF
go
declare @P1 int
set @P1=NULL
exec sp_prepexec @P1 output, N'@P1 int,@P2 varchar(30)', N'delete from
"TABLE1"
where
"TBL1_ID" = @P1 and
"TBL1_NOTE" = @P2
', 1, 'First'
select @P1
go
IF @@TRANCOUNT>0 COMMIT TRAN
Go
====================================================
But we can't see an error in our example! The line
"SimpleDataSet1.ApplyUpdates( 0 );" think that delete method is successful.
It is wrong!
Then I try this example with ADO, And see such picture. I have an
ADOCommand1:T ADOCommand with such properties
object ADOCommand1: TADOCommand
CommandText = 'delete from TABLE1 where TBL1_ID=2'
Connection = ADOConnection1
Parameters = <>
Left = 272
Top = 160
End
And such code
procedure TForm1.Button2Click(Sender: TObject);
begin
ADOCommand1.Connection.Open;
ADOCommand1.Connection.BeginTrans;
try
ADOCommand1.Execute;
ADOCommand1.Connection.CommitTrans;
except
ADOCommand1.Connection.RollbackTrans;
Application.HandleException( Self );
end;
end;
Run example and click on Button2. We can see in SQL Profiler this log
set implicit_transactions on
go
delete from TABLE1 where TBL1_ID=2
go
IF @@TRANCOUNT>0 COMMIT TRAN
go
And again we can't see our error! Then we set some add properties in the
ADOCommand as
object ADOCommand1: TADOCommand
CommandText = 'delete from TABLE1 where TBL1_ID=2'
Connection = ADOConnection1
ExecuteOptions = [eoExecuteNoRecords] (!! Set ExecuteOptions to
eoExecuteNoRecords]
Parameters = <>
Left = 272
Top = 160
end
Run example again and click on Button2. We can see in SQL Profiler this log
set implicit_transactions on
go
delete from TABLE1 where TBL1_ID=2
go
IF @@TRANCOUNT>0 ROLLBACK TRAN
Go
An now I can see my Error generated in the trigger( I have an Exception! ) -
that's work right.
"Can not delete record with ID.=2"
So, I have a question. How is solved this problem( bag ????) using
DBExpress?
Now we use DBExpress to make our program work with different database
servers, such as MS-SQL, Oracle, Interbase. And we have found an error that
we can't to cross. What can we do?
 
 

Re:Delphi7->DBExpress->MS SQL200-> Missing database Error.

You can give a try to this one:
RAISERROR ('Can not delete record with ID=. ' + IsNull(@TBL1_STR ), 18, 1)
with seterror.
It works for me through ADO eveb withouth eoExecuteNoRecords option.
Regards,
Vitali
 

Re:Delphi7->DBExpress->MS SQL200-> Missing database Error.

"Vitali Kalinin" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях
следующе? news:XXXX@XXXXX.COM...
Quote
You can give a try to this one:
RAISERROR ('Can not delete record with ID=. ' + IsNull(@TBL1_STR ), 18, 1)
with seterror.

It works for me through ADO eveb withouth eoExecuteNoRecords option.

Regards,
Vitali


The same situation. Does not work. ADO version 2.7 and 2.8. The error does
not appear.
 

Re:Delphi7->DBExpress->MS SQL200-> Missing database Error.

"Vitali Kalinin" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях
следующе? news:XXXX@XXXXX.COM...
Quote
You can give a try to this one:
RAISERROR ('Can not delete record with ID=. ' + IsNull(@TBL1_STR ), 18, 1)
with seterror.

It works for me through ADO eveb withouth eoExecuteNoRecords option.

Regards,
Vitali


In really all works if I change trigger as
============================================
CREATE trigger TABLE1_BD on TABLE1
instead of delete
as
begin
declare @TBL1_ID integer
declare @TBL1_STR varchar(255)
select @TBL1_ID=TBL1_ID from deleted
-- delete from table1 where TBL1_ID=@TBL1_ID
set @TBL1_STR=cast(@TBL1_ID as varchar(255) )
IF @@TRANCOUNT>0 ROLLBACK TRAN
raiserror('Can not delete record with ID=. %s',16,1, @TBL1_STR )
end
============================================
that is rem delete statement. Now i see error, but why the line "delete
from table1 where TBL1_ID=@TBL1_ID" kills my error?
 

Re:Delphi7->DBExpress->MS SQL200-> Missing database Error.

"Oleg Zyryanov" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
Quote

The problem was solved when I wrote
SET NOCOUNT ON
in the start of trigger.