Title: SQL without burocracy
Question: 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!
Answer:
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