[Openroad-users] Connecting to other Databases from OpenROAD

[Paul White]

EA/MSSQL is nearly no hassle.

Peercore runs the same code over MSSQL and ingres. Also, we installed
and configured EA/Oracle in under a day. The interface helped get a huge
integration / migration across the line (SAP I think). The customer had
less than an hour downtime and the old database / applications were able
to run continuously until we had finished the functional migration some
months later. The relatively low cost was absolutely worth it.

EA
You can choose to install Enterprise Access at the client end,  MSSQL
server end or on a completely separate machine. It can be installed into
an Ingres/OpenROAD installation. You can directly link to MSSQL from the
ingres server. (I don't know about replications)

You can configure unlimited db connections.  EA/MSSQL runs well in an
automatic failover configuration.

Setting up vnodes is a snack using vdba. The connection string is
basically the same:

 Lv_Database = 'vnode::dbname/mssql';
 Li_RtnStatus = CurFrame.DbSession.Connect(database = :Lv_Database);

If you are planning on migrating applications you do need to expend a
bit more effort.
Understanding data mapping rules is important. Eg treatment of null and
empty dates in mssql.
And one or two changes in syntax. Eg update from.
Also you need to consider differences in record locking, data storage
and some DML is different.

Session settings are different also:

   li_RtnStatus = 0;
    select :Lv_ServerClass = ifnull(dbmsinfo('Server_Class'),'');
    inquire_sql (Li_RtnStatus = errorno, Lv_Msg = errortext);

    if Li_RtnStatus <> 0 then
        Lv_Msg = 'Error determining server class. Error=' + Lv_Msg;
    elseif Lv_ServerClass = 'MSSQL' then
        direct execute immediate 'SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED';
        direct execute immediate 'SET LOCK_TIMEOUT 10000';
        direct execute immediate 'SET XACT_ABORT ON';
    elseif Lv_ServerClass = 'Ingres DBMS Server' then
        set lockmode session where readlock = nolock, timeout = 10;
    else 
        Lv_Msg = 'Warning server class ' + lv_ServerClass + ' not
implemented';
        Li_RtnStatus = -1;
    endif;
    commit;


If all this doesn't sound exciting enough, you might like to venture
into deeper water, take a look at a few postings around 13th March.
There are examples accessing MSADO.  See:
http://www.peerlessit.com/pipermail/openroad-users/

Also, I can put you in touch with a business partner who has developed
an interface to retreive data directly via ODBC. I think it was
MSACCESS, but I'm sure it wouldn't be hard for them to do the same for
any other odbc complient db.



Paul