/*******************************************************************************
**
** Source File Name = showpic2.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 :
** - Displays all employees with pictures, and prompts the user to
** select a picture (BLOB) from the emp_photo table
** - Uses piecewise ouput (SQLGetData) to retrieve the BLOB
** - xwud is used to display the xwd format (unix)
** - iconedit is used to display the bitmap format (OS/2)
** (edit this file to use a different viewer).
**
** 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
/*
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 ;
SQLRETURN rc ;
SQLCHAR * stmt1 = ( SQLCHAR * )
"select employee.empno, firstnme || lastname as name "
"from employee, emp_photo "
"where employee.empno = emp_photo.empno and photo_format = ?" ;
SQLCHAR * stmt2 = ( SQLCHAR * )
"SELECT picture FROM emp_photo "
"WHERE empno = ? AND photo_format = ?" ;
SQLCHAR Photo_Format[11] = {""};
SQLCHAR Desc[61];
struct {
SQLINTEGER ind;
SQLCHAR s[30];
} Name;
struct {
SQLINTEGER ind;
SQLCHAR s[7];
} Empno;
SQLSMALLINT FNLength = 13;
SQLCHAR FName[13];
char buffer[256];
SQLCHAR Fbuffer[BUFSIZ];
SQLINTEGER Buf_Ind;
FILE *PicFile;
size_t n;
size_t Fsize;
char showpic[255] = "N";
/* 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
/* 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 ) ;
/* Get picture File Format */
while (1) {
printf("Which Picture Format [xwd (XWindows), bitmap]?\n");
gets((char *)Photo_Format);
if (strcmp((char *)Photo_Format, "xwd") == 0 ||
strcmp((char *)Photo_Format, "bitmap") == 0) break;
printf("Invalid Format!\n");
}
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
11,
0,
Photo_Format,
0,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLExecDirect(hstmt, stmt1, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, Empno.s, 7, &Empno.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, Name.s, 30, &Name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Empno Name \n");
printf("------- ---------------------\n");
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf("%-6s %-30s \n", Empno.s, Name.s);
}
if ( rc != SQL_NO_DATA_FOUND )
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 ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
7, 0, Empno.s, 7, &Empno.ind);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
11, 0, Photo_Format, 11, NULL);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Get Employee Number */
printf("Select a Employee Number from the list above\n");
gets((char *)Empno.s);
rc = SQLExecDirect(hstmt, stmt2, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFetch(hstmt);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
switch (Photo_Format[0]) {
case 'x':
sprintf((char *)FName, "P%s.xwd", Empno.s, Photo_Format);
/* Since the system call isn't supported on Windows,
just create the file, don't try to display iamge */
#ifndef DB2WIN
sprintf(buffer, "xwud -in %s &", FName);
printf("xwud will be used to display image\n");
#endif
break;
case 'b':
sprintf((char *)FName, "P%s.bmp", Empno.s, Photo_Format);
#ifndef DB2WIN
sprintf(buffer, "iconedit %s", FName);
printf("iconedit will be used to display image\n");
#endif
break;
default :
printf("Unknown Format, will attempt to create file \n", FName);
}
PicFile = fopen((char *)FName, "w+b");
if (PicFile == NULL)
{ printf(">---- ERROR Opening File -------");
return( terminate( hdbc, SQL_ERROR ) ) ;
}
/* Get BUFSIZ bytes at a time, Buf_Ind indcates number of Bytes LEFT */
rc = SQLGetData(hstmt, 1, SQL_C_BINARY, (SQLPOINTER) Fbuffer,
BUFSIZ, &Buf_Ind);
Fsize = Buf_Ind;
while( rc == SQL_SUCCESS_WITH_INFO || rc == SQL_SUCCESS )
{ if (Buf_Ind > BUFSIZ) /* Full Buffer */
n = BUFSIZ;
else
n = Buf_Ind; /* Partial Buffer on last GetData */
fwrite(Fbuffer, sizeof(char), n, PicFile);
rc = SQLGetData(hstmt, 1, SQL_C_BINARY, (SQLPOINTER) Fbuffer,
BUFSIZ, &Buf_Ind);
}
if ( rc != SQL_NO_DATA )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
fflush(PicFile);
fclose(PicFile);
printf("%s (%u bytes) has been sucessfully created,\n", FName, Fsize);
printf("do you want to display it now? (Y/N)\n", FName) ;
gets( (char *) showpic ) ;
#ifndef DB2WIN
/* execute local display tool with the file just created */
if ( (showpic[0] == 'Y') || (showpic[0] == 'y') )
system(buffer);
#endif
/* free statement resources */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, 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 */