WOW! Thanks so much Helen. That is exactly what I needed :).

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

>
> At 08:22 PM 26/10/2005 +0000, you wrote:
> >Hello all,
> >I'm having a great deal of difficulty executing a stored procedure
> >from DSQL. When I execute it I get a message saying:
> >
> >"Unsuccessful execution caused by system error that does not

preclude

> >successful execution of subsequent statements
> >message length error (encountered 0, expected 104)"
>
> Primarily, it just means you used EXECUTE PROCEDURE to call a

selectable

> SP. Call your SP using SELECT (see below) and make sure your app

has a

> structure into which to fetch multiple output rows one at a time.
>
>
> >The stored procedure code is
> >
> >CREATE PROCEDURE LOGINUSER
> > (
> > USERNAME VARCHAR (50) CHARACTER SET NONE,
> > USERPASSWORD VARCHAR (50) CHARACTER SET NONE
> > )
> > RETURNS
> > (
> > RIGHTNAME VARCHAR (100) CHARACTER SET NONE
> > )
> > AS
> >
> > DECLARE VARIABLE UserId CHAR(37);
> > DECLARE VARIABLE GroupId CHAR(37);
> > DECLARE VARIABLE RightsId CHAR(37);
> > DECLARE VARIABLE TablePassword VARCHAR(50);
> >
> > BEGIN
> > SELECT UserId, UserPassword FROM Users
> > WHERE UserName=:UserName

INTO :UserId, :TablePassword;

> >
> > IF( :UserId IS NULL ) THEN
> > EXCEPTION EXC_NO_SUCH_USER;
> >
> > IF( :TablePassword != :UserPassword ) THEN
> > EXCEPTION EXC_INCORRECT_PASSWORD;
> >
> > FOR SELECT GroupId FROM GroupUsers WHERE

UserId=:UserId

> >INTO :GroupId DO
> > BEGIN
> > FOR SELECT RightsId FROM AccessControl
> > WHERE UserId=:GroupId INTO :RightsId DO
> > BEGIN
> > SELECT RightName FROM Rights
> > WHERE RightId=:RightsId INTO :RightName;
> > SUSPEND;
> > END
> > END
> > FOR SELECT RightsId FROM AccessControl
> > WHERE UserId=:UserId INTO :RightsId DO
> > BEGIN
> > SELECT RightName FROM Rights
> > WHERE RightId=:RightsId INTO :RightName;
> > SUSPEND;
> > END
> > END
> >
> >I am setting up the SQL statement in my code like this:
> >"sprintf(sqlQuery, "EXECUTE PROCEDURE LoginUser('%s', '%s')",

OLE2T

> >(UserName), OLE2T(Password));"
>
> A parameterised statement looks like this:
>
> EXECUTE PROCEDURE LoginUser(?, ?)
>
> or, in this case, because it's not an executable procedure but a

selectable

> one:
>
> SELECT RightName from LoginUser(?, ?)
>
> The database engine knows about SQL statement syntax and the API

knows

> about its request structure; but neither knows about your

application

> code's native conventions for passing values.
>
> In your code, either pass the two parameter values (in the right

order) to

> the place holders by filling the XSQLVAR structures that you have
> previously prepared; or (less desirable), construct the complete

statement

> in the client code as a string, using concatenation or whatever it

takes to

> compose a string that is only a valid SQL statement.
>
> If you are using a data access abstraction layer like IBPP, ODBC,

a .NET

> provider, etc., each will have its own way of surfacing the API

function

> structures to encapsulate the whole thing. You can consult the

relevant

> list for help with your particular choice of layer.
>
> If you are bravely doing this with the raw API, then you will need

the IB6

> API Guide - Google for "ApiGuide.pdf", it's in lots of

repositories; and

> you will find some examples in Firebird's examples subdirectory.
>
> ./heLen
>