firebird-net-provider — All about development of the MS .Net provider

Re: [Firebird-net-provider] Feature Request: Multiple Transactions

[Willibald K. <wi...@dw...>]

Hi!

> One question in your scenario the connection is open permanently ?? ( I
> ask this because we need to think that ADO .NET is designed for work in
> disconnected mode )


Yes, of course - otherwise I could not hold the transaction open, can I?

Hmm, seems that ADO .NET supports - to me - a completely new way of working
with databases.. I've got an IBO/IBX background and therefore I'm used to
long lasting connections and 'lots' of transactions, that can be 'nested' /
concurrently started.

ADO.NET seems to favour an 'as many transactions - as many connections'
philosophy. But why do we need transaction in the first place then?! (We
could trade a connection as transaction)

?-(
 Willi

Re: [Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

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

Hello:

> Well, if an user goes into 'edit' mode in one of the windows, I could do a
> dummy update in the transaction that's responsible for that window (to lock
> the record). If the user now (window 1 is in edit mode) decides to open
> another window, I can not just commit the changes from window 1 and use the
> transaction to open window 2. If you've read
> http://www.interbase-world.com/modules.php?name=News&file=article&sid=423 ,
> you'll understnd that I want to use transactions for 'locking' records.

One question in your scenario the connection is open permanently ?? ( I 
ask this because we need to think that ADO .NET is designed for work in 
disconnected mode )




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

- Miembro del Proyecto FirebirdSQL.
- Miembro honorario de la Fundación FirebirdSQL.

http://www.firebirdsql.org

Re: [Firebird-net-provider] .NET Provider 1.5 for .NET 1.1

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

Hello:

> When do you think you will have the 1.5 version for the 1.1 framework 
> availabel?


I'm waiting for te creation of a new module in the CVS for the sources 
after this i will release the first alpha version.



-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

[Willibald K. <wi...@dw...>]

""Lee Connell"" <lee...@ad...> schrieb im Newsbeitrag
news:019101c35375$b62d5890$0c00a8c0@s0phtinc...
>
> Hi Willibald,

Hi Lee,

> I am not sure I totally understood what you said but wouldn't you in a =
> case like this need to use only 1 connection to the database?  You send =
> 1 transaction at a time updating those seperate windows.  The only way =
> "I" see it (like i said i might be missing something) is that you want =
> to run multi-threaded app that can send concurrent transactions?

Well, if an user goes into 'edit' mode in one of the windows, I could do a
dummy update in the transaction that's responsible for that window (to lock
the record). If the user now (window 1 is in edit mode) decides to open
another window, I can not just commit the changes from window 1 and use the
transaction to open window 2. If you've read
http://www.interbase-world.com/modules.php?name=News&file=article&sid=423 ,
you'll understnd that I want to use transactions for 'locking' records.

Of course it would also be a nice feature to be able to run a report in a
separate thread, using the same connection. But I already need more than one
transaction per Client without going multi-thread.

Willi

[Firebird-net-provider] .NET Provider 1.5 for .NET 1.1

[Lee C. <lee...@ad...>]

Hi Carlos,

When do you think you will have the 1.5 version for the 1.1 framework =
availabel?

Lee

Re: [Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

[Lee C. <lee...@ad...>]

Hi Willibald,

I am not sure I totally understood what you said but wouldn't you in a =
case like this need to use only 1 connection to the database?  You send =
1 transaction at a time updating those seperate windows.  The only way =
"I" see it (like i said i might be missing something) is that you want =
to run multi-threaded app that can send concurrent transactions?

Lee

"Willibald Krenn" <wi...@dw...> wrote in message =
news:bftn6d$5up$1...@ne......
> Hi!
>=20
> I'm using the Firebird ADO.NET provider 1.0 for some time now and I =
have to
> say that it's well done.

Re: [Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

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

Hello:

> Well, following is not possible (but should be - IMHO):

If i'm wrong correct me but i think he means anything like this:

Open Connection

Start transaction
Execute command(s)
Commit or Rollback

Start transaction
Execute command(s)
Commit or Rollback

Start transaction
Execute command(s)
Commit or Rollback

...

Close Connection







-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

[Willibald K. <wi...@dw...>]

Hello!

> I don't quite see the problem since one can associate a different
> transaction to every FbCommand-object
> e.g:
>  FbConnection conn = new FbConnection(...)
>  FbTransaction txn = conn.BeginTransaction();

Well, following is not possible (but should be - IMHO):
  FbTransaction txn1 = conn.BeginTransaction();
  FbTransaction txn2 = conn.BeginTransaction(); // throws exception.

So you would have to do

  FbConnection con1 = new FbConnection(..)
  FbConnection con2 = new FbConnection(..)
  FbConnection con3 = new FbConnection(..)
  FbTransaction txn1 = con1.BeginTransaction();
  FbTransaction txn2 = con2.BeginTransaction();
  FbTransaction txn3 = con3.BeginTransaction();
Just to get three independant transactions! (e.g one for generating a
report, the other for fetching some data and the third e.g. for  modifying
some data)

See what I mean? Now if you've got five clients with all doing the same, you
end up with 5*3 connections....

Willi

Re: [Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

[Jan J. <ja...@uv...>]

Hi!
I don't quite see the problem since one can associate a different
transaction to every FbCommand-object

e.g:

 FbConnection	conn	= new FbConnection(...)
 FbTransaction	txn		= conn.BeginTransaction();

    custDA.SelectCommand = new FbCommand("SELECT custno, customer FROM
CUSTOMER", conn, txn); //here you could add a different transaction-object!
    custDA.InsertCommand = new FbCommand("INSERT INTO customer (CustomerID,
customer) " +
                                            "VALUES (?, ?)", conn, txn); //
//here you could also add different transaction-object!

    custDA.InsertCommand.Parameters.Add("@custno", FbDbType.Int32, 4,
"custno");
    custDA.InsertCommand.Parameters.Add("@customer", FbType.VarChar, 25,
"customer");

}


Regards
Jan Jensen
""Willibald Krenn"" <wi...@dw...> skrev i en meddelelse
news:bftn6d$5up$1...@ne......
> Hi!
>
> I'm using the Firebird ADO.NET provider 1.0 for some time now and I have
to
> say that it's well done.
>
> Of course there are some things I would like to see improved, like it
would
> be nice to have differnet FbExceptions: (FbDatabaseNotFoundException,
> FbSQLException with an Enum for the Error, ..). However, this is  just a
> 'nice to have' feature and one can work around that by directly comparing
> the error code with a hard coded value to obtain the reason for the
> exception. (Yes, it's ugly, but it works)
>
> But there is one behaviour, which is considered ADO.NET standard (AFAIWT),
> that makes me feel sick: Only one transaction per connection is allowed.
> Let me provide you some details of why I think the .NET provider should
have
> an option in the connection string to turn this behaviour off:
>
> We all know that Firebird is a multi-generational architecture database
> management system. This implies that working with one long lasting
> transaction is considered to be bad practice. Instead updates/inserts
should
> be made in a separate - short - transaction besides the longer lasting
read
> only transaction that fetches the data.
> Currently the .NET provider allows only one transaction per connection. If
> you consider a client application that has several windows that display
> different data and these can be edited (for each dataset in edit state you
> need a transaction for 'locking' ) / shown at the same time, you'll come
to
> the conclusion, that you need more than one transaction per client.
> Unfortunately this leads to the consequence that one needs more than one
> _connection_ per client.
> This is not only impractical when you want to implement some licensing
> schema that allows only a fixed number of connections (= 'clients'), but
> also brings unnecessary load to the firebird server!
> Just imagine you have client applications that might open five concurrent
> transactions and you have five clients attached - then the server has to
> manage 25 connections instead of 5...
> If you happen to implement the database logic on a server too (so the
> clients are getting their data through this 'application server' and are
> considered to be somewhat 'thin clients'), you see that the application
> server needs to create 20 more unnecessary connection objects (with all
the
> things attached to them).
>
> In my mind multiple transaction per connection is a cornerstone and a
'must
> have' for writing efficient and good style firebird client applications.
>
> Thanks for reading,
>  Willibald Krenn
>
>
>
>
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
>
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>

[Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

[Willibald K. <wi...@dw...>]

Hi!

I'm using the Firebird ADO.NET provider 1.0 for some time now and I have to
say that it's well done.

Of course there are some things I would like to see improved, like it would
be nice to have differnet FbExceptions: (FbDatabaseNotFoundException,
FbSQLException with an Enum for the Error, ..). However, this is  just a
'nice to have' feature and one can work around that by directly comparing
the error code with a hard coded value to obtain the reason for the
exception. (Yes, it's ugly, but it works)

But there is one behaviour, which is considered ADO.NET standard (AFAIWT),
that makes me feel sick: Only one transaction per connection is allowed.
Let me provide you some details of why I think the .NET provider should have
an option in the connection string to turn this behaviour off:

We all know that Firebird is a multi-generational architecture database
management system. This implies that working with one long lasting
transaction is considered to be bad practice. Instead updates/inserts should
be made in a separate - short - transaction besides the longer lasting read
only transaction that fetches the data.
Currently the .NET provider allows only one transaction per connection. If
you consider a client application that has several windows that display
different data and these can be edited (for each dataset in edit state you
need a transaction for 'locking' ) / shown at the same time, you'll come to
the conclusion, that you need more than one transaction per client.
Unfortunately this leads to the consequence that one needs more than one
_connection_ per client.
This is not only impractical when you want to implement some licensing
schema that allows only a fixed number of connections (= 'clients'), but
also brings unnecessary load to the firebird server!
Just imagine you have client applications that might open five concurrent
transactions and you have five clients attached - then the server has to
manage 25 connections instead of 5...
If you happen to implement the database logic on a server too (so the
clients are getting their data through this 'application server' and are
considered to be somewhat 'thin clients'), you see that the application
server needs to create 20 more unnecessary connection objects (with all the
things attached to them).

In my mind multiple transaction per connection is a cornerstone and a 'must
have' for writing efficient and good style firebird client applications.

Thanks for reading,
 Willibald Krenn

Re: [Firebird-net-provider] Database Schema support

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

Hello:

> I'm thinking in add other for Dependencies but i have not decided yet, 
> what do you can be this elements enough or not ??

I have added one  more for Generators.





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] Database Schema support

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

Hello:

I'm finishing the Database Schema support for the new version, here is 
the enum that will be used for specifiing the type of schema you want to 
retrieval:

	public enum FbDbSchemaType
	{
		Character_Sets,
		Check_Constraints,
		Check_Constraints_By_Table,
		Collations,
		Columns,
		Column_Privileges,
		Domains,
		Foreign_Keys,
		Functions,
		Indexes,
		Primary_Keys,
		Procedure_Parameters,
		Procedure_Privileges,
		Procedures,
		Provider_Types,
		Roles,
		Statistics,
		Tables,
		Table_Constraint,
		Tables_Info,
		Table_Privileges,
		Table_Statistics,
		Triggers,
		Usage_Privileges,
		View_Column_Usage,
		Views,
		View_Privileges,
	}


I'm thinking in add other for Dependencies but i have not decided yet, 
what do you can be this elements enough or not ??


Comments are Welcome !!





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] NET Provider, stored procedures

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

Hello:

 > Also how come it is required to have a transaction for queries?  I 
tried to
 > query the database without one and it said it was invalid.  So now i 
guess i
 > have to make a method in a class that will return me a result set and 
i'll
 > have to define a ref parameter that sets a transaction object so i can
 > either commit the transaction or roll it back?


This will be no needed in next version (1.5), i'm waiting for the 
creation of a new module in the CVS for commit the new sources.



-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] NET Provider, stored procedures

[Lee C. <lee...@ad...>]

>ExecuteNonQuery can't return a resultset but it updates the output
>values of an stored proc execution.

Thank you Carlos,

Also how come it is required to have a transaction for queries?  I tried to
query the database without one and it said it was invalid.  So now i guess i
have to make a method in a class that will return me a result set and i'll
have to define a ref parameter that sets a transaction object so i can
either commit the transaction or roll it back?


Lee

Re: [Firebird-net-provider] NET Provider, stored procedures

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

Hello:

> An ExecuteNonQuery can return results?  I'm gonna give the code a run 
> and let you know what happens. 

ExecuteNonQuery can't return a resultset but it updates the output 
values of an stored proc execution.




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Carlos: Multiple posts

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

Hello:

> I don't know if it's on purpose for some reason or you don't know but 
> just to let you know you're posts are sent to me twice for everyone of 
> your replies.


Sorry this is because usually i use reply to all :), and this send the 
message to the list and to the sender.



-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] Carlos: Multiple posts

[Lee C. <lee...@ad...>]

Carlos,

I don't know if it's on purpose for some reason or you don't know but =
just to let you know you're posts are sent to me twice for everyone of =
your replies.

Lee

Re: [Firebird-net-provider] NET Provider, stored procedures

[Lee C. <lee...@ad...>]

Carlos,

An ExecuteNonQuery can return results?  I'm gonna give the code a run =
and let you know what happens. =20

Thanks alot!,

Lee

Re: [Firebird-net-provider] NET Provider, stored procedures

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

Hello:

> Here is the stored procedure.  Could you just write me code of how YOU 
> would execute this stored procedure?  It takes in a username and 
> userpassword, and it returns a userloginid and a userprivilegeid.

Here is a sample of code that it's working for me, tell me if it's 
correct or not please :):


FbConnection connection = new FbConnection(connectionString);
connection.Open();

FbTransaction transaction = connection.BeginTransaction();

FbCommand command = new FbCommand("EXECUTE PROCEDURE 
LOGINUSER(@userloginname, @userloginpass)", connection, transaction);

command.Parameters.Add("@userloginname", FbType.VarChar).Value = "A";
command.Parameters.Add("@userloginpass", FbType.VarChar).Value = "A";

command.Parameters.Add("@userloginid", FbType.Integer).Direction = 
ParameterDirection.Output;
command.Parameters.Add("@userprivilegeid", FbType.SmallInt).Direction = 
ParameterDirection.Output;

command.CommandType = CommandType.StoredProcedure;

command.ExecuteNonQuery();

Console.WriteLine("Stored proc result: {0} {1}",
	command.Parameters["@userloginid"].Value,
	command.Parameters["@userprivilegeid"].Value);

transaction.Rollback();

connection.Close();





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] NET Provider, stored procedures

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

Hello:

> Here is the stored procedure.  Could you just write me code of how YOU 
> would execute this stored procedure?  It takes in a username and 
> userpassword, and it returns a userloginid and a userprivilegeid.

Ok i will made a test later.

> I closed the reader first and then commited.  It executed fine, but no 
> results in the database.

What you mean for reslts ?? :) This SP do not made any insert, update, 
delete.

Finally if you want to get a resultset with all the users logins, you 
need to execute the command as SELECT * FROM LOGINUSER(?,?).



-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] NET Provider, stored procedures

[Lee C. <lee...@ad...>]

>What is the function of the SP ??

Here is the stored procedure.  Could you just write me code of how YOU =
would execute this stored procedure?  It takes in a username and =
userpassword, and it returns a userloginid and a userprivilegeid.

//// code ////
CREATE PROCEDURE LOGINUSER (
    USERLOGINNAME VARCHAR(255),
    USERLOGINPASS VARCHAR(255))
RETURNS (
    USERLOGINID INTEGER,
    USERPRIVILEGEID SMALLINT)
AS
begin
  select distinct userid, privilegeid from authenticate where =
username=3D:userloginname and
   userpass=3D:userloginpass into :userloginid, :userprivilegeid;
  =20
  if (:userloginid is null) then
  begin
   userloginid =3D 0;
  end
 =20
  if (:userprivilegeid is null) then
  begin
   userloginid =3D 0;
  end
 =20
  suspend;
end
////////////////

>If you are using ExecuteReader, you need to close the reader before=20
>commit the transaction.

I closed the reader first and then commited.  It executed fine, but no =
results in the database.

Re: [Firebird-net-provider] NET Provider, stored procedures

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

Hello:

> You're problably getting sick of me by now.  The method finally executed 
> without an error, but it did not put any data into the database.  

What is the function of the SP ??


> Also i try to call commit after the call to the database and it gives me an 
> error saying its busy, open, fetching.

If you are using ExecuteReader, you need to close the reader before 
commit the transaction.





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] The values =?windows-1252?Q?=27=80=27?= =?windows-1252?Q?=2C=27=A5=27=2C=27=A3=27_are_not_getting_added=2E?=

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

Hello:

>    The values '€','¥','£' are not getting added.

Which character set are you using in the database and in Connection 
String??




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] NET Provider Pooling

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

Hello:

> Just for your information, in your NET Provider docs it says that you do 
> not currently support connection pooling.  

Where ??


> This would mean they are "closed" connections now.  
> How does this work?

The connection is not really closed, it's back to the pool, and it will 
be closed when the lifetime expires.




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] The values '€','¥','£' are not getting added.

[Ghanshyam P. <gha...@ms...>]

Hello,

    The values '€','¥','£' are not getting added.

    When i am trying to insert any one of the values - '€','¥','£' into the 
table, it's not being added. I am trying to execute the query from ASP.Net.
    But if the same query is executed using IBConsole, then the values are 
getting added successfully.
    If the same query is executed using isql,then also it's not being added.

    e.g.
    INSERT INTO currency (currencyid, currencyname, rate, symbol) VALUES (1, 
'Euro', 1.073, '€');

    Can anyone help me?

    Thanks in advance for any help.

    Regards,
    Ghanshyam.

_________________________________________________________________
They are beautiful. They are in danger. 
http://server1.msn.co.in/Slideshow/BeautyoftheBeast/index.asp Our 
four-legged friends.