Thread: SQLExecute failing, Need Some Pointers

  1. #1
    Registered User
    Join Date
    May 2004

    SQLExecute failing, Need Some Pointers

    I am working with MS VS C++ 6.0 building a dialog based app to delete records from SQL Server 2000 db. I am connecting to the db and table fine, but the SQLExecute command is failing. I have tried to write some error return code to catch the error but have had poor results, as I am new to SQL server based stuff.

    I am connecting to the db as an admin so and I have written similar code that works to do select statements and bind columns, but the delete fails. I attached some code below. Any help is greatly appreciated.

    //get db info to see if tote exists in inventory
    	HENV                      hEnv = NULL; // Env Handle from SQLAllocEnv()
        HDBC                      hDBC = NULL; // Connection handle
        HSTMT                     hStmt = NULL;// Statement handle
        UCHAR                     szDSN[SQL_MAX_DSN_LENGTH] = "purge";// Data Source Name buffer
    	UCHAR                     szUID[10] = "##";// User ID buffer 
        UCHAR                     szPasswd[10] = "##";// Password buffer
    	UCHAR                     szSqlStr [200];
    	char					  AdjustLog [ ] = "DELETE FROM HEBMeatIntrack..AdjustLog WHERE (TransLogID like '";
    	char					  endquote [] = "%');"; //syntax to end sql statement
    	string					  statement;
    	string					  table = "HEBMeatInTrack..AdjustLog";
    	RETCODE					  retcode;
    	//display table to edit box for user visual of the table being purged
    //	strcpy (m_Table, table.c_str());
    	//set statement 
    	statement += AdjustLog;
    	statement += translogid;
    	statement += endquote;
    	strcpy ((char*) szSqlStr, statement.c_str());
    	//testing purposes only
    	ofstream fout;"D:\\HEB\\MeatPlant\\Purge\\purgelog.txt", ios::app);
    	fout <<"Inside DeleteAdjustLog Function"<<endl;
    	fout <<"SQL Statement: "<<statement.c_str()<<endl;
    	fout <<"TranslogID : "<<translogid<<endl;
    	// Allocate memory for ODBC Environment handle
        SQLAllocEnv (&hEnv);
        // Allocate memory for the connection handle
        SQLAllocConnect (hEnv, &hDBC);
        // Connect to the data source "test" using userid and password.
        retcode = SQLConnect (hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd, SQL_NTS);
    	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
               // Allocate memory for the statement handle
               retcode = SQLAllocStmt (hDBC, &hStmt);
               // Prepare the SQL statement by assigning it to the statement handle
               retcode = SQLPrepare (hStmt, szSqlStr, sizeof (szSqlStr));
              // Execute the SQL statement handle
    	      retcode = SQLExecute (hStmt);
    		  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
    			  fout<<"SQLExecute Successful"<<endl;
    		  else if (retcode == SQL_ERROR || retcode == SQL_NO_DATA || retcode==SQL_INVALID_HANDLE)
    			   displayODBCError (retcode);
    		  // Free the allocated statement handle
              SQLFreeStmt (hStmt, SQL_DROP);
              // Disconnect from datasource
              SQLDisconnect (hDBC);
    		  AfxMessageBox ("ERROR: Could Not Connect to Database: HEBMeatInTrack!\nPlease Contact I.S. Support.");
        // Free the allocated connection handle
        SQLFreeConnect (hDBC);
    	// Free the allocated ODBC environment handle
        SQLFreeEnv (hEnv);

  2. #2
    Algorithm Dissector iMalc's Avatar
    Join Date
    Dec 2005
    New Zealand
    Well... what is the program outputting? What is the error code?
    You left out the most important piece of information...

  3. #3
    Registered User
    Join Date
    May 2004
    Well thats just it, I have not been able to determine that, I get native error code 273. Which I can not find in any documentation on the MSDN or the web. So I think thats a fluke.

    But from just the file output I have in place, all the values for the strings I am passing, especially the SQL statement itself are correct, or what I am trying to pass.

    I can even take the DELETE statment and paste and copy it into query analyzer and it works fine. The only hint that I have a problem is the if statemetnt thats checks for SQL_ERROR and it writes back to my output file:
    "SQLExecute failed! Native Error code 273"

    the wording of course what I fout to the file. I hope that clearifies some, and thanks for looking.

  4. #4
    Registered User
    Join Date
    May 2004
    OK, did not get much on my question so I have been trying to come up with some better error checking and my question has altered a bit....

    I am trying to follow some example code off the web where I can tie a string variable for assigning the return errors from my testing of the SQLExecute, can some one help me understand how to tie *inMessage into my return code checks and balance statmenet of :
     else if (retcode == SQL_ERROR || retcode == SQL_NO_DATA || retcode==SQL_INVALID_HANDLE)
    					SQLCHAR SqlState[6];
    					SQLINTEGER NativeError;
    					int i = 1;
    					char message[512];
    					strcpy (message, "");
    					if (inMessage) {
    						strcpy(message, inMessage);
    						strcat(message, "  ");
    					sprintf(message, "%sError in SQLConnect(): %d.",
    							message, retcode);
    					while(SQLGetDiagRec(SQL_HANDLE_DBC, hDbConn, i,
    							SqlState, &NativeError,
    							ErrMsg, sizeof(ErrMsg), NULL)
    							!= SQL_NO_DATA) 
    						sprintf(message,"Diag: %d, SQLSTATE: %s NativeError: %d ErrMsg: %s",
    							i++, SqlState, NativeError, ErrMsg);
    I understand the principles of this sample code, which the much better coder, the original author, uses a function call and passes retcode and *inMessage to the function and then uses that in the output. I understand the retcode, I think, but I never saw where const char *inMessage was passed into the "if" statement to grab text based return error messaging. Which I am hoping will point out my SQLExecute failure..... Sorry for the lengthy question, the frustration is setting in more and I am finding my online research a bit over my head. Thanks-

  5. #5
    Registered User
    Join Date
    May 2004
    sample of my output file, I am gettig garble out from my errormessaging from not properly tying in the return code properties to my variable values... MSDN has information, just not finding good examples to follow to try and use SQLGetDiagField..

    Inside DeleteAdjustLog Function
    SQL Statement: DELETE FROM AdjustLog WHERE (TransLogID like '1ET00001FQ0000047%');
    TranslogID : 1ET00001FQ0000047
      Error in SQLConnect(): -1.
    Diag: 1, SQLSTATE: ..0sfs NativeError: 0 ErrMsg: 
    Diag: 2, SQLSTATE: ..0sfs NativeError: 0 ErrMsg: 
    Diag: 3, SQLSTATE: ..0sfs NativeError: 0 ErrMsg: 

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Variable pointers and function pointers
    By Luciferek in forum C++ Programming
    Replies: 11
    Last Post: 08-02-2008, 02:04 AM
  2. Using pointers to pointers
    By steve1_rm in forum C Programming
    Replies: 18
    Last Post: 05-29-2008, 05:59 AM
  3. Request for comments
    By Prelude in forum A Brief History of
    Replies: 15
    Last Post: 01-02-2004, 10:33 AM
  4. Staticly Bound Member Function Pointers
    By Polymorphic OOP in forum C++ Programming
    Replies: 29
    Last Post: 11-28-2002, 01:18 PM