Discover Caché SQL Gateway for building federated systems or multibases

In complex systems, there is often a question of integrating data from different sources.
Such systems are called integrated, federated or multibase .

In Caché, this integration is performed using a special gateway ( Caché SQL Gateway ), which basically uses ODBC / JDBC connections to external data sources. Moreover, the source in this case can be understood not only a DBMS, since there are JDBC / ODBC drivers for MS Excel, DBF, text files, graphic files, WMI, etc.

Briefly, how to use Caché SQL Gateway :

1. in the System Management Portal ( SMP ) create a connection of the desired type, specifying the connection string, login, password, etc. Here you can check the created connection;
2. using the Binding Wizard, you link the required tables and / or procedures to Caché from the external DBMS using the connection from the step above. In this case, no data is copied anywhere, but only special virtual classes are created, they are also tables;
3. Now, having connected to the field - a logical database in Caché terms - you will see tables, views, stored procedures (CPs) from external sources in it: Oracle, DB2, MSSQL, MySQL, Excel, DBF, CSV, etc.
4. then you can work with these tables / CPs in both directions, as if they were physically located in Caché.

Note: Anticipating questions about the support of heterogeneous queries to different data sources, I hasten to notice about the presence of some limitations in the queries.
')
In other words, the JOIN between tables from Oracle and MSSQL will fail.

These restrictions apply only to so-called related tables. If you use the Migration Wizard of structures and data instead of the Binding Wizard , then there will be no such restrictions.

---

Creating linked tables

As mentioned above, you can use either ODBC or JDBC driver to connect to an external data source. Consider both options.

For ODBC, you must first configure system DSNs:





And for JDBC , the path to the Java virtual machine ( hereinafter all the pictures are clickable ):



Next, in SMP, you need to create connections for the Caché SQL Gateway of the desired type ( JDBC or ODBC ), on which these or other settings will depend. Here you can check the newly created connection:





Then, using the Tables or procedures Binding Wizard , you should create the necessary virtual tables or CPs:





The wizard will query for each table its new name, new names for each of the fields, primary key, etc. In most cases, you can leave all the names by default, but there are situations where some of these identifiers are reserved words from the Caché DBMS.

An example of a generated virtual class for an external job_titles table:

Class dbo.jobtitles Extends% Library.Persistent [ ClassType = persistent, Not ProcedureBlock , SqlRowIdPrivate , SqlTableName = job_titles , StorageStrategy = GSQLStorage]
{

Parameter CONNECTION = "ems, NOCREATE" ;

Parameter EXTDBNAME = "Microsoft SQL Server" ;

Parameter EXTERNALTABLENAME = "dbo.job_titles" ;

Property INTERETHNICVALUE As% String ( EXTERNALSQLNAME = "INTERETHNIC_VALUE" , EXTERNALSQLTYPE = 12 , MAXLEN = 50 ) [ Required , SqlColumnNumber = 5, SqlFieldName = INTERETHNIC_VALUE ];

Property INTERNATIONALVALUE As% String ( EXTERNALSQLNAME = "INTERNATIONAL_VALUE" , EXTERNALSQLTYPE = 12 , MAXLEN = 50 ) [ Required , SqlColumnNumber = 4, SqlFieldName = INTERNATIONAL_VALUE ];

Property NATIONALVALUE As% String ( EXTERNALSQLNAME = "NATIONAL_VALUE" , EXTERNALSQLTYPE = 12 , MAXLEN = 50 ) [ Required , SqlColumnNumber = 6, SqlFieldName = NATIONAL_VALUE ];

Property id As% Integer ( EXTERNALSQLNAME = "id" , EXTERNALSQLTYPE = 4 ) [ Required , SqlColumnNumber = 2, SqlFieldName = id ];

Property priority As% Integer ( EXTERNALSQLNAME = "priority" , EXTERNALSQLTYPE = 4 ) [ Required , SqlColumnNumber = 3, SqlFieldName = priority ];

Index MainIndex On id [ IdKey , PrimaryKey ];

}

Note: if necessary, you can add the code of the generated class with some plugs from Caché - class and / or object methods, calculated fields, superclasses, CP, - which are absent in the original tables.
But we should not forget that when you re-generate the associated tables / CP all your additional code will be lost.

Everything, now you can, connecting to Caché using any ODBC / JDBC client (see one of the previous articles ), see all tables and procedures, both internal and external; and the difference between them is difficult to determine immediately.

Well, of course, you can insert / delete / modify data using the native SQL syntax of any DBMS.

Caché SQL Gateway can also be used for DeepSee embedded business intelligence, but that’s another topic.

---

Program access to external data

In addition to creating linked tables, you can work with external data programmatically:

• Jdbc
• ODBC

An example of software ODBC access using a system DSN:

set db = ## class ( % SQLGatewayConnection ). % New ()
set res = db. Connect ( "DSNName" , "username" , "password" )
set rs = ## class ( % ResultSet ). % New ( "% DynamicQueryGW: SQLGW" )
do rs. Prepare ( "SELECT * FROM users WHERE id =?" ,, db)
do rs. Execute (46)
while rs. Next () {
for i = 1: 1: rs. GetColumnCount () {
write rs. GetData (i)
if i '= rs. GetColumnCount () {
write ","
} else {
write !
}
}
}
do db. Disconnect ()

In this case, you no longer need to create connections for the Caché SQL Gateway , since "DSNName" is the DSN name defined in the OC itself.

The example above demonstrates the so-called high-level access to working with ODBC using the % ResultSet and % DynamicQueryGW classes , but low-level access is also possible. In this case, only methods of class % SQLGatewayConnection are used .

The following is an example of querying an external table using low-level access. This query selects all fields from the INFO table of an external ODBC source (with the name DSNName ) for which the value of the field Age = 21 and the value of the field Name begins with the letter "D".

set db = ## class ( % SQLGatewayConnection ). % New ()
// establish connection
do db. Connect ( "DSNName" , "sa" , "pwd" )
// create a new team
set sc = db. AllocateStatement (.Stat)
// preparation of the request
set sc = db. Prepare (Stat, "SELECT * FROM INFO WHERE Age =? AND Name LIKE?" )
// preparation of parameters
set sc = db. BindParameters (Stat, $ listbuild (1,1), $ listbuild (4.12), $ listbuild (4.50), $ listbuild (0,0), $ listbuild (4.50))
set sc = db. SetParameter (Stat, $ listbuild (21), 1)
set sc = db. SetParameter (Stat, $ listbuild ( "D%" ), 2)
// query execution
set sc = db. Execute (Stat)
for {
quit : 'db. Fetch (stat)
set sc = db. GetOneRow (Stat, .Row)
for j = 1: 1: $ listlength (Row) write $ listget (Row, j) _ ""
write !
}
// delete command
set sc = db. DropStatement (Stat)
// disconnect
set sc = db. Disconnect ()

So, consider this example in more detail.

• The Connect method (DSN, User, Password) is used to connect to an ODBC data source (DSN ) :

set db = ## class ( % SQLGatewayConnection ). % New ()
// establish connection
do db. Connect ( "DSNName" , "sa" , "pwd" )

• To disconnect, use the Disconnect () method:

set sc = db. Disconnect ()

• Before you execute a query, you must first create a Statement command. To create a command, the AllocateStatement () method is used, the Handle argument to the AllocateStatement () method is passed by reference (before the name of the argument you need to put a “.”):

set sc = db. AllocateStatement (.Stat)

• To remove a command, use the DropStatement () method:

set sc = db. DropStatement (Stat)

• Before executing a query, it must be "prepared", for which the Prepare method (Stat, sql) is used . The created command and the SQL query string are passed as arguments to the method. It is possible to execute queries with parameters, then in the sql line in place of the parameters, the characters "?" Are put:

// preparation of the request
set sc = db. Prepare (Stat, "SELECT * FROM INFO WHERE Age =? AND Name LIKE?" )

• If an sql query with parameters is transmitted, these parameters must be prepared and assigned to them certain values. To prepare the parameters, use the BindParameters () method. Arguments of the BindParameters () method:

• created team;
• Parameter types: 1 - in (input), 2-in / out (input / output), 4-out (output, ...);
• The list of ODBC data types (for example: 4-INTEGER, 9-DATE, 12-VARCHAR, 8-DOUBLE, ...);
• buffer sizes in bytes;
• the number of characters after the point (only for Decimal and Float);
• list of data type lengths in bytes.

• All arguments to the BindParameters () method, starting from the second, are of type % List and must be passed as arguments to the $ listbuild () function. If there are several parameters in the request, then the values ​​of $ listbuild () are passed the values ​​separated by commas, corresponding to each parameter in the order they appear in the query string, for example:

set sc = db. BindParameters (Stat, $ listbuild (1,1), $ listbuild (4.12), $ listbuild (4.50), $ listbuild (0,0), $ listbuild (4.50))

or

#include % occODBC ; do not forget to include the necessary files with macros
set sc = db. BindParameters (
Stat,
$ listbuild ( $$$ SQLPARAMINPUT , $$$ SQLPARAMINPUT ),
$ listbuild ( $$$ ODBCTYPEinteger , $$$ ODBCTYPEvarchar ),
$ listbuild (4.50),
$ listbuild (0,0),
$ listbuild (4.50)
)

Note: in the code presented initially, numeric parameter type identifiers are used. In real code, it is better to use macros from the % occODBC.inc or % msql.inc files , for example:

• $$$ GetOdbcTypeNumber ("INTEGER") or $$$ ODBCTYPEinteger will return 4;
• $$$ SQLPARAMINPUT returns 1 (input);
• $$$ SQLPARAMINPUTOUTPUT returns 2 (input / output);
• $$$ SQLPARAMOUTPUT returns 4 (output);
• etc.

A complete list of macros can be found in the corresponding files, using, for example, Caché Studio .

• Assigning a value to a parameter using the SetParameter method (Stat, $ listbuild (val), Numb) . As arguments, the SetParameter () method is passed:

• created team;
• parameter value as an argument of the $ listbuild () function;
• the sequence number of the parameter in the query string.

• The SetParameter () method is called separately for each parameter passed:

set sc = db. SetParameter (Stat, $ listbuild (21), 1)
set sc = db. SetParameter (Stat, $ listbuild ( "D%" ), 2)

• To execute the query, the Execute (Stat) method is used, to which the created command is passed as an argument:

set sc = db. Execute (Stat)

Note: Additional examples of programmatic access to external data using JDBC / ODBC can be found in the source code of the % UnitTest.JDBCSQL and % UnitTest.ODBCSQL classes, respectively.