BorlandTalk.com

Andy · Guest

I have the following components on my form:

TDBGrid dbgrdData
TDataSource dsrcSource
TADOQuery adoQuery
TADOConnection adocMain

Properties are set as follows:
dbgrdData->DataSource = dsrcSource
dsrcSource->DataSet = adoQuery
adoQuery->Connection = adocMain

As the user edits records in the DBGrid they are updated in the
database. I want to keep a log of all the SQL that is executed.

I have found the OnUpdateData event in the DataSource and the
BeforePost/AfterPost events in the TDataSet (which TADOQuery
descends from), which I am thinking would be good places to put code
to store the SQL statement that is going to be executed. But I can't find
anyway of getting hold of the SQL!

Is this possible?

I've also tried looking at the TADOConnection object, to get at all SQL
executed on the connection. But I haven't been able to work out how to
do this either.

Does anyone know how to do this or if it is possible?

Thankyou,
Andy

Andy · Guest

I have been unable to find a way to get hold of the SQL executed by a DBGrid update, so I am no creating it myself.

However I've hit another stumbling block and that is how to find the key fields of a record?

TCustomADODataSet->IndexFields and TField->KeyFields are both NULL in my program. I need to know the key fields of a record in order to build up the WHERE clause. Can anyone help?

Here is the code I've written to generate the UPDATE statement:

TDataSource* ds1;
TADOTable adoTable;
TADOQuery adoQuery;

void __fastcall TfmChildWin::CreateCurrentUpdateStatements()
{
TFields* lFieldList;
TField* lField;
TStringList* lSQLStatements = NULL;

try
{
if(ds1->DataSet)
{
AnsiString lTableName;

//If we're using the adoTable to drive the grid,
//we know what the table name is
if(ds1->DataSet == adoTable)
{
lTableName = adoTable->TableName;
}
lFieldList = ds1->DataSet->Fields;

//Go through all the fields looking for ones that
//have changed
for(int loop = 0; loop < lFieldList->Count; loop++)
{
lField = lFieldList->Fields[loop];

if(lField->NewValue != lField->OldValue)
{
//If this is the 1st field thats change,
//create a new list to store our SQL
//statements in.
if(lSQLStatements == NULL)
{
lSQLStatements = new TStringList;
}

//If we're using the query to drive the
//DBGrid, we'll have to get the table name
//from the field
if(ds1->DataSet == adoQuery)
{
//The origin should be
//<table_name>.<field_name>
lTableName = lField->Origin;
lTableName =
lTableName.SubString(0,
lTableName.Pos("."));
}

AddToSQLUpdate_NewFieldValue(
lField,
lTableName,
lSQLStatements
);
}
}

AddToSQLUpdate_WhereClauses(lSQLStatements);
}
}
__finally
{
if(lSQLStatements)
{
lSQLStatements->Clear();
delete lSQLStatements;
}
}
}
//-------------------------------------------------------------

void __fastcall TfmChildWin::AddToSQLUpdate_NewFieldValue(
TField* aField,
const AnsiString &aTableName,
TStringList* aSQLStatements
)
{
AnsiString lSQLStatement;
int lListPos;

for(lListPos = 0;
lListPos < aSQLStatements->Count;
lListPos++)
{
if(aSQLStatements->Names[lListPos] == aTableName)
{
lSQLStatement = aSQLStatements->Strings[lListPos];
break;
}
}

//This is the first field for this table, so create a new
//sql statement
if(lSQLStatement.IsEmpty())
{
lSQLStatement = aTableName + "=UPDATE " +
aTableName + " SET";
aSQLStatements->Add(lSQLStatement);
}

lSQLStatement += (" " + aField->FieldName + "=" +
aField->NewValue);

aSQLStatements->Strings[lListPos] = lSQLStatement;
}
//-------------------------------------------------------------

void __fastcall TfmChildWin::AddToSQLUpdate_WhereClauses(
TStringList* aSQLStatements
)
{
AnsiString lSQLStatement;
AnsiString lTableName;

for(int loop = 0; loop < aSQLStatements->Count; loop++)
{
lSQLStatement = aSQLStatements->Strings[loop];
lTableName = aSQLStatements->Names[loop];

lSQLStatement += " WHERE ";
//**************************************
//Loop through all key fields here and add them to the
//where clause
//**************************************

aSQLStatements->Strings[loop] = lSQLStatement;
}
}
//-------------------------------------------------------------

Thanks

-Andy