# OTL 4.0, Example 471 (DB2, stored procedure with output parameters and a return code)

This example demonstrates a DB2 SQL/PL stored procedure with output parameters and a return code.. This example should work as is with DB2 8.2 or higher.

## Source Code

    #include <iostream>
    using namespace std;

    #include <stdio.h>
    #define OTL_DB2_CLI // Compile OTL 4.0/DB2-CLI
    #include <otlv4.h>

    otl_connect db; // connect object

    void stored_proc(void)
    // invoking stored procedure
    {
     otl_stream o(1, // buffer size should be equal to 1 in case of stored procedure call
                  "{ :rc<int,out> = call my_proc("
                  " :A<int,inout>, "
                  " :B<char[31],out>, "
                  " :C<char[31],in> "
                  ")}",
                     // stored procedure call
                  db // connect object
                 );

     o.set_commit(0); // set stream auto-commit off since
                      // the stream does not generate transaction

     o<<1<<"Test String1"; // assigning :A = 1, :C = "Test String1"

     int rc;
     int a;
     char b[31];

     o>>rc>>a>>b;
     cout<<"RC="<<rc<<", A="<<a<<", B="<<b<<endl;

    }

    int main()
    {
     otl_connect::otl_initialize(); // initialize DB2 CLI environment
     try{
      db.rlogon("scott/tiger@db2sql"); // connect to DB2
      otl_cursor::direct_exec
       (db,"DROP PROCEDURE my_proc",0); // drop procedure and ignore any errors
      otl_cursor::direct_exec
       (db,
        "create procedure my_proc "
         "(inout a integer, "
         " out b varchar(60), "
         " inout c varchar(60)) "
         "language SQL "
         "begin "
         "  set a = a + 1; "
         "  set b = c; "
         "  return 100; "
         "end "
       );  // create stored procedure

      stored_proc(); // invoking stored procedure
     }
     catch(otl_exception& p){ // intercept OTL exceptions
      cerr<<p.msg<<endl; // print out error message
      cerr<<p.code<<endl; // print out error code
      cerr<<p.var_info<<endl; // print out the variable that caused the error
      cerr<<p.sqlstate<<endl; // print out SQLSTATE message
      cerr<<p.stm_text<<endl; // print out SQL that caused the error
     }
     db.logoff(); // disconnect from the data source
     return 0;
    }

## Output

    RC=100, A=2, B=Test String1

Examples Contents Go Home

Copyright © 1996, 2007, Sergei Kuchin, email: skuchin@ispwest.com, skuchin@yahogmail
.

Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies.