On 4/19/05, Tim Gahnström <tim.gahnstrom@...> wrote:
>
>
> I think I might have misunderstood something fundamental in the handling of parameters to Stored procedures because I get some strange errors and cant seem to get ahold of what is causing them.
>
> I have a simple VB.NET program that calls a SP in firebird 1.5
>
> It looks like this:
> ------------------
> Private sConnectionString = "ServerType=0;" + "User=SYSDBA; " + " Password=masterkey;" + "Dialect=3;" + "Database=C:\download\KWDATA.FDB"
>
> Dim con As New FirebirdSql.Data.Firebird.FbConnection(sConnectionString)
> con.Open()
>
> Dim fc As FbCommand = New FbCommand("SP_GET_MOVIES_ALL(?)", con)
>

Have you tried

Dim fc As FbCommand = New FbCommand("SELECT * FROM SP_GET_MOVIES_ALL(?)", con)

> fc.CommandType() = CommandType.StoredProcedure
>
> fc.Parameters.Add("ISTART", 0).Direction = ParameterDirection.Input
>
> Dim fdr As FbDataReader = fc.ExecuteReader(CommandBehavior.Default)
> ----------------
>
> This I think should call SP_GET_MOVIES_ALL with the Parameter ISTART set to 0. But it doesn't, instead I get the following error:
> -------
> An unhandled exception of type 'FirebirdSql.Data.Firebird.FbException' occurred in firebirdsql.data.firebird.dll
> Additional information: Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 1, char 35
> (
> -----------
>
> The line it crashes on is the last line where I execute the Reader.
>
> It works very well if I don't include a parameter and writer 0 instead of the "?" like this:
> New FirebirdSql.Data.Firebird.FbCommand("SP_GET_MOVIES_ALL(0)", con)
>
> I have also tried a former suggestion and written my code with EXECUTE PROCEDURE instead like this:
>
> ....FbCommand("EXECUTE PROCEDURE SP_GET_MOVIES_ALL(?)", con)
>
> That works somewhat (it doesn't crash) but instead fdr.Read() always returns FALSE. It is the same if I use CommandType.Text or CommandType.StoredProcedure.
>
> I am sorry for the rather long mail but I have been mopping around with this for quite some time and I figured it is best to include whatever I have tried. I am pretty sure that I have even had it working from time to time. I can just not figure out what it is I have changed. But to me it seems like I have missed something simple and fundamental.
>
> Thanks a lot in advance
>
> The stored procedure itself follows below, it is copied from the DDL tab in IBExpert.
> -----------------
> SET TERM ^ ;
>
> CREATE PROCEDURE SP_GET_MOVIES_ALL (
> ISTART INTEGER)
> RETURNS (
> NAME_OUT VARCHAR(50),
> DESCRIPTION_OUT VARCHAR(1000))
> AS
> begin
> for
> select NAME, DESCRIPTION
> from T_MOVIES
> into :NAME_OUT, :DESCRIPTION_OUT
> do suspend;
> end
> ^
>
> SET TERM ; ^
>
> GRANT SELECT ON T_MOVIES TO PROCEDURE SP_GET_MOVIES_ALL;
>
> GRANT EXECUTE ON PROCEDURE SP_GET_MOVIES_ALL TO SYSDBA;
>
>
> Yahoo! Groups Links
>
>
>
>
>


Regards, Stefan