#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Portugal
    Posts
    106
    Rep Power
    12

    Question 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
  2. #2
  3. Bug Hunter
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Transylvania (Romania)
    Posts
    309
    Rep Power
    24

    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 :
    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
    Last edited by mariuz; June 12th, 2003 at 02:28 PM.
    My home page: http://www.firebirdsql.org and work place :http://www.reea.net
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Portugal
    Posts
    106
    Rep Power
    12

    Talking


    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.
  6. #4
  7. Bug Hunter
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Transylvania (Romania)
    Posts
    309
    Rep Power
    24

    Talking here is the .net provider list where you can subscribe


    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/l...d-net-provider
    Last edited by mariuz; June 12th, 2003 at 06:01 PM.
    My home page: http://www.firebirdsql.org and work place :http://www.reea.net

IMN logo majestic logo threadwatch logo seochat tools logo