Title: Trap and View bad SQL statements at run time (Debug Tool)
Question: You know the situation, dynamically build a SQL statement at run time ... try to open the query ... It fails ... get presented with some cryptic message that means as much as soup in a basket!
This is particularly bad with the Oracle ODBC driver. Most errors return "Key Violation". From experience I know that this is a syntax problem in the SQL text. I got tired of setting break points before the "Query.Open" and trying to examine "Query.Text" in the IDE debugger (ever seen a long SQL statement here with carriage returns and line feeds ? - Uuugh). Copying the text from the IDE Debugger evaluater to notepad was also a pain because of the #13#10 CrLf pairs embeded in the text. (it would come thru as one long line with many occurances of #13#10 in it)
The answer was to create a function that would trap the Query.Open error at runtime. It must also save the offending SQL statement to a file as well as popping up the statement in a view window. Viewing the statement in the popup window may be enough to resolve why the statement was failing, being a memo you can change the statement and cut it to clipboard (right click).
If you still cannot see the wood for the trees then import the created SQLERR.SQL file into SqlPlus (or your favorite SQL interpretor) and debug it in style. You will at least get Errors that indicate which line and position the problem lies at.
A very simple function, but can save you hours when debugging SQL scripts. (have included both Open and ExecSQL types)
with the opion to Terminate after Error or continue.
Answer:
Modified in response to comments 26/07/2001
uses Windows, Sysutils,Controls, StdCtrls, Classes, Forms, Dialogs,
DBTables, Db;
// ======================================================
// Returns TRUE if query opened or execed Ok, else FALSE
// if FALSE then the SQL text is saved to SQLERR.SQL
// and a popup view window is presented with the
// offending SQL statement
// =====================================================
type TSqlRunMode = (sqlOpen,sqlOpenTerminate,sqlExec,sqlExecTerminate);
function sqlRunLog(Query : TQuery; SqlRunMode : TSqlRunMode = sqlOpen) : boolean;
var RetVar : boolean;
Form : TForm;
Memo : TMemo;
begin
try
if SqlRunMode in [sqlOpen,sqlOpenTerminate] then
Query.Open
else
Query.ExecSQL;
RetVar := true;
except
on E : Exception do begin
RetVar := false;
MessageDlg('Query Open/Exec Failure [' + Query.Name + ']'#13#10 +
'SQL Statement written to SQLERR.SQL'#13#10 +
'Press "OK" to view SQL Statement'#13#10#13#10 +
E.Message + #13#10#13#10 +
'Press "OK" to view SQL Statement',
mtError,[mbOk],0);
Query.SQL.SaveToFile('sqlerr.sql'); // Save text to sql file
// Create and display memo form and load sqlerr.sql
Form := TForm.Create(nil);
Memo := Tmemo.Create(Form);
Memo.Parent := Form;
Memo.Align := alClient;
Memo.Font.Name := 'Courier New';
Memo.Font.Size := 9;
Memo.Lines.LoadFromFile('sqlerr.sql');
Form.ShowModal;
Form.Free; // Free Form and all components in it
if SqlRunMode in [sqlOpenTerminate,sqlExecTerminate] then
Application.Terminate;
//HaltApplication(''); {My Customized Terminator}
end;
end;
Result := RetVar;
end;