Unit for fast preparing and executing SQL scripts

GAF writes “Design time application development is good for small or middle projects. But when count of forms in the project is more than 10 and number of queries on each form is more than 3 it’s too hard to easy modify application. My unit can be helpful for creating and executing SQL scripts in runtime level but not in design time.

Why you need for each (even simple) sql queries make Query on form? Each time set published properties… If changed just ONE option but for all queries it needs to modify all queries at all forms :(.
Published properties sometimes change their values themselfs. For example, after component reinstall.It’s more easy to modify it in units. Default properties for all queries specify one time… With runtime scripts it’s easy to goes to then next versions of ZeosDBO and other.

With each database structure modification it needs to modify SQL scripts in application. How you are finding what you need to correct? I have use for it Search in TotalCommander in application folder. I can’t use Delphi option Find in Files… because it don’t find information in dfm files.
If all scripts stores in pas units then it possible to
a) easy find information with Delphi option Find in Files…
b) easy work with CVS (for your own needs or for collaborate working with another developers)



Examples of using:
FastSQLInt(‘select my_val from my_tbl where id=’+IntToStr(ID));
InsertFromControls(‘my_tbl’, [‘id’, ‘val1’, ‘val2’], [ID, edName.Text, StrToInt(edVal.Text)]]);
UpdateFromControls(‘my_tbl’, ‘id’, ID, [‘val1’, ‘val2’], [edName.Text, StrToInt(edVal.Text)]]);

As you can see, it’s possible even easy insert data, update data or delete. Structure of SQL statements in pas units like usual scripts in database tools.

Queries with using procedures from this unit are executes more fast because they use DBC procedures calling (not DBAware). Perfomance growing easy visible in applications with big count of queries. For example, if you need to insert 1000 records in one table.

One of the big limitation of using this unit: with it not possible to work with DBAware controls and components like DBEdit, DBLookup, DBGrid, etc. For each of them you still need use design time queries.

Or you can prepare additional procedures what will prepare datasources for each query and set some DBAware controls properties… I do so. For all lookups I have setup their properties via one addtional procedure. I am not using DBGrids because I am using VirtualTreeView’s. All data there can be displayed in tree view. But this is subject of another story :).



unit FastQuery;

interface

uses
SysUtils, ZAbstractRODataset, ZAbstractDataset, ZDataset, ZConnection,
Variants, DB, Classes, ZMessages, ZVariant, ZDbcResultSet, ZDbcIntfs,
ZDbcStatement;

type
fSQLScript = string;


{Create ZQuery with SQLscript but don’t open it. Needs to be free.}
function CreateTempQuery(SQLscript: fSQLscript; Owner: TComponent = nil): TZQuery;

{Create ZQuery with SQLscript and open it. Needs to be free.}
function GiveMeResultSET(SQLscript: fSQLscript): TZQuery;

{Check for empty}
function CheckExistence(SQLscript: fSQLscript): boolean;

{Get first value from resultset after opening SQLscript}
function FastSQLInt(SQLscript: fSQLscript): integer;
function FastSQLFlt(SQLscript: fSQLscript): double;
function FastSQLStr(SQLscript: fSQLscript): string;
function FastSQLVal(SQLscript: fSQLscript): Variant;

{Execute SQL script and return result of execution}
function ExecuteSQL(SQLscript: fSQLscript): boolean;

{ Execute a SQL query }
function ExecSql(Query: TZQuery; Sql: string): Boolean;

{For closing and opening query. Also move cursor to another place
Pos – prior = -1, don’t move = 0, next = 1}
procedure ReQuery(Query: TZQuery; Pos: integer = 0);

procedure Commit;
procedure Rollback;

{Prepare query and insert Values into Fields of table with cpecified TableName
Length(Fields) must be equal to Length(Values)}
function InsertFromControls(TableName: string;
Fields: array of string; Values: array of variant):boolean;

{Prepare query and update Fields in specified table with TableName.
New values must be specified in array Values}
function UpdateFromControls(TableName: string;
IDField : string; IDValue: variant;
Fields: array of string; Values: array of variant):boolean;

{Delete all rows from TableName where field with name IDField equal IDValue}
function DeleteFromControls(TableName: string;
IDField : string; IDValue: variant):boolean;

function VarToSQLStr(const Value: variant): string;

var
Con: TZConnection; //This var must be a link to default application connection
Debug: boolean = False; //If debug=true then all raises will be on screen.

implementation

procedure Commit;
begin
Con.Commit;
end;

procedure Rollback;
begin
Con.Rollback;
end;

function CreateTempQuery(SQLscript: fSQLscript; Owner: TComponent = nil): TZQuery;
begin
Result:= TZQuery.Create(Owner);
with Result do
begin
Connection:= Con;
RequestLive:= False;
SQL.Text := SQLscript;
// My default options for all queries. You can modify it for yourself.
Options:=[];
Properties.Add(‘defaults=no’);
end;
end;

function ExecuteSQL(SQLscript: fSQLscript): boolean;
var Stat: IZPreparedStatement;
begin
Stat:=Con.DbcConnection.PrepareStatement(SQLscript);
try
Stat.ExecutePrepared;
Result := True;
except
on E: Exception do
begin
raise;
end;
end;
end;

function CheckExistence(SQLscript: fSQLscript): boolean;
// True – not empty, False – empty
begin
with GiveMeResultSET(SQLscript) do
try
Result := NOT EOF;
finally
Close;
Free;
end;
end;

function GiveMeResultSET(SQLscript: fSQLscript): TZQuery;
begin
Result := CreateTempQuery(SQLscript);
with Result do
try
Open;
except
on E: Exception do
begin
FreeAndNil(Result);
raise;
end;
end;
end;

function FastSQLInt(SQLscript: fSQLscript): integer;
var Stat: IZPreparedStatement;
Rezs: IZResultSet;
begin
Stat:=Con.DbcConnection.PrepareStatement(SQLscript);
try
Rezs:=Stat.ExecuteQueryPrepared;
Rezs.Next;
Result:=Rezs.GetInt(1);
except
on E: Exception do
begin
Result:= 0;
if Debug and (E.MessageSRowDataIsNotAvailable) then
raise;
end;
end;
end;

function FastSQLFlt(SQLscript: fSQLscript): double;
var Stat: IZPreparedStatement;
Rezs: IZResultSet;
begin
Stat:=Con.DbcConnection.PrepareStatement(SQLscript);
try
Rezs:=Stat.ExecuteQueryPrepared;
Rezs.Next;
Result:=Rezs.GetDouble(1);
except
on E: Exception do
begin
Result:= 0;
if Debug and (E.MessageSRowDataIsNotAvailable) then
raise;
end;
end;
end;

function FastSQLStr(SQLscript: fSQLscript): string;
var Stat: IZPreparedStatement;
Rezs: IZResultSet;
begin
Stat:=Con.DbcConnection.PrepareStatement(SQLscript);
try
Rezs:=Stat.ExecuteQueryPrepared;
Rezs.Next;
Result:=Rezs.GetString(1);
except
on E: Exception do
begin
Result:= ”;
if Debug and (E.MessageSRowDataIsNotAvailable) then
raise;
end;
end;
end;

function FastSQLVal(SQLscript: fSQLscript): Variant;
var Stat: IZPreparedStatement;
Rezs: IZResultSet;
begin
Stat:=Con.DbcConnection.PrepareStatement(SQLscript);
try
Rezs:=Stat.ExecuteQueryPrepared;
Rezs.Next;
Result:=EncodeVariant(Rezs.GetValue(1));
except
on E: Exception do
begin
Result:= null;
if Debug and (E.MessageSRowDataIsNotAvailable) then
raise;
end;
end;
end;

{ Execute a SQL query }
function ExecSql(Query: TZQuery; Sql: string): Boolean;
begin
if Trim(Sql) = ” then
begin
Result:=False;
Exit;
end;

Query.Close;
try
Query.Sql.Text:=sql;
Query.ExecSQL;
Result := True;
except
on E: Exception do
begin
raise;
end;
end;
end;

procedure ReQuery(Query: TZQuery; Pos: integer = 0);
var Bookmark: TBookmark;
NeedBookmarkFree: boolean;
begin
NeedBookmarkFree:=True;

Bookmark:=nil;
if Assigned(Query) then
begin
if Query.Active then
begin
if Pos= -1 then Query.Prior;
Bookmark:= Query.GetBookmark;
Query.Close;
end;

Query.Open;

try
if (Bookmarknil) and Query.Active then
begin
if not (Query.Eof and Query.Bof) then
Query.GotoBookmark(Bookmark);
if Pos= 1 then
Query.Next;
end;
except
on E:Exception do
begin
if E.MessageSBookmarkWasNotFound then
begin
if Assigned(Bookmark) then
Query.FreeBookmark(Bookmark);
end
else
NeedBookmarkFree:=False;
end;
end;

if Assigned(Bookmark) and NeedBookmarkFree then
Query.FreeBookmark(Bookmark);
end;
end;

function UpdateFromControls(TableName: string; IDField : string;
IDValue: variant; Fields: array of string;
Values: array of variant):boolean;
var Stat: IZPreparedStatement;
upd, flds, whe, s: string;
i: integer;
begin
Result:=False;
// Arrays Fields and Values must have equal lengths
if Length(Fields) Length(Values) then Exit;

upd:=’Update ‘ + TableName;

flds:=’Set ‘;
s:=”;
for i:=0 to Length(Fields)-1 do
begin
flds:=flds + s + Fields[i] + ‘=’ + VarToSQLStr(Values[i]);
s:=’,’;
end;

whe:=’Where ‘ + IDField + ‘=’+VarToSQLStr(IDValue);

try
Stat:=Con.DbcConnection.PrepareStatement(upd+#13#10+flds+#13#10+whe);
Stat.ExecutePrepared;
Result:=True;
except
on E: Exception do
begin
if Debug then
E.Message:=E.Message+#13#10+upd+
#13#10+flds+
#13#10+whe;

raise;
end;
end;
end;

function DeleteFromControls(TableName: string;
IDField : string; IDValue: variant):boolean;
var Stat: IZPreparedStatement;
del, whe: string;
begin
del:=’Delete from ‘ + TableName;
if IDValuenull then
whe:=’Where ‘ + IDField + ‘=’ + VarToSQLStr(IDValue)
else
whe:=’Where ‘ + IDField + ‘ is ‘ + VarToSQLStr(IDValue);

Stat:=Con.DbcConnection.PrepareStatement(del+#13#10+whe);

try
Stat.ExecutePrepared;
Result:=True;
except
on E: Exception do
begin
if Debug then
E.Message:=E.Message+#13#10+del+
#13#10+whe;
raise;
end;
end;
end;

function InsertFromControls(TableName: string;
Fields: array of string; Values: array of variant):boolean;
var Stat: IZPreparedStatement;
ins, flds, vals, s, d: string;
i: integer;
begin
Result:=False;
// Arrays Fields and Values must have equal lengths
if Length(Fields) Length(Values) then
Exit;

ins:=’Insert into ‘ + TableName;

flds:='(‘;
s:=”;
for i:=0 to Length(Fields)-1 do
begin
flds:=flds + s + Fields[i];
s:=’,’;
end;
flds:=flds+’)’;

vals:=’values (‘;
d:=”;
for i:=0 to Length(Fields)-1 do
begin
s:=VarToSQLStr(Values[i]);
vals:=vals + d + s;
d:=’,’;
end;
vals:=vals+’)’;

try
Stat:=Con.DbcConnection.PrepareStatement(ins+#13#10+flds+#13#10+vals);
Stat.ExecutePrepared;
Result:=True;
except
on E: Exception do
begin
if Debug then
begin
E.Message:=E.Message+#13#10+ins+
#13#10+flds+
#13#10+vals;
end;

raise;
end;
end;
end;

function VarToSQLStr(const Value: variant): string;
var i: integer;
begin
Result:=”;
if VarIsClear(Value) then
begin
Result:=’null’;
end
else
begin
if VarIsNull(Value) then
Result:=’null’
else
begin
case VarType(Value) of
varSmallInt,
varInteger,
varShortInt,
varByte,
varWord,
varLongWord,
varInt64: Result:=IntToStr(Value);
varSingle,
varDouble,
varCurrency: Result:=Q_ReplaceStr(FloatToStr(Value),DecimalSeparator,’.’);
varDate: Result:=QuotedStr(VarToStr(Value));
varBoolean:
begin
if Value then i:=1 else i:=0;
Result:=IntToStr(i);
end;
varString: Result:=QuotedStr(Value);
else
raise Exception.Create(‘Unknown type’);
end;
end;
end;
end;

end.

This entry was posted in Code Hacks. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *