Solved

COM for database on server

Posted on 2000-04-03
22
230 Views
Last Modified: 2010-04-04
{*

Hi COM-experts,

This is generated source of a COM object in an ActiveX library. Description:
Database object for SQL Server or other database.

I want to place this dll on a server and have clients call it's methods; they can retrieve data (by sql, / stored proc.), get restult sets and perhaps see life result sets.

How could I simply change thise wizard generated framework, for an optimal working object? Or at least a more optimal.

Also; with different users and perhaps lot's of updates after eachother, what about the apartment threading model?

I know this could be a too big question, but I'll settle for good practical Delphi-COM-hints for this situation. I do have some COM experience.

Thanks,
Floris.
*}

unit Unit1;

interface

uses
  Windows, ActiveX, Classes, ComObj, Project2_TLB, StdVcl;

type
  TSQLServerComObject = class(TTypedComObject, ISQLServerComObject)
  protected
    function GetRecord(out record_: WideString): HResult; stdcall;
    function Next: HResult; stdcall;
    function openDB: HResult; stdcall;
    function openTable: HResult; stdcall;
    {Declare ISQLServerComObject methods here}
  end;

implementation uses ComServ;

function TSQLServerComObject.GetRecord(out record_: WideString): HResult;
begin end;

function TSQLServerComObject.Next: HResult;
begin end;

function TSQLServerComObject.openDB: HResult;
begin end;

function TSQLServerComObject.openTable: HResult;
begin end;

initialization
  TTypedComObjectFactory.Create(ComServer, TSQLServerComObject, Class_SQLServerComObject,
    ciMultiInstance, tmApartment);
end.
0
Comment
Question by:florisb
  • 13
  • 8
22 Comments
 
LVL 13

Expert Comment

by:Epsylon
ID: 2681481
I don't understand what you are asking here but I'll give it a try.

You could use Delphi's RemoteDataModule object (Delphi's DCOM variant). That looks something like this:


unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Classes, ComServ, ComObj, VCLCom, DataBkr,
  DBClient, Project1_TLB, StdVcl;

type
  TSQLServerComObject = class(TRemoteDataModule, ISQLServerComObject)
  private
    { Private declarations }
  protected
    class procedure UpdateRegistry(Register: Boolean; const ClassID, ProgID: string); override;
    procedure GetRecord(out record_: WideString); safecall;
    procedure Next; safecall;
    procedure openDB; safecall;
    procedure openTable; safecall;
  public
    { Public declarations }
  end;

implementation

{$R *.DFM}

class procedure TSQLServerComObject.UpdateRegistry(Register: Boolean; const ClassID, ProgID: string);
begin
  if Register then
  begin
    inherited UpdateRegistry(Register, ClassID, ProgID);
    EnableSocketTransport(ClassID);
    EnableWebTransport(ClassID);
  end else
  begin
    DisableSocketTransport(ClassID);
    DisableWebTransport(ClassID);
    inherited UpdateRegistry(Register, ClassID, ProgID);
  end;
end;

procedure TSQLServerComObject.GetRecord(out record_: WideString);
begin end;

procedure TSQLServerComObject.Next;
begin end;

procedure TSQLServerComObject.openDB;
begin end;

procedure TSQLServerComObject.openTable;
begin end;

initialization
  TComponentFactory.Create(ComServer, TSQLServerComObject,
    Class_SQLServerComObject, ciMultiInstance, tmApartment);
end.



But more important is how do you implement the database control. For instance, Delphi has components to transport record from the server to the client and visa versa. TClientDataSet and TDataSetProvider can take care of that. This way you won't need the four methods you have right now.

The threading model depends on how the database is implemented but you can change the threading model at any time if necessary. I suggest to leave it as it is right now.

Epsylon.
0
 
LVL 1

Expert Comment

by:xsoft
ID: 2691383
What do you mean by:
How could I simply change thise wizard generated framework, for an optimal working object? Or at least a more optimal

I am new to experts exchange, so I do not about the policies here. Is it the usual way, that one provides a more or less empty unit and asks somebody else for a complete implementation? Please, don't get me wrong, but I am just trying to find out where exactly your problem is. Which parts of the implemantation are those on which you need help?
Which version of Delphi do you use ?
3,4,5, Pro or Enterprise...
0
 
LVL 2

Author Comment

by:florisb
ID: 2695745
Epsylon & xsoft; thanks...

Bit weird question maybe; I'll explain.

I worked a bit with COM before, and I'm creating a COM object now, that will be placed on an application server, for a time management programm; 50-200 users and lot's of database-update's because of a time-clock that's linked.

I just want to use the wizard generated framework and am looking for some ways to change this standard for faster / better access.

Thanks,
Floris.
0
 
LVL 1

Accepted Solution

by:
xsoft earned 151 total points
ID: 2695917
Hi Floris,

I don't exactly know, what you mean by a time management programm. But if it deals with collecting the check-in and check-out times for the employees of a company and processing those dates later for salaries etc. then I would recommend the you don't implement methods like Open.., GetRecord, Next etc. I think some specialized methods like:
CheckIn(EmpId,Date)
CheckOut(EmpId,Date)
GetWorkingTimes(EmpId,DateStart,DateEnd):DesiredResult
would be more useful.
The COM-server would then do all the "lower level" database stuff like connecting to the database, querying the db, inserting, updating etc. based on the parameters which you provide. Then it will do the neccessary calculations and return only the success of the method call or a small result set consisting of one or more user-defined records stuffed into an OleVariant (variant array of varByte).
This way you will reduce network traffic by reducing the amount of roundtrips and data exchange.
I think that implementing a stateless server and thinking about MTS would be a good aproach too.
The actual implementation depends on the delphi version you have, as you would need to do the packing and unpacking of your data by yourself in case you only have the Pro version. Also in that case you won't have the MtsObj unit which means that there will be some more work to do.
HTH,
Thomas
0
 
LVL 2

Author Comment

by:florisb
ID: 2697552
Hi,

Thanks Thomas, usefull information!
I'll give points, but if you don;t mind, I'll ask one more thing:

What I mainly have to send to this COM object (because of legacy types / structures) is arrays of bytes (like array[1..2000] of byte. At the moment I convert those array's to a widestring and send them to the COM object, and the COM object converts it to an array of bytes again (like this, we can use some old-types, by moving those array's to own types again). I'm testing this now... ...see code sample below..

Perhaps you know: is OleVariant a faster way to send those array's of bytes?

I'm working with the enterprise edition at the moment.

Sincerely,
Floris.

source sample; type to COM

unit Unit1;

interface

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


type TStr25 = string[25];
     TStr4  = string[4];
     TStr9  = string[9];
     Tstr8 = string[8];
     Tstr1 = string[1];

Type
  PEmp1St = ^TEmp1St;
  TEmp1St = Record
                TbNr      : TStr4;  
                BadgeNr   : TStr4;  
                SofiNr    : TStr9;  
                PersNr    : TStr9;  
                BadgeRef  : TStr8;  
                ANaam     : TStr25;  
                Straat    : TStr25;  
                Plaats    : TStr25;  
  end;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
Var
  a   : SmallInt;
  Mw1 : TEmp1St;
  Mw2 : TEmp1St;
  T : Array[1..2000] Of Byte;
  u,v : widestring;
  w : byte;
Begin
  //init Mw1, Mw2
  FillChar( Mw1, SizeOf( TEmp1St), 0);
  FillChar( Mw2, SizeOf( TEmp1St), 0);

  //values Mw1
  Mw1.ANaam := 'Floris';
  Mw1.Straat := 'Kerkstraat 16';
  Mw1.Plaats := 'Amsterdam';

  //Mw1 to array
  Move( Mw1, T, SizeOf( TEmp1St));

  //now convert T to a widestring.
  u := '';
  for a := 1 to 2000 do
    begin
    u := u + char(T[a]);
    end;

  //to COM object
  //*********************************************
  //  u' can be send to COM object as widestring
  //*********************************************

  //reset T (just for test)
  for a := 1 to 2000 do
    begin
    T[a] := byte(0);
    end;

  //now convert widestring again to T
  for a := 1 to 2000 do
    begin
    T[a] := byte(u[a]);
    end;

  // move T to Mw2 and show some fields.
  Move( T, Mw2, SizeOf( TEmp1St));
  showMessage(Mw2.Anaam+';'+Mw2.Straat+';'+Mw2.plaats);
end;
end.
0
 
LVL 1

Expert Comment

by:xsoft
ID: 2698067
Hi Floris,

nice to hear that I can help you.
I cannot tell you if converting Records to Bytearrays and then to Widestrings and vice versa is slower than converting Records to OleVariants and back, because I did not try that before.
But if you use the VarArrayLock and VarArrayUnlock functions on a VariantArray you can directly move a Record, Bytearray or whatever to and from a pointer to a VariantArray which speeds up the conversion very much. So I would imagine this way would at least not be slower and for sure it is much more convinient and flexible to use.
In the following I give you two functions to convert your TEmp1St to an OleVariant and back. Keep in mind, that these functions will also work if your TEmp1St record would look totally different, e.g.:

TEmp1St = Record
                TbNr      : Integer;    
                BadgeNr   : Integer;    
                SofiNr    : Integer;    
                PersNr    : Integer;    
                BadgeRef  : Integer;    
                ANaam     : TStr25;  
                Straat    : TStr25;  
                Plaats    : TStr25;  
 

function Emp1St2Var(const Value:TEmp1St):OleVariant;
var P:Pointer;
begin
 Result:=VarArrayCreate([0,SizeOf(Value)-1],varByte);
 P:=VarArrayLock(Result);
 try
  Move(Value,P^,SizeOf(Value));
 finally
  VarArrayUnlock(Result);
 end;
end;

function Var2Emp1St(const Value:OleVariant):TEmp1St;
var P:Pointer;
begin
 P:=VarArrayLock(Value);
 try
  Move(P^,Result,SizeOf(Result));
 finally
  VarArrayUnlock(Value);
 end;
end;

I used functions like this to move arrays of records between COM-server and client as a result of a query to a database.
A week ago I really got tired of creating lots of simular functions which only differ by the type they are used on. So I tried a generic approach by using an untyped parameter instead of a specialized type.
The Delhi helpfile says that var-,const- and out- parameters could be untyped.
So I use the OLEVariant and my Record as parameters, defining them as const or out and provide the size of my Record as an additional parameter:

function Rec2Var(const Rec;const Size:integer;out OLE:OLEVariant):integer;
var P:Pointer;
begin
try  
  try
    Result:=Size;
    OLE:=VarArrayCreate([0,Result-1],varByte);
    P:=VarArrayLock(OLE);
    Move(Rec,P^,Result);
  except
    Result:=-1;
  end;
finally
  VarArrayUnLock(OLE);
end;
end;

function Var2Rec(const Ole:OleVariant;const Size:integer;out Rec):integer;
var P:Pointer;
begin
try
  try
    Result:=Size;
    P:=VarArrayLock(Ole);
    Move(P^,Rec,Result);
  except
    Result:=-1;
  end;
finally
  VarArrayUnLock(Ole);
end;
end;

To use this functions I first calculate the size of my Record, call the function and compare the result of the function with my precalculated size of my record. If they match, everything is okay, if not... well that has never happened since I use it. I cannot imagine any situation in which these functions will not work properly (at least so far) but if you would like to be on the safe side use the functions I mentioned first as they are the standard approach to this problem as it is published by Calvert, Teixeira and many others.

Example:

Type TMyType=record
     a,b,c:string;
     d,e:integer;
end;  

procedure something;
var MyType:TMyType;OLEVar:OLEVariant;
begin
//assign values to MyType here
.....
MySize:=SizeOf(MyType);
If Rec2Var(MyType,MySize,OLEVar)=MySize then //everything is fine
....
else//Oops
.....
end;

Have fun,

Thomas

P.S. If you have the time to compare the speed of the two implementations (widestring vs. VariantArray) let me know the results if you don't mind.

0
 
LVL 1

Expert Comment

by:xsoft
ID: 2701614
Hi Floris,

just wanted to know if you received my last comment as someone else told me, that the notifications are sometimes delayed.

Thomas
0
 
LVL 2

Author Comment

by:florisb
ID: 2706526
received & saved to check; working off-line now-a-days...

Will check / give points in about 3 days.

Floris

0
 
LVL 2

Author Comment

by:florisb
ID: 2706528
received & saved to check; working off-line now-a-days...

Will check / give points in about 3 days.

Floris

0
 
LVL 2

Author Comment

by:florisb
ID: 2709649
You said you are new here? First points then!?
Thanks for info. Perhaps you canm give last comments on:

"I think that implementing a stateless server and thinking about MTS would be a good aproach too.
The actual implementation depends on the delphi version you have, as you would need to do the packing and unpacking of your data by yourself in case you only have the Pro version. Also in that case you won't have the MtsObj unit which means that there will be some more work to do.".

I'm first going to implement without MTS; Mts needs licensing, doens't it?
I have to support SQL Server, Oracle (and perhaps paradox, ...). Have a working COM-test project; just simple COm-code generated by the wizard. I have the enterprise version.

What is a stateless server in COM. also possible with IUnknown?

good luck,
Floris.
0
 
LVL 1

Expert Comment

by:xsoft
ID: 2710183
Hi Floris,

You said you are new here? First points then!?
I joined the Club on 04/06/2000.
At that day I answered 2 questions. From the other one I received my points already...

If you have the enterprise version (which I don't have), then you should have MTS-Object and MTS-Datamodule on the Multi-Tier page.
I don't exactly know the complete code these wizards will generate. For MTS you will have to create an inproc server which has a typelibrary and its interface-methods will have to support the standard marshaller.
If you play around with the wizards you can easily find out if they generate a simple COM- or an Automation-object.
As MTS is asking for some defined functions and interfaces which your object has to provide I think, that a COM-object would be ok. (Same as with shell-extensions)
MTS does not need any licensing! It is included in the NT-Option Pack and you can download it from the MS-website. (http://www.microsoft.com/Com/resources/downloads.asp)
In Windows 2000 its already included as it is part of COM+.
MTS does all the threading for you, so that you would only have to code a single-threaded COM-object.
As resource dispensers (RD)are included the ODBC-RD and the Shared Property Manager. The BDE has its own RD. Therefore you will be able to support all DBM's for which you have an ODBC-driver or which work with the BDE. AFAIK you should also be able to use ADO.
The concept of MTS is that it manages a pool of COM-objects, each in its own thread. When you connect to an object, you will get an interface pointer to a deactivated object.
When you call a method of an object MTS will activate an object from its pool call the method or function and then deactivate the object again.
If the object is no longer needed, then MTS can release the resources allocated for this object or hand out this object to another caller.
Therefore a client cannot rely on the fact, that if he calls twice for the same object, he will get the same instance of that object in both cases.
Due to this fact the object should not save its state (properties) between the calls. If it would do -which is possible- the object could not be managed in the forementioned way which means, you will loose the advantage of MTS.
If you would need to maintain state between two calls you would have to save the state on the client side and include it in the next call to the object as a parameter.
Another possibility is to use the Shared Property Manager.

I hope, thats what you wanted to know, otherwise might consider to ask a new question with new points ;-)

Thomas
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 2

Author Comment

by:florisb
ID: 2712913
you write:
"Therefore a client cannot rely on the fact, that if he calls twice for the same object, he will get the same instance of that object in both cases."

How I implement now; every client will create it's own instance on an application server. I didn;t really consider sharing object, they have privat variables in them too.

He, where's the accept answer button?
0
 
LVL 2

Author Comment

by:florisb
ID: 2712922
Adjusted points from 150 to 151
0
 
LVL 2

Author Comment

by:florisb
ID: 2712923
?
0
 
LVL 2

Author Comment

by:florisb
ID: 2712929
thanks so far...

later.

(where's that button!#@$%^&)
0
 
LVL 1

Expert Comment

by:xsoft
ID: 2713450
Hi Floris,
you wrote:
He, where's the accept answer button?
Does that mean you would like to accept my answer?
If yes, I will combine all my comments into one an post it as an answer, so that you can accept it.

How I implement now; every client will create it's own instance on an application server. I didn;t really consider sharing object, they have privat variables in them too.

As I mentioned before, you can also maintain state. There are 4 different possibilities on how to achieve that:
Client managed state, per-object state, shared transient state and persistent state.
To find out, which one suits your needs best, I recommend that you read the Microsoft Transaction Server Programming Guide in the MTS20.hlp which you will have on your pc after installing MTS.
The sentence "Therefore a client cannot rely on the fact, that if he calls twice for the same object, he will get the same instance of that object in both cases" applies to stateless objects which you would use to save resources and make your app better scalable.

OK?

Thomas.
P.S.
I will now withdraw my answer from the "basic database differences" thread as I understood you couldn't use my answer.

0
 
LVL 2

Author Comment

by:florisb
ID: 2720526
Combine posts? I just want to be able to accept it here. Will mail E.E.

I won't use MTS, like I said. I'll check the maintaining of  states without it then.

You can't withdraw and you gave no answer there.


C.U.
Floris

0
 
LVL 2

Author Comment

by:florisb
ID: 2720528
Combine posts? I just want to be able to accept it here. Will mail E.E.

I won't use MTS, like I said. I'll check the maintaining of  states without it then.

You can't withdraw and you gave no answer there.


C.U.
Floris

0
 
LVL 2

Author Comment

by:florisb
ID: 2720530
Ok!?
0
 
LVL 1

Expert Comment

by:xsoft
ID: 2720703
Hi Floris,

Just receided your points.

Combine posts? I just want to be able to accept it here. Will mail E.E.

The idea was, that instead of having only my answer from April 08 2000 - 12:35PM marked as the accepted one, combine that answer and my following posts together into one. I don't know, if someone other would like to access this question later as a PAQ what would he see?
Your q and my a or all the postings?

I won't use MTS, like I said. I'll check the maintaining of  states without it then.

Maybe I got you wrong. You said that first you are going to implement without MTS and then asked if MTS needed licencing. So I thought you would not like to use MTS because it costs extra money. Due to the fact that there are no extra costs I suggested that you could use MTS and told you that you could maintain state also within MTS.
You said: "with different users and perhaps lot's of updates after eachother, what about the apartment threading model?"
That's why I considered MTS because MTS would simplify the theading stuff for you and the sharing of resources, especailly database connections.



You can't withdraw and you gave no answer there.
I first gave an answer on that other thread, which I withdrew later.

CU,

Thomas
0
 
LVL 2

Author Comment

by:florisb
ID: 2724096
Aaaahaaaa.
he, thanks for the infomation. You didn't get wrong; I focused a bit on other things... ...thought I got you irritated a bit; was not my intention.

"MTS would simplify the theading stuff for you"; is that needed?
So much is done by the Wizard; I read about COM, threading, instancing. Does MTS magic to performance in my situation?

Just started  a new question!
Same subject...

C.U.
Floris.



0
 
LVL 1

Expert Comment

by:xsoft
ID: 2724192
Hi Floris,

"MTS would simplify the theading stuff for you"; is that needed?
So much is done by the Wizard; I read about COM, threading, instancing. Does MTS magic to performance in my situation?

Just started  a new question!
Same subject...

I will give a comment to this on the new thread. Ok?

Thomas
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now