Thread: 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.

Code:

//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;
	fout.open("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);

		  fout.close();
	} 
	else
	{
		  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);

Well... what is the program outputting? What is the error code?
You left out the most important piece of information...

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.

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 :

Code:

 else if (retcode == SQL_ERROR || retcode == SQL_NO_DATA || retcode==SQL_INVALID_HANDLE)
		  {
					SQLCHAR SqlState[6];
					SQLINTEGER NativeError;
					SQLCHAR ErrMsg[SQL_MAX_MESSAGE_LENGTH];
					int i = 1;
					char message[512];
					strcpy (message, "");
					if (inMessage) {
						strcpy(message, inMessage);
						strcat(message, " — ");
					}
					sprintf(message, "%sError in SQLConnect(): %d.",
							message, retcode);
					fout<<message<<endl;
					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);
						fout<<message<<endl;
					}
		  
		  }

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-

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..

Code:

Inside DeleteAdjustLog Function
SQL Statement: DELETE FROM AdjustLog WHERE (TransLogID like '1ET00001FQ0000047%');
TranslogID : 1ET00001FQ0000047
 — Error in SQLConnect(): -1.
Diag: 1, SQLSTATE: ..0ÒsfùÚs„þ NativeError: 0 ErrMsg: „þ
Diag: 2, SQLSTATE: ..0ÒsfùÚs„þ NativeError: 0 ErrMsg: „þ
Diag: 3, SQLSTATE: ..0ÒsfùÚs„þ NativeError: 0 ErrMsg: „þ