Why parameters are good
References
SQL query components
IBQuery | Allows both Select (via Open) and non-Select SQL (via ExecSQL) statements |
IBUpdateSQL | Allows only non-Select SQL statements - Should be linked to a TIBQuery component via the TIBQuery.UpdateObject property |
IBDataSet | Allows both Select and non-Select SQL statements - to prepare a query, a select statement is required and all the non-null non-Select SQL statements are also prepared. Each SQL type is implemented via an automatically provided IBSQL component. These must be individually accessed to run their queries. This component provides a way to add several paramertized queries to an application using a single component. |
IBSQL | Unidirectional with no interface to data-aware controls - each IBDataSet includes 4 of these. |
Example Code for IBQuery
procedure TBasicDataModule.ExecuteSQL(s: string); begin IBQuery_Basic.SQL.Text := s; IBQuery_Basic.ExecSQL; IBQuery_Basic.SQL.Text := 'commit'; // lets other users see the changes IBQuery_Basic.ExecSQL; end; // ExecuteSQL procedure TBasicDataModule.ExecuteSelectSQL(s: string); begin IBQuery_Basic.Close; IBQuery_Basic.SQL.Text := s; IBQuery_Basic.Open; end; // ExecuteSelectSQL
Example Code for IBDataSet
For test purposes, I have a form with 2 Memo fields and 2 buttons. In general
IBDataSet1.QModify.ExecQuery;
procedure TForm1.Select_UIButtonClick(Sender: TObject); begin IBDataSet1.Active := false; IBDataSet1.SelectSQL.Text := Select_UIMemo.Text; IBDataSet1.Prepare; IBDataSet1.Active := true; end; procedure TForm1.Modify_UIButtonClick(Sender: TObject); begin IBDataSet1.Active := false; IBDataSet1.ModifySQL.Text := Modify_UIMemo.Text; IBDataSet1.Prepare; IBDataSet1.Active := true; IBDataSet1.QModify.ExecQuery; end; Select query Select * from "SPECTRA" where "Flag" is not null Modify query update "SPECTRA" SET "X0"=190 where "Flag" is not null and "WHICH" = 'NSF-0005-13'Apparently, simply toggling active causes the select query to be executed again.
Examples of different types of SQL statements
CREATE DOMAIN "Domain_ShortString_IDs" As VARCHAR(5) ; / * generic IDs * / CREATE DOMAIN "Domain_Short_Descriptions" AS VARCHAR(50); / * Name and Type in LU tables * / CREATE TABLE "Data_Type_LU" ( "Data_Type_ID" "Domain_ShortString_IDs" NOT NULL, "Data_Type_Text" "Domain_Short_Descriptions", "Comment" VARCHAR(200) ); /* deletes all the records in the table */ Delete From "Data_Type_LU" ; Delete From "Data_Type_LU" where "Data_Type_ID" = 'IR' ; insert into "Data_Type_LU" ("Data_Type_ID", "Data_Type_Text", "Comment") values ('IR', 'IR Spectra','This data is stored as a JCAMP file'); UPDATE "Data_Type_LU" SET "Data_Type_Text"='IR Spectra', "Comment"='New comment' WHERE ("Data_Type_ID"= 'IR') /* increments the generator, use 0 to just read it */ SELECT GEN_ID ( "Generator_Name", 1 ) FROM RDB$DATABASEFor many queries, I place the final semicolon (;) on a separate line. In Delphi, it is never required - it is required as a command delimiter in ISQL and SQL file parsers. Author: Robert Clemenzi - clemenzi@cpcug.org