firebird-net-provider

[Firebird-net-provider] How can I execute several commands in a single FbCommand object

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

Hello,

I'd like to do the following using a single FbCommand object :

"CREATE TABLE Test(ID INTEGER NOT NULL,
                   REV INTEGER NOT NULL,
                   DATA VARCHAR(100) CHARACTER SET UNICODE_FSS);

 ALTER TABLE Test ADD CONSTRAINT PK_TEST PRIMARY KEY (ID, REV);"

Is this possible ? If so, can I do it ? I tried to define the
command text to be exactly as above, and it executes fine. However,
when I check with IB Expert, I don't see the PK_TEST constraint.

It is as if it had not been processed by the command.

Any ideas ?

    Pierre

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[Carlos G. A. <car...@te...>]

Hello:

> I'd like to do the following using a single FbCommand object :
> 
> "CREATE TABLE Test(ID INTEGER NOT NULL,
>                    REV INTEGER NOT NULL,
>                    DATA VARCHAR(100) CHARACTER SET UNICODE_FSS);
> 
>  ALTER TABLE Test ADD CONSTRAINT PK_TEST PRIMARY KEY (ID, REV);"
> 
> Is this possible ? If so, can I do it ? I tried to define the
> command text to be exactly as above, and it executes fine. However,
> when I check with IB Expert, I don't see the PK_TEST constraint.
> 
> It is as if it had not been processed by the command.
> 
> Any ideas ?


You have two ways use the FbScript class, or use FbCommand.ExecuteReader 
and FbDataReader.NextResult.

Which version of the .NET Provider are you using ??


-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain


"Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las 
mismas cosas."

Albert Einstein.

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

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

>> I'd like to do the following using a single FbCommand object :
>> 
>> "CREATE TABLE Test(ID INTEGER NOT NULL,
>>                    REV INTEGER NOT NULL,
>>                    DATA VARCHAR(100) CHARACTER SET UNICODE_FSS);
>> 
>>  ALTER TABLE Test ADD CONSTRAINT PK_TEST PRIMARY KEY (ID, REV);"
[...]
> You have two ways use the FbScript class, or use FbCommand.ExecuteReader
> and FbDataReader.NextResult.

Well, FbScript is not an option, since it does not have the IDbCommand
interface. I'd like to be able to define the parameters for my multiple
commands once, then let it execute everything in one call.

The second solution, using ExecuteReader, almost works. It feels strange
to have to use it, since both commands above don't return any results.
Moreover, if I do :

for (;;)
{
    while (reader.Read ())
    {
        ...
    }
    if (! reader.NextResult ()) break;
}

Then I get an exception on "Read", telling me "invalid request handle".
If I drop the reader.Read (), then it will be OK.

Why do I get an invalid request handle if the command has not produced
any results ? I'd rather expect Read to return false.

> Which version of the .NET Provider are you using ??

I am using assembly version 1.5.1333.25082.


    Pierre

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[mailmur <ma...@ya...>]

[slightly offtopic comment]
This is what I've always found MSSQLServer to be one
of the best dbserver atm. It can run an arbitrary
script queries just fine through a normal executequery
commands.

Firebird cannot run simple compound queries such as
"INSERT... ; UPDATE...;" in one go through
executequery.

> >> I'd like to do the following using a single
> FbCommand object :
> >> 
> >> "CREATE TABLE Test(ID INTEGER NOT NULL,
> >>                    REV INTEGER NOT NULL,
> >>                    DATA VARCHAR(100) CHARACTER
> SET UNICODE_FSS);
> >> 
> >>  ALTER TABLE Test ADD CONSTRAINT PK_TEST PRIMARY
> KEY (ID, REV);"
> [...]
> > You have two ways use the FbScript class, or use
> FbCommand.ExecuteReader
> > and FbDataReader.NextResult.
> 
> Well, FbScript is not an option, since it does not
> have the IDbCommand
> interface. I'd like to be able to define the
> parameters for my multiple
> commands once, then let it execute everything in one
> call.
> 
> The second solution, using ExecuteReader, almost
> works. It feels strange
> to have to use it, since both commands above don't
> return any results.
> Moreover, if I do :
> 
> for (;;)
> {
>     while (reader.Read ())
>     {
>         ...
>     }
>     if (! reader.NextResult ()) break;
> }
> 
> Then I get an exception on "Read", telling me
> "invalid request handle".
> If I drop the reader.Read (), then it will be OK.
> 
> Why do I get an invalid request handle if the
> command has not produced
> any results ? I'd rather expect Read to return
> false.
> 
> > Which version of the .NET Provider are you using
> ??
> 
> I am using assembly version 1.5.1333.25082.

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[Carlos G. A. <car...@te...>]

Hello:

> Firebird cannot run simple compound queries such as
> "INSERT... ; UPDATE...;" in one go through
> executequery.

But it can't be executed using executenonquery because Firebid server 
doesn't support batch command execution ;)




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain


"Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las 
mismas cosas."

Albert Einstein.

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[mailmur <ma...@ya...>]

>> Firebird cannot run simple compound queries such
>> as "INSERT... ; UPDATE...;" in one go through
>> executequery.

> But it can't be executed using executenonquery
> because Firebid server 
> doesn't support batch command execution ;)

Yes, I know. Thats why I expressed my feelings on this
case ;-)

It will often complicates the clientside programs to
overcome this lack of FB feature. If I am right, a
sort of workaround is to create stored procedures for
such operations.

But if batch queries are dynamically generated on fly
and varies heavily based on business logic, then
storedprocs are not a solution. Implementing dynamic
queries at serverside are very very tricky. 

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[Carlos G. A. <car...@te...>]

Hello:

> Well, FbScript is not an option, since it does not have the IDbCommand
> interface. I'd like to be able to define the parameters for my multiple
> commands once, then let it execute everything in one call.

Ok.

> The second solution, using ExecuteReader, almost works. It feels strange
> to have to use it, since both commands above don't return any results.

If i'm not wring this the only way for exec batch commands in ADO.NET

> Why do I get an invalid request handle if the command has not produced
> any results ? I'd rather expect Read to return false.


Can you etst this using the latest CVS sources ??

I can send to you a dll built with .net 1.0 or a zip with the sources if 
needed.

> I am using assembly version 1.5.1333.25082.

jeje I mean if you are using 1.5 Alpha 1, 1.5 Alpha2, 1.5 Beta 1, etc.. :)


-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain


"Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las 
mismas cosas."

Albert Einstein.

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

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

Carlos Guzman Alvarez wrote:

>> Why do I get an invalid request handle if the command has not produced
>> any results ? I'd rather expect Read to return false.
>
> Can you etst this using the latest CVS sources ??

I have not been able to connect to CVS today. Can you please send
me either the .NET 1.1 binaries, or the current sources and I will
be glad to check.

> jeje I mean if you are using 1.5 Alpha 1, 1.5 Alpha2, 1.5 Beta 1, etc.. :)

I don't remember :-(

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

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

> Can you etst this using the latest CVS sources ??

OK. I've been able to download CVS sources. Last entry in
Changelog is dated 2003-10-04.


I can confirm that following code does not work:

   command.CommandType = System.Data.CommandType.Text;
   command.CommandText =
    "CREATE TABLE A(F1 INTEGER NOT NULL, F2 INTEGER NOT NULL);" +
    "ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (F1, F2);";
   command.Transaction = my_transaction;

   reader = command.ExecuteReader ();

   for (;;)
   {
     while (reader.Read ())
     {
       System.Console.Out.WriteLine ("{0} columns found.",
                                     reader.FieldCount);
     }
     if (reader.NextResult () == false)
     {
       break;
     }
   }
   reader.Close ();

I get an exception thrown in the first call to Read() with
"invalid request handle" as explanation.

I get the following trace:

    Read()
      GdsStatement.Fetch, line 523
        GdsAttachment.ReceiveResponse, line 319
          GdsAttachment.ReadStatusVector, line 222

    with arg = 0x1 and er  = 0x14000007

Do you have any idea how to fix this ?

    Pierre

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[Carlos G. A. <car...@te...>]

Hello:


> I can confirm that following code does not work:

Try this:

FbTransaction transaction = connection.BeginTransaction();

string sql = "CREATE TABLE A(F1 INTEGER NOT NULL, F2 INTEGER NOT NULL);" 
+ "ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (F1, F2);";
			
FbCommand command = new FbCommand(sql, connection, transaction);

FbDataReader reader = command.ExecuteReader();
while (reader.NextResult())
{
}

reader.Close();
transaction.Commit();






-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain


"Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las 
mismas cosas."

Albert Einstein.

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

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

Carlos Guzman Alvarez wrote:

> Try this:
> 
> FbTransaction transaction = connection.BeginTransaction();
> 
> string sql = "CREATE TABLE A(F1 INTEGER NOT NULL, F2 INTEGER NOT NULL);"
> + "ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (F1, F2);";
> 
> FbCommand command = new FbCommand(sql, connection, transaction);
> 
> FbDataReader reader = command.ExecuteReader();
> while (reader.NextResult())
> {
> }
> 
> reader.Close();
> transaction.Commit();

It crashes on the second call to NextResult, at FbCommand.cs:588,
with commandText == null. The 'if (commandText.Length > 0)' can
not execute; if I replace it with :

if ((commandText != null) && (commandText.Length > 0))

then it works.

Can you please fix this ?


And I still do not understand why calling reader.Read() would
crash ?

    Pierre

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[Carlos G. A. <car...@te...>]

Hello:

> Can you please fix this ?

I'm going to add some changes to this, now FbCommand.NextResult will be:

internal bool NextResult()
{
	bool returnValue = false;

	actualCommand++;

	if (actualCommand >= commands.Length)
	{
		actualCommand--;
	}
	else
	{
		string commandText = commands[actualCommand];

		if (commandText != null &&
		commandText.Trim().Length > 0)
		{
			statement.Drop();
			statement = null;

			InternalPrepare();
			InternalExecute();
	
			returnValue = true;
		}
	}

	return returnValue;
}




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain


"Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las 
mismas cosas."
Albert Einstein.

Re: [Firebird-net-provider] How can I execute several commands in a single FbCommand object

[Carlos G. A. <car...@te...>]

Hello:

> It crashes on the second call to NextResult, at FbCommand.cs:588,
> with commandText == null. The 'if (commandText.Length > 0)' can
> not execute; if I replace it with :
> 
> if ((commandText != null) && (commandText.Length > 0))
> 
> then it works.
> 
> Can you please fix this ?

Huummm it's working fine for me, without change anything, can i send to 
you the latest CVS sources ??

> And I still do not understand why calling reader.Read() would
> crash ?

Probably because the Read method makes a fetch and there are no 
resultset created in the server for this type of command ;)




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain


"Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las 
mismas cosas."
Albert Einstein.