Calling stored procedure with parameters - how?

Thread is closed for posting
2 posts, 0 answers
  1. Ray
    Ray avatar
    5 posts
    Member since:
    Mar 2011

    Posted 16 May 2011 Link to this post

    Greetings!

    I have spent a great deal of time trying to call a basic stored procedure in a Firebird database without success.  I cannot believe that it should be so difficult!  I have googled the web and have found some examples, but when I implement them, exceptions are generated.

    I have tried the following code:

    IObjectScope scope = dbContext.GetInternalScope();
    IQuery query = scope.GetSqlQuery(    "auto_inc_master_detail ?, ?, ?, ?", null,
                                         "INT inMasterCode, SMALLINT inReservationType, VARCHAR inWorkstation, SMALLINT inFetchFolioCode");
    IQueryResult resultSet = query.Execute(0, Reservation.MAST_IN_PROGRESS, Environment.MachineName, (Int16)1);

    When I execute it, I receive the following exception:

    SQLState=;Dynamic SQL Error
    SQL error code = -104
    Token unknown - line 1, column 38
    0

    My situation is simple: I have a stored procedure named auto_inc_master_detail that accepts four parameters and returns four values.  I would like to call this stored procedure from a project that uses Telerik ORM Domain Model, but direct SQL is okay.  At this point, anything will be accepted.

    Here is a typical call as I would use it using exact parameter names:

    select outRecordNo, outMasterCode, outItemNumber, outFolioCode
    from auto_inc_master_detail(0, 17, 'WORKSTATION', 1)

    Input parameter types: INTEGER, SMALLINT, VARCHAR, SMALLINT
    Output types: INTEGER, INTEGER, INTEGER, INTEGER

    Any working code example of this type would be greatly appreciated.

    -Ray.
  2.
  3. Ray
    Ray avatar
    5 posts
    Member since:
    Mar 2011

    Posted 16 May 2011 Link to this post

    Okay, the problem was with my code.  Surprise, surprise!  :)
    The following code works:

    IObjectScope scope = dbContext.GetInternalScope();
    IQuery query = scope.GetSqlQuery("auto_inc_master_detail ?, ?, ?, ?", null,
           "INTEGER inMasterCode, SMALLINT inReservationType, VARCHAR inWorkstation, SMALLINT inFetchFolioCode");
    IQueryResult resultSet = query.Execute(new Object[] {0, Reservation.MAST_IN_PROGRESS, Environment.MachineName, 1});
    int resultSetCount = resultSet.Count;
    foreach (Object[] resultLine in resultSet)
    {
       recordNo = Convert.ToInt32(resultLine[0]);
       masterCode = Convert.ToInt32(resultLine[1]);
       itemNumber = Convert.ToInt32(resultLine[2]);
       folioCode = Convert.ToInt32(resultLine[3]);
     
       success = true;
       break;
    }

    I'll leave this here for anyone else that happens to encounter the same issue.

    -Ray.
Back to Top