Work with Caché DBMS Objects on the Example of Delphi

Despite Delphi's permanent burial, this platform for building Desktop applications is alive and well, and with the change of ownership it even takes a second wind and continues to be the main tool for thousands of developers around the world.
As with any other DBMS, Delphi perfectly interacts with the Caché DBMS.

From Delphi, you can connect to Caché using the following interfaces:

• Caché Objects (object, using CacheObject.dll / CacheActiveX.dll libraries);
• Caché SQL (relational using ODBC driver);
• Caché Direct (direct, using the Vism.ocx library);
• Web services ;
• Sockets

This article will discuss examples of using the object interface when working with Caché DBMS.

')
But first, I’ll give you a few short VBScript examples that you can run directly from Windows Explorer.

An example of direct access:

Set f = CreateObject("VISM.VisMCtrl.1") f.Server="CN_IPTCP:localhost[1972]:_system:@ SYS" f.NameSpace="SAMPLES" f.Execute("=$zv") '   WScript.Echo f.VALUE 

An example of relational access:

 Set cn=Createobject("ADODB.Connection") cn.ConnectionString="DRIVER={InterSystems ODBC35}; SERVER=127.0.0.1; PORT=1972; DATABASE=SAMPLES; UID=_system; PWD=SYS" cn.open WScript.Echo "Succesfully!" 

Example object access:

 Set f = CreateObject("CacheActiveX.Factory") Set rs = CreateObject("CacheActiveX.ResultSet") If Not f.IsConnected() Then f.Connect("cn_iptcp:127.0.0.1[1972]:SAMPLES:_SYSTEM:SYS") Set rs=f.DynamicSQL("select TOP 3 * from Sample.Person") rs.Execute() while rs.Next WScript.Echo rs.Get("SSN") '  SSN      Sample.Person wend rs.Close() Set person = f.Static("Sample.Person") age=person.CurrentAge(45678) '   Sample.Person WScript.Echo age End If 

Similarly, you can work with Caché DBMS using JScript, Visual Basic, C ++ Builder, etc.

Note: For Java and .NET there are several native interfaces that provide much more features.

Late and early binding

There are two approaches for working with Caché objects from Delphi:

• late binding;
• early binding .

Each of these approaches has its advantages and disadvantages, which, however, compensate each other.

When late binding, the developer is unavailable to the code prompter, respectively, there is a high probability of making a mistake in the code, which will be detected only during program execution. The speed of operation is somewhat lower than with early binding, since the code is executed dynamically.

Early binding is devoid of these shortcomings, but you have to pay for this by changing the interface of user classes in the database or when switching to a new version of the Caché DBMS, it is necessary to regenerate the proxy classes for the client application.

By combining both of these approaches, you can achieve optimal performance and ease of operation.

This article will mainly use early binding, since this approach is less described compared to late binding.

Caché Objects uses two libraries that are installed by default in C: \ Program Files \ Common Files \ InterSystems \ Cache \ :

• CacheObject.dll (outdated);
• CacheActiveX.dll (recommended).

Starting with Caché 5.1, InterSystems developers strongly recommend using the new CacheActiveX.dll library. The CacheObject.dll library is left only for compatibility with old applications. Therefore, in this article I will be based on CacheActiveX.dll .

You can read about Upgrading from CacheObject.dll about the differences between these libraries and the nuances that need to be considered when upgrading to a new version.

In the above directory, you can find other files that may be useful to you:

• DelphiCallback.dll ;
• CacheList.ocx ;
• CacheQuery.ocx ;
• VISM.ocx .

Import and install Caché Objects ActiveX components in Delphi

This section describes the installation of the main classes and interfaces in the Delphi environment for working with objects in the Caché DBMS using early binding. You can skip this section to work with late binding.

So, in order:

1. select the menu item Component> Import Component ... ;
   
   
   
   
   
   
2. choose to start the type library CacheActiveX 2.0 Type Library ;
   
   
   
   
3. we set the name of our bookmark, where we want to install our components, as well as other parameters;
   
   
   
   
4. we create our module, while without installation;
   
   
   
   
5. repeat steps 2-4 for the following libraries:
   
   • CacheActiveX 2.0 Type Library ;
   • CacheList ActiveX Control module ;
   • CacheQuery ActiveX Control module ;
   • DelphiCallback 1.0 Type Library ;
   • VisM 7.2 ActiveX Control ;
   • TL 1.0 Type Library .

   Note: You can create modules using the tlibimp.exe utility included in the Delphi package.

   
   
6. create a new project of type Package and add all the previously created modules to it. We compile the project and install our package. This is what we should end up with:
   
   
   
   
   

   Note: For earlier versions of Delphi, the process of creating modules differs slightly:
   

   • select the menu item Project> Import Type Library ... ;
     
     
     
     
   • see further paragraphs above.

Generating custom proxy classes

This section describes the generation of custom proxy classes for working with objects in the Caché DBMS using early binding. You can skip this section to work with late binding.

First you need to generate an ODL file containing all the custom classes we need. To do this, use the odl_generator.exe utility supplied with the Caché DBMS.

EXAMPLE:

 odl_generator.exe -conn cn_iptcp:localhost[1972]:USER:_system:SYS -class-list test.txt -lib-name test -dir MIDL 

In this example, the utility connects to the USER area , generates proxy classes for the classes listed in the test.txt file, and saves the result to the file MIDL \ test.odl

Attention : The resulting file is intended for use with the CacheActiveX.dll library. To generate an ODL file designed to work with the old library, you should use the ExportODL method of the % SYSTEM.OBJ class

EXAMPLE:
set list= "%Library.Status,Sample.Person"
do $system .OBJ . ExportODL (list, "c:\MIDL\test.odl" , "-d" ,.err)


So, the ODL file is received, now you need to compile it to get a TLB file, and then a PAS file. To do this, you can use the utilities midl.exe or mktypelib.exe , included in the developer kit Visual C ++.

EXAMPLE:

 midl /I . test.odl /tlb test.tlb tlibimp.exe -C- -P+ -Hr- -Ha- -Hs- -XM- test.tlb 

Note: You can automate all the above steps using the Caché DBMS MAC program.

Preparatory work on the server

Let's create the following data classes in our test database:

///
Class pas.s Extends %SerialObject
{

/// (64-).
Property aInteger As %Integer ;

/// . - 50 .
Property aString As %String ;

}

/// .
Class pas.a Extends %Persistent
{

/// aA;
Index aAIndex On aA;

/// --, "". SQL foreign key.
Relationship aA As pas.test [ Cardinality = one, Inverse = aChilds ];

Property aInteger As %Integer ;

Property aString As %String ;

}

/// %occIO.inc .
Include %occIO

/// .
Class pas.test Extends %Persistent
{

/// , ;
Parameter EXTENTQUERYSPEC As ROWSPEC [ Flags = LIST ] = aBoolean,aInteger,aString,aDate,aTimeStamp" ;

/// (true/false/null);
Property aBoolean As %Boolean ;

Property aInteger As %Integer ;

Property aString As %String ;

/// ;
Property aDate As %Date ;

/// +;
Property aTimeStamp As %TimeStamp ;

/// (CLOB);
Property aMemo As %GlobalCharacterStream ;

/// (BLOB);
Property aPhoto As %GlobalBinaryStream ;

/// --, "". SQL .
Relationship aChilds As pas.a [ Cardinality = many, Inverse = aA ];

/// - ;
///<br> SQL
///<br> -.
Property aS As pas.s ;

/// - ;
///<br> SQL , , .
Property aListOfString As list Of %String ;

/// - ;
///<br> SQL , , .
Property aListOfA As list Of pas.a ;

/// - ;
///<br> SQL .
Property aArrOfString As array Of %String ;

/// - ;
///<br> SQL .
Property aArrOfA As array Of pas.a ;

/// .
/// <br> , .
Method %OnBeforeSave( insert As %Boolean ) As %Status [ Private , ServerOnly = 1 ]
{
;
write "Hello from Cache! (" , $$$CurrentClass , ":" , $$$CurrentMethod , ")" ,!

quit $$$OK
}

/// - : .
Query test1( ABoolean As %Boolean , AInteger As %Integer , AString As %String , ADate As %Date , ATimeStamp As %TimeStamp ) As %SQLQuery ( CONTAINID = 1 , ROWSPEC = "ID:%String,aBoolean:%Boolean,aInteger:%Integer,aString:%String,aDate:%Date,aTimeStamp:%TimeStamp" ) [ SqlProc ]
{
SELECT %ID , aBoolean , aInteger , aString , aDate , aTimeStamp FROM pas . test WHERE
( aBoolean = :ABoolean or :ABoolean is null )
AND ( aInteger = :AInteger or :AInteger is null )
AND ( aString = :AString or :AString is null )
AND ( aDate < :ADate or :ADate is null )
AND ( aTimeStamp <= :ATimeStamp or :ATimeStamp is null )
}

/// - : ""
///<br> %INLIST.
Query test2( AList As %List ) As %SQLQuery ( CONTAINID = 1 , ROWSPEC = "ID:%String,aBoolean:%Boolean,aInteger:%Integer,aString:%String,aDate:%Date,aTimeStamp:%TimeStamp" ) [ SqlProc ]
{
SELECT %ID , aBoolean , aInteger , aString , aDate , aTimeStamp FROM pas . test WHERE ID %INLIST :AList
}

/// .
ClassMethod test3( AList As %List ) As %Status
{
; AList
write AList

;
set ^pastest=AList

quit $$$OK
}

/// .
ClassMethod test4() As %Status
{
quit $$$ERROR ( $$$GeneralError , "My error!" )
}

/// .
ClassMethod test5( Arg1... As %List ) As %Status
{
;
write "Invocation has " , $get (Arg1, 0), " element" , $select (( $get (Arg1, 0)=1): "" , 1: "s" ),!
for i = 1 : 1 : $get (Arg1, 0)
{
write :( $data (Arg1(i))>0) "Argument[" ,i, "]:" ,?15, $get (Arg1(i), "<NULL>" ),!
}
quit $$$OK
}

/// : .
/// <br> .
/// <br>:
/// <br><var>ID</var> - ;
/// <br><var>A</var> - ;
/// <br><var>BLOB</var> - ;
/// <br><var>RS1</var> - , Borland ® MyBase (DataSnap (TM)) XML DataSet;
/// <br><var>RS2</var> - , Borland ® MyBase (DataSnap (TM)) XML DataSet;
ClassMethod test6(
ID As %String ,
ByRef A As pas.a ,
Output BLOB As %BinaryStream ,
Output RS1 As %CharacterStream ,
Output RS2 As %CharacterStream ) As %Status
{
// ,
set A. aString =999

//
set BLOB= ##class ( %GlobalBinaryStream ). %New ()

//
do BLOB. Write ( "123" )

//
set RS1= ##class ( %GlobalCharacterStream ). %New ()
set RS2= ##class ( %GlobalCharacterStream ). %New ()

// , XML TClientDataSet
set cds= ##class ( %XML.ZMyBaseDataSet ). %New ()

//
do cds. Prepare ( "select * from pas.a where id %inlist ?" )

//
do cds. SetArgs ( $listbuild (1,2,3,9))

// XML
do cds. XMLExportToStream (.RS1)

// ( )
do cds. Close ()
do cds. Prepare ( "select ID,aBoolean,aInteger,aString,aDate,aTimeStamp from pas.test" )
do cds. XMLExportToStream (.RS2)
do cds. Close ()

// ""
quit $$$OK
}

}

We will generate proxy classes for them, as well as for the following classes:

• % Library.ArrayOfDataTypes
• % Library.ArrayOfObjects
• % Library.ListOfObjects
• % Library.ListOfDataTypes
• % Library.RelationshipObject

Now, if you connect the generated PAS file to the project, the most important methods and properties of our classes become available:


^{to increase}

Attention : Threads classes and some others should not be imported from Caché DBMS, since they are already protected into CacheActiveX.dll and are incompatible with the generated proxy classes.

Connect to Caché DBMS

Late binding (old library):

 var _f:variant; begin _f:=CreateOleObject('CacheObject.Factory'); if _f.Connect(_f.ConnectDlg('+%up')) then ShowMessage('OK') else ShowMessage('ERROR'); 

Late binding (new library):

 var _f:variant; begin _f:=CreateOleObject('CacheActiveX.Factory'); if _f.Connect(_f.ConnectDlg('+%up')) then ShowMessage('OK') else ShowMessage('ERROR'); 

Early binding (new library):

 type Tfm = class(TForm) f: TFactory; ... begin ... if f.Connect1(f.ConnectDlg('+%up')) then ShowMessage('OK') else ShowMessage('ERROR'); ... 

Warning : The old library uses the % Service_CacheDirect service and only unauthenticated access, the new one uses% Service_Bindings and other access methods.

A detailed description of the TFactobry class methods , as well as other classes, can be found in the ActiveX API Reference

Examples of calling queries and methods using early binding

Using CallBack functionality

To use CallBack functionality from Delphi, you should use the ready-made TCallback class from the DelphiCallback.dll file.

 var f:TFactory; Callback1: TCallback; mm: TMemo; ... f.SetOutput(Callback1.OleObject); ... procedure Tfm.Callback1TextChanged(Sender: TObject; const p_bstrText: WideString); begin mm.Lines.Append(p_bstrText); end; 

Delete all data

  mm.Lines.Text:='KillExtent'#10#13; //   test_(f.Static('pas.test')).SYS_KillExtent(1); a(f.Static('pas.a')).SYS_KillExtent(1); 

An example of creating, filling, saving and releasing objects of various types

You can read about the features of closing objects when working through ActiveX in the article by Sergey Kudinov: Features of closing objects when working through ActiveX, CPP-binding .

Code example:

 ... uses test_TLB, AxCtrls, ComObj, ActiveX, Types; ... const N = 3; var i: integer; _t: test_; _a: A; _s: s; rel: RelationshipObject; listStr: ListOfDataTypes; listA: ListOfObjects; arrStr: ArrayOfDataTypes; arrA: ArrayOfObjects; stream: IDispatch; begin Screen.Cursor := crSQLWait; mm.Lines.Text:='Save'#10#13; try try //     pas.test _t := test_(f.New('pas.test')); //     _t.aBoolean := true; _t.aInteger := 50; _t.aString := ' '; //     null    // (%Integer,%Boolean,%Date  ..) Variant(_t).aDate := nil; _t.aDate := _t.aDateDisplayToLogical('02.03.2001'); //  t.aDate:=StrToDate('02.03.2001'); _t.aTimeStamp := _t.aTimeStampDisplayToLogical('1900-01-02 12:34:55'); //  t.aTimeStamp:=StrToDateTime('02.01.1900 12:34:55'); //     stream := _t.aMemo; ICharStream(stream).Write(' '); stream := nil; //     stream := _t.aPhoto; IBinaryStream(stream).FileRead('C:\test.jpg'); stream := nil; //  N ""   pas.a rel := RelationshipObject(_t.aChilds); for i := 1 to N do begin _a := A(f.New('pas.a')); _a.aInteger := i; _a.aString := 'rel' + IntToStr(i); rel.Insert(_a); _a.SYS_Close; end; _t.aChilds := rel; rel.SYS_Close; //     _s := s(_t.aS_); _s.aInteger := 1; _s.aString := 's1'; _s.SYS_Close; //     .     listStr := ListOfDataTypes(f.New('%ListOfDataTypes')); for i := 1 to N do listStr.Insert('str' + IntToStr(i)); _t.aListOfString := listStr; listStr.SYS_Close; //    pas.a     listA := ListOfObjects(f.New('%ListOfObjects')); for i := 1 to N do begin _a := A(f.New('pas.a')); _a.aInteger := i; _a.aString := 'listA' + IntToStr(i); listA.Insert(_a); _a.SYS_Close; end; _t.aListOfA := listA; listA.SYS_Close; //     .     arrStr := ArrayOfDataTypes(f.New('%ArrayOfDataTypes')); for i := 1 to N do arrStr.SetAt('astr' + IntToStr(i), 'arraykey' + IntToStr(i)); _t.aArrOfString := arrStr; arrStr.SYS_Close; //    pas.a     arrA := ArrayOfObjects(f.New('%ArrayOfObjects')); for i := 1 to N do begin _a := A(f.New('pas.a')); _a.aInteger := i; _a.aString := 'arrayA' + IntToStr(i); arrA.SetAt(_a, 'arraykey' + IntToStr(i)); _a.SYS_Close; end; _t.aArrOfA := arrA; arrA.SYS_Close; //         .   //          // . _t.SYS_Save(0); //          stream := _t.aPhoto; SetOlePicture(img.Picture, IBinaryStream(stream).GetPicture); stream := nil; //    .      //   ,  "" (%sysOrefs). //         //       . _t.SYS_Close; //     _t := nil; mm.Lines.Append('OK'); except on E: Exception do begin mm.Lines.Append(E.Message); end; end; finally Screen.Cursor := crDefault; //      , //      f.ForceSync; end; 

Extent request (selection of all instances of the stored class)

 var mm: TMemo; rs: TResultSet; ... mm.Lines.Text:='Extent'#10#13; rs.ConnectTo(IResultSet(f.ResultSet('pas.test', 'Extent'))); rs.Execute; while rs.Next do begin mm.Lines.Append(Format('ID = %s',[rs.GetDataAsString(1)])); mm.Lines.Append(Format('aBoolean = %s',[rs.Get('aBoolean')])); mm.Lines.Append(Format('aInteger = %s',[rs.Get('aInteger')])); mm.Lines.Append(Format('aString = %s',[rs.Get('aString')])); mm.Lines.Append(Format('aDate = %s',[rs.Get('aDate')])); mm.Lines.Append(Format('aTimeStamp = %s',[rs.Get('aTimeStamp')])); mm.Lines.Append('-----'); end; rs.Close; rs.Disconnect; 

Request test1

 var i: integer; ... mm.Lines.Text:='test1'#10#13; rs.ConnectTo(IResultSet(f.ResultSet('pas.test', 'test1'))); rs.SetParam(1, null); rs.SetParam(2, 50); rs.SetParam(3, null); rs.SetParam(4, '03.03.2001'); rs.SetParam(5, '1900-01-02 12:34:55.0'); rs.Execute; while rs.Next do begin for i := 1 to rs.GetColumnCount do mm.Lines.Append(rs.GetColumnName(i)+' = '+rs.GetDataAsString(i)); mm.Lines.Append('-----'); end; rs.Close; rs.Disconnect; 

Request test2

 var i: integer; syslist: TSyslist; ... mm.Lines.Text:='test2'#10#13; //    %List syslist.Clear; syslist.Add(1); syslist.Add(2); syslist.Add(3); rs.ConnectTo(IResultSet(f.ResultSet('pas.test', 'test2'))); rs.Execute(syslist.DefaultInterface); while rs.Next do begin for i := 1 to rs.GetColumnCount do mm.Lines.Append(rs.GetColumnName(i)+' = '+rs.GetDataAsString(i)); mm.Lines.Append('-----'); end; rs.Close; rs.Disconnect; 

Test3 method Working with an object of class TSysList (type% List in Caché)

  mm.Lines.Text:='test3'#10#13; //    %List syslist.Clear; syslist.Add('16'); syslist.Add('42'); syslist.Add('35'); test_(f.Static('pas.test')).test3(syslist.DefaultInterface); 

Test4 method Error processing

  mm.Lines.Text:='test4'#10#13; try test_(f.Static('pas.test')).test4(); except on E: Exception do begin mm.Lines.Append(E.Message); end; end; 

Test5 method

  mm.Lines.Text:='test5'#10#13; //    %List syslist.Clear; syslist.Add('16'); syslist.Add('42'); syslist.Add('35'); test_(f.Static('pas.test')).test5(syslist.DefaultInterface); 

Test6 method

This example uses the class % XML.ZMyBaseDataSet , which can be found here . With it, you can form data on the server in the format of Borland ® MyBase (DataSnap (TM)) XML DataSet . Including for web services.

 cds1,cds2:TClientDataSet; ... var _a:a; __a,blob,rs1,rs2:IDispatch; cs1,cs2:ICharStream; begin mm.Lines.Text:='test6'#10#13; try _a:=a(f.OpenId('pas.a','1')); __a:=_a; test_(f.Static('pas.test')).test6('1',__a,blob,rs1,rs2); cs1:=ICharStream(rs1); cs2:=ICharStream(rs2); mm.Lines.Append('A.aString = '+_a.aString); mm.Lines.Append('BLOB.Size = '+IntToStr(IBinaryStream(blob).size)); mm.Lines.Append('RS1.Size = '+IntToStr(cs1.size)); mm.Lines.Append('RS2.Size = '+IntToStr(cs2.size)); cds1.XMLData:=cs1.Data; cds2.XMLData:=cs2.Data; finally _a.SYS_Close; _a:=nil; __a:=nil; blob:=nil; rs1:=nil; rs2:=nil; cs1:=nil; cs2:=nil; f.ForceSync; end; 

To support the % XML.ZMyBaseDataSet class of data in Unicode, as well as other unimplemented data types, you will need to customize it yourself.

Application distribution

To install with the application all the necessary drivers to the new computer, copy everything (for simplicity. You can limit the set of necessary files) files from the C: \ Program Files \ Common Files \ InterSystems \ Cache \ directory and register some of them in the system using the utility regsvr32 .exe .

EXAMPLE:

 regsvr32.exe /s "C:\Program Files (x86)\Common Files\Intersystems\Cache\CacheQuery.ocx" regsvr32.exe /s "C:\Program Files (x86)\Common Files\Intersystems\Cache\CacheFormWizard.dll" regsvr32.exe /s "C:\Program Files (x86)\Common Files\Intersystems\Cache\CacheList.ocx" regsvr32.exe /s "C:\Program Files (x86)\Common Files\Intersystems\Cache\CacheActiveX.dll" regsvr32.exe /s "C:\Program Files (x86)\Common Files\Intersystems\Cache\vism.ocx" regsvr32.exe /s "C:\Program Files (x86)\Common Files\Intersystems\Cache\TL.dll" 

Sources

• server part

    pas.xml 

• client part

    Unit1.dfm
   test_TLB.pas
   Unit1.pas 

• generated proxy classes

    test.odl