Call the build script in the following manner:
ksh bldCCsrv <.sqC file without extension>
*************Stored Procedure starts here**************
#include#include #include #include #include #include #include extern C SQL_API_RC SQL_API_FN outtest( void *reserved1, void *reserved2, struct sqlda *inout_sqlda, struct sqlca *ca) { EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; short num_records; char stmt[512]; EXEC SQL END DECLARE SECTION; /* Declare Miscellaneous Variables */ int counter = 0; EXEC SQL WHENEVER SQLERROR GOTO error_exit; EXEC SQL WHENEVER SQLWARNING CONTINUE; EXEC SQL DECLARE c1 CURSOR FOR s1; /* Prepare a Statement to Obtain and Order all Salaries */ /*Table OUTSTRING: ID(integer) NAME(varchar(700)) 101 Mickey Mouse */ strcpy( stmt, SELECT name FROM OUTSTRING WHERE name='Mickey Mouse' ); EXEC SQL PREPARE s1 FROM :stmt; EXEC SQL OPEN c1; EXEC SQL FETCH c1 USING DESCRIPTOR :*inout_sqlda; EXEC SQL CLOSE c1; EXEC SQL COMMIT; memcpy( ca, &sqlca, sizeof( struct sqlca ) ); return(SQLZ_DISCONNECT_PROC); error_exit: /* An Error has occurred -- ROLLBACK and return to Calling Program */ EXEC SQL WHENEVER SQLERROR CONTINUE; memcpy( ca, &sqlca, sizeof( struct sqlca ) ); EXEC SQL ROLLBACK; return(SQLZ_DISCONNECT_PROC); } *************Stored Procedure ends here**************
Now change to the directory to which the stored procedure was copied, and set its permissions to at least 755 so the database (and anyone else) can execute it.
Registering your stored procedure
grant execute on packageThe executable will be the name of your .sqC file (without the extension).to public
create procedure outtest(OUT out_string VARCHAR(700)) EXTERNAL NAME 'drprocs!outtest' FENCED LANGUAGE C PARAMETER STYLE DB2DARI NULL CALLIn this example:
*************DBTools code starts here**************
/* File: db2_Shared.cpp
**
** First attempt at connecting to database
*/
#include#include #include #include void errorHandler (const RWDBStatus& aStatus) { // Print out the error. cout << Error code: << (int) aStatus.errorCode() << endl << Error message << aStatus.message() << endl << Is terminal: << (aStatus.isTerminal() ? Yes : No) << endl << Vendor error 1: << aStatus.vendorError1() << endl << Vendor error 2: << aStatus.vendorError2() << endl << Vendor message 1: << aStatus.vendorMessage1() << endl << Vendor message 2: << aStatus.vendorMessage2() << endl; } int main() { RWDBDatabase::connect(FALSE); RWDBManager::setErrorHandler(errorHandler); RWDBDatabase dataBase = RWDBManager::database(db27d.dll,DatabaseName,User,Password,); ofstream outsql(outsql.txt); RWDBTracer& tracer = dataBase.tracer(); tracer.setOn(RWDBTracer::SQL); tracer.stream(cout); // Check to see if connection was made to database if(dataBase.isValid()) { cout << Connected to database << endl; } else { cout << dataBase.status().message() << endl; } RWDBConnection conn = dataBase.connection(); RWDBStoredProc proc; { proc = dataBase.storedProc(OUTTEST,conn); } RWCString output; proc << &output; RWDBResult res = proc.execute(conn); proc.fetchReturnParams(); RWDBTable table = res.table(); cout << Out is << output << . << endl; cout << String length is << output.length() << endl; return 0; } *************DBTools code ends here**************
Call the build script in the following manner:
ksh bldCC <.sqC file without extension>
*************DB2 Native test case starts here***********
#include#include #include #include #include #include #include util.h #define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1; int main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; char database[9]; char userid[9]; char passwd[19]; /* Declare a Local Variable for Holding the Procedure's Name */ char procname[51] = OUTTEST; /* Declare Local Variables for Holding Returned Data */ char string[701]; EXEC SQL END DECLARE SECTION; /* Declare the output SQLDA */ struct sqlda *inout_sqlda = (struct sqlda *) malloc(SQLDASIZE(1)); /* Declare the SQLCA */ struct sqlca sqlca; if (argc != 4) { printf ( USAGE: nativeDB2 remote_database userid passwd ); return 1; } strcpy (database, argv[1]); strcpy (userid, argv[2]); strcpy (passwd, argv[3]); /* Connect to Remote Database */ printf(CONNECT TO Remote Database. ); EXEC SQL CONNECT TO :database USER :userid USING :passwd; CHECKERR (CONNECT TO SUPPORT); /******************************************************** * Call the Remote Procedure via CALL with Host Variables * ********************************************************/ printf(Use CALL with Host Variable to invoke the Server Procedure named outtest ); EXEC SQL CALL :procname (:string); CHECKERR (CALL WITH HOST VARIABLES); printf(Server Procedure Complete. ); /* Print Name Returned in The Host Variables */ printf(name = %s , string ); /* Disconnect from Remote Database */ EXEC SQL CONNECT RESET; CHECKERR (CONNECT RESET); return 0; } *************DB2 Native test case ends here***********