Mega Code Archive
How to deal with OLE DB directly, using its interfaces
<![CDATA[
Title: How to deal with OLE DB directly, using its interfaces
Question: An example for how to deal with OLE DB directly, using its interfaces.
Answer:
Sometimes we need to deal with OLE DB directly without using data aware components. Interfaces provided by Microsoft allow us work with them easily. And Borland has translates them into Delphi in ADOINT.PAS. Because I love Interbase/Firebird then I try to search the Internet for free Interbase OLE DB provider. And I found SIBProvider, here are the information about it (I have no relationship with SIBProvider).
SIBPROvider - Interbase OLE DB Provider v. 1.00
Web site: www.sibprovider.com
Developed by: Marcio Wesley Borges
e-mail: marciowb@technologist.com
ICQ#: 105147566
This OLE DB Provider works fine for my example but didnt work for ADOExpress and I dont know why. OK enough talking heres the source code. Thank you to delphi3000 admins, you can download the source code at the link above.
unit ufrmProject;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ExtCtrls, Grids, ADOInt;
type
TfrmProject = class(TForm)
pnlRight: TPanel;
pnlLeft: TPanel;
strgrdMaster: TStringGrid;
spltrMiddle: TSplitter;
strgrdDetail: TStringGrid;
btnPopulate: TButton;
btnCommit: TButton;
btnRollback: TButton;
btnAppendMstr: TButton;
btnDeleteMstr: TButton;
procedure FormCreate(Sender: TObject);
procedure btnPopulateClick(Sender: TObject);
procedure strgrdMasterSelectCell(Sender: TObject; ACol, ARow: Integer;
var CanSelect: Boolean);
procedure strgrdMasterSetEditText(Sender: TObject; ACol, ARow: Integer;
const Value: String);
procedure btnCommitClick(Sender: TObject);
procedure btnRollbackClick(Sender: TObject);
procedure btnAppendMstrClick(Sender: TObject);
procedure btnDeleteMstrClick(Sender: TObject);
private
{ Private declarations }
public
cmd:_Command;
con:_Connection;
rs:_RecordSet;
proj_id:string;//last row project id
mstLastRow:integer;
mstEdits:array of boolean;//which columns of last row are edited
end;
var
frmProject: TfrmProject;
implementation
{$R *.DFM}
procedure TfrmProject.FormCreate(Sender: TObject);
begin
//create instances using their factories
con:=CoConnection.Create;
cmd:=CoCommand.Create;
rs:=CoRecordSet.Create;
with con do
begin
Provider:='sibprovi.SIBProvider';
ConnectionString:='Data Source=C:\Temp\Employee.gdb;Location=localhost:;Extended Properties="";Persist Encrypted=True;Encrypt Password=True;Mask Password=True;Cache Authentication=True;Persist Security Info=True;CHARACTER SET="";ROLE=""';
Open(ConnectionString,'sysdba','masterkey',0);
BeginTrans;
end;
cmd.Set_ActiveConnection(con);
strgrdMaster.ColWidths[0]:=20;
strgrdDetail.ColWidths[0]:=20;
//make last row invalid
mstLastRow:=-1;
end;
procedure TfrmProject.btnPopulateClick(Sender: TObject);
var
i,j,upr:integer;
recAfctd:OleVariant;
flag:boolean;
begin
with cmd do
begin
CommandText:='select a.*,b.full_name from project a left join employee b on b.emp_no=a.team_leader';
rs:=Execute(recAfctd,EmptyParam,0);
end;
with rs,strgrdMaster do
begin
//clean up the grid's row
RowCount:=2;
//no column has edited
SetLength(mstEdits,Fields.Count);
ColCount:=Fields.Count+1;
upr:=Fields.Count-1;
//set column's name
for i:=0 to upr do
begin
mstEdits[i]:=false;
Cells[i+1,0]:=Fields[i].Name;
Cells[i+1,1]:='';
end;
i:=0;
while not Eof do
begin
inc(i);
RowCount:=i+1;
for j:=0 to upr do
if VarIsNull(Fields[j].Value) then
Cells[j+1,i]:=''
else
Cells[j+1,i]:=Fields[j].Value;
MoveNext;
end;
Close;
end;
//populate detail by imitating select cell
if i0 then
with strgrdMaster do
strgrdMasterSelectCell(strgrdMaster,1,1,flag);
end;
procedure TfrmProject.strgrdMasterSelectCell(Sender: TObject; ACol,
ARow: Integer; var CanSelect: Boolean);
var
i,j,upr:integer;
recAfctd:OleVariant;
anyEdit:boolean;
updStmt,temp:string;
grid:TStringGrid;
begin
//row change detected
if (ARowmstLastRow)and(ARow0) then
begin
anyEdit:=false;
grid:=TStringGrid(Sender);
upr:=grid.ColCount-2;
//are there any edits
for i:=0 to upr do
if mstEdits[i] then
begin
anyEdit:=true;
break;
end;
if anyEdit then
begin
//it has been updated
if proj_id'' then
begin
//bla, bla, bla and presto here comes the update statement
updStmt:='update project set ';
temp:='';
for i:=0 to upr do
if mstEdits[i] then
begin
if temp'' then
temp:=temp+',';
temp:=temp+grid.Cells[i+1,0]+'=:'+grid.Cells[i+1,0];
end;
updStmt:=updStmt+temp;
updStmt:=updStmt+' where proj_id=:proj_id';
with cmd do
begin
CommandText:=updStmt;
Parameters.Refresh;
for i:=0 to upr do
if mstEdits[i] then
begin
Parameters[grid.Cells[i+1,0]].Value:=grid.Cells[i+1,mstLastRow];
mstEdits[i]:=false;
end;
Parameters['proj_id'].Value:=proj_id;
Execute(recAfctd,EmptyParam,0);
end;
end
//it has been inserted
else
begin
//generate insert statement
updStmt:='insert into project (';
temp:='';
for i:=0 to upr do
if mstEdits[i] then
begin
if temp'' then
temp:=temp+',';
temp:=temp+':'+grid.Cells[i+1,0];
end;
updStmt:=updStmt+StringReplace(temp,':','',[rfReplaceAll])+') values (';
updStmt:=updStmt+temp+')';
with cmd do
begin
CommandText:=updStmt;
Parameters.Refresh;
for i:=0 to upr do
if mstEdits[i] then
begin
Parameters[grid.Cells[i+1,0]].Value:=grid.Cells[i+1,mstLastRow];
mstEdits[i]:=false;
end;
Execute(recAfctd,EmptyParam,0);
end;
end
end;
//get new project id
proj_id:=TStringGrid(Sender).Cells[1,ARow];
//populate detail using new project id
with cmd do
begin
CommandText:='select a.*,b.full_name from employee_project a,employee b where a.proj_id=:proj_id and b.emp_no=a.emp_no';
Parameters.Refresh;
Parameters['proj_id'].Value:=proj_id;
rs:=Execute(recAfctd,EmptyParam,0);
end;
with rs,strgrdDetail do
begin
RowCount:=2;
ColCount:=Fields.Count+1;
upr:=Fields.Count-1;
for i:=0 to upr do
begin
Cells[i+1,0]:=Fields[i].Name;
Cells[i+1,1]:='';
end;
i:=0;
while not Eof do
begin
inc(i);
RowCount:=i+1;
for j:=0 to upr do
if VarIsNull(Fields[j].Value) then
Cells[j+1,i]:=''
else
Cells[j+1,i]:=Fields[j].Value;
MoveNext;
end;
Close;
end;
mstLastRow:=ARow;
end;
end;
procedure TfrmProject.strgrdMasterSetEditText(Sender: TObject; ACol,
ARow: Integer; const Value: String);
begin
//a cell is edited
mstEdits[ACol-1]:=true;
end;
procedure TfrmProject.btnCommitClick(Sender: TObject);
begin
with con do
begin
CommitTrans;
BeginTrans;
end;
end;
procedure TfrmProject.btnRollbackClick(Sender: TObject);
begin
with con do
begin
RollbackTrans;
BeginTrans;
end;
end;
procedure TfrmProject.btnAppendMstrClick(Sender: TObject);
begin
strgrdMaster.RowCount:=strgrdMaster.RowCount+1;
end;
procedure TfrmProject.btnDeleteMstrClick(Sender: TObject);
var
i,upr:integer;
recAfctd:OleVariant;
begin
with cmd do
begin
CommandText:='delete from project where proj_id=:proj_id';
Parameters.Refresh;
Parameters['proj_id'].Value:=proj_id;
Execute(recAfctd,EmptyParam,0);
end;
with strgrdMaster do
begin
upr:=ColCount-1;
for i:=1 to upr do
Cells[i,mstLastRow]:='';
end;
end;
end.
]]>