CRecordset - Retrieving SQL & Parameters on Fail

[HKafeman]

Help please - I have an INSERT query of the form:
INSERT INTO MyTable (Field1, Field2...) VALUES (Value1, Value2...)
into a SQL Server Database. It is failing due to invalid text in one of the Parameters.
Now, I want to report the SQL query and Parameter Values when the failure is detected to aid debugging any future issues.
But GetSQL() only returns the SELECT query - the m_strUpdateSQL contains the "INSERT ... VALUES (?, ?...)" but there is no access Function!
Also if I try to use GetFieldValue() (to try to report the parameter values) my application just dies with an exception - presumably because they are only valid for a SELECT?
Is there any way to get at the Parameter Values?

Any help from someone out there who is familiar with CRecordsets, etc. would be gratefully received.

 

[Zyrenthian]

Here is a function I wrote up which formats the SQL and dumps it to a file. Make sure there are single quotes around your text fields. If you cant figure it out from the dump, post the SQL dump and I will take a look at it.

matt

================================================================================================

void CDBMedia::DumpSQLDebug(CString filename,CString SQL)
{

#ifdef _DEBUG
	CString dbgSQL = SQL;
	dbgSQL.MakeUpper();
	dbgSQL.Replace("\t"," ");
	dbgSQL.Replace("("," ( ");
	dbgSQL.Replace(")"," ) ");
	dbgSQL.Replace(" AND "," \nAND ");
	dbgSQL.Replace("SELECT "," \nSELECT\n ");
	dbgSQL.Replace(",",",\n        ");
	dbgSQL.Replace(" FROM "," \nFROM\n ");
	dbgSQL.Replace(" WHERE "," \nWHERE\n ");
	dbgSQL.Replace(" IN "," IN\n ");
	dbgSQL.Replace(" OR ", "\nOR ");
	dbgSQL.Replace(" ORDER ", " \nORDER ");

	CFile dbgFile(filename,CFile::modeCreate|CFile::modeWrite|CFile::shareDenyNone);
	dbgFile.Write(dbgSQL,dbgSQL.GetLength());
	dbgFile.Close();
#endif
}

 

[HKafeman]

Zyrenthian
Thanks for that, but my problem is how to get at the actual SQL String and the Parameters used. The actual SQL is in m_strUpdateSQL in the CRecordset and looks like "INSERT INTO MyTable (Field1, Field2...) VALUES (?, ?...)", so I want to be able to dump this and the Parameter values that go into the "?" marks.
I know why my original Insert failed, but want to put code in place to report any future problems so that I can debug.

 

[Zyrenthian]

I am not sure if I fully understand but CRecordset should throw an exception when you try to execute the SQL command. By putting try catch blocks in and an assert in the catch you will break there automatically when an error occurs in debug. The assert will do nothing in release.

Is this what you were looking for?

Matt

 

[HKafeman]

I am looking to log some meaningful information from my Release version in the Event of a problem!
I already have a try/catch and reporting of the SQL Error returned by SQL Server. But I want to report enough information to know which particular Query with which particular Parameters is causing the problem.

 

[Zyrenthian]

Ahh ok,
When you get teh CExeption / CDBException you can pull the message out and write it to a log file. This can be a cfile you have as a private member variable and you create it every time you run. You can either append OR empty it depending on your preference. Also there are a couple of useful macros you can append to your text. These are __LINE__ and __FILE__ so when you output to the log file you can pinpoint the file and the line number.

Is this what you are looking for?

Matt

 

[HKafeman]

Thanks, but all things I know about already and we already make use of in our code in various places!!
It is the specific information within the CRecordset that I am having the problem with getting at.