dotConnect connection timeout from tnsnames.ora

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
zhuqijun
Posts: 23
Joined: Fri 05 Jul 2013 06:56

dotConnect connection timeout from tnsnames.ora

Post by zhuqijun » Wed 20 Jan 2016 03:40

Hi,

I am using Devart dotConnect to connect to Oracle database.
In the connection string, there is a field to specify the connection timeout. But I do not want to change the connection string(it is hard code in our application).
My question is, is it possible to specify the connection timeout in tnsnames.ora, like:
myserver =
(DESCRIPTION =
(CONNECT_TIMEOUT=10)( RETRY_COUNT=1)
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbserver)
)
)

will the dotconnect use this 'CONNECT_TIMEOUT=10'?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: dotConnect connection timeout from tnsnames.ora

Post by Pinturiccio » Thu 21 Jan 2016 16:25

dotConnect for Oracle does not use the CONNECT_TIMEOUT parameter from the tnsnames.ora file directly. Our provider sends information from a connection string to Oracle Client and Oracle Client establishes a connection. And Oracle Client decides how the CONNECT_TIMEOUT parameter is used.

We performed two tests:
1. We specified an invalid host in a net service in a tnsnames.ora. We got the following results:

- CONNECT_TIMEOUT is set to a small value, and our 'Connection Timeout' connection string parameter is set to a large value. In this case establishing a connection is not interrupted when reaching the CONNECT_TIMEOUT limit. It continues until the "ORA-12545: Connect failed because target host or object does not exist" exception occurs. Connection establishing takes significantly longer than specified in the CONNECT_TIMEOUT parameter.

- Our 'Connection Timeout' connection string parameter is set to a small value. When the limit, specified in this parameter is reached, connection establishing is immediately interrupted and the "Server did not respond within the specified timeout interval" exception occurs. Connection establishing is no longer than specified in our 'Connection Timeout' connection string parameter.

In case of invalid host, the CONNECT_TIMEOUT parameter does not limit the time of establishing a connection. This is not specific to dotConnect for Oracle. SQL*Plus also tries to establish a connection for a time longer than specified in the CONNECT_TIMEOUT parameter in such case.

2. We specified a valid net service in a tnsnames.ora. But in connection string we use a wrong user name.

- CONNECT_TIMEOUT is set to a small value, and our 'Connection Timeout' connection string parameter is set to a larger value. In this case connection establishing is interrupted when reaching the CONNECT_TIMEOUT limit with the "ORA-12170: TNS:Connect timeout occurred" exception. It takes no longer than specified in the CONNECT_TIMEOUT parameter.

- Our 'Connection Timeout' connection string parameter is set to a small value and CONNECT_TIMEOUT is set to a larger value. In this case connection establishing is interrupted when reaching the 'Connection Timeout' limit with the "Server did not respond within the specified timeout interval" exception. It takes no longer than specified in the "Connection Timeout" connection string parameter.

In this case (of an invalid user name) CONNECT_TIMEOUT works in the same way as "Connection Timeout". You can change CONNECT_TIMEOUT in a net service if you want to reduce the time of connection establishing attempts without changing your connection string.

For more detailed CONNECT_TIMEOUT description and information why it works in some cases and doesn't work in other cases, contact Oracle support.

zhuqijun
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: dotConnect connection timeout from tnsnames.ora

Post by zhuqijun » Fri 22 Jan 2016 05:06

Hello,

Thanks for your information.

Let me provide more details for my case. I get the exception "Server did not respond within the specified timeout interval", so I want to increase the connection timeout, but I cannot modify the connection string, because it is hard code. So I am seeking a way to modify it in tnsnames.ora

Can you confirm that by modify tnsnames.ora cannot solve the issue "Server did not respond within the specified timeout interval" ?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: dotConnect connection timeout from tnsnames.ora

Post by Pinturiccio » Fri 22 Jan 2016 15:42

The 'Connection Timeout' connection string parameter does not depend on any parameter in tnsnames.ora file. If you change CONNECT_TIMEOUT in the tnsnames.ora file, it does not affect the 'Connection Timeout' parameter value. Connection establishing cannot take longer than specified in the 'Connection Timeout' parameter. The only way to increase connection timeout - is to increase the 'Connection Timeout' connection string parameter value.

Post Reply