FireBird 1.5 RC3 and Stored Procedures w/ FireBird.Net Data provider

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

June 12th, 2003, 11:56 AM

fetcher

Contributing User

Join Date: Jun 2003

Location: Portugal

Posts: 106

Time spent in forums: 2 h 11 m 33 sec
Reputation Power: 6

FireBird 1.5 RC3 and Stored Procedures w/ FireBird.Net Data provider

Giving my firsteps in FireBird I got stuck with an error when I tried to call a stored procedure. I don't know if the error is in the stored procedure or in th C# code.

Procedure:

Code:

CREATE PROCEDURE ADD_CLIENTE (
    NAME VARCHAR(255),
    EMAIL VARCHAR(75),
    ACTIVATED CHAR(1))
AS
begin
  BEGIN
INSERT INTO CLIENTES (NAME,EMAIL,ACTIVATED)
VALUES (:NAME, :EMAIL,:ACTIVATED);
END
  suspend;
end

C# Code:

Code:

FbConnection fb = new FbConnection(myConnectionString);
Fb.Open();
FbTransaction ft = fb.BeginTransaction();

FbCommand fc = new FbCommand("ADD_CLIENTE",fb,ft);
fc.CommandType = CommandType.StoredProcedure;

fc.Parameters.Add("@NAME",FbType.VarChar,255,"NAME");
fc.Parameters.Add("@EMAIL",FbType.VarChar,75,"EMAIL");
fc.Parameters.Add("@ACTIVATED",FbType.Char,1,"ACTIVATED");
fc.Parameters[0].Value = Server.HtmlEncode(txtName.Text);
fc.Parameters[1].Value = Server.HtmlEncode(txtName.Text);
fc.Parameters[2].Value = cboActivated.SelectedItem.Value;

fc.ExecuteReader(CommandBehavior.Default);

ft.Commit();
fb.Close();

and here is the error I get:

Code:

Exception Details: FirebirdSql.Data.INGDS.GDSException: Exception of type FirebirdSql.Data.INGDS.GDSException was thrown.


[GDSException: Exception of type FirebirdSql.Data.INGDS.GDSException was thrown.]
   FirebirdSql.Data.Firebird.FbStatement.Prepare() +392
   FirebirdSql.Data.Firebird.FbCommand.Prepare() +408

[FbException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 1
ADD_CLIENTE
]

I'm using Firebird.NEt Data Provider v1.0

If anyone can help me out....
Thanks

June 12th, 2003, 01:05 PM

mariuz

Bug Hunter

Join Date: May 2003

Location: Transylvania (Romania)

Posts: 274

Time spent in forums: 19 h 57 m 57 sec
Reputation Power: 9

this is an example done by carlos

I think is better to run "EXECUTE PROCEDURE(@NAME,@EMAIL,@ACTIVATED)" statement with the FbCommand
Here is how i would write it :

Quote:

FbConnection fb = new FbConnection(myConnectionString);
Fb.Open();
FbTransaction ft = fb.BeginTransaction();

FbCommand fc = new FbCommand("EXECUTE PROCEDURE ADD_CLIENTE(@NAME,@EMAIL,@ACTIVATED)",fb,ft);
fc.CommandType = CommandType.StoredProcedure;

fc.Parameters.Add("@NAME",FbType.VarChar,255,"NAME").Direction =
ParameterDirection.Input;
fc.Parameters.Add("@EMAIL",FbType.VarChar,75,"EMAIL").Direction =
ParameterDirection.Input;
fc.Parameters.Add("@ACTIVATED",FbType.Char,1,"ACTIVATED").Direction =
ParameterDirection.Input;
fc.Parameters[0].Value = Server.HtmlEncode(txtName.Text);
fc.Parameters[1].Value = Server.HtmlEncode(txtName.Text);
fc.Parameters[2].Value = cboActivated.SelectedItem.Value;

fc.ExecuteNonQuery();

ft.Commit();
fb.Close();

And carlos(.net provider creator) example :
I'm making test with C# here is my test case:

FbTransaction myTransaction;
FbConnection myConnection = new FbConnection(connectionString);

myConnection.Open();

myTransaction = myConnection.BeginTransaction();

FbCommand myCommand = new FbCommand("EXECUTE PROCEDURE
GETVARCHARFIELD(?)", myConnection, myTransaction);

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.Add("@INT_FIELD", FbType.Integer).Direction =
ParameterDirection.Input;
myCommand.Parameters[0].Value = 1;

myCommand.Parameters.Add("@VARCHAR_FIELD", FbType.VarChar).Direction =
ParameterDirection.Output;

myCommand.ExecuteNonQuery();

Console.WriteLine(myCommand.Parameters[1].Value);

myTransaction.Commit();

myConnection.Close();

Console.ReadLine();

And this is my test SP:

CREATE PROCEDURE GETVARCHARFIELD (
ID INTEGER)
RETURNS (
VARCHAR_FIELD VARCHAR(100))
AS
begin
for select varchar_field from test_table_01 where int_field = :id
into :varchar_field
do
suspend;
end

--
Best regards

Carlos Guzma'n A'lvarez
Vigo-Spain

__________________
My home page: http://www.firebirdsql.org and work place :http://www.reea.net

Last edited by mariuz : June 12th, 2003 at 01:28 PM.

June 12th, 2003, 04:23 PM

fetcher

Contributing User

Join Date: Jun 2003

Location: Portugal

Posts: 106

Time spent in forums: 2 h 11 m 33 sec
Reputation Power: 6

Thanks a lot! It works now.

The more I learn about firebird the more I like it. And congratulations on the job with the FireBird .net Data Provider. It is simply great.

June 12th, 2003, 04:59 PM

mariuz

Bug Hunter

Join Date: May 2003

Location: Transylvania (Romania)

Posts: 274

Time spent in forums: 19 h 57 m 57 sec
Reputation Power: 9

here is the .net provider list where you can subscribe

Quote:

Originally posted by fetcher
Thanks a lot! It works now.

The more I learn about firebird the more I like it. And congratulations on the job with the FireBird .net Data Provider. It is simply great.

Good

If you need more help then here is the .net provider list
where you can subscribe (Carlos Alvarez is on the list and he knows more advanced things about provider - he wrote it)

http://lists.sourceforge.net/lists/...rd-net-provider

Last edited by mariuz : June 12th, 2003 at 05:01 PM.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: