CODEWITHSUNDEEP
sqlsql-serverdelphiado
Franz
Franz

Reputation: 2033

How to handle ADO Query with results vs. Query with no results?

I run various SQL statements using MSSQL and ADO.

The code sequence looks like this: 

aADOQuery.Active := False;
aADOQuery.SQL.Text := ' MY SQL STATEMENT ';
aADOQuery.ExecSQL;
aADOQuery.Active := True;

The last statement fails if the SQL return result is empty. How to check for this case to avoid run time errors?

Note: The SQL statement comes from a memo where the user is typing the SQL.

Upvotes: 2

Views: 4707

Answers (2)

No'am Newman
No'am Newman

Reputation: 6477

Whilst this isn't a direct answer to your question, I wrote an SQL command interpreter tool which allows various commands to be sent to the database. If the first word of the query is not 'select' then 'execsql' is the command. For 'select', I determined whether the query was what Borland calls a live query, in which interactive editing is possible. The 'query' variable in my program is a TClientDataSet which is connected via a TDataSetProvider to a TSQLDataSet, but this should also work with AdoQuery.

procedure TForm1.Button1Click(Sender: TObject);  // this is the 'execute query' button
var
 i: integer;
 cmd: string[6];
 tmp: string;

begin
 tmp:= mem.lines[0];
 i:= 1;
 while tmp[i] = ' ' do inc (i);
 dec (i);
 if i > 0 then tmp:= copy (tmp, i + 1, length (tmp) - i);
 cmd:= '';
 for i:= 1 to 6 do cmd:= cmd + upcase (tmp[i]);
 query.close;
 sdsquery.commandtext:= mem.text;
 if cmd = 'SELECT' then
  begin
   if livequery then
    begin
     dbgrid.options:= dbgrid.Options + [dgEditing];
     dbNavigator1.visiblebuttons:= [nbfirst, nbprior, nbNext, nbLast, nbInsert,
                                    nbDelete, nbedit, nbpost, nbcancel,
                                    nbrefresh];
    end
   else
    begin
     dbgrid.options:= dbgrid.Options - [dgEditing];
     dbNavigator1.visiblebuttons:= [nbfirst, nbprior, nbNext, nbLast];
    end;
   query.open
  end
 else sdsquery.ExecSQL;
end;

function TForm1.LiveQuery: boolean;
// check what the second token after 'from' is
const
 EOI = #26;

var
 cmdlen, curpos: integer;
 ch: char;
 tmp: string;

 Procedure GetChar;
 begin
  inc (curpos);
  if curpos <= cmdlen
   then ch:= mem.text[curpos]
   else ch:= EOI;
 end;

 Function Token: string;
 const
  punct: set of char = [' ', ',', ';', EOI, #10, #13];

 begin
  result:= '';
  while ch in punct do getchar;
  while not (ch in punct) do
   begin
    result:= result + upcase (ch);
    getchar
   end
 end;

begin
 ch:= ' ';
 cmdlen:= length (mem.text);
 curpos:= 0;
 tmp:= token;
 while tmp <> 'FROM' do tmp:= token;
 tmp:= token;  // this should be the first table name
 if ch = ',' then result:= false    // select ... from table1, table2
 else if ch = EOI then result:= true  // select * from table1
 else
  begin
   tmp:= token;
   result:= (tmp = 'WHERE') or (tmp = 'ORDER')
  end;
end;

Upvotes: 2

kobik
kobik

Reputation: 21232

If your query returns a record set (SELECT statements) you should not use ExecSQL but simply aADOQuery.Open or Active := True.

For queries that do not return a record set e.g. INSERT/UPDATE/DELETE, use ExecSQL. in most cases you will get back aADOQuery.RowsAffected by your query.

Other SQL statements that you should use ExecSQLare CREATE/ALTER/DROP/EXEC etc... (no RowsAffected return in this case)

If the query does not return a cursor to data (such as INSERT statement), trying to Open or setting such TDataSet to Active will fail.

You could use ADOConnection.Execute instead of TADOQuery to execute your command-text, and then inspect if there is a valid Recordset returning from ADOConnection. In ADOConnection.OnExecuteComplete your could do something like this:

procedure TForm1.ADOConnection1ExecuteComplete(Connection: TADOConnection;
  RecordsAffected: Integer; const Error: Error;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
  // check for errors
  if Assigned(Error) then
  begin        
    Memo1.Lines.Add('Error: ' + Error.Description);
  end;
  // check for a valid recordset
  if Assigned(Recordset) then
  begin
    MyDataSet.Recordset := Recordset; // MyDataSet is TADODataSet
  end;
  // check for affected rows
  if RecordsAffected >= 0 then
    Memo1.Lines.Add('Records affected: ' + IntToStr(RecordsAffected))
  else
    Memo1.Lines.Add('Record count: ' + IntToStr(MyDataSet.RecordCount));
end;

Upvotes: 8

Related Questions