DB2: Create a stored procedure using a sample application

Use a sample application to create a stored procedure

In this exampleMyproc (). This process uses five parameters: Account name, option, transfer amount, savings balance, and check balance. The following list identifies the purpose of each parameter:

Account name: The input parameter used to identify the account. Option: determines the input parameters of the operation. There are three options: 1: Check the balance. 2: transfer money from the savings account to the check account. 3: transfer money from the check account to the savings account. Transfer amount: enter a parameter for the amount to be transferred between the check account and the savings account. Savings balance: return the output parameters of the savings account balance. Check balance: return the output parameters of the check account balance.

Run the following code to build a stored procedure:

SQL_API_RC SQL_API_FNmyProc(char * szName, int * nCmd, int * nAmount, int * nSaving,int * nChecking){SQLHENVhenv;SQLHDBChdbc;SQLHSTMThstmt;SQLRETURNrc;intnRetSize; SQLCHAR  str1[]="select saving, checking from db2e.myaccount where name = ?";SQLCHAR  str2[]="update db2e.myaccount set saving=saving - ?,           checking=checking + ? where name=?";SQLCHAR  str3[]="update db2e.myaccount set saving=saving + ?,           checking=checking - ? where name=?";//****************************************************************//* Prepare connection and statement//****************************************************************rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);//checkerrorrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc);//checkerrorrc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_NTS);//checkerror    rc = SQLConnect(hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);//checkerrorrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);//checkerror    //****************************************************************//* Update account//****************************************************************if ( *nCmd == 2 || *nCmd == 3 ){if ( *nCmd == 2 ){ //Transfer from saving to checkingrc = SQLPrepare(hstmt, str2, SQL_NTS); //checkerror}if ( *nCmd == 3 ){ //Transfer from checking to savingrc = SQLPrepare(hstmt, str3, SQL_NTS); //checkerror}rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,(SQLPOINTER)nAmount,0,NULL ); //checkerrorrc = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,(SQLPOINTER)nAmount,0,NULL ); //checkerror rc = SQLBindParameter(hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,0,0,(SQLPOINTER)szName,0,NULL ); //checkerrorrc = SQLExecute(hstmt); //checkerror} //****************************************************************//* Retrieve account balance//****************************************************************rc = SQLPrepare(hstmt, str1, SQL_NTS); //checkerrorrc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,0,0,(SQLPOINTER)szName,0,NULL); //checkerrorrc = SQLExecute(hstmt); //checkerrorif ( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO ){while ( (rc = SQLFetch(hstmt) ) == SQL_SUCCESS ){rc = SQLGetData( hstmt,(SQLSMALLINT)1,SQL_C_LONG,nSaving,sizeof(int) ,&nRetSize ); //checkerrorrc = SQLGetData( hstmt,(SQLSMALLINT)2,SQL_C_LONG,nChecking,sizeof(int) ,&nRetSize ); //checkerror}}//****************************************************************//* Clean up//****************************************************************    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );     SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return (0);} 

On the Win32 platform, build the stored procedure into a dynamic link library (mydll. dll) and copy it/Sqllib/FunctionDirectory. Then, register the stored procedure.

1. Open the DB2 command window.
2. Run the following command to connect to the mysample database:

   DB2 CONNECT TO MYSAMPLE

3. Use the script named regscript. scr to register the stored procedure to configure options. Run the following code for this script:

   CREATE PROCEDURE db2e.MYPROC (IN szName CHAR(16),                               IN nCmd INTEGER,                               IN nAmount INTEGER,                               OUT nSaving INTEGER,                               OUT nChecking INTEGER )DYNAMIC RESULT SETS 1LANGUAGE C PARAMETER STYLE GENERAL NO DBINFOFENCEDMODIFIES SQL DATAPROGRAM TYPE SUBEXTERNAL NAME 'mydll!myProc'@

   To run the script, enter the following command:

   db2 -td@ -vf regscript.scr

Now configure the Stored Procedure db2e. myproc. Next, use the "mobile device management center" to create a reservation.

Related Concepts

Bytes ----------------------------------------------------------------------------------------------------------

Ask the stored procedure of DB2 C-embedded I wrote a C-embedded Stored Procedure sumuporg. SQC, pre-compiled and bound it to generate sumuporg. c sumuporg. BND. Compile sumuporg. c Under Microsoft Vc to generate sumuporg. dll and copy the DLL to D:/sqllib/function (My DB2 installation directory ).
The pre-compilation process is as follows:
Connect to smns_ins user db2admin using db2admin @
Prep D:/share/sumuporg. SQC bindfile using D:/share/sumuporg. Bnd @
Bind D:/share/sumuporg. Bnd @
Connect reset @

Then create the stored procedure:
Create procedure sumuporg (in org char (21), in table char (9), in date char (21 ))
External name 'sumuporg! Sumuporg'
Language C
Fenced
Parameter Style General @

Run the stored procedure in strore procedure builder. The error message is as follows:
Db2admin. sumuporg-the stored procedure is being called.
Db2admin. sumuporg-input parameter value:
Org = 04
Table = a2
Date = 2003/0/1/00/0/0/0/00
[IBM] [CLI driver] [DB2/NT] sql1106n has been loaded into the specified DLL "sumuporg" module, but the function "sumuporg" cannot be executed ".
Db2admin. sumuporg-change rollback.
Db2admin. sumuporg-the stored procedure is called.

The content of sumuporg. SQC is as follows:
# Include <stdio. h>
# Include <stdlib. h>
# Include <string. h>
# Include <conio. h>
# Include "SQL. H"
# Include "sqlenv. H"

Exec SQL include sqlca;

SQL _api_rc SQL _api_fn sumuporg (char * org, char * Table, char * Date)
{

Exec SQL begin declare section;
Char inorgcode [21];
Char intablecode [9];
Char indate [21];
Char rowcode [10];
Char columncode [10];
Char suborgcode [21];
Long lvalue;
Char svalue [31];
Long linecode;
Exec SQL end declare section;

/* Initialize */
Memset (inorgcode, 0, sizeof (inorgcode ));
Memset (intablecode, 0, sizeof (intablecode ));
Memset (indate, 0, sizeof (indate ));
Strcpy (inorgcode, org );
Strcpy (intablecode, table );
Strcpy (indate, date );

Exec SQL declare row cursor
Select fd_target_code from tb_tem_target
Where fd_tb_code =: intablecode and fd_target_kind = 'H ';

Exec SQL declare column cursor
Select fd_target_code from tb_tem_target
Where fd_tb_code =: intablecode and fd_target_kind = 'l'
And fd_sum = '1 ';

Exec SQL declare org cursor
Select fd_code from tb_org
Where fd_code like: inorgcode | '__';

Exec SQL open row;

While (1) {/* row indicator */

Memset (rowcode, 0, sizeof (rowcode ));
Exec SQL fetch row into: rowcode;
If (sqlca. sqlcode = 100 ){
Exec SQL close row;
Break;
}
Else if (sqlca. sqlcode! = 0 ){
Exec SQL close row;
Return (-1 );
}

Exec SQL open column;

While (1) {/* column indicator */

Memset (columncode, 0, sizeof (columncode ));
Exec SQL fetch column into: columncode;
If (sqlca. sqlcode = 100 ){
Exec SQL close column;
Break;
}
Else if (sqlca. sqlcode! = 0 ){
Exec SQL close column;
Exec SQL close row;
Return (-1 );
}

Lvalue = 0;

Exec SQL open org;

While (1) {/* institution */

Memset (suborgcode, 0, sizeof (suborgcode ));
Exec SQL fetch org into: suborgcode;
If (sqlca. sqlcode = 100 ){
Exec SQL close org;
Break;
}
Else if (sqlca. sqlcode! = 0 ){
Exec SQL close org;
Exec SQL close column;
Exec SQL close row;
Return (-1 );
}

Memset (svalue, 0, sizeof (svalue ));

Exec SQL select fd_value into: svalue from tb_datadic
Where fd_date =: indate and fd_orgcode =: suborgcode
And fd_targetcode =: columncode and fd_targetkindscode =: rowcode
And fd_tablecode =: intablecode and fd_moneytype = 1 and fd_flag = '0 ';
If (sqlca. sqlcode! = 0 & sqlca. sqlcode! = 100 ){
Exec SQL close org;
Exec SQL close column;
Exec SQL close row;
Return (-1 );
}

Lvalue + = atoi (svalue );

}/* End of institution */

/* If the value is not 0, insert */
If (lvalue! = 0 ){
Linecode = 0;
Exec SQL select fd_sortnum into: linecode from tb_tem_target
Where fd_tb_code =: intablecode and fd_target_code =: rowcode;

Memset (svalue, 0, sizeof (svalue ));
Sprintf (svalue, "% d", lvalue );

Exec SQL insert into tb_datadic_tmp
Values (: indate,: inorgcode,: columncode,: rowcode,: linecode,: intablecode,: svalue, 1, '2 ');
If (sqlca. sqlcode! = 0 ){
Exec SQL close column;
Exec SQL close row;
Return (-1 );
}
}/* End of IF (lvalue! = 0 )*/

}/* End of column indicator */

}/* End of row indicator */

Return (0 );

}

Could you tell me what the problem is?

------------------------------------------------------------------- 1.15 C Stored Procedure Parameter note
Create procedure pr_clear_task_ctrl (
In in_branch_code char (4 ),
In in_tradedate char (8 ),
In in_task_id char (2 ),
In in_sub_task_id char (4 ),
Out out_success_flag integer)

Dynamic result sets 0
Language C
Parameter Style General with nulls (if not, the SQL SP will not be able to call the stored procedure written in C, resulting in protective errors)
No dbinfo
Fenced
Modifies SQL data
External name 'pr _ clear_task_ctrl! Pr_clear_task_ctrl '@