/*******************************************************************************
**
** Source File Name = lookres.c 1.3
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Search a resume CLOB using a CLOB Locator, print employees interests.
** Only the result is sent from the server to the application.
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "samputil.h" /* Header file for CLI sample code */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define MAX_STMT_LEN 255
#define ROWSET_SIZE 10
/* Field descriptor structure */
typedef struct {
SQLCHAR s[255] ;
SQLINTEGER ind ;
SQLUSMALLINT num ;
SQLSMALLINT type ;
SQLINTEGER length ;
} fld_desc ;
/*
Global Variables for user id and password.
To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
/* main */
int main( int argc, char * argv[] ) {
SQLHANDLE henv, hdbc, hstmt, lhstmt ;
SQLRETURN rc ;
SQLCHAR * stmt1 = ( SQLCHAR * )
"SELECT employee.empno, firstnme || lastname as name "
"FROM employee, emp_resume "
"WHERE employee.empno = emp_resume.empno "
"AND resume_format = 'ascii'" ;
/*--> SQLL1X22.SCRIPT */
SQLCHAR * stmt2 = ( SQLCHAR * ) "SELECT resume FROM emp_resume "
"WHERE empno = ? AND resume_format = 'ascii'" ;
/*<-- */
SQLCHAR * stmt3 = ( SQLCHAR * ) "FREE LOCATOR ?" ;
/* Field descriptor data */
fld_desc emp_name, emp_no ;
SQLINTEGER ClobLoc1 ; /* A LOB locator used for resume */
SQLINTEGER pcbValue ;
SQLINTEGER SLength ;
SQLUINTEGER Pos1 ;
SQLINTEGER OutLength, Ind ;
SQLCHAR * buffer ;
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Before making any API calls for 68K environment,
need to initialize the Library Manager */
InitLibraryManager( 0, kCurrentZone, kNormalMemory ) ;
atexit( CleanupLibraryManager ) ;
#endif
printf( "\nSet the value of UDF Shared Memory Set Size (udf_mem_sz)\n" ) ;
printf( " at least 2 pages larger than the size of the input\n" ) ;
printf( " arguments and the result of the external function.\n\n" ) ;
printf( " (i.e. db2 UPDATE DBM CFG USING udf_mem_sz 1024 )\n\n") ;
printf("The server must then be stopped and re-started for the\n") ;
printf(" change to take effect.\n\n") ;
/* macro to initalize server, uid and pwd */
INIT_UID_PWD ;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect */
rc = DBconnect( henv, &hdbc ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Display list of employee's with resumes on file */
rc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
emp_no.num = 1 ;
emp_no.type = SQL_C_CHAR ;
emp_no.length = 7 ;
rc = SQLBindCol( hstmt,
emp_no.num,
emp_no.type,
emp_no.s,
emp_no.length,
&emp_no.ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
emp_name.num = 2 ;
emp_name.type = SQL_C_CHAR ;
emp_name.length = 31 ;
rc = SQLBindCol( hstmt,
emp_name.num,
emp_name.type,
emp_name.s,
emp_name.length,
&emp_name.ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "\nEmpno Name \n" ) ;
printf( "------- ---------------------\n" ) ;
while ( SQLFetch( hstmt ) != SQL_NO_DATA )
printf( "%-6s %-30s \n", emp_no.s, emp_name.s ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/*--> */
/* Get CLOB locator to selected Resume */
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_OUTPUT,
emp_no.type,
SQL_CHAR,
emp_no.length,
0,
emp_no.s,
emp_no.length,
&emp_no.ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "\n>Enter an employee number:\n" ) ;
gets( ( char * ) emp_no.s ) ;
rc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt,
1,
SQL_C_CLOB_LOCATOR,
&ClobLoc1,
0,
&pcbValue
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFetch( hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/*
Search CLOB locator to find "Interests"
Get substring of resume ( from position of interests to end )
*/
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &lhstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Get total length */
rc = SQLGetLength( lhstmt,
SQL_C_CLOB_LOCATOR,
ClobLoc1,
&SLength,
&Ind ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
/* Get Starting postion */
rc = SQLGetPosition( lhstmt,
SQL_C_CLOB_LOCATOR,
ClobLoc1,
0,
( SQLCHAR * ) "Interests",
9,
1,
&Pos1,
&Ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
rc = SQLFreeStmt( lhstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
buffer = ( SQLCHAR * ) malloc( SLength - Pos1 + 1 ) ;
/* Get just the "Interests" section of the Resume CLOB */
/* ( From Pos1 to end of CLOB ) */
rc = SQLGetSubString( lhstmt,
SQL_C_CLOB_LOCATOR,
ClobLoc1,
Pos1,
SLength - Pos1,
SQL_C_CHAR,
buffer,
SLength - Pos1 + 1,
&OutLength,
&Ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
/* Print Interest section of Employee's resume */
printf( "\nEmployee #: %s\n %s\n", emp_no.s, buffer ) ;
/*<-- */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* FREE LOCATOR */
rc = SQLSetParam( hstmt,
1,
SQL_C_CLOB_LOCATOR,
SQL_CLOB_LOCATOR,
0,
0,
&ClobLoc1,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLExecDirect( hstmt, stmt3, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_STMT, lhstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
/* COMMIT, free resources and exit */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
} /* end main */