/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dtformat.sqC
**
** SAMPLE: Load and import data format extensions
**
** This sample:
** 1. Creates two tables, all_date_time and zoned_dec.
** 2. Generates two raw data files: one for import, one for load.
** 3. Imports the contents of a DEL type file with date, time,
** timestamp, and keepblanks type modifiers into the
** all_date_time table.
** 4. Loads the contents of an ASC type file with zoned decimal
** modifier into the zoned_dec table.
** 5. Displays the contents of the tables.
** 6. Drops the tables.
**
** DB2 APIs USED:
** db2Load -- Load
** db2Import -- Import
**
** SQL STATEMENTS USED:
** INCLUDE
** PREPARE
** DECLARE
** DROP TABLE
** OPEN
** CLOSE
** COMMIT
** CONNECT
**
** STRUCTURES USED:
** sqlca
** sqlchar
** sqldcol
** sqlu_media_list
** db2LoadStruct
** db2LoadIn
** db2LoadOut
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <db2AuCfg.h>
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
(DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
#include <iomanip>
#include <iostream>
using namespace std;
#else
#include <iomanip.h>
#include <iostream.h>
#endif
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
double decimalA_field;
double decimalB_field;
char time_stamp[30];
char date_field[20];
char time_field[20];
char varchar_field[20];
char st[80];
EXEC SQL END DECLARE SECTION;
class DtFormat
{
public:
int ImportUserDefinedDateAndTimeFormat();
int LoadZonedDecimalFormat();
private:
int LoadData(char*, char*, char*);
int ImportData(char*, char*, char*);
};
int DtFormat::ImportUserDefinedDateAndTimeFormat()
{
char dataFile[] = "DATA.DEL";
char *modifier = "DATEFORMAT=\"mm/dd/yyyy\" \
TIMEFORMAT=\"h:m:s tt\"\
TIMESTAMPFORMAT=\"mm/dd/yyyy/h:m:s.uuuuuu\" \
KEEPBLANKS ";
char *statement = "INSERT INTO all_date_time \
(varcharField, dateField, timeField, timeStampField)";
int i;
FILE *fp;
char *delData[] = {"\"no blanks\",09/03/2001,10:34:22 AM, \
09/03/2001/10:34:22.124321\n",
"\"blanks \",12/28/1993,01:59:44 PM, \
12/28/1993/01:59:44.757390\n"};
cout << "-----------------------------------------------------------\n";
cout << "USE THE SQL STATEMENT:" << endl;
cout << " CREATE TABLE" << endl;
cout << " INSERT INTO" << endl;
cout << "AND USE THE DB2 APIs:" << endl;
cout << " db2Import -- IMPORT" << endl;
cout << "TO IMPORT USER-DEFINED DATE AND TIME FORMAT INTO A TABLE\n\n";
// Write data to the file
if((fp = fopen("DATA.DEL", "w")) == NULL) return 1;
for(i = 0; i < 2; i++)
{
fputs(delData[i], fp);
}
fclose(fp);
cout << " Created file 'DATA.DEL' for IMPORT\n" << endl;
cout << " CREATE TABLE all_date_time(" << endl;
cout << " varcharField VARCHAR(20)," << endl;
cout << " dateField DATE," << endl;
cout << " timeField TIME," << endl;
cout << " timestampField TIMESTAMP)\n" << endl;
// Create table all_date_time
EXEC SQL CREATE TABLE all_date_time (varcharField VARCHAR(20),
dateField DATE,
timeField TIME,
timestampField TIMESTAMP);
EMB_SQL_CHECK("CREATE TABLE");
cout << " Use the DB2 API db2Import to import the file 'DATA.DEL'" << endl;
cout << " into the table all_date_time\n" << endl;
// Import data file into table
ImportData(statement, modifier, dataFile);
// Display the contents of the table
cout << " SELECT * FROM all_date_time" << endl;
strcpy(st, "SELECT * FROM all_date_time");
EXEC SQL PREPARE s1 FROM :st;
EMB_SQL_CHECK("PREPARE");
EXEC SQL DECLARE c1 CURSOR FOR s1;
EXEC SQL OPEN c1;
EMB_SQL_CHECK("OPEN");
cout << " varcharField dateField timeField timeStampField" << endl;
cout << " ------------ ---------- ---------";
cout << " --------------------------" << endl;
do
{
EXEC SQL FETCH c1 INTO :varchar_field,
:date_field,
:time_field,
:time_stamp;
if (SQLCODE != 0) break;
cout << " " << varchar_field << " " << date_field
<< " " << time_field << " " << time_stamp << endl;
} while(1);
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("CLOSE");
EXEC SQL COMMIT;
EMB_SQL_CHECK("COMMIT");
// Drop table
cout << "\n DROP TABLE all_date_time\n" << endl;
EXEC SQL DROP TABLE all_date_time;
EMB_SQL_CHECK("DROP TABLE");
return 0;
} // DtFormat::ImportUserDefinedDateAndTimeFormat
int DtFormat::LoadZonedDecimalFormat()
{
char dataFile[] = "DATA.ASC";
char *modifier = "ZONEDDECIMAL reclen=11";
char *statement = "INSERT INTO zoned_dec (decimalA, decimalB)";
int i;
FILE *fp;
char *ascData[] = {"\x30\x31\x32\xF3\x4E\x30\xF1\xF2\xF3\xD4\x4E",
"\xF1\xF2\x33\xA4\x4E\xF5\xF4\xF3\xF2\xA1\x4E"};
cout << "-----------------------------------------------------------\n";
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " CREATE TABLE" << endl;
cout << " INSERT INTO" << endl;
cout << "AND THE DB2 API:" << endl;
cout << " db2Load" << endl;
cout << "TO LOAD ZONED DECIMAL FORMAT INTO A TABLE\n" << endl;
// Write data to the files
if((fp = fopen("DATA.ASC", "w+b")) == NULL) return 1;
for(i = 0; i < 2; i++)
{
fputs(ascData[i], fp);
}
fclose(fp);
cout << " Created file 'DATA.ASC' for LOAD\n" << endl;
cout << " CREATE TABLE zoned_dec(" << endl;
cout << " decimalA DECIMAL(4,0)," << endl;
cout << " decimalB DECIMAL(5,2)) \n" << endl;
// Create table zoned_dec
EXEC SQL CREATE TABLE zoned_dec (decimalA DECIMAL(4,0),
decimalB DECIMAL(5,2));
EMB_SQL_CHECK("CREATE TABLE");
cout << " Use the DB2 API db2Load to load the file 'DATA.ASC' into \n";
cout << " the table zoned_dec\n" << endl;
// Load table with data file
LoadData(statement, modifier, dataFile);
// Display the contents of the table
cout << " All blanks in DEL format for char";
cout << " and varchar columns are preserved\n" << endl;
cout << " SELECT * FROM zoned_dec" << endl;
strcpy(st, "SELECT * FROM zoned_dec");
EXEC SQL PREPARE s2 FROM :st;
EMB_SQL_CHECK("PREPARE");
EXEC SQL DECLARE c2 CURSOR FOR s2;
EXEC SQL OPEN c2;
EMB_SQL_CHECK("OPEN");
cout << " decimalA decimalB" << endl;
cout << " -------- --------" << endl;
do
{
EXEC SQL FETCH c2 INTO :decimalA_field,
:decimalB_field;
if(SQLCODE != 0) break;
cout.setf(ios::right, ios::floatfield);
cout << " "
<< setw(4) << setprecision(4) << decimalA_field
<< " "
<< setw(7) << setprecision(5) << decimalB_field
<< endl;
} while(1);
EXEC SQL CLOSE c2;
EMB_SQL_CHECK("CLOSE");
EXEC SQL COMMIT;
EMB_SQL_CHECK("COMMIT");
// Drop the table
cout << "\n DROP TABLE zoned_dec" << endl;
EXEC SQL DROP TABLE zoned_dec;
EMB_SQL_CHECK("DROP TABLE");
return 0;
} // DtFormat::LoadZonedDecimalFormat
// Import a file into a table using the sql statement,
// modifier and name of file to be loaded.
int DtFormat::ImportData(char *actionString, char *modifier, char *dataFile)
{
struct sqlchar *pAction = {0};
struct sqlchar *fileTMod = {0};
struct sqldcol dataDescriptor = {0};
char fileFormat[] = "DEL";
char localMsgFileName[128];
struct db2ImportIn inputInfo = {0};
struct db2ImportOut outputInfo = {0};
struct db2ImportStruct importParmStruct = {0};
// Initialize LOAD API structures and variables.
strcpy(localMsgFileName, "MSG.TXT");
dataDescriptor.dcolmeth = SQL_METH_D;
/* Setup db2ImportIn structure */
inputInfo.iRowcount = inputInfo.iRestartcount = 0;
inputInfo.iSkipcount = inputInfo.iWarningcount = 0;
inputInfo.iNoTimeout = inputInfo.iAccessLevel = 0;
inputInfo.piCommitcount = 0;
// Allocate the required space for the actionString and modifier
// and initialize the actionString and modifier variables
pAction = new sqlchar[strlen(actionString) + sizeof(struct sqlchar)];
pAction->length = strlen(actionString);
strncpy (pAction->data, actionString, strlen(actionString));
fileTMod = new sqlchar[strlen(modifier) + sizeof(struct sqlchar)];
fileTMod->length = strlen(modifier);
strncpy (fileTMod->data, modifier, strlen(modifier));
importParmStruct.piDataFileName = dataFile;
importParmStruct.piLobPathList = NULL;
importParmStruct.piDataDescriptor = &dataDescriptor;
importParmStruct.piActionString = pAction;
importParmStruct.piFileType = fileFormat;
importParmStruct.piFileTypeMod = fileTMod;
importParmStruct.piMsgFileName = localMsgFileName;
importParmStruct.iCallerAction = 0;
importParmStruct.piImportInfoIn = &inputInfo;
importParmStruct.poImportInfoOut = &outputInfo;
importParmStruct.piNullIndicators = NULL;
/* From V9.1 the structure db2ImportStruct will */
/* have a new member.This is piXmlPathList. */
importParmStruct.piXmlPathList = NULL;
// call the IMPORT API
db2Import (db2Version970,
&importParmStruct,
&sqlca);
DB2_API_CHECK("data -- import");
delete [] pAction;
delete [] fileTMod;
// display import info
cout << " Import info." << endl;
cout << " rows read : " << (int) outputInfo.oRowsRead << endl;
cout << " rows skipped : " << (int) outputInfo.oRowsSkipped << endl;
cout << " rows inserted : " << (int) outputInfo.oRowsInserted << endl;
cout << " rows updated : " << (int) outputInfo.oRowsUpdated << endl;
cout << " rows rejected : " << (int) outputInfo.oRowsRejected << endl;
cout << " rows committed: " << (int) outputInfo.oRowsCommitted << endl << endl;
return 0;
} // DtFormat::ImportData
// Load a file into a table using the sql statement,
// modifier and name of the file to be loaded.
int DtFormat::LoadData(char *actionString,
char *modifier,
char *dataFile)
{
int rc = 0;
struct sqlca sqlca = {0};
struct sqlu_media_list mediaList = {0};
struct sqldcol *dataDescriptor = {0};
struct sqlchar *pAction = {0};
char localMsgFileName[128];
struct sqlchar *fileTMod = {0};
struct sqldcoln *pdcolnEntry = {0};
struct sqlloctab *pdcolnptrEntry = {0};
struct sqlloctab *pdcolnptrEntry2 = {0};
db2int32 *pNullIndicators = NULL;
char *fileFormat = SQL_ASC;
int numColumns = 2;
struct db2LoadStruct paramStruct = {0};
struct db2LoadIn inputInfoStruct = {0};
struct db2LoadOut outputInfoStruct = {0};
// Initialize LOAD API structures and variables.
strcpy(localMsgFileName, "MSG.TXT");
// Setup sqlu_media_list struct containing the filename to be loaded
mediaList.media_type = SQLU_SERVER_LOCATION;
mediaList.sessions = 1;
mediaList.target.location = new sqlu_location_entry[mediaList.sessions];
strcpy (mediaList.target.location->location_entry, dataFile);
// Setup sqldcol structure
dataDescriptor = new sqldcol[(2*(sizeof(short))) +
(numColumns * sizeof(struct sqldcoln))];
dataDescriptor->dcolnum = numColumns;
pdcolnEntry = dataDescriptor->dcolname;
pdcolnEntry->dcolnptr =
new char[(dataDescriptor->dcolnum * sizeof(struct sqlloctab))];
dataDescriptor->dcolmeth = SQL_METH_L;
dataDescriptor->dcolname->dcolnlen = sizeof(struct sqllocpair)*numColumns;
pdcolnptrEntry = (struct sqlloctab *)pdcolnEntry[0].dcolnptr;
// Assign column beginning and ending points
pdcolnptrEntry->locpair[0].begin_loc = 1;
pdcolnptrEntry->locpair[0].end_loc = 4;
pdcolnptrEntry->locpair[1].begin_loc = 6;
pdcolnptrEntry->locpair[1].end_loc = 10;
// Allocate the required space for the actionString and modifier
// and initialize the actionString and modifier variables
pAction = new sqlchar [strlen(actionString) +
sizeof(struct sqlchar)];
pAction->length = strlen(actionString);
strncpy(pAction->data, actionString, strlen(actionString));
fileTMod = new sqlchar [strlen(modifier) +
sizeof(struct sqlchar)];
fileTMod->length = strlen(modifier);
strncpy (fileTMod->data, modifier, strlen(modifier));
// Setup the input information structure
inputInfoStruct.piUseTablespace = NULL;
inputInfoStruct.iSavecount = 0; // consistency points as
// infrequently as possible
inputInfoStruct.iRestartcount = 0; // start at row 1
inputInfoStruct.iRowcount = 0; // load all rows
inputInfoStruct.iWarningcount = 0; // don't stop for warnings
inputInfoStruct.iDataBufferSize = 0; // default data buffer size
inputInfoStruct.iSortBufferSize = 0; // def. warning buffer size
inputInfoStruct.iHoldQuiesce = 0; // don't hold the quiesce
inputInfoStruct.iRestartphase = ' '; // ignored anyway
inputInfoStruct.iStatsOpt = SQLU_STATS_NONE; // don't bother with them
inputInfoStruct.iIndexingMode = SQLU_INX_AUTOSELECT;// let load choose
// indexing mode
inputInfoStruct.iCpuParallelism = 0;
inputInfoStruct.iNonrecoverable = SQLU_NON_RECOVERABLE_LOAD;
inputInfoStruct.iAccessLevel = SQLU_ALLOW_NO_ACCESS;
inputInfoStruct.iLockWithForce = SQLU_NO_FORCE;
/* From V9.0 onwards, the structure member iCheckPending is */
/* deprecated and replaced with iSetIntegrityPending. Also the */
/* possible value to set this variable SQLU_CHECK_PENDING_CASCADE_DEFERRED */
/* has been replaced with SQLU_SI_PENDING_CASCADE_DEFERRED. */
inputInfoStruct.iSetIntegrityPending = SQLU_SI_PENDING_CASCADE_DEFERRED;
// Setup the parameter structure
paramStruct.piSourceList = &mediaList;
paramStruct.piLobPathList = NULL;
paramStruct.piDataDescriptor = dataDescriptor;
paramStruct.piActionString = pAction;
paramStruct.piFileType = fileFormat;
paramStruct.piFileTypeMod = fileTMod;
paramStruct.piLocalMsgFileName = localMsgFileName;
paramStruct.piTempFilesPath = NULL;
paramStruct.piVendorSortWorkPaths = NULL;
paramStruct.piCopyTargetList = NULL;
paramStruct.piNullIndicators = pNullIndicators;
paramStruct.piLoadInfoIn = &inputInfoStruct;
paramStruct.poLoadInfoOut = &outputInfoStruct;
paramStruct.piPartLoadInfoIn = NULL;
paramStruct.poPartLoadInfoOut = NULL;
paramStruct.iCallerAction = SQLU_INITIAL;
// load table
db2Load (db2Version970, // Database version number
¶mStruct, // In/out parameters
&sqlca); // SQLCA
DB2_API_CHECK("data -- load");
delete [] pAction;
delete [] fileTMod;
// display load info
cout << " Load info." << endl;
cout << " rows read : " << (db2int32) outputInfoStruct.oRowsRead
<< endl;
cout << " rows skipped : " << (db2int32) outputInfoStruct.oRowsSkipped
<< endl;
cout << " rows rejected : " << (db2int32) outputInfoStruct.oRowsRejected
<< endl;
cout << " rows committed: " << (db2int32) outputInfoStruct.oRowsCommitted
<< endl << endl;
return 0;
} // DtFormat::LoadData
int main(int argc, char *argv[])
{
int rc = 0;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
CmdLineArgs check;
DbEmb db;
DtFormat dtformat;
// Check the command line arguments
rc = check.CmdLineArgsCheck1(argc, argv, db);
if(rc != 0)
{
return rc;
}
cout << "\nHOW TO LOAD AND IMPORT DATA FORMAT EXTENSIONS." << endl;
// Connect to database
rc = db.Connect();
if(rc != 0)
{
return rc;
}
dtformat.ImportUserDefinedDateAndTimeFormat();
dtformat.LoadZonedDecimalFormat();
// Disconnect from the database
rc = db.Disconnect();
if(rc != 0)
{
return rc;
}
return 0;
} // main