Connecting to legacy databases and publishing a RESTful interface to it

Most systems, especially in the DDD area, needs to integrate to a legacy system. In our case our we had to communicate to a Firebird 1.5 database.

The first step was to define our Data Transfer Objects:

type
  TLegacyID = type RAWUTF8;

  TLegacyAccount = class(TSynPersistent)
  private
    fLegacyID: TLegacyID;
    fDateModified: TDateTime;
    fUserCreated: RAWUTF8;
    fDateCreated: TDateTime;
    fName: RAWUTF8;
    fisActive: Boolean;
  public
    constructor Create; overload; override;
    constructor Create( aID : TLegacyID; aName : RAWUTF8; aIsActive : Boolean;
      aDateCreated, aDateModified : TDateTime; aUserCreated : RAWUTF8 ); overload;
  published
    property ID : TLegacyID read fRevelightID write fRevelightID;
    property Name : RAWUTF8 read fName write fName;
    property isActive : Boolean read fisActive write fisActive;
    property DateCreated : TDateTime read fDateCreated write fDateCreated;
    property DateModified : TDateTime read fDateModified write fDateModified;
    property UserCreated : RAWUTF8 read fUserCreated write fUserCreated;
  end;

  TLegacySupplier = class(TLegacyAccount)
  end;

Here we declare a unique type to identify our legacy IDs (strings). We also do a basic map of our data layout, with a customized constructor for ease of creation. One can add other methods later to handle copies and assignments.

The next step was to define our service:

type
  ILegacyStockQuery = interface(IInvokable)
    ['{2BDC9F78-B9C2-4621-A557-F87F02AC0581}']
    function GetSupplier(const aID: TLegacyID; out Supplier: TLegacySupplier): TCQRSResult;
  end;

we’ll publish a service as LegacyStockQuery with a single method GetSupplier. This method will return a JSON encoded representation of our TLegacySupplier, ready to be consumed by a client.

To implement it:

type
  TLegacyStockQuery = class(TInterfacedObject, ILegacyStockQuery)
  private
    fDbConnection : TSQLDBConnectionProperties;
  public
    constructor Create( const aProps: TSQLDBConnectionProperties ); overload;
    function GetSupplier(const aID: TRevelightID; out Supplier: TLegacySupplier): TCQRSResult;

    property DbConnection : TSQLDBConnectionProperties read fDbConnection write fDbConnection;
  end;

We keep a copy of our database connection properties local to our instance to ensure thread safety.

{ TLegacyStockQuery }

constructor TLegacyStockQuery.Create(const aProps: TSQLDBConnectionProperties);
begin
  fDbConnection := aProps;
  inherited Create;
end;

function TLegacyStockQuery.GetSupplier(const aID: TLegacyID; out Supplier: TLegacySupplier): TCQRSResult;
var
  Res, Address : ISQLDBRows;
begin
  Result := cqrsNotFound;
  Res := fDbConnection.Execute( 'select * from SUPPLIERS where SUPPLIER_ID=? ', [aID] );
  if Res.Step then begin
    Result := cqrsSuccess;
    Supplier.ID := Res['SUPPLIER_ID'];
    Supplier.Name := Res['SUPPLIER_NAME'];
    Supplier.isActive := Res['ACTIVE_FLAG'] = 'Y';

    Supplier.DateCreated := Res['DATE_CREATED'];
    Supplier.DateModified := Res['DATE_MODIFIED'];
    Supplier.UserCreated := Res['USER_CREATED'];
  end;
end;

Execute the query against the legacy database and populate the DTO. Using the ISQLDBRows interface means less object maintenance and cleaner code.

To kick the whole thing off we have:

procedure StartServer( aDbURI : RawURF8 );
var
  aDbConnection : TSQLDBConnectionProperties;
  aStockServer  : TSQLRestServerFullMemory;
  aHTTPServer   : TSQLHttpServer;
begin
  aDbConnection := TSQLDBZEOSConnectionProperties.Create( aDbURI, '', '', '' );
  aStockServer  := TSQLRestServerFullMemory.Create([]);
  try
    aStockServer.ServiceDefine( TLegacyStockQuery.Create( aDbConnection ), [ILegacyStockQuery]);
    aHTTPServer := TSQLHttpServer.Create( DEFAULT_HTTP_PORT, [aStockServer] );
    try
      aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
      writeln('Background server is running.'#10);
      writeln('Cross-Platform wrappers are available at ',
          DEFAULT_HTTP_PORT ,'/', DEFAULT_SERVER_ROOT );
      write('Press [Enter] to close the server.');
      readln;
    finally
      aHTTPServer.Free;
    end;
  finally
    aStockServer.Free;
  end;
end; 

It would be better to use dependency injection here, but we’ll get in to that later.

When invoking it, we used a Zeos DB URI, like this one:

zdbc:firebird-1.5://tst-db-svr-1/srv/db/EXAMPLE.FDB?username=SYSDBA;password=masterkey

Tips!

Remember to register your Object array types with

TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(<ArrayType>),<ObjType>);

e.g.

TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(TLegacySupplierObjArray),TLegacySupplier);
Advertisements

14 thoughts on “Connecting to legacy databases and publishing a RESTful interface to it

    1. Well spotted! Not sure how that managed to creep in there!

      aStockServer should be of type TSQLRestServerFullMemory, and the constructor takes as a parameter a dynamic array of ORM classes to publish. Since we’re not using the ORM in this example, we pass in an empty array.

      Like

  1. Thank you for your quick reply, I was trying to mimic the example here with my own DTO and my own database. If I was successful what would be the uri to test from a browser ? http://serverip:port/root/GetSupplier/25 ?

    I used a model as parameter to the server constructor, inherit my DTO from TSQLRecord instead of TSynPersistent and I added a line VirtualTableExternalRegisterAll(model,aprops) it compiles. But I keep getting {
    “errorCode”:400,
    “errorText”:”Bad Request”
    } on the browser.
    when I tried to load http://localhost:8888/root/getUserSession/2

    Like

    1. For interface based services, all the parameters are either POSTed to it, or retrieved from GET parameters. That means your URL will be:
      http://serverip:port/root/GetSupplier?aID=25
      alternatively you can POST
      {aID:25} to http://serverip:port/root/GetSupplier

      Like

  2. Any advises about “Exception with message ‘DBError : [10007] : General SQL Server error: Check messages from the SQL Server. Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier” ?

    Like

  3. IMHO you may better use the NewThreadSafeStatementPrepared method, at TSQLDBConnectionProperties level, instead of maintaining your own single connection. Or you should ensure that the whole service is executed in a lock, for thread safety.

    Like

  4. Hi Willio,
    In your Interface example you have an example on how to retrieve Suppliers information with GetSupplier, how would you send a new supplier record to the legacy database?
    What should be the parameters and how to handle the Json data received?
    I would like to know how to code the function SetSupplier( Supplier: TLegacySupplier): TCQRSResult;

    Like

  5. Hi Winslas ,

    You wouldn’t need to handle the JSON, as mORMot already does that for you.

    From the top of my headI’d do something like this:
    function TLegacyStockQuery.SetSupplier(const Supplier: TLegacySupplier; out aID: TLegacyID): TCQRSResult;
    begin
    Result := cqrsNotFound;
    (* Perform what ever steps you need to, to persist the Supplier, and populat ethe ID to be returned. *)
    end;

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s