Question For Todd Niec - Need help on various functions that will read and transfer files to an SQL database

The following code is complete, there is no single question to post.  As stated in the question title, this code covers a range of questions I had regarding processing a directory and all of its files.  Another concern was how to make the SQL API calls so that I could transfer the data to an SQL Server database.

/*
10/20/99

This program will establish an SQL connection and process the specified
directory by scrolling through each file to extract the file's contents.
As each line of the file is extracted, the data is stored in variables.
Those variables are then passed to an SQL stored procedure which inserts
the data into the database.

Six routines including main(), are used to facilitate the above procedure.
These routines are:

Check_ODBC_Errors()
CountFields()
StrTok()
ProcessFile()
ProcessDirectory()
main()

The flow of the program begins by establishing an SQL connection in main().
The Check_ODBC_Errors()function ensures an SQL connection is established.
CountFields() verifies that each line of a file contains the proper number of
fields.  Once the number of fields are verified, StrTok() extracts each
field of the line and places the value into a variable to await the call
which dumps the data to the database.  Additional checks on each field format,
length and type are not included in this program.

This program was developed under the assumption that valid files are placed
into the appropriate directory for processing.  Although errors in file content
are checked, this program will process any file with a 'dat' extension.    
Therefore, no error reporting is done for files which should not be in the
specified directory.
*/


#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string>
#include <iostream>
#include <fstream>
#include <conio.h>



using namespace std ;


/**********************   ODBC Error Check  ******************************
*
* desc    : This function is called after each request for an SQL handle
*           within main().  If the request fails, this function provides the
*           error information of the SQL environment as stated above.
* input   : The handle type to be checked and the actual handle value.
*           The calling function also passes to this function the return code
*           which the calling function received for the handle type.
* output  : Prints to the screen the SQL State, Native Error and a
*           diagnostic message text string if available
* returns : Returns a bool to the caller if necessary, but the return value
*           is not used in this program since this function also provides
*           various error info printed to the screen.
*/


BOOL CHECK_ODBC_ERRORS(SQLSMALLINT HandType,  SQLHANDLE HandValue, RETCODE rc)
{
// Helper function for debugging those cryptic ODBC errors.
SQLSMALLINT RecNum = 1; // then 2, then 3....
SQLCHAR State[5];
SQLINTEGER NativeError;
SQLSMALLINT MsgLen;
const SQLINTEGER ErrorMsgMax = 1024;
SQLCHAR MsgTxt[ErrorMsgMax];


if ( SQL_SUCCEEDED(rc) )
return FALSE;   // Skip if everything is ok.

if(HandValue == SQL_INVALID_HANDLE){
printf("SQLAllocHandle() failed to obtain a handle!\n");
return FALSE;
}



printf( "******************** ODBC Errors ********************\n" );
printf( "Return code = %d.\n", rc );

for ( ; ; )
{

rc = SQLGetDiagRec(HandType, HandValue,RecNum, State, &NativeError,
   MsgTxt, ErrorMsgMax, &MsgLen);
if ( ( rc != SQL_SUCCESS ) && ( rc != SQL_SUCCESS_WITH_INFO) ) break;

printf("MsgTxt[%02d] SQLState '%s'\n",RecNum, State);
printf("MsgTxt[%02d] NativeError %lu\n",RecNum, NativeError);
printf("MsgTxt[%02d] Message '%s'\n", RecNum, MsgLen);
RecNum++;
}

if (RecNum == 1)
{
printf("First call to SQLGetDiagRec failed, unidentified RETCODE = %d\n",rc);

if ( rc == SQL_NO_DATA_FOUND )
printf("First call to SQLGetDiagRec failed with RETCODE");
printf(" = SQL_NO_DATA_FOUND, See SQLError documentation\n");
printf("SQLState - '%s'\n", State);

if ( rc == SQL_ERROR )
printf("First call to SQLGetDiagRec failed with RETCODE");
printf(" = SQL_ERROR, See SQLError documentation\n");

if ( rc == SQL_INVALID_HANDLE )
printf("First call to SQLGetDiagRec failed with RETCODE");
printf(" = SQL_INVALID_HANDLE, See SQLError documentation\n");
}

printf( "******************** ODBC Errors ********************\n" );

return TRUE;
}  // End of BOOL CHECK_ODBC_ERRORS()



/***************************** StrTok  ****************************
*
* desc    : This procedure is called from the ProcessFile() function.  It's
*           purpose is to return each field of data within the line it
*           receives.
* input   : Accepts two const strings and an integer.  The 1st string is the
*           entire line in the file to be tokenized, and the 2nd is the
*           delimiter(s) which mark the separation of each field in the file.
*           The int is used to control which token is return and where the
*           following call to StrTok() should proceed from.
* output  : -
* returns : Returns the extracted field of data from the file.  Regarding
*           '&StartPos', after receiving the initial starting point of '0'
*           from the caller, StrTok() will set the value of 'StartPos' as
*           needed to get the next token and returns 0 when last element is
* obtained.
*/

string StrTok(const string &Str, const string &Dlm, int &StartPos)
   {

StartPos  = Str.find_first_not_of(' ',StartPos); // Skip initial spaces.

int       BgnPos = StartPos;
int       NxtPos = Str.find_first_of(Dlm,StartPos);
const int StrLen = Str.length();

if (NxtPos == basic_string<char>::npos) // if no delimiter was found, then
NxtPos = StrLen; // Set token end position to 1 past end of string.


StartPos = NxtPos + 1;

if (StartPos >= StrLen) // If token is at end of ths tring.
StartPos = 0; // Indicate this is the last token
return Str.substr(BgnPos,NxtPos - BgnPos); // Return token found.
}



/***************************** CountFields  ****************************
*
* desc    : This procedure is called by ProcessFile() and uses StrTok() to
*           check a line of the file for the correct number of fields.
*           Unlike ProcessFile(), CheckFields() does not use the return value
*           of StrTok(), but rather uses StrTok() to traverse the line.  Thus
*           proving the number of fields within the line.
* input   : Receives a string which is one line of the file.
* output  : -
* returns : Returns the number of fields found.
*/

int CountFields(const string &StrLine)
{
string DlmReg = " ";
string LastDlm = "\n";
string Dlm = DlmReg;
int StartPos = 0; // starting point to check non-empty lines
int FieldCount = 0;


while(TRUE){

if(FieldCount == 7)
Dlm = LastDlm;

StrTok(StrLine,Dlm,StartPos);

FieldCount++;
if((StartPos == 0) || (FieldCount > 8))   // end the loop
return FieldCount;

}

return FieldCount;
}


/**************************   ProcessFile    **************************
*
* desc    : This procedure is called by ProcessDirectory() and uses StrTok()
*           to extract each field in each line of the file it receives.
*           The values returned by StrTok() are used as parameters for
*           SQLExecDirect() which in turn passes the data to the SQL data
*           source as a stored procedure.
* input   : Receives the HDBC environment handle obtained within main(), and
*           a string which is the path of a file.
* output  : This procedure will currently send error messages to the screen
*           if a file cannot be read or if the call to SQLExecDirect() fails.
*           In the future, these errors will be printed to a log only.
    * returns : -
*/

void ProcessFile(HSTMT hstmt, string FileName)
{

RETCODE rc;
int StartPos = 0; // starting point to check non-empty lines
int lineCheck = 0; // starting point to check for empty line
int lineResult;
int line = 0; // used to report error location
int FieldCount;
char szSQL[1024];
string StrLine;
string Dlm = " \t";


fstream LoadFile;

LoadFile.open(FileName.c_str(), ios::in);

if(LoadFile.fail()){
printf("Error reading file %s\n", FileName);
return;
}


while (!LoadFile.eof()){


getline(LoadFile, StrLine, LoadFile.widen('\n'));

lineResult = StrLine.find_first_not_of(' ', lineCheck);
if(lineResult == -1)
return;

FieldCount = CountFields(StrLine);

if(FieldCount != 8)
continue;

StartPos = 0; // reset StartPos parameter

string Field1 = StrTok(StrLine,Dlm,StartPos);
string Field2 = StrTok(StrLine,Dlm,StartPos);
string Field3 = StrTok(StrLine,Dlm,StartPos);
string Field4 = StrTok(StrLine,Dlm,StartPos);
string Field5 = StrTok(StrLine,Dlm,StartPos);
string Field6 = StrTok(StrLine,Dlm,StartPos);
Field5 =  Field5 + " " + Field6;   //combines the date & time
string Field7 = StrTok(StrLine,Dlm,StartPos);
string Field8 = StrTok(StrLine,"\n",StartPos);

sprintf( szSQL,
"execute LoadData '%s', '%s', '%s', '%s', '%s','%s', '%s'",
Field1.c_str(),   Field2.c_str(), Field3.c_str(), Field4.c_str(),
Field5.c_str(),   Field7.c_str(), Field8.c_str());

rc = SQLExecDirect( hstmt, (SQLCHAR*) szSQL, SQL_NTS );
CHECK_ODBC_ERRORS(SQL_HANDLE_STMT, hstmt, rc);

                SQLFreeStmt(hstmt, SQL_CLOSE);


if (( rc != SQL_SUCCESS ) && ( rc != SQL_SUCCESS_WITH_INFO))
{
printf( "Error processing file %s line %d\n", FileName.c_str(), line );
break;
}

line++;

} // end of while loop

return;
}

/*************************** Process Directory **************************
*
* desc    : This procedure is called from main().  It's purpose is to scroll
*           through the specified directory passing each file's location
*           to ProcessFile().  FindFirstFile() and FindNextFile() are used
*           to traverse the directory.
* input   : Receives the HDBC environment handle obtained within main()
* output  : -
* returns : -
*/

void ProcessDirectory(HSTMT hstmt)
{

BOOL  Done;           // used for searching the directory for files
HANDLE  FndHnd;              // used for FindFirstFile()
WIN32_FIND_DATA FindDat; // allocate a local structure.

string Fname;
string FilePath = "C:\\Dats\\TestDats\\";
string FullFilePath = "C:\\Dats\\TestDats\\*.dat";
string TempFilePath = "C:\\Dats\\TestDats\\TempDats\\";

FndHnd = FindFirstFile(FullFilePath.c_str(), &FindDat);
Done = (FndHnd == INVALID_HANDLE_VALUE);


while (!Done){

Fname = FindDat.cFileName;
FilePath = FilePath + Fname;
ProcessFile(hstmt, FilePath);
TempFilePath = TempFilePath + Fname;
MoveFile(FilePath.c_str(), TempFilePath.c_str());
FilePath = "C:\\Dats\\TestDats\\"; // Reset File Path
TempFilePath = "C:\\Dats\\TestDats\\TempDats\\"; // Reset Temp Directory Path
Done = !FindNextFile(FndHnd, &FindDat); // Pass pointer to local structure
}
if (FndHnd)
FindClose(FndHnd);

return;

}


/*****************************    Main      ********************************
*
* desc    : main() establishes a connection to the SQL data source.  After
*           the connection is made, ProcessDirectory() is called to extract
*           files from a specified directory
* output  : Prints to the screen the error information obtained by
*           Check_ODBC_Errors().  Or, if no errors were incurred, the only
*           messages which appear are those verifying a connection was
*           made and that the program has terminated.
*/


void main(void)
{

RETCODE rc;
HENV henv = SQL_NULL_HENV;
HDBC    hdbc = SQL_NULL_HDBC;
HSTMT   hstmt = SQL_NULL_HSTMT;
const DWORD TimeOut = 5;


rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);

CHECK_ODBC_ERRORS(SQL_HANDLE_ENV, henv, rc);

if (rc == SQL_SUCCESS)
{
rc = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);
CHECK_ODBC_ERRORS(SQL_HANDLE_ENV, henv, rc);

}

if (rc == SQL_SUCCESS)
{
rc = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
CHECK_ODBC_ERRORS(SQL_HANDLE_ENV, henv, rc);
}

if(rc == SQL_SUCCESS)
{
printf( "Connecting to data source..." );
rc = SQLConnect(hdbc, (SQLCHAR *)"DSN", SQL_NTS, (SQLCHAR *)"sa", SQL_NTS,
(SQLCHAR *)"", SQL_NTS);
CHECK_ODBC_ERRORS(SQL_HANDLE_DBC, hdbc, rc);
printf( "Done.\n" );

}

if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
rc = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
CHECK_ODBC_ERRORS(SQL_HANDLE_STMT, hstmt, rc);
}


if (rc == SQL_SUCCESS)
{
ProcessDirectory(hstmt);
}

// Clean up all ODBC handles.
if (hstmt)
{
rc = SQLFreeStmt( hstmt, SQL_CLOSE);
CHECK_ODBC_ERRORS(SQL_HANDLE_STMT, hstmt, rc);
}
if (hdbc)
{
rc = SQLDisconnect( hdbc );
CHECK_ODBC_ERRORS(SQL_HANDLE_DBC, hdbc, rc);

rc = SQLFreeConnect( hdbc );
CHECK_ODBC_ERRORS(SQL_HANDLE_DBC, hdbc, rc);
}
if (henv)
{
rc = SQLFreeEnv( henv );
CHECK_ODBC_ERRORS(SQL_HANDLE_ENV, henv, rc);
}


printf( "\nTest complete. Press any key to exit.\n" );
_getch();


}


Thanks Tod
 
John500Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nietodCommented:
thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KangaRooCommented:
I'm still not clear on what the actual question is.
EE is the forum for dialogues.
0
nietodCommented:
John is learning to program and had a project for his work (where he is not working as a programmer) that I was helping him on via e-mail.  This is the result of about a million e-mails.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.