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