firebird-net-provider

[Firebird-net-provider] Bug with multiple commands in same transaction

[Pierre A. <pie...@op...>]

Hello,

I have done following:

- Created transaction with IsolationLevel.RepeatableRead.
- Executed two commands using FbCommand, while keeping the
  same transaction active :

  (1)  CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
  (2)  INSERT INTO X(A, B) VALUES (1,1);

When executing command (2), I get an error stating that I
am referring to an unknown table named X (but it should
exist !?) with SQL error code -204.

I am using latest .NET provider 1.5 BETA (last update in
CHANGELOG is from 2003-11-10), .NET 1.1, Firebird 1.5 RC7.

Kind regards,

    Pierre

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[<car...@te...>]

Hello:

> I have done following:
> 
> - Created transaction with IsolationLevel.RepeatableRead.
> - Executed two commands using FbCommand, while keeping the
>   same transaction active :
> 
>   (1)  CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
>   (2)  INSERT INTO X(A, B) VALUES (1,1);

I think it's working ok, as the first command is not committed yet.





--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[Denis D. <fir...@De...>]

Hi,
about your replay to:

> > - Created transaction with IsolationLevel.RepeatableRead.
> > - Executed two commands using FbCommand, while keeping the
> >   same transaction active :
> >
> >   (1)  CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
> >   (2)  INSERT INTO X(A, B) VALUES (1,1);
>
> I think it's working ok, as the first command is not committed yet.

Certainly not, transactions are conceptualy for making a serie of SQL
command, and then commit (or rollback) all of them if ok (or not)

(1) START TRANSACTION
(2) CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
(3)  INSERT INTO X(A, B) VALUES (1,1);
(4) COMMIT / END TRANSACTION

should work, without "commit" in the middle.
Is'nt that true ?

Best regard,
-- 
Denis Dumoulin

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[<car...@te...>]

Hello:

> Certainly not, transactions are conceptualy for making a serie of SQL
> command, and then commit (or rollback) all of them if ok (or not)
> 
> (1) START TRANSACTION
> (2) CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
> (3)  INSERT INTO X(A, B) VALUES (1,1);
> (4) COMMIT / END TRANSACTION

I think it's working ok.

The CREATE TABLE is a DDL command and i think that will be not possible 
to make anything with the table until the transaction is commited.

I have make the same test using IBExpert and raises the same error.

The same can be applied to isql, try this on it:

SET AUTODDL OFF;
CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
INSERT INTO X(A, B) VALUES (1,1);

I get the same error.


If i'm wrong, please correct me :)




--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[Pierre A. <pie...@op...>]

Carlos Guzm=E1n =C1lvarez wrote:
>> Certainly not, transactions are conceptualy for making a serie of SQL
>> command, and then commit (or rollback) all of them if ok (or not)
>>
>> (1) START TRANSACTION
>> (2) CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
>> (3)  INSERT INTO X(A, B) VALUES (1,1);
>> (4) COMMIT / END TRANSACTION
>
> I think it's working ok.
>
> The CREATE TABLE is a DDL command and i think that will be not possible
> to make anything with the table until the transaction is commited.

I just ran the following script in IBExpert's "Script Executive",
and it works just fine :

CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
INSERT INTO X(A, B) VALUES (1,1);
COMMIT WORK;

I don't see why the table creation should not be visible from within
the transaction. This would be really silly !?

> I have make the same test using IBExpert and raises the same error.

See my example; does it raise an error in your IBExpert ?

> If i'm wrong, please correct me :)

I am no SQL guru, but the fact that a table is not visible unless
the full transaction is commited does not make sense to me.

    Pierre

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[<car...@te...>]

Hello:

> I just ran the following script in IBExpert's "Script Executive",
> and it works just fine :
> 
> CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
> INSERT INTO X(A, B) VALUES (1,1);
> COMMIT WORK;

I have tested it from the sql editor of ib expert, but just test this in 
the script executive:

CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);

You are not going to get a message telling that there are uncommited 
statements ( this message appears if you exec only the insert for 
example ), i think that they are making an autocommit for DDL 
statements, as ISQL makes by default.

Has you tested the sample i have sent for ISQL ??



--
Best regards

Carlos Guzmán álvarez
Vigo-Spain

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[Pierre A. <pie...@op...>]

Carlos Guzm=E1n =C1lvarez wrote:
>> CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
>> INSERT INTO X(A, B) VALUES (1,1);
>> COMMIT WORK;
>
> I have tested it from the sql editor of ib expert, but just test this i=
n
> the script executive:
>
> CREATE TABLE X(A INTEGER NOT NULL, B INTEGER);
>
> You are not going to get a message telling that there are uncommited
> statements ( this message appears if you exec only the insert for
> example ), i think that they are making an autocommit for DDL
> statements, as ISQL makes by default.

You mean that after every DDL statement, I have to do a commit also
on my own. This seems pretty weird.

But I have to agree with you: IBExpert seems to be committing its
work automagically.

> Has you tested the sample i have sent for ISQL ??

I don't have ISQL, but I believe what you tell me. It just seems
really strange. Adding the "SET AUTODLL OFF;" line in IBExpert
causes also IBExpert to generate an error on the INSERT statement.


So how can I do what I want, namely create a table and fill it
in, then rollback the table creation ?

Thanks a lot.

    Pierre

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[<car...@te...>]

Hello:

> I don't have ISQL, but I believe what you tell me. It just seems
> really strange. Adding the "SET AUTODLL OFF;" line in IBExpert
> causes also IBExpert to generate an error on the INSERT statement.

If you have firebird installed you have isql too, just see in the 
firebird bin directory ( isql.exe ).

> So how can I do what I want, namely create a table and fill it
> in, then rollback the table creation ?

But you don't want to mantain the table created after have it filled ?? 
In that case, Why not ?? :)

If you want to mantain it created after have it filled, and drop it if 
you get an error, at this moment you can do:

(1) Start transaction.
(2) Create table.
(3) Commit transaction (using Commit method)
(4) Start transaction.
(5) Fill the Table
(6) Commit transaction (using Commit method)

if you get an error when filling:

(1) Rollback transaction.
(2) Drop the table.

or:

(1) Start transaction.
(2) Create table.
(3) Commit transction (using CommitRetaining method.)
(4) Fill the Table.
(5) Commit transaction (using Commit method.)

if you get an error when filling:

(1) Rollback transaction.
(2) Drop the table.


--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

P.S.: I'm thinking on add to the FbCommand a new property called 
FbCommandType for allow to know which type of command is being executed, 
it will be possible to know it after have the statement prepared 
(GdsStatement has yet this information in the StatementType property), 
comments are wellcome ;)

Re: [Firebird-net-provider] Bug with multiple commands in same transaction

[Pierre A. <pie...@op...>]

Carlos Guzm=E1n =C1lvarez wrote:
> If you have firebird installed you have isql too, just see in the
> firebird bin directory ( isql.exe ).

Oh. Thanks ! I was not aware of that.

>> So how can I do what I want, namely create a table and fill it
>> in, then rollback the table creation ?
>
> But you don't want to mantain the table created after have it filled ??
> In that case, Why not ?? :)

Well, I wanted to create a few tables and fill them and make it look
like an atomic operation to other processes; either the tables would
be there with their content, or they would not. So in fact what was
of interest here was really the fact that the transaction was hiding
the tables until the COMMIT.

> If you want to mantain it created after have it filled, and drop it if
> you get an error, at this moment you can do:
>
> (1) Start transaction.
> (2) Create table.
> (3) Commit transaction (using Commit method)
> (4) Start transaction.
> (5) Fill the Table
> (6) Commit transaction (using Commit method)
>
> if you get an error when filling:
>
> (1) Rollback transaction.
> (2) Drop the table.
>
> or:
>
> (1) Start transaction.
> (2) Create table.
> (3) Commit transction (using CommitRetaining method.)
> (4) Fill the Table.
> (5) Commit transaction (using Commit method.)
>
> if you get an error when filling:
>
> (1) Rollback transaction.
> (2) Drop the table.

Yes, I guess that this is what I will do, and I will have to add
logic to handle the presence of empty tables, but this should
not be too complicated.

Thanks for your help.

    Pierre

commit after each DDL (Re: [Firebird-net-provider] Bug with multiple commands in same transaction)

[David G. <li...@za...>]

(Cc-ing to the docs list).

Pierre Arnaud wrote:
> You mean that after every DDL statement, I have to do a commit also
> on my own. This seems pretty weird.

This is also one of my first traps in Firebird :) I hope this makes it 
to a prominent place in the FAQ.

-- 
dave