How do I reconnect?

[mzel]

Suppose I lost the connection to the database (DB2 in my case) due to some physical problem. I catch the exception and try to reconnect. No matter what I do I get an eception again. If I just issue a rlogin() call I get the exception which says that the connection is already in use. otl_initialize gives me the same problem. Calling logoff() produces some other exception.
So, my question is: what is the general approach to recover a lost connection to the same or a backup database?

[Sergei Kuchin]

try{
  db.logoff();
} catch(otl_exception& ex){
  // ignore the exception
}

at this point the "db" otl_connect object can be reused.

Sergei

[Juergen Stiebert]

Hello , I'm running into the same problem of reconnecting after database connection was lost.
My environment is Oracle 11.2 using OTL library version 4.0.360 from C++.
The connection is broken by changing the network connection from LAN to WLAN (user requirement). An otl_exception is generated and in the catch block I do the following:
otl_connect::logoff()
otl_connect::otl_terminate()
otl_connect::otl_initialize()
otl_connect::rlogon (user/pwd@DBAlias)

All parameters are correct and have been working fine during initial login.
I get a memory access violation in the 'server_attach' method (s. screenshot)
Thanks for any support, what to change here.

Juergen

[Juergen Stiebert]

Hello , I'm running into the same problem of reconnecting after database connection was lost.
My environment is Oracle 11.2 using OTL library version 4.0.360 from C++.
The connection is broken by changing the network connection from LAN to WLAN (user requirement). An otl_exception is generated and in the catch block I do the following:
otl_connect::logoff()
otl_connect::otl_terminate()
otl_connect::otl_initialize()
otl_connect::rlogon (user/pwd@DBAlias)

All parameters are correct and have been working fine during initial login.
I get a memory access violation in the 'server_attach' method (s. screenshot)
Thanks for any support, what to change here.

Juergen

[Juergen Stiebert]

Hello *

Here is a very simple program trying to reconnect to Oracle DB after connection has been terminated. This program causes a memory access violation in server_attach as described above in this discussion.
Can anybody explain to me, what is wrong here and how to reconnect successfully ?

Thanks
Juergen

using namespace std;
#include <stdio.h>
#include <iostream>

#define OTL_ORA11G_R2 // Compile OTL 4.0/OCI11.2
#include "otlv4.h" // include the OTL 4.0 header file

int main()
{

 otl_connect db;    

 otl_connect::otl_initialize(); // initialize OCI environment
 try
 {
    db.rlogon(<user>/<pwd>@db_alias"); // connect to Oracle
    otl_cursor::direct_exec
    (
        db, 
        "select <something>",
        otl_exception::disabled
    );  
 }

 catch(otl_exception& p)
 { 
    std::cout <<"--> EXCEPTION !! \n" << std::endl;
    std::cout<<p.msg<<endl; // print out error message
    std::cout<<p.stm_text<<endl; // print out SQL that caused the error
    std::cout<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff();       // disconnect from Oracle
 otl_connect::otl_terminate();  

 std::cout <<"--> Initializing OTL again ..  \n" << std::endl;
 otl_connect db2;
 otl_connect::otl_initialize();

 try
 {
     db2.rlogon(<user>/<pwd>@db_alias"); // connect to Oracle
    otl_cursor::direct_exec
    (
        db2, 
        "select <something>",
        otl_exception::disabled
    );  
 }

 catch(otl_exception& p2)
 { 
    // intercept OTL exceptions
    std::cout <<"--> EXCEPTION after reconnect !! \n" << std::endl;
    std::cout<<p2.msg<<endl; // print out error message
    std::cout<<p2.stm_text<<endl; // print out SQL that caused the error
    std::cout<<p2.var_info<<endl; // print out the variable that caused the error
 }

 db2.logoff(); // disconnect from Oracle

 return 0;

[Sergei Kuchin]

Juergen,

I don't watch the OTL forum that closely these days. Here's a quick code example that may be useful in your case:

include <iostream>

using namespace std;

include <stdio.h>

define OTL_ORA11G_R2

include <otlv4.h>

int main()
{
otl_connect::otl_initialize();
otl_connect db;

try{
db.rlogon("user/pwd@tns1");
db.direct_exec("DROP TABLE test_tab",otl_exception::disabled);
db.direct_exec("XXXCREATE TABLE test_tab(f1 NUMBER, f2 VARCHAR2(30))");
db.logoff();
}catch(otl_exception& e){
cerr<<e.msg<<endl;
cerr<<e.stm_text<<endl;
cerr<<e.var_info<<endl;
try{db.logoff();}catch(otl_exception&){} // ignore any errors
}

try{
db.rlogon("user/pwd@tns2");
db.direct_exec("DROP TABLE test_tab",otl_exception::disabled);
db.direct_exec("XXXCREATE TABLE test_tab(f1 NUMBER, f2 VARCHAR2(30))");
db.logoff();
}catch(otl_exception& e){
cerr<<e.msg<<endl;
cerr<<e.stm_text<<endl;
cerr<<e.var_info<<endl;
try{db.logoff();}catch(otl_exception&){} // ignore any errors
}

otl_connect::otl_terminate();
return 0;

}

otl_connect::otl_terminate() functions invokes OCITerminate() that closes the OCI environment, so you can't make any more OCI calls. If you need more help, drop me an email (email address is on the OTL Web site). Here's an excerpt from the OTL doc on otl_terminate:

It needs to be called only once at the end of the program after closing the very last database connection. This function is just a wrapper around the OCITerminate() call. Usually, in multi-threaded programs, in order to be able to terminate/end the main thread of control, otl_terminate needs to be called, because it detaches the process from the OCI client side shared memory, and does something else, that is not well documented.

Cheers,
Sergei

[Sergei Kuchin]

Juergen,

I received an email from you, but I get an email delivery error when I try to send a reply.

Answering your email here. If you have another email address from which you could email me, that would be helpful.

There may be some SQL Client settings for this kind of interruptions /
fail-overs. You'll have to dig into the Oracle manuals.

Did you say that you were just trying to simulate this scenario? If you can
reproduce the real scenario, capture the actual OCI errors / otl_exceptions
and email them to me, I could a take a closer look.

Sergei

[mzel]

&#1057;&#1087;&#1072;&#1089;&#1080;&#1073;&#1086; &#1073;&#1086;&#1083;&#1100;&#1096;&#1086;&#1077;, &#1087;&#1086;&#1084;&#1086;&#1075;&#1083;&#1086;!

[mzel]

Well, I guess this forum does not support Russian.
Sergei, thanks a lot that helped!