If you write database software, you can admit that it's not an easy thing to drop tquerys (or tsimpledataset) components every time you must run a SQL query. It has to be a quicker way!

Sometimes, in a large database application, you must run small SQLs in order to continue your business logic. In a regular BDE environment, you must drop a TQUERY component, write the SQL code in it and then write

Query1.Close;
set parameters, blah, blah, blah
Query1.Open;

Do you agree that this is not very clever and this technic breaks your logic apart ? In other IDEs, like Oracle Developer 2000, you write your SQL directly on your code. Imagine this code:

  InvoiceTotal := IntegerSQL('SELECT SUM(TOTAL) FROM INVOICE WHERE SQINVOICE = %d',[ InvoiceNumber ]);

or

  SupplierName := StringSQL('SELECT NAME FROM SUPPLIER WHERE SUPPLIERCODE = ''%s''',[ SupplierCode ]);

or either

  SQLExec('UPDATE INVOICE SET TYPE = ''%s'' WHERE SQINVOICE = %d',
          [ InvType, InvoiceNumber ]);

So I wrote this simple unit in order to easy my work days:

unit BDELib;

interface

uses Classes, SysUtils, DbiProcs, DbiTypes,
     Db, BDE, DbTables, DbConsts, StdLib, TypInfo;

procedure SetMaxRows( Qry: TQuery; const Rows: longint );

Function BooleanSQL(const SQLCommand: string;
                    const Args: array of const;
                    const DBName : string = '') : boolean;

function SQLCursor( const SQLCommand:String;
                    const Args: array of const;
                    const DbName: String = ''): TQuery;

function SQLRestrictRows( const SQLCommand:String;
                          const Args: array of const;
                          const MaxRow: longint;
                          const DBName : string = '' ): TQuery;

function IntegerSQL( const SQLCommand: String;
                     const Args: array of const;
                     const DBName : string = '' ):longint;

function StringSQL( const SQLCommand: String;
                    const Args: array of const;
                    const DBName : string = '' ):String;

function NumberSQL( const SQLCommand: String;
                    const Args: array of const;
                    const DBName : String = '' ): double;

procedure SQLExec( SQLCommand: String;
                   const Args: array of const );

var MainDatabase: TDatabase;

implementation

function SQLCursor( const SQLCommand:String;
                    const Args: array of const;
                    const DbName: string ): TQuery;

var QueryTemp: TQuery;
    rslt: DbiResult;

begin

  QueryTemp := TQuery.Create( nil );
  with QueryTemp do
  try

    DatabaseName := IfDefault(DbName,'',MainDatabase.DatabaseName);

    if GlobalMaxRows > 0 then
      begin
        rslt := DbiValidateProp( hDBIObj( Handle ), curMAXROWS, True);
        if (rslt = DBIERR_NONE) then
           Check( DbiSetProp( hDBIObj( Handle ), curMAXROWS, GlobalMaxRows ));
      end;

    SQL.Add( Format( UpperCase(SQLCommand), Args) );
    Open;
    Result := QueryTemp;
  except
    on E: Exception do
    begin
      QueryTemp.Free;
      raise;
    end;
  end;
end;

function SQLRestrictRows( const SQLCommand:String;
                          const Args: array of const;
                          const MaxRow: longint;
                          const DbName: string ): TQuery;
begin
  try
    GlobalMaxRows := MaxRow;
    Result := SQLCursor( SQLCommand, Args, DbName );
  finally
    GlobalMaxRows := 0;
  end;
end;

function BooleanSQL(const SQLCommand:String;
                    const Args: array of const;
                    const DbName: string ):boolean;
begin
  with SQLRestrictRows(SQLCommand, Args, 1, DbName) do
    try
      Result := not Eof;
    finally
      Free;
    end;
end;

function IntegerSQL( const SQLCommand: String;
                     const Args: array of const;
                     const DbName: string ):longint;
begin
  with SQLRestrictRows(SQLCommand, Args,1, DbName) do
   try
     if Eof or Fields[0].IsNull then
        Result := 0
     else
        Result := Fields[ 0 ].AsInteger;
   finally
     Free;
   end;
end;

function StringSQL( const SQLCommand: String;
                     const Args: array of const;
                     const DbName: string ):String;
begin
  with SQLRestrictRows(SQLCommand, Args,1, DbName) do
   try
     if Eof or Fields[0].IsNull then
        Result := ''
     else
        Result := Fields[ 0 ].AsString;
   finally
     Free;
   end;
end;

function NumberSQL(const SQLCommand: String;
                     const Args: array of const;
                     const DBName : string ): double;
begin
  with SQLRestrictRows(SQLCommand, Args,1, DBName) do
  try
    if Eof or Fields[0].IsNull then
       Result := 0
    else
       Result := Fields[ 0 ].AsFloat;
  finally
    Free;
  end;
end;

procedure SQLExec( SQLCommand: String;
                   const Args: array of const );
begin
  SQLCommand := Format(SQLCommand, Args);
  Check( DbiQExecDirect( MainDatabase.Handle, qryLangSQL,
                         PChar( SQLCommand ), nil) );
end;

end;

Note 1: The variable MainDatabase is just a shortcut - in order to use it, assign your current database to it before start any call. But this  technic is not wise if you work with more than one session opened.

Note 2: The RestrictedRows parameter just work for Oracle driver.
Note 3: This techinque can easily be tranlated to DbExpress or any other engine.

Health and Freedom,
Josir Gomes