/*******************************************************************************
**
** Source File Name = showda.c 1.4
**
** 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 :
** An input/output stored procedure sample.
** Showda accepts any number of arguments,
** and modifies the argument values.
**
** Pass a CLOB OUTPUT buffer as the LAST parameter
** and the information will be written to this
** buffer and can then be displayed by the client.
** See the sendda.c sample.
**
**
** 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 <sql.h>
#include <sqlda.h>
#include <sqlcli1.h>
#include "samputil.h" /* Header file for CLI sample code */
#ifndef max
#define max(A, B) ((A) > (B) ? (A) : (B))
#endif
#ifndef min
#define min(A, B) ((A) < (B) ? (A) : (B))
#endif
#define LOBLENGTH 23
struct lob_file {
unsigned long name_length ;
unsigned long data_length ;
unsigned long file_option ;
char name[255] ;
} ;
/* Global buufer to send info back to clients as an output CLOB */
void update_da (struct sqlda *sqldaPointer);
char returnbuf[1023] = "No Info found!\n" ;
char * prbuf = &returnbuf[0] ;
int SQL_API_FN showda( void * reserved1,
void * reserved2,
struct sqlda * inout_sqlda,
struct sqlca * ca
) {
/* Declare a local SQLCA */
struct sqlca sqlca;
/* Delare CLI Variables */
SQLINTEGER sqlvarIndex;
SQLINTEGER i;
SQL_STRUCTURE sqlvar2 *sqlvar2;
long *len_ptr;
short lob_ind ;
/*-----------------------------------------------------------------*/
/* Setup CLI required environment */
/*-----------------------------------------------------------------*/
/*
Skip the CLI setup since this example only inpsects and
modifes the SQLDA, and does not execute any SQL statements
*/
prbuf += sprintf(prbuf, "\nDumping SQLDA\n");
prbuf += sprintf(prbuf, "SQLN = %d SQLD = %d \n", inout_sqlda->sqln,
inout_sqlda->sqld);
/* Update the SQLDA information */
update_da(inout_sqlda);
sqlvarIndex = inout_sqlda->sqld-1; /* Last SQLVAR */
/* If the last parameter is a CLOB, use it to return SQLDA info */
if ( inout_sqlda->sqlvar[sqlvarIndex].sqltype == SQL_TYP_CLOB ||
inout_sqlda->sqlvar[sqlvarIndex].sqltype == SQL_TYP_NCLOB
) { /* copy global returnbuf to the last sqlvar entry */
strcpy( ( char * ) inout_sqlda->sqlvar[sqlvarIndex].sqldata,
returnbuf
) ;
/* Get pointer to length */
memcpy( (char *) &lob_ind,
(char *) inout_sqlda->sqlvar[sqlvarIndex].sqlind,
sizeof( short )
) ;
if ( lob_ind == 0 ) {
len_ptr = GETSQLDALENPTR( inout_sqlda, inout_sqlda->sqld-1 ) ;
/* Set length of OUTOUT CLOB */
if (len_ptr != NULL ) *len_ptr = prbuf - returnbuf;
/* len_ptr should never be NULL */
}
}
/*-----------------------------------------------------------------*/
/* Return to caller */
/* - Copy the SQLCA */
/* - Commit or Rollback the inserts. */
/*-----------------------------------------------------------------*/
/*
ext:
Skip the usual exit steps, since this sample just inspects and
modifies the SQLDA.
*/
return( SQLZ_DISCONNECT_PROC ) ;
}
/*<-- */
/*******************************************************************************
* PROCEDURE : print_var
* The following procedure prints a SQLDA SQLVAR variable in the buffer prbuf.
*
*******************************************************************************/
void print_var (char *ptr, int type, long length)
{
short idx, ind ; /* Array idx variables */
/* Variables for decoding packed decimal data */
short bottom, point ;
unsigned short top, precision, scale;
int i;
/*
** Determine the type of data, coerce or decode the data if necessary,
** then writes the data (or first 20 characters) in prbuf.
*/
switch ( type ) {
case SQL_TYP_INTEGER: /* long */
case SQL_TYP_NINTEGER: /* long with null indicator */
prbuf += sprintf(prbuf, "%ld", *(long *) ptr) ;
break ;
case SQL_TYP_SMALL: /* short */
case SQL_TYP_NSMALL: /* short with null indicator */
prbuf += sprintf(prbuf, "%d", *(short *) ptr ) ;
break ;
case SQL_TYP_DECIMAL: /* decimal */
case SQL_TYP_NDECIMAL: /* decimal with null indicator */
/* Determine the scale and precision */
/* Precsion is 1 byte of short, 3 byte of long length */
/* Scale is 2 byte of short, 4 byte of long length */
precision = ((char *)&(length))[2];
scale = ((char *)&(length))[3];
/*****************************************************************************/
/* Note: Precision can only be odd because internally only odd are stored. */
/* When and if it happens that an even precision can occur, the */
/* precision must be incremented by 1 in order for the proper */
/* calculation of "idx" and "point" to occur. */
/*****************************************************************************/
if ((precision %2) == 0) precision += 1;
/* Calculate the total number of bytes */
idx = ( precision + 2 ) / 2 ;
point = precision - scale ;
/* Determine the sign */
bottom = *(ptr + idx -1) & 0x000F ; /* sign */
if ( (bottom == 0x000D) || (bottom == 0x000B) ) {
prbuf += sprintf(prbuf, "-") ;
} else {
prbuf += sprintf(prbuf, " ") ;
}
/* Decode and print the decimal number */
for (ind=0; ind < idx; ind++) {
top = *(ptr + ind) & 0x00F0 ;
top = (top >> 4 ) ;
bottom = *(ptr + ind) & 0x000F ;
if ( point-- == 0 )
{ prbuf += sprintf(prbuf, ".");
}
prbuf += sprintf(prbuf, "%d", top ) ;
/*****************************************************************************/
/* Ignore bottom of last half byte because its the sign. */
/*****************************************************************************/
if ( ind < idx - 1 ) { /* sign half byte ? */
if ( point-- == 0 )
{ prbuf += sprintf(prbuf, ".");
}
prbuf += sprintf(prbuf, "%d", bottom ) ;
}
}
if ( scale == 0 )
{ prbuf += sprintf(prbuf, ".") ;
}
break ;
case SQL_TYP_FLOAT: /* double */
case SQL_TYP_NFLOAT: /* double with null indicator */
prbuf += sprintf(prbuf, "%e", *(double *) ptr) ;
break ;
case SQL_TYP_CLOB: /* Character LOB */
case SQL_TYP_NCLOB:
case SQL_TYP_CHAR: /* fixed length character string */
case SQL_TYP_NCHAR: /* fixed length character string with null
indicator */
prbuf += sprintf (prbuf, "%-20.20s", ptr);
break;
case SQL_TYP_DBCLOB: /* Double-Byte Character LOB */
case SQL_TYP_NDBCLOB:
case SQL_TYP_BLOB: /* Binary LOB */
case SQL_TYP_NBLOB:
for (i = 0; i<20 && i<length; i++)
{ prbuf += sprintf(prbuf, "%02X", (int)ptr[i]);
}
prbuf += sprintf (prbuf, " ");
break;
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
/* Print a maximum of the first 20 characters */
prbuf += sprintf (prbuf, "%-20.*s",
min( ((struct SQLCHAR *)ptr)->length, 20 ),
((struct SQLCHAR *)ptr)->data);
break;
case SQL_TYP_CSTR: /* null terminated varying length character string */
case SQL_TYP_NCSTR: /* null terminate varying length character
string with null indicator */
break ;
prbuf += sprintf (prbuf, "%-20.20s", ptr);
default:
/* Unknown data type */
prbuf += sprintf(prbuf, "%-20.20s", "UNKNOWN");
break ;
}
}
/*******************************************************************************
* PROCEDURE : update_var
* Updates the SQLDA SQLVAR variables.
*
*******************************************************************************/
void update_var (char *ptr, int type, long length)
{
short idx, ind ; /* Array idx variables */
/* Variables for decoding packed decimal data */
short bottom, point ;
unsigned short top, precision, scale;
int i;
char tc;
/*
** Determine the type of data, then update
*/
switch ( type ) {
case SQL_TYP_INTEGER: /* long */
case SQL_TYP_NINTEGER: /* long with null indicator */
*(long *) ptr = 99999;
break ;
case SQL_TYP_SMALL: /* short */
case SQL_TYP_NSMALL: /* short with null indicator */
*(short *) ptr = 999;
break ;
case SQL_TYP_DECIMAL: /* decimal */
case SQL_TYP_NDECIMAL: /* decimal with null indicator */
/* Determine the scale and precision */
/* Precsion is 1 byte of short, 3 byte of long length */
/* Scale is 2 byte of short, 4 byte of long length */
precision = ((char *)&(length))[2];
scale = ((char *)&(length))[3];
if ((precision %2) == 0) precision += 1;
/* Calculate the total number of bytes */
idx = ( precision + 2 ) / 2 ;
/* Reverse the sign */
bottom = (*(ptr + idx -1) & 0x000F); /* sign */
if ( (bottom == 0x000C) ) /* Positive, set negative */
{ *(ptr + idx -1) = (*(ptr + idx -1) & 0xFFF0) | 0x000D;
} else
{ *(ptr + idx -1) = (*(ptr + idx -1) & 0xFFF0) | 0x000C;
}
*( ptr + idx ) = '\0' ;
break ;
case SQL_TYP_FLOAT: /* double */
case SQL_TYP_NFLOAT: /* double with null indicator */
*(double *) ptr = 99.99;
break ;
case SQL_TYP_CLOB: /* Character LOB */
case SQL_TYP_NCLOB:
case SQL_TYP_CHAR: /* fixed length character string */
case SQL_TYP_NCHAR: /* fixed length character string with null
indicator */
/* Reverse String */
for (i=0;i<length/2;i++ ) {
tc = ptr[i];
ptr[i] = ptr[length-i-1];
ptr[length-i-1] = tc;
} /* endfor */
break;
case SQL_TYP_DBCLOB: /* Double-Byte Character LOB */
case SQL_TYP_NDBCLOB:
case SQL_TYP_BLOB: /* Binary LOB */
case SQL_TYP_NBLOB:
/* Reverse String */
for (i=0;i<length/2;i++ ) {
tc = ptr[i];
ptr[i] = ptr[length-i-1];
ptr[length-i] = tc;
} /* endfor */
break;
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
/* Reverse String */
for (i=0;i<length/2;i++ ) {
tc = ((struct SQLCHAR *)ptr)->data[i];
((struct SQLCHAR *)ptr)->data[i] =
((struct SQLCHAR *)ptr)->data[length-i-1];
((struct SQLCHAR *)ptr)->data[length-i-1] = tc;
} /* endfor */
break;
case SQL_TYP_CSTR: /* null terminated varying length character string */
case SQL_TYP_NCSTR: /* null terminate varying length character
string with null indicator */
break ;
sprintf (ptr,"%-*.*s", length, length, "!");
default:
/* Unknown data type */
break ;
}
}
/*******************************************************************************
* PROCEDURE : update_da
* Calls update_var to update the value of each SQLVAR entry.
*******************************************************************************/
void update_da (struct sqlda *sqldaPointer) {
short variableIndex, numBytes, sqlvarIndex, nullIndicator;
short sqltype;
short isnull;
short *indptr;
long *lenptr;
long length;
long width;
char * dec_length ;
struct lob {
long length;
char *data;
} *lobPointer;
/* Output the contents for all host variables */
for(sqlvarIndex=0;sqlvarIndex < sqldaPointer->sqld; sqlvarIndex++) {
sqltype = sqldaPointer->sqlvar[sqlvarIndex].sqltype;
indptr = sqldaPointer->sqlvar[sqlvarIndex].sqlind;
prbuf += sprintf(prbuf, "SQLVAR[%d] : Type = %ld ", sqlvarIndex, sqltype);
if ( (indptr != NULL ) && (*(indptr) < 0) )
isnull = 1;
else
isnull = 0;
switch (sqltype) {
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
case SQL_TYP_BLOB:
case SQL_TYP_NBLOB:
case SQL_TYP_DBCLOB:
case SQL_TYP_NDBCLOB:
/* LOB type, get lob length */
lenptr = (long *)GETSQLDALENPTR(sqldaPointer, sqlvarIndex);
if (lenptr == NULL)
length = ((struct lob *)
(sqldaPointer->sqlvar[sqlvarIndex].sqldata))->length;
else
length = *lenptr;
width = GETSQLDALONGLEN(sqldaPointer, sqlvarIndex);
if (isnull == 0)
{ prbuf += sprintf(prbuf, "Length = %ld Width=%ld\n", length, width);
}
break;
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
length = ((struct SQLCHAR *)
(sqldaPointer->sqlvar[sqlvarIndex].sqldata))->length;
width = sqldaPointer->sqlvar[sqlvarIndex].sqllen;
if (isnull == 0)
{ prbuf += sprintf(prbuf, "Length = %ld Width=%ld\n", length, width);
}
break;
case SQL_TYP_DECIMAL: /* decimal */
case SQL_TYP_NDECIMAL: /* decimal with null indicator */
/* Precision is 1 byte of sqllen */
/* Scale is 2 byte of sqllen */
if (isnull == 1) break; /* Null, no info */
prbuf += sprintf(prbuf, "Precision= %d Scale = %d\n",
((char *)&(sqldaPointer->sqlvar[sqlvarIndex].sqllen))[0],
((char *)&(sqldaPointer->sqlvar[sqlvarIndex].sqllen))[1]);
dec_length = (char *) &length ;
memcpy( dec_length,
( char * ) &( sqldaPointer->sqlvar[sqlvarIndex].sqllen ),
2
) ;
memcpy( ( dec_length + 2 ),
( char * ) &( sqldaPointer->sqlvar[sqlvarIndex].sqllen ),
2
) ;
break;
default:
length = (sqldaPointer->sqlvar[sqlvarIndex].sqllen);
if (isnull == 0)
{ prbuf += sprintf(prbuf, "Length = %ld\n", length);
}
break;
} /* endswitch */
/* If indptr is not NULL, and the indicator is < 0, the value is null */
if (isnull == 1)
{ prbuf += sprintf(prbuf, "\n Value = NULL");
}
else
{ prbuf += sprintf(prbuf, " Value = ");
print_var(sqldaPointer->sqlvar[sqlvarIndex].sqldata,
sqltype, length);
update_var(sqldaPointer->sqlvar[sqlvarIndex].sqldata,
sqltype, length);
}
prbuf += sprintf(prbuf, "\n") ;
} /* endfor */
}