firebird-net-provider

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

[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:

> 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] 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:

 > 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

[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

[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