/*---------------------------------------------------------------------------------------------------------------------------------------------------- Program Listing for: odbc_console.cpp Project: datapiece Namespace: c++ ---------------------------------------------------------------------------------------------------------------------------------------------------- */ /**************************************************************************************************************** // odbc_console.cpp // odbc_console C++ project, Visual C++ 6 // Scott Laing // SBOE // // odbc_console.cpp : Defines the entry point for the console application. // // this is the main calling C++ module file for odbc_console.cpp project // // this project is a port to C++ of a perl cgi script which appears to be failing due to a driver error // the cgi script is crc2.pl and it's located in /cgi-bin directory on the web server. // // what it does: when passed a select statement this returns this info from the select along with // critical column information on types, widths, decimals, nulls etc. from this info the remote // appeals program (REMA.PJX) can recreate a dynamic fox cursor on the fly from the information returned. the // process is fairly transperant from Foxpro you can call // // r_sql("select * from agents", "agent_cursor") // // and this operates almost exactly like // // select * from agents into cursor agent_cursor // // the details of how r_sql function works in fox is in the remote.prg procedure file in the rema project. basically // it creates a cursor in the background based on a call to this (or crc2.pl) cgi program which returns the info // as well as the critical column information, using this info I think create a temporary table in foxpro of the // same structure, fill it with the returned info, and then return that cursor to the calling fox program. this // allows me to with minor modifications (basically replacing selects with r_sql() statements) rewrite a local // Foxpro program to run raw via the web. Altho it is not highly secure it could easily be modified to be as // secure as desired (since this script is read-only it is not a major issue on the state site as the info is // public anyway). i.e. you could add a des or triple-des encryption process to the data. since some home systems // aren't that fast and this would affect performance I'm not doing this soon but it is a possible addition to // make this work fairly quickly. // // note: this program requires a System DSN (data source in ODBC Setup in control panel) be defined to point to the app and // assumes it is named "VFPTEST" it should point to azsboe.dbc foxpro database container. // // note that win32::odbc, one of the perl drivers I am using on the web site scripts, does not return // column descriptor information of this type but only very simple column values, so it is impossible to recreate // crc2.pl to work with win32::odbc. I was using perls dbi module with dbd:odbc and that appeared to work and was // stable under our old setup, however now that we've upgraded the web computers it is acting difficult possibly // a driver version issue OR an issue with this version of perl OR an issue with IIS 5 or Windows 2000 and Perl // or dbi module. Rather than exhaustively research this and/or try to go back a version, which may not work // anyway for sure, I'm rewriting crc2.pl in C++ using MFC and Visual C++ 6. // // last mod (that I bothered to document): Feb 18, 2002 // // 2/16-18 (ish) added memo update features these relate to sending info to update a memo value // this must be treated separately as the normal update chokes on more than 255 chars // in a field. see notes below in code ****************************************************************************************************************/ #include "stdafx.h" #include "odbc_console.h" #include "MyRecordSet.h" #include "Field.h" #include "CgiLib.h" #ifdef _DEBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE[] = __FILE__; #endif ///////////////////////////////////////////////////////////////////////////// // The one and only application object CWinApp theApp; using namespace std; // i luvvvv the STL, statement required for STL function usage int _tmain(int argc, TCHAR* argv[], TCHAR* envp[]) { int nRetCode = 0; bool bFromCmd = false; // set to true for debugging from command line bool bUpdateCall = false; bool bMemoSafeVersion = true; bool bMemoUpdate = false; char *memo_field; char *memo_table; char *where_clause; char *memo_value; char *test_only = NULL; CString bigchar; // initialize MFC and print an error on failure if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0)) { // MFC failure of some kind //cout << _T("ERROR: MFC initialization failed") << endl; //nRetCode = 1; cout << "0:MFC error executing odbc_console.exe"; cout << "\n::END_OF_DATA::\n"; exit(1); } else { //AfxMessageBox("\"aaa\""); /* need to declare a pointer variable of type LIST to keep track of our list */ // this uses the cgi-lib functions elsewhere in the project LIST *head; /* need to call this function at the beginning to initiate and setup out list */ if (! bFromCmd ) head = cgi_input_parse(); // basic cgi-function to get the cgi parameters // always send standard http reply header (so we can view in the browser, and remote program // direct socket connection expects it also). cout << "HTTP/1.0\r\n"; cout << "Content-type: text/html\r\n"; cout << "\r\n"; if (0) { cout << "<html><body>\n\r"; cout << theApp.m_lpCmdLine; // cgi parameters aren't included in this variable, interesting note // as i learned during debugging, they are a part of the environment string this // cgi param stuff is now grabbed by cgi-lib functions included in the project. cout << "<br>\n\r"; } char select[801]; if (! bFromCmd ) { //list_print(head); // required cgi params: name, should be "scooby" // update, sql update command // select, sql select command // password, should be "magic" for update call char *name = find_val(head, "name"); if (! name ) { cout << "0:bad params"; cout << "\n::END_OF_DATA::\n"; exit(0); } if ( strcmp(name, "scooby") ) { cout << "0:bad params"; cout << "\n::END_OF_DATA::\n"; exit(0); } test_only = find_val(head, "test"); char *memo = find_val(head, "memo"); // see if a memo update, this is a special update requires some special logic if (memo) { memo_field = find_val(head, "field"); memo_table = find_val(head, "table"); where_clause = find_val(head, "where"); memo_value = find_val(head, "value"); if (! memo_field || ! memo_table || ! where_clause || ! memo_value ) { cout << "0:bad param1"; // dont want to say bad password, i'm misspelling paramss to clue myself // in to how this failed cout << "\n::END_OF_DATA::\n"; exit(0); } bMemoUpdate = true; } else { // foxpro update call (from remote.prg in remote appeals project rema.pjx) //mstr = GetCGIRequest("", "DBI.PL?update=" + tcupdate2 + "&name=scooby&password=magic") // foxpro select call // ms = gettextback("GET /CGI-BIN/CRC2.PL?select=" + tcSelect + "&name=scooby" + " HTTP/1.1" + ; // cr + cr, 500) // this may look arcane for a full understanding of these functions see rema.pjx project // note: either select or update params are defined, either but not both, both is an error char *select_val = find_val(head, "select"); char *update_param = find_val(head, "update"); // neither param defined if (! select_val && ! update_param) { cout << "0:bad params"; cout << "\n::END_OF_DATA::\n"; exit(0); } // both defined if ( select_val != NULL && update_param != NULL ) { cout << "0:bad params, unclear process type"; cout << "\n::END_OF_DATA::\n"; exit(0); } // this is a select type call if (select_val != NULL) { if ( strlen( select_val) > 800 ) { cout << "0:error: passed select string is too long > 800 chars"; cout << "\n::END_OF_DATA::\n"; exit(0); } strcpy(select, select_val) ; // otherwise this is an update call, i.e. data changing call like "update" or "delete" or "insert" } else { // make sure they are passing the password param with the right value before allowing update char *password = find_val(head, "password"); if (password == NULL ) { cout << "0:bad params"; // dont want to say bad password, i'm misspelling paramss to clue myself // in to how this failed cout << "\n::END_OF_DATA::\n"; exit(0); } if ( strcmp( password, "magic") ) { cout << "0:bad paramss"; // dont want to say bad password, i'm misspelling paramss to clue myself // in to how this failed cout << "\n::END_OF_DATA::\n"; exit(0); } if ( strlen( update_param) > 800 ) { cout << "0:error: passed update string is too long > 800 chars"; cout << "\n::END_OF_DATA::\n"; exit(0); } strcpy(select, update_param) ; bUpdateCall = true; } } // otherwise command line call probably from testing, if so, just do a simple little sample command } else { //strcpy(select, "select * from agents order by nagentid where recno() < 10"); // sample memo call if (1) { cout << "memo test" << "\n"; bigchar = ""; int n; char misc[100]; strcpy(misc, "abc\\rde[fghijklmn\\nopqrstu]vwxy[1234567890!@#$%^&*()") ; // throw in a newline and carriage return to test the translation misc[5] = '\x0a'; misc[10] = '\x0d'; // build a very long string for (n=0;n<60;n++) bigchar += misc; memo_field = (char *)malloc(200); strcpy(memo_field, "mcontent"); memo_table = (char *)malloc(200); strcpy(memo_table, "mailer"); where_clause = (char *)malloc(200); strcpy(where_clause, "mail_id = 200"); memo_value = (char *)malloc(6000); strcpy(memo_value, bigchar.GetBuffer(0) ); bMemoUpdate = true; // sample update call } else if (1) { strcpy(select, "insert into hrlink (hrlink_id) values (3843)"); bUpdateCall = true; // sample select call } else { strcpy(select, "select * from agents where recno() < 50"); bUpdateCall = false; } } MyRecordSet myRS; // the main recordset for the transaction using my own odbc class CString memo_select; // these variables used with special "memo update" type cgi call logic directly below CString parse_value; CString str_memo_value; int nCode; // sql return string, sometimes an error code // notes on this code in the below if statement: // // we are doing a special memo update, the reason this is special is because odbc doesn't like // to have field values in update be longer than 255, it gives an error, so what this does is // breaks it into 255 length chars and appends it via a few update commands, the first sql call does this: // // update table set field = [value] where condition // // where value is the left 255 chars of the full string // // then it calls a bunch of updates as follows: // while (1) { // value = next 255 length piece of full string // update table set field = field + [value] where condition // if (done with string) // break; // } // // where value is the the full string broken down into 255 char length pieces (254 actually). // // this way - using these cgi parameters - they can send a large memo update via these special parameters and // update a foxpro memo field also even if // the field is greater than 255 in length without getting that odbc error (field too long error). // // note: it may be that using prepared parameters with low level sql allows you to get around this 255 limit // but I am under some deadline pressures to get this fully working and this methodology appears fairly // fast. (in other words, I could possibly rework the low level Field and MyRecordSet classes to use a // different mechanism than several odbc calls breaking the string up like below). however for speed I'm doing // it this way (the other way, using prepared parameter odbc calls may or may not accept longer strings). // // note: we could have the remote program call a bunch of normal updates via this same logic, rather // than handling it special. i.e. from the calling remote program they could do a // // r_sql("update table set field = left(val,255) where condition") && note: r_sql = remote sql call // r_sql("update table set field = substr(val, 256,255) where condition") // // etc. (see rema.pjx and remote.prg procedure file in foxpro for details on remote program) // // but this way - by handling on the server - they can send the string once = one connect, instead of a bunch // of broken down updates which would involve a bunch of calls which is slower and more abuse of the server (loads // in the cgi program over and over and isn't as fast, esp for very large memo fields, could call this procedure // 20 or more times if I was using this logic, of breaking down the string on the foxpro calling end). // also it is not quite as safe, using this logic doing set field = field + [value] assumes the transactions are // somewhat atomic as we wouldn't want someone else changing the field half-way through the update process, since the // remote program may have to run on very slow connections, having them go ahead and send the long memo string // in one cgi sending (invocation of this program), and having the // server then break it up and do the updates all at once with it's own logic below (without delays as // would be involved in the client sending the statements) should make the process more atomic and safe as ODBC // queries on the server run very very quickly. // additional note on the atomic issue: the most common field we are going to be updating // is the mcontent memo field in the mailer table (mailer.dbf) which would not be changed by other processes during // normal operation - this process alone would change those fields for the record we are looking at, so the risk // of atomic transaction failure is less, at least in that case, but it is something to keep aware of nevertheless. // (atomic = doing everything at once, indivisible operation, like a transaction in sql) // Scott // memo type update if (bMemoUpdate) { // save to a CString for convenience str_memo_value = memo_value; // replace any "\" with "\~" (translation safety) // this avoids the below translation logic from interfering with any naturally occuring // "\r" or "\n" characters in the string, while these are unlikely they are quite possible, so now any // naturally occuring "\r" will look like "\~r" and not be untranslated back to a carriage return as the // artificially created "\r" escape sequences will be. for example they could say, // "my property tax was really too high \not understandable at all\ and I want it lowered" // if this was the memo string the "\n" above would get translated to a carriage return in the untranslate // sequence using the below logic, however if I pre-wrap the translation with this "\" -> "\~" translation first // this mistake is avoided, see general parsing escaping logic texts. str_memo_value.Replace("\\", "\\~"); // translate out newline, carriage returns, as these will cause sql update to FAIL // replace newlines with "\n" str_memo_value.Replace("\x0a", "\\n"); // replace cr's with "\r" str_memo_value.Replace("\x0d", "\\r"); // also replace brackets (will cause update statement to choke if I don't do this) str_memo_value.Replace("[", "\\b"); str_memo_value.Replace("]", "\\c"); int length; length = str_memo_value.GetLength(); // get first 254 chars to the parsing CString parse_value = str_memo_value.Left(254); int npos = 254; int nlast_no_updated = 0; // set first time flag bool bFirst = true; while (1) { // first time we update it if (bFirst) memo_select.Format("update %s set %s = [%s] where %s", memo_table, memo_field, parse_value.GetBuffer(0), where_clause); // second time and further calls we are appending to the rest of the string else memo_select.Format("update %s set %s = %s + [%s] where %s", memo_table, memo_field, memo_field, parse_value.GetBuffer(0), where_clause); // if testing return the parsed up string only if (test_only) { cout << memo_select.GetBuffer(0); cout << "<br>"; // otherwise run the sql command } else { nCode = myRS.DoQuery("VFPTEST", memo_select.GetBuffer(0) ); handle_errs(nCode); if (myRS.getRowCount() == 0 && bFirst) { cout << "1:rows affected:0"; cout << "\n::END_OF_DATA::\n"; exit(0); } if (myRS.getRowCount() == 0 && ! bFirst) { cout << "0:only partial memo update email will be corrupted"; cout << "\n::END_OF_DATA::\n"; exit(0); } if (! bFirst && nlast_no_updated != myRS.getRowCount() ) { cout << "0:partial update failure on memo-stuff different rowcounts between calls"; cout << "\n::END_OF_DATA::\n"; exit(0); } nlast_no_updated = myRS.getRowCount(); } // string completely processed quit if (npos >= length) break; // get next piece of string parse_value = str_memo_value.Mid(npos, 254); // if next piece is empty string, quit if ( parse_value.IsEmpty() ) break; // adjust offset npos += 254; bFirst = false; } // the last step: untranslate out the escape sequences for carriage return and newline // ODBC calls will FAIL with newline or carriage returns in the field value this appears somewhat // poorly designed and memo-unfriendly, however, it is the what it is. we now fix the translation // of the fields via a call to strtran() in fox, which is like a CString::Replace() call in Foxpro. if (!test_only) { // we had to translate out brackets, newlines and carriage returns so now translate them back in memo_select.Format("update %s set %s = strtran(%s, [\\b], \"[\" ) where %s", memo_table, memo_field, memo_field, where_clause); nCode = myRS.DoQuery("VFPTEST", memo_select.GetBuffer(0) ); handle_errs(nCode); memo_select.Format("update %s set %s = strtran(%s, [\\c], \"]\" ) where %s", memo_table, memo_field, memo_field, where_clause); nCode = myRS.DoQuery("VFPTEST", memo_select.GetBuffer(0) ); handle_errs(nCode); memo_select.Format("update %s set %s = strtran(%s, [\\r], chr(13) ) where %s", memo_table, memo_field, memo_field, where_clause); nCode = myRS.DoQuery("VFPTEST", memo_select.GetBuffer(0) ); handle_errs(nCode); memo_select.Format("update %s set %s = strtran(%s, [\\n], chr(10) ) where %s", memo_table, memo_field, memo_field, where_clause); nCode = myRS.DoQuery("VFPTEST", memo_select.GetBuffer(0) ); handle_errs(nCode); // we also translated backslash to a safe char, so undo this translation now also memo_select.Format("update %s set %s = strtran(%s, [\\~], [\\] ) where %s", memo_table, memo_field, memo_field, where_clause); nCode = myRS.DoQuery("VFPTEST", memo_select.GetBuffer(0) ); handle_errs(nCode); char buffer[20]; // get the rows affected itoa( nlast_no_updated, buffer, 10); // old code from dbi.pl (perl) //print "1:rows affected:$rc\n"; // send string, "1" means succeed, then the value after the colon cout << "1:rows affected:"; cout << buffer; cout << "\n::END_OF_DATA::\n"; } // free malloced fields if called from the command line if (bFromCmd) { free(memo_field); free(memo_table); free(where_clause); free(memo_value); } myRS.Cleanup(); exit(0); } nCode = myRS.DoQuery("VFPTEST", select); // "select * from agents order by nagentid" //myRS.ShowColumns(); if (nCode == MyRecordSet::CONNECT_ERROR) { cout << "0:trouble connecting to database"; cout << "\n::END_OF_DATA::\n"; exit(0); } if (nCode == MyRecordSet::SQL_PREPARE_ERROR) { cout << "0:trouble preparing statement"; cout << "\n::END_OF_DATA::\n"; exit(0); } if (nCode == MyRecordSet::SQL_EXECUTE_ERROR) { cout << "0:trouble executing select"; cout << "\n::END_OF_DATA::\n"; exit(0); } // if this call is an update call (sql "update" or "delete" or "insert") // then we return how many rows were affected, this is similar to old /cgi-bin/dbi.pl operation if (bUpdateCall) { /* an old sample insert call myRS.DoQuery("VFPTEST","insert into agents values (907, [first],[m],[last]," "[company],[add1],[add2],[city],[st],[posta],[5551212],[5551313]," ".T.,[],.T.)"); */ char buffer[20]; // get the rows affected ltoa( myRS.getRowCount(), buffer, 10); // old code from dbi.pl (perl) //print "1:rows affected:$rc\n"; // send string, "1" means succeed, then the value after the colon cout << "1:rows affected:"; cout << buffer; cout << "\n::END_OF_DATA::\n"; myRS.Cleanup(); // and we're done, that's it, update returns less info than select exit(0); // otherwise return normal select information according to defined protocol of rema.pjx project and old crc2.pl } else { // according to crc2.pl sometimes odbc errs by returning empty columns, altho this could // be a perl dbi.pm error possibly, at any rate to be paranoid this check is retained if (myRS.column_count == 0) { cout << "0:bad select statement, no columns returned"; cout << "\n::END_OF_DATA::\n"; exit(0); } CString m_MainEdit = ""; m_MainEdit = myRS.GetColNameStr(); m_MainEdit += myRS.GetTypesStr(); m_MainEdit += myRS.GetWidthsStr() ; m_MainEdit += myRS.GetDecStr(); m_MainEdit += myRS.GetNullsStr() ; m_MainEdit += "data\n"; cout << m_MainEdit.GetBuffer(0); m_MainEdit = ""; CString thisfield; int n; // fetch the actual data and send while (myRS.Fetch()) { // iterate thru columns for this record for (n=1;n<= myRS.column_count;n++) { thisfield = myRS.getField(n); // the standard field separator character is two vertical line chars "||" // however since this could in theory be contained in the sql field return value itself we must // translate around this somehow, I do this by translating "|" with "\|" for any // occurrences of "|" in the field string itself. I then untranslate // any occurrences of "\|" back to "|" on the foxpro create cursor end. This way I can // be sure that "||" is a correct separator as it won't occur naturally in the field string // as all the "|" will be escaped to "\|" (so "||" would show up as "\|\|" and not look // like a field separator and not throw off the field parsing logic). see rema.pjx if this // is confusing. thisfield.Replace("|", "\\|"); thisfield.TrimRight(" "); // for faster data sent we can trim right characters off the end of // field data as fox will send all these blank spaces by default and it's not // required. cout << thisfield.GetBuffer(0); cout << "||"; } if (bMemoSafeVersion) cout << "\x05\n"; else cout << "\n"; } cout << "::END_OF_DATA::\n"; } myRS.Cleanup(); } return nRetCode; } // handle a few default odbc errors void handle_errs(int nCode) { if (nCode == MyRecordSet::CONNECT_ERROR) { cout << "0:trouble connecting to database"; cout << "\n::END_OF_DATA::\n"; exit(0); } if (nCode == MyRecordSet::SQL_PREPARE_ERROR) { cout << "0:trouble preparing statement"; cout << "\n::END_OF_DATA::\n"; exit(0); } if (nCode == MyRecordSet::SQL_EXECUTE_ERROR) { cout << "0:trouble executing select"; cout << "\n::END_OF_DATA::\n"; exit(0); } }