Passing ADO Connection from exe to dll 1

[PlutoDev]

I built my application with many dll (for reporting)

but when i load dll, I reconnect TAdoConnection.. I want to avoid this
reconnection.. but how to do this ??.

I have try passing parameter ADOConnectionObject :_Connection from exe to dll
so when i load dll,
i write dm.dbCon.ConnectionObject := AdoCon;

dm -> datamodule
dbcon -> TADOConnection
adoCon -> _Connection

it's succesfully. but after exit from dll, back to exe..

my connection is lost cause of FreeLibrary(dlllib)
before freeLibrary, i write dm.dbCon.ConnectionObject := Nil;

any sugesstion about this problem.

thank's in advance

 

[buho]

Please, post the involved parts of the real code.

buho (A).

 

[PlutoDev]

(exe)
program Project1;

uses
Forms,
Unit1 in 'Unit1.pas' {Form1},
dmdmain in '..\Common\dmdMain.PAS' {dmMain: TDataModule};

{$R *.res}

begin
Application.Initialize;
Application.CreateForm(TForm1, Form1);
Application.CreateForm(TdmMain, dmMain);
Application.Run;
end.


unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ADODb, Grids, DBGrids, DB, ExtCtrls;

type THandleFormDlg = Procedure(AppHandle : THandle; AdoCon : _Connection); stdcall;

type
TForm1 = class(TForm)
DataSource1: TDataSource;
ADOQuery1: TADOQuery;
Button1: TButton;
Panel1: TPanel;
DBGrid1: TDBGrid;
Label1: TLabel;
Label2: TLabel;
quAct: TADOQuery;
procedure Button1Click(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

uses dmdmain;

{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);
begin
dmMain.dbCon.Connected := True;
ADOQuery1.Active := True;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
dmMain.dbCon.Connected := False;
ADOQuery1.Active := False;
end;

procedure TForm1.Button1Click(Sender: TObject);
var Fn : Array[0..255] of Char;
P : THandleFormDlg;
AppLib: THandle;
FileName : ShortString;
FAdoCon : _Connection;
begin
FileName := 'Project2.dll';
StrPCopy(Fn,FileName);
If FileExists(FileName) then begin
With quAct, Sql do begin
Close; Clear;
Add('create table #test (V Varchar(10) Null)');
Add('Insert into #Test Values (''12345'')');
Add('Insert into #Test Values (''67890'')');
ExecSql;
end;

FAdoCon := dmMain.dbCon.ConnectionObject;

AppLib := LoadLibrary(Fn);
@P := GetProcAddress(AppLib,'ShowFormDlg');
P(Application.Handle, FAdoCon);

dmMain.dbCon.ConnectionObject := Nil;
FreeLibrary(AppLib); //connection lost after free library
dmMain.dbCon.ConnectionObject := FAdoCon; //not effect

If dmMain.dbCon.Connected then begin
ShowMessage('database is connected');
end else begin
ShowMessage('database is not connected after library, Why ???????');
end;
end else begin
ShowMessage('File '+FileName+' not found');
end;
end;

end.


(dll)
library Project2;

uses
SysUtils,
Classes,
ADODb,
Unit1 in 'Unit1.pas' {Form1},
dmdmain in '..\Common\dmdMain.PAS' {dmMain: TDataModule};

{$R *.res}


Procedure ShowFormDlg(AppHandle : THandle; AdoCon : _Connection); stdcall;
var Id : LongInt;
begin
Id := Show_FormDlg(AppHandle, AdoCon);
Close_FormDlg(Id);
end;

Exports ShowFormDlg;

begin

end.


unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, DBGrids, ExtCtrls, StdCtrls, DB, ADODB;

type
TForm1 = class(TForm)
ADOQuery1: TADOQuery;
DataSource1: TDataSource;
Button1: TButton;
Panel1: TPanel;
DBGrid1: TDBGrid;
Label1: TLabel;
Label2: TLabel;
procedure Button1Click(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
private
{ Private declarations }
public
{ Public declarations }
end;

Function Show_FormDlg(AppHandle : THandle; AdoCon : _Connection) : Integer;
Procedure Close_FormDlg(Id : Longint);

var
Form1: TForm1;

var TmpH : THandle;

implementation

uses dmdmain;

{$R *.dfm}

Function Show_FormDlg(AppHandle : THandle; AdoCon : _Connection) : Integer;
begin
TmpH := Application.Handle;
Application.Handle := AppHandle;
Application.CreateForm(TdmMain,dmMain);
dmMain.dbCon.ConnectionObject := AdoCon;

Application.CreateForm(TForm1, Form1);
Result := LongInt(Form1);
Form1.ShowModal;
end;

Procedure Close_FormDlg(Id : Longint);
begin
dmMain.dbCon.ConnectionObject := Nil;
Application.Handle := TmpH;
TForm1(Id).Free;
//dmMain.dbCon.Connected := False;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
Close;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
ADOQuery1.Active := True;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
ADOQuery1.Active := False;
end;

end.


(dmmain)
unit dmdmain;

interface

uses
SysUtils, Classes, DB, DBTables, ADODB, Dialogs, Controls;

type
TdmMain = class(TDataModule)
dbCon: TADOConnection;
private
{ Private declarations }
public
{ Public declarations }
end;

var
dmMain: TdmMain;

implementation

uses Math;

{$R *.dfm}

end.


dmmain.dfm
object dmMain: TdmMain
OldCreateOrder = False
Left = 330
Top = 181
Height = 176
Width = 245
object dbCon: TADOConnection
ConnectionString =
'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initi' +
'al Catalog=Northwind;Data Source=Hasan;Use Procedure for Prepare' +
'=1;Auto Translate=True;Packet Size=4096;Workstation ID=Jupiter;Use' +
' Encryption for Data=False;Tag with column collation when possib' +
'le=False'
LoginPrompt = False
Mode = cmShareDenyRead
Provider = 'SQLOLEDB.1'
Left = 16
Top = 16
end
end

 

[buho]

1) Are you aware you have two different dmMain datamodules? One is in the application (Project1) and another in the DLL (Project2). Is the code supposed to work this way?

2) In D6, ConnectionObject of TADOConnection is a readonly property, it changed in the Delphi version you are using or you stepped on a compiler bug?

Im going to try to streamline you code a bit and see what is going on. Please, answer the above questions in the while.

buho (A).

 

[buho]

Please, a third question: is your DLL going to work only with Delphi applications or you are developing it to work with non-Delphi ones too?

buho (A).

 

[buho]

Well... im posting here my checking code. It works well.

First click the "DLL Query" button to fire the query in the DLL, next the "APP Query" to fire another query in the app, using the same _Connection.

Note I'm not using SQL but Jet/Access.

See some comments below.

[b]APPLICATION MAIN FORM[/B]

[b][COLOR=red]{For clarity I'm not using the datamodule here but an ADOConnection
dropped in the form. It works on the same MDB as the datamodule.}[/color][/b]

unit ADFMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TFADAMain = class(TForm)
    appCon: TADOConnection;
    btnDLL: TButton;
    btnAPP: TButton;
    procedure btnDLLClick(Sender: TObject);
    procedure btnAPPClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FADAMain: TFADAMain;

implementation

{$R *.dfm}

{---------------------}
type
  TDLLQuery = procedure(AppHandle : THandle; Conn : _Connection);
  TDLLClose = procedure;
{---------------------}

{Fire the DLL Query.}
procedure TFADAMain.btnDLLClick(Sender: TObject);
  var
    LibHandle : THandle;
    DLLQuery  : TDLLQuery;
    DLLClose  : TDLLClose;
  begin
    {Start our ADOConnection.}
    appCon.Connected := True;
    {Load the DLL and get the pointers.}
    LibHandle := LoadLibrary('ADDLL.DLL');
    DLLQuery := GetProcAddress(LibHandle, 'OpenAndQuery');
    DLLClose := GetProcAddress(LibHandle, 'UnhookAndClose');
    {Call the DLL query.}
    DLLQuery(Application.Handle, appCon.ConnectionObject);
    {Close the DLL connection and unload.}
    DLLClose;
    [COLOR=red]{NOTE: it is taking and inordinate ammount of time
    to free the library.}[/color]
    FreeLibrary(LibHandle);
    {Change buttons.}
    btnDLL.Enabled := False;
    btnAPP.Enabled := True;
  end;

{Fire the app query.}
procedure TFADAMain.btnAPPClick(Sender: TObject);
  var
    DS    : TADODataSet;
    Total : integer;
  begin
    {Create and hook the dataset.}
    DS := TADODataset.Create(Self);
    DS.Connection := appCon;
    appCon.Connected := True;
    {Fire the query.}
    DS.CommandText := 'SELECT COUNT (*) AS Total FROM url';
    DS.Active := True;
    {!} // TRACE POINT
    Total := DS.FieldValues['Total'];
    Application.MessageBox(PChar('Total: ' + IntToStr(Total)), 'APP');
    {Free the dataset.}
    DS.Active := False;
    DS.Free;
  end;

end.

[b]MAIN FORM DFM[/b]

object FADAMain: TFADAMain
  Left = 207
  Top = 133
  Width = 112
  Height = 160
  Caption = 'FADAMain'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object btnDLL: TButton
    Left = 14
    Top = 72
    Width = 75
    Height = 25
    Caption = 'DLL Query'
    TabOrder = 0
    OnClick = btnDLLClick
  end
  object btnAPP: TButton
    Left = 14
    Top = 104
    Width = 75
    Height = 25
    Caption = 'APP Query'
    Enabled = False
    TabOrder = 1
    OnClick = btnAPPClick
  end
  object appCon: TADOConnection
    ConnectionString = 
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Works\_SearchSpa' +
      'ce_576_s_bio_ana\bus_576_s_bio_ana.mdb;Persist Security Info=Fal' +
      'se'
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 16
    Top = 24
  end
end

[b]DLL CODE[/b]

library ADDLL;
uses
  ADODB,
  Forms,
  {-}
  SysUtils,
  Classes,
  ADDM in 'ADDM.pas' {dmData: TDataModule};

{$R *.res}

{-----------------------}
var
  HandleStore : THandle;

{-----------------------}
procedure OpenAndQuery(AppHandle : THandle; Conn : _Connection);
  var
    DS    : TADODataSet;
    Total : integer;
  begin
    {Hook the app and create de datamodule.}
    HandleStore := Application.Handle;
    Application.Handle := AppHandle;
    Application.CreateForm(TdmData, dmData);
    [COLOR=red]{Supposedly ConnectionObject is readonly!!!}[/color]
    dmData.dmCon.ConnectionObject := Conn;
    {Create and hook the dataset.}
    DS := TADODataset.Create(dmData);
    DS.Connection := dmData.dmCon;
    dmData.dmCon.Connected := True;
    {Fire the query.}
    DS.CommandText := 'SELECT COUNT (*) AS Total FROM url';
    DS.Active := True;
    {!} // TRACE POINT
    Total := DS.FieldValues['Total'];
    Application.MessageBox(PChar('Total: ' + IntToStr(Total)), 'DLL');
    {Free the dataset.}
    DS.Active := False;
    DS.Free;
  end;

{-----------------------}
procedure UnhookAndClose;
  begin
    {This procedure frees the underlaying ConnectionObject.}
    Application.Handle := HandleStore;
    dmData.dmCon.ConnectionObject := Nil;
  end;

{-----------------------}
Exports OpenAndQuery;
Exports UnhookAndClose;
{-----------------------}

begin
end.

[b]DATAMODULE CODE[/b]
[b][COLOR=red]Datamodule used ONLY in the DLL[/color][/b]

unit ADDM;

interface

uses
  SysUtils, Classes, DB, ADODB;

type
  TdmData = class(TDataModule)
    dmCon: TADOConnection;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  dmData : TdmData;
  
implementation

{$R *.dfm}

end.

[b]DATAMODULE DFM[/b]
object dmData: TdmData
  OldCreateOrder = False
  Left = 652
  Top = 159
  Height = 150
  Width = 215
  object dmCon: TADOConnection
    ConnectionString = 
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Works\_SearchSpa' +
      'ce_576_s_bio_ana\bus_576_s_bio_ana.mdb;Persist Security Info=Fal' +
      'se'
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 64
    Top = 40
  end
end

For clarity, I'm not using the datamodule in the application but a TADOConnection I dropped in the form. The datamodule is used only in the DLL.

In D6 TADOConnection.ConnectionObject is a readonly property but I'm having no problem assigning and using it. Apparently you stepped on a compiler bug.

May be the difference is in the dbase driver. I'm using Jet/Access. Or may be it is in the parts of your code I've not reproduced/checked.

Play a little with the code and change it to better suit your scope (I'm a little lost about your scope/intentions) and let me know what happens.

buho (A).

 

[buho]

May be you need to add a
dmMain.dbCon.ConnectionObject := nil;
before the line
dmMain.dbCon.ConnectionObject := AdoCon;

The TADOConnection is creating a ConnectionObject in the constructor and nilling it in the destructor.

As you are changing the object, may be you are letting the old one floating around without freeing it. Not sure about how the object reference count is working here, but nilling it will do no harm.

buho (A).

 

[jgudnas]

just jumping into this one without really going through the code, but i do the same thing in my apps using ado.

i just pass a pointer to my TADOConnection to the dll using some sort of init funtion.
in the dll, i assign the connection to the adodatasources, etc. and use.
unloading the library doesnt do anything to the original connection.

type PTADOConnection = ^TADOConnection;


inside DLL:

procedure dllProvideConnection(conn TADOConnection); export;
begin

FViewJob.sproc.Connection := conn^;
FViewJob.sql1.Connection := conn^;
FViewJob.sqlds1.Connection := conn^;
FViewJob.sqlds2.Connection := conn^;
end;

 

[whosrdaddy]

when using ADO in a DLL or not the main VCL thread in a normal application, you must call Coinitialize(nil); (in the dll) to init COM. if you don't do this, you'll get AV's.

clean up with CoUninitialize;

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[shmilyca]

Hi Andrew,
Wow..Great code!! Do you think same functionality can be achieved by just using ADO & not using datamodule ? Can you perform DM operations using dll..like insert update? I tried it I get an error coinitiaize has not been initilized.
As always appriciate your help. thank you

 

[whosrdaddy]

read my last post...



-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[buho]

Weee... long forgotten thread...

That thing of assigning a value to a read-only property still makes me feel unconfortable.

Actually, I can't see the point in having a DLL doing queries (except may be in an extensible application).

Due to the slow pacing this forum is having, may be some user can (and will) sketch here a couple reasons to use a so bizarre approach. I think I'm missing something here.

TIA.
buho (A).

 

[shmilyca]

I did read your last post,tried to use initiaization section..it wont compile. What is good place to insert initilization section in sample code given by Andrew? I thought notmally it is at the end of unit.

 

[shmilyca]

Well..One of reason to use dll approach can be separating business logic, creating one more layer. I dont see real use unless you can able to perform data manupulation operations...

 

[buho]

1) Coinitialization is a per-thread issue.

2) Only due to the fact of calling code in a DLL you are not producing a thread context switching; the DLL function code will run in the caller context.

3) If you have threads, the right place to put the code is in your thread "Execute" method. Coinitializing in the initialization section will do no good, as the initialization section runs in the context of the main thread. As whosrdaddy said, don't forget un-coinitialization at end.

4) If you can't not compile putting the coinitialization call in the initialization section (in despite the call being useless) you have another problems: a) you are not "using" the unit which defines the coinitialization function (ActiveX unit) or b) you have a syntactical error.

5) As usual, saying things like "it wont compile" is not helping us to help you: why it is not compiling? what error the compiler is reporting?

6) In the code posted by buho, you don't need to coinitialize anywhere. All the code is running in the main thread and Delphi coinitializes (and un-coinitializes) the main thread as needed (based on the units you "use").

7) Lets clarify this a little more:

7.1) Main app using COM units with only one thread and calling functions in a DLL: you DO NOT need to coinitialize; Delphi will coinitialize the main thread automagically and all the DLL functions will run in the main thread.

7.2) Main app NOT using COM units with only one thread and calling functions in a DLL: you NEED to initialize somewhere (the main app or the DLL itself). As you are not using COM units in the app, Delphi can't guess you'll be using them in an external DLL and will not coinitialize automagically.

7.3) Main app using multiple threads or DLL using multiple threads: you NEED to coinitialize in a per-thread basis. The main thread will be (or will be not) coinitialized automagically (depending on the units you "using" in it) but all the other threads will need coinitialization.

8) Of course, if you are launching a thread in a DLL, you need to coinitialize that thread. But if you are not launching a thread, things are more obscure and arguable. Imagine some thread is calling one of your DLL functions. What thread it is? Is the thread coinitialized or not? And where you are going to coinitialize in your DLL? Not in the initialization, as you dont know what thread is loading the library and what thread will be calling the functions. In every function? May be, but you are paying a penalty coinitializing and un-coinitializing in every function call.

9) My opinion: a thread calling a function using COM needs to be aware it is calling a function using COM. If the function is in a DLL or not it does not matter. The caller thread is responsible to coinitialize, not the DLL code.

HTH.
buho (A).

 

[buho]

Well..One of reason to use dll approach can be separating business logic, creating one more layer. I dont see real use unless you can able to perform data manupulation operations...

Separating business logic is one thing, spliting functionality between the main application and a DLL is another.

What is the rationale here? One team working with the main app and other team working with the middleware in total isolation? So isolated they works in two different projects? Personally, I can think on better and easier ways to maintain the isolation.

And how you are going to return complex data back from the DLL? You can't return classes back from a DLL without some extra work (a run time package is another different beast).

Are you going to return an untyped pointer from the DLL and have the main app casting it to a recordset? It makes me creepy. What you are eventually gaining in isolation you are lossing (and then more) in robustness.

buho (A).

 

[shmilyca]

Well..Im new to delphi..so I dont know lot abt memory leaks n stuff.. i just read post from whosrdaddy it said you have to have Coinitialization. I tried it using in different sample i found on net and it didnt work. It gives me an error "Coinitialization not found or something". May be Im wrong abt using dll, you guys know better than me. I was just thiniking it loud and try to know better way of doing it.. becuase I thought it would be easy to maintain code if you split in differant layers...or may be i was thinking we can use it like plugin? if you find dll..you could run those features..I know you guys are best..so guide me..

 

[buho]

1) Daddy was making a point about a very special situation (see 7.2 in my post above). That situation is not related to the code posted here.

2) My advise for coinitialization is to put it in the threads which calls the function dealing with the COM object (the dbase).

3) Splitting code in different layers is a very good thing, but "layer" is not the same as "DLL". Your code will be easy to maintain if you have it well organized in only one project.

4) Of course, if you are thinking about and extensible application (with plug-ins) you need to resource to DLLs or run-time packages or COM objects. Think about it very seriously, returning Delphi classes from a DLL is not easy and make the code prone to errors and difficult to maintain. A real-time package or a COM object is the way here.

6) Same for a true three-layer system: create your middleware logic in something more palatable than a DLL. Anyway, the middleware is going to be accessed for multiple clients, diseminating DLLs for every client (may be geographically sparsed) when the middleware changes is against the three-layer philosophy.

buho (A).

 

[shmilyca]

Ok..Thank you for your help...I appriciate it.!!