Title: How to get data from InterBase via WebServices
Question: This DBServer with a Server and Client part demonstrates how to use WebServices to create your own Database Server to retrieve or update Data and Types.
Answer:
First we have to define a project (CGI or shared object) with an output to the apache directory in my example:
output directory:
/usr/local/httpd/cgi-bin/
The goal is to start a client which gets InterBase data over the web through SOAP, CLX and dbExpress:
http://localhost:80/cgi-bin/soapdbserver.cgi/soap
The 2 example are more or less available under
/opt/kylix2/demos/webservices/dbsamples/server
or with delphi6 in demos/webservices/dbserver
The server directory project, soapdbserversample.dpr, should be built
first, then the client directory project.
If you can run the DbxExplorer example in demos/db, and explore the IBConnection files, you are configured to access InterBase data.
Tip: Just for testing (either in a production or testing environment),you can define and compile a CGI program and later on uncomment the // in the following project to get an real Apache DSO module:
//library soapdbserversample;
program soapdbserver;
{$APPTYPE CONSOLE}
uses
WebBroker,
CGIApp,
//HTTPD,
//ApacheApp,
SOAPDbSampleUnit in 'SOAPDbSampleUnit.pas' {WebModule1: TWebModule};
{$E cgi}
//exports
//apache_module name 'soapdbserversample';
begin
//ContentType := 'soapdbserversample-handler';
//ModuleName := 'soapdbserversample_module';
application.Initialize;
application.CreateForm(TWebModule1, WebModule1);
application.Run;
end.
The SoapDBServerUnit will do the following jobs and steps:
1. Defines IWebServicesDataSet interface derived from
IInvokable.
It has two methods, RetrieveDataSet and UpdateDataset. The
Interface is registered in the initialization section
(RegisterInterface).
2. Defines and Implements TWebServicesDataSet, which is derived
from TInvokableClass and IWebServicesDataSet. Is registered
in the initialization section (RegisterInvokableClass).
3. Defines and implements a number of Classes (TIndexDesc,
TColDesc, TFieldValue and TSoapDataPacket) which are derived
from TRemotable.
These objects are registered in the initialization section
(RegisterXSClass).
4. Defines arrays of the objects in 3 and registers these arrays
in the initialization section (RegisterXSInfo).
5. Define and implements a set of functions and procedures to
convert TSoapDataPacket to DataSet and vice versa.
1. From Face to Face with an Interface
-----------------------------------------------------------
The invocation registry (InvRegistry) knows how to create instances of TInvokableClass and (because it has a virtual constructor) its descendants. This allows the registry to supply an invoker in a Web Service application with an instance of the invokable class that can handle an incoming request.
initialization
InvRegistry.RegisterInterface(TypeInfo(IWebServicesDataSet), 'urn:SOAPDBServerUnit-IWebServicesDataSet','');
IWebServicesDataSet = interface(IInvokable)
['{38ABE9A2-A6A8-D511-87C6-00C04FA06B45}']
procedure RetrieveDataSet(const SQL: String; var DataSet:
TSoapDataPacket; var UpdateInfo: TUpdateInfo); stdcall;
function UpdateDataSet(const UpdatePacket: TSoapDataPacket;
var UpdateInfo: TUpdateInfo; out
UpdateErrors: TDBErrorArray):Integer; stdcall;
end;
2. Implement a bit
-----------------------------------------------------------
In the implementation section of the server-unit that declares this class, we fill in the RetrieveDataSet and UpdateDataSet methods later.
TWebServicesDataSet = class(TInvokableClass, IWebServicesDataSet)
public
procedure RetrieveDataSet(SQL: string; var DataSet:
TSoapDataPacket; var UpdateInfo: TUpdateInfo); stdcall;
function UpdateDataSet(UpdatePacket: TSoapDataPacket; var
UpdateInfo: TUpdateInfo; var UpdateErrors:
TDBErrorArray): Integer; stdcall;
end;
3. Now let's have a look at the corresponding classes
-----------------------------------------------------------
like TSoapDataPacket = class(TRemotable), which is the important one and descends from TRemotable. TRemotable is the base class for classes that can be passed as parameters or return values in a Web Service application.
Tip: Both the client app and server app must register a TRemotable descendant with the remotable class registry before they can use it.
The caller of an invokable interface (client) is responsible for creating any TRemotable instances that it passes as input parameters, and for freeing any TRemotable instances it creates or that are returned as output parameters or method results.
Use TRemotable as a base class when defining a class to represent a complex data type on an invokable interface. For example, in the case where you would ordinarily pass a record as a parameter, you would instead define a TRemotable descendant where every member of the record is a published property on your new class.
In the initialization section of the unit that defines the TRemotable descendant, you must register this class with the remotable type registry and don't forget adding the InvokeRegistry unit to the uses clause. This unit declares two global functions, RemClassRegistry and RemTypeRegistry, which return the remotable type registry.
Unit Structure and Registration
--------------------------------
It is an idea to implement and register TRemotable descendants in a separate unit from the rest of your server application, including from the units that declare and register invokable interfaces. In this way, you can use the unit that defines your type in both the client and server, and you can use the type for more than one interface.
For example, the following line comes from the IWebServiceUnit4. It registers TSoapDataPacket, a TRemotable descendant that represents DataPackets values:
Unit IWebServiceUnit4;
implementation
initialization
RemClassRegistry.RegisterXSClass(TSoapDataPacket,'urn:ServerDBObjects','TSoapDataPacket','');
......
The first parameter is the name of the TRemotable descendant. The second is a uniform resource identifier (URI) that uniquely identifies the namespace of the new class. If you supply an empty string, the registry can generate a URI for you.
TRemotable is compiled with runtime type information (RTTI) and has a virtual constructor that the remotable class registry recognizes and uses to supply class instances.
4. Define the arrays of objects
-----------------------------------------------------------
We have to decompose a complex structrue in its elements so the class
TSoapDataPacket has a member of TRowArray which is an array of TSoapRow with the Fields inside:
TRowArray = array of TSoapRow;
TSoapRow = class(TRemotable)
private
FRowID: Integer;
FFieldValueArray: TFieldValueArray;
FUpdateType: TUpdateType;
published
property RowID: Integer read FRowID write FRowID;
property FieldValueArray: TFieldValueArray read FFieldValueArray
write FFieldValueArray;
property UpdateType: TUpdateType read FUpdateType
write FUpdateType;
end;
As you see, we must map these complex types to a class like TSoapRow that includes runtime type information (RTTI, in published), which the invoker can use to convert between data in the SOAP stream and type values.
(It can also handle dynamic arrays, as long as they are registered with the remotable type registry).
If you are using dynamic arrays, enum types, or booleans for parameters, you don't need to create a remotable class to represent them, but you do have to register them with the remotable type registry. Thus, for example, if your interface uses the type mentioned before such as:
type
TRowArray = array of TSoapRow;
then you must add the following registration to the initialization section of the unit where you declare this dynamic array:
RemClassRegistry.RegisterXSInfo(TypeInfo(TRowArray),'urn:ServerDBObjects','TRowArray','');
RemClassRegistry.RegisterXSInfo(TypeInfo(TUpdateType),'urn:ServerDBObjects','TUpdateType','');
RemClassRegistry.RegisterXSInfo(TypeInfo(TColDescArray),'urn:ServerDBObjects','TColDescArray','');
Now here are the two important classes at last:
TSoapDataPacket = class(TRemotable)
private
FColDescArray: TColDescArray;
FIndexDescArray: TIndexDescArray;
FRowArray: TRowArray;
FTableName: string;
public
function UpdateRow(Row: TSoapRow; var UpdateInfo: TUpdateInfo;
var UpdateErrors: TDBErrorArray): Integer; virtual;
published
property ColDescArray: TColDescArray read FColDescArray write
FColDescArray;
property IndexDescArray: TIndexDescArray read FIndexDescArray
write FIndexDescArray;
property RowArray: TRowArray read FRowArray write FRowArray;
property TableName: string read FTableName write FTableName;
end;
TPooledData = class
private
FInUse: Boolean;
FLock: TRTLCriticalSection;
FIndex: Integer;
FThreadID: Cardinal;
procedure ServerSetup;
public
SQLConnection: TSQLConnection;
SQLDataSet: TSQLDataSet;
procedure Lock;
procedure UnLock;
constructor Create(AIndex: Integer);
destructor Destroy; override;
end;
5. Implement the client/server methods
-----------------------------------------------------------
Next I will show from a client view how the methods are implemented and in which sequence they are called. The client creates a TLinkedRIO object, and uses it (cast as an IWebServicesDataSet) to call RetrieveDataSet and get the SoapDataPacket.
procedure TClientForm.FormCreate(Sender: TObject);
var
SURL: string;
begin
SURL:= InputBox('Webservices', 'Input URL:', SURL);
HTTPRIO1.URL:= SURL;
WebServicesDS:= HTTPRio1 As IWebServicesDataSet;
FUpdateInfo:= TUpdateInfo.Create;
FUpdateInfo.UseIndexMetadata := True;
SetLength(FUpdateErrors, 0);
end;
When a Button or event is hit, it calls the IWebServicesDataSet.RetrieveDataSet by calling the interface method:
event() ---
if Assigned(SoapDataPacket) then
ClearPacket(SoapDataPacket);
WebServicesDS.RetrieveDataSet(Edit1.Text, SoapDataPacket, FUpdateInfo);
The client uses utility functions to convert SoapDataPacket (passed as parameter) to DataSet Data and sets this to a navigationale ClientDataSet.
DataSet:= TSoapDataPacket.Create;
DataSetFromRowArray(ClientDataSet1, SoapDataPacket.RowArray);
Soap Server Storm
--------------------------------
On the server side the important method goes like this:
procedure TWebServicesDataSet.RetrieveDataSet(SQL: string;
var DataSet: TSoapDataPacket;
var UpdateInfo: TUpdateInfo); stdcall;
var
AData: TPooledData;
begin
try
AData:= PooledData.GetAvailableConnection;
try
DataSet:= NIL;
try
AData.SqlDataSet.CommandText:= SQL;
AData.SqlDataSet.Open;
DataSet:= TSoapDataPacket.Create;
DataSet.TableName:= GetTableNameFromSQL(SQL);
The question arises, how the server know which database connection is valid. Before we pass a SQL statement, the method getAvailableConnection calls for each connection ServerSetup so the magic of this method is responsible for the configuration of dbExpress:
function TGetPooledData.GetAvailableConnection: TPooledData;
......
if Result = NIL then begin
SetLength(FPooledData, Length(FPooledData) + 1);
FPooledData[Length(FPooledData)-1]:=
TPooledData.Create(Length(FPooledData) -1);
FPooledData[Length(FPooledData)-1].ServerSetup;
......
procedure TPooledData.ServerSetup;
begin
SQLConnection:= TSQLConnection.Create(Nil);
with SQLConnection do begin
DriverName:= 'INTERBASE';
VendorLib:= 'libgds.so';
GetDriverFunc:= 'getSQLDriverINTERBASE';
LibraryName:= 'libsqlib.so.1.0';
ConnectionName:= 'IB_WebBank';
LoadParamsFromIniFile('/usr/local/httpd/cgi-bin/dbxconnections');
LoginPrompt:= False;
end;
SQLDataSet:= TSQLDataSet.Create(Nil);
TSQLDataSet(SQLDataSet).SQLConnection:= SQLConnection;
end;
--------------------------------------------------------------
Update 1: dynamic dbexpress with Delphi 10
const
StrDatabase2Dfr =
'Database=APSN21:D:\kleiner2005\ekon9_10\soa_vcl\umlbank2.gdb';
StrInsertSQLStat = 'insert into KINGS values ("%s", "%s", "%s")';
procedure TVCLScanner.PostUser(const Email, FirstName, LastName: WideString);
var
SQLConnection1: TSQLConnection;
DataSet: TSQLDataSet;
begin
//webModule1.SQLConnection1.Open;
SQLConnection1:= TSQLConnection.Create(NIL);
with SQLConnection1 do begin
ConnectionName := 'VCLScanner';
DriverName := 'INTERBASE';
//LibraryName := 'dbexpint.dll' in D6;
LibraryName:= 'dbxint30.dll';
VendorLib:= 'GDS32.DLL';
GetDriverFunc:= 'getSQLDriverINTERBASE';
Params.Add('User_Name=SYSDBA');
Params.Add('Password=masterkey');
//Params.Add('Database=myserver:X:\vclscanner.gdb');
Params.Add(StrDatabase3Dfr);
LoginPrompt:= False;
Open;
end;
DataSet:= TSQLDataSet.Create(nil);
with DataSet do begin
SQLConnection:= SQLConnection1;
CommandText:= Format(StrInsertSQLStat,
[Email,FirstName,LastName]);
try
ExecSQL;
//silent cause of CGI Webscript
except
end;
end;
SQLConnection1.Close;
DataSet.Free;
SQLConnection1.Free;
end;
One powerful feature of WebBroker, WebSnap or a WebService with dbExpres in CLX is that they offer several different target server types (CGI, DSO, NSAPI etc.) or databases (InterBase, DB2, Oracle etc.). Also Kylix allows you to easily convert from one target type to another.
See you in the book "Patterns konkret" ;)