Board index » delphi » Re: TADOConnection and connection lifetime

Re: TADOConnection and connection lifetime


2005-05-31 10:26:53 PM
delphi228
Regarding the connection issue, are you connecting via Listener or is this
local connection to the database?
<XXXX@XXXXX.COM>writes
Quote
Hi,

i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC 2.7
and up).

1) on my application i have made a limited tries login process. First a
successful login is done, then after closing the application, i test my
"security" process by making x wrong tries that will finally drop the
current user. I got then an Oracle error:
ORA-01940: cannot drop a user that is currently connected
I saw in the V$SESSION table that indeed the user i wish to drop is still
alive.
How can i kill sooner a connection?

2) Another part of my application is using Global Temporary Tables, design
by sessions. When the session is closed, then all the data inside
disappear.
When i reconnect from the same user i got a connection that seems to be
dirty, i mean keeping inside some data from previous session.
How can i clean the connection when i close it or return it to the pool?


I saw that there is more properties that can be add to a connection
string, like:

Connection Lifetime
Connection Reset

I try to use these but without great changes!

I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
workstation.

Any direction of research will be welcomed.

Tia

Laurent
 
 

Re: TADOConnection and connection lifetime

Hi,
i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC 2.7
and up).
1) on my application i have made a limited tries login process. First a
successful login is done, then after closing the application, i test my
"security" process by making x wrong tries that will finally drop the
current user. I got then an Oracle error:
ORA-01940: cannot drop a user that is currently connected
I saw in the V$SESSION table that indeed the user i wish to drop is
still alive.
How can i kill sooner a connection?
2) Another part of my application is using Global Temporary Tables,
design by sessions. When the session is closed, then all the data inside
disappear.
When i reconnect from the same user i got a connection that seems to be
dirty, i mean keeping inside some data from previous session.
How can i clean the connection when i close it or return it to the pool?
I saw that there is more properties that can be add to a connection
string, like:
Connection Lifetime
Connection Reset
I try to use these but without great changes!
I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
workstation.
Any direction of research will be welcomed.
Tia
Laurent
 

Re: TADOConnection and connection lifetime

Hi,
i'm connecting via Listener (TNSnames.ora).
Michael Jacobs writes:
Quote
Regarding the connection issue, are you connecting via Listener or is this
local connection to the database?

<XXXX@XXXXX.COM>writes
news:429c68bf$XXXX@XXXXX.COM...

>Hi,
>
>i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC 2.7
>and up).
>
>1) on my application i have made a limited tries login process. First a
>successful login is done, then after closing the application, i test my
>"security" process by making x wrong tries that will finally drop the
>current user. I got then an Oracle error:
>ORA-01940: cannot drop a user that is currently connected
>I saw in the V$SESSION table that indeed the user i wish to drop is still
>alive.
>How can i kill sooner a connection?
>
>2) Another part of my application is using Global Temporary Tables, design
>by sessions. When the session is closed, then all the data inside
>disappear.
>When i reconnect from the same user i got a connection that seems to be
>dirty, i mean keeping inside some data from previous session.
>How can i clean the connection when i close it or return it to the pool?
>
>
>I saw that there is more properties that can be add to a connection
>string, like:
>
>Connection Lifetime
>Connection Reset
>
>I try to use these but without great changes!
>
>I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
>workstation.
>
>Any direction of research will be welcomed.
>
>Tia
>
>Laurent



 

Re: TADOConnection and connection lifetime

Hi,
it should be disconnecting when you close your cnnection unless you have
Listener configured to keep connections open. For that you need to look
into the Listener.ora file. Something you might try is to send 'exit' as
the final SQL statement when the user is closing the connection. Finally, a
workaround is to issue 'revoke connect from username' rather 'drop user
username'. The revoke will always work regardless of whether the user is
currently logged on or not and will 'lock' them out the next time they
attempt to login to the RDBMS.
HTH,
Michael
<XXXX@XXXXX.COM>writes
Quote
Hi,

i'm connecting via Listener (TNSnames.ora).


Michael Jacobs writes:

>Regarding the connection issue, are you connecting via Listener or is
>this local connection to the database?
>
><XXXX@XXXXX.COM>writes
>news:429c68bf$XXXX@XXXXX.COM...
>
>>Hi,
>>
>>i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC 2.7
>>and up).
>>
>>1) on my application i have made a limited tries login process. First a
>>successful login is done, then after closing the application, i test my
>>"security" process by making x wrong tries that will finally drop the
>>current user. I got then an Oracle error:
>>ORA-01940: cannot drop a user that is currently connected
>>I saw in the V$SESSION table that indeed the user i wish to drop is still
>>alive.
>>How can i kill sooner a connection?
>>
>>2) Another part of my application is using Global Temporary Tables,
>>design by sessions. When the session is closed, then all the data inside
>>disappear.
>>When i reconnect from the same user i got a connection that seems to be
>>dirty, i mean keeping inside some data from previous session.
>>How can i clean the connection when i close it or return it to the pool?
>>
>>
>>I saw that there is more properties that can be add to a connection
>>string, like:
>>
>>Connection Lifetime
>>Connection Reset
>>
>>I try to use these but without great changes!
>>
>>I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
>>workstation.
>>
>>Any direction of research will be welcomed.
>>
>>Tia
>>
>>Laurent
>
>
 

Re: TADOConnection and connection lifetime

Hi Michael,
tnx for u input.
I try something a little bit different. A simple application with a
TAdoConnection created on the fly.
I do insert with the same Component via the command, do a disconnect,
when i reconnect, i still see the data i just inserted (also using the
same connection component, and doing another command (SELECT)), thing
that must be impossible because I am using an Oracle Global Temporary
table defined by session!
It is some how random, when i change the connection string, and then
reconnect sometimes the table is empty.
I try the same thing with Delphi 7, same result!
Or I am doing something wrong or something wrong with ADO!
Tia
Laurent
Michael Jacobs writes:
Quote
Hi,

it should be disconnecting when you close your cnnection unless you have
Listener configured to keep connections open. For that you need to look
into the Listener.ora file. Something you might try is to send 'exit' as
the final SQL statement when the user is closing the connection. Finally, a
workaround is to issue 'revoke connect from username' rather 'drop user
username'. The revoke will always work regardless of whether the user is
currently logged on or not and will 'lock' them out the next time they
attempt to login to the RDBMS.

HTH,

Michael
<XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...

>Hi,
>
>i'm connecting via Listener (TNSnames.ora).
>
>
>Michael Jacobs writes:
>
>
>>Regarding the connection issue, are you connecting via Listener or is
>>this local connection to the database?
>>
>><XXXX@XXXXX.COM>writes
>>news:429c68bf$XXXX@XXXXX.COM...
>>
>>
>>>Hi,
>>>
>>>i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC 2.7
>>>and up).
>>>
>>>1) on my application i have made a limited tries login process. First a
>>>successful login is done, then after closing the application, i test my
>>>"security" process by making x wrong tries that will finally drop the
>>>current user. I got then an Oracle error:
>>>ORA-01940: cannot drop a user that is currently connected
>>>I saw in the V$SESSION table that indeed the user i wish to drop is still
>>>alive.
>>>How can i kill sooner a connection?
>>>
>>>2) Another part of my application is using Global Temporary Tables,
>>>design by sessions. When the session is closed, then all the data inside
>>>disappear.
>>>When i reconnect from the same user i got a connection that seems to be
>>>dirty, i mean keeping inside some data from previous session.
>>>How can i clean the connection when i close it or return it to the pool?
>>>
>>>
>>>I saw that there is more properties that can be add to a connection
>>>string, like:
>>>
>>>Connection Lifetime
>>>Connection Reset
>>>
>>>I try to use these but without great changes!
>>>
>>>I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
>>>workstation.
>>>
>>>Any direction of research will be welcomed.
>>>
>>>Tia
>>>
>>>Laurent
>>
>>

 

Re: TADOConnection and connection lifetime

Hi Michael,
1) Listener.ora: i did not find nothing on my machine. Do i have to
check on the Oracle server?
2) after googleling a lot, i did not found any Exit command except the
one to exit a loop.
3) Indeed, i thought about revoking the user, but somehow it is a little
problematic in my environment.
Have a nice week end and tnx again for ur time.
Bye
Laurent
Michael Jacobs writes:
Quote
Hi,

it should be disconnecting when you close your cnnection unless you have
Listener configured to keep connections open. For that you need to look
into the Listener.ora file. Something you might try is to send 'exit' as
the final SQL statement when the user is closing the connection. Finally, a
workaround is to issue 'revoke connect from username' rather 'drop user
username'. The revoke will always work regardless of whether the user is
currently logged on or not and will 'lock' them out the next time they
attempt to login to the RDBMS.

HTH,

Michael
<XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...

>Hi,
>
>i'm connecting via Listener (TNSnames.ora).
>
>
>Michael Jacobs writes:
>
>
>>Regarding the connection issue, are you connecting via Listener or is
>>this local connection to the database?
>>
>><XXXX@XXXXX.COM>writes
>>news:429c68bf$XXXX@XXXXX.COM...
>>
>>
>>>Hi,
>>>
>>>i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC 2.7
>>>and up).
>>>
>>>1) on my application i have made a limited tries login process. First a
>>>successful login is done, then after closing the application, i test my
>>>"security" process by making x wrong tries that will finally drop the
>>>current user. I got then an Oracle error:
>>>ORA-01940: cannot drop a user that is currently connected
>>>I saw in the V$SESSION table that indeed the user i wish to drop is still
>>>alive.
>>>How can i kill sooner a connection?
>>>
>>>2) Another part of my application is using Global Temporary Tables,
>>>design by sessions. When the session is closed, then all the data inside
>>>disappear.
>>>When i reconnect from the same user i got a connection that seems to be
>>>dirty, i mean keeping inside some data from previous session.
>>>How can i clean the connection when i close it or return it to the pool?
>>>
>>>
>>>I saw that there is more properties that can be add to a connection
>>>string, like:
>>>
>>>Connection Lifetime
>>>Connection Reset
>>>
>>>I try to use these but without great changes!
>>>
>>>I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
>>>workstation.
>>>
>>>Any direction of research will be welcomed.
>>>
>>>Tia
>>>
>>>Laurent
>>
>>

 

Re: TADOConnection and connection lifetime

Hi Laurent,
1. Yes, the Listener.ora file will be on the server hosting Oracle.
2. the exit command is simply that 'exit'
HTH,
Michael
PS. I will look in the global temp tables when I have moment
<XXXX@XXXXX.COM>writes
Quote
Hi Michael,

1) Listener.ora: i did not find nothing on my machine. Do i have to check
on the Oracle server?

2) after googleling a lot, i did not found any Exit command except the one
to exit a loop.

3) Indeed, i thought about revoking the user, but somehow it is a little
problematic in my environment.

Have a nice week end and tnx again for ur time.

Bye

Laurent

Michael Jacobs writes:

>Hi,
>
>it should be disconnecting when you close your cnnection unless you
>have Listener configured to keep connections open. For that you need to
>look into the Listener.ora file. Something you might try is to send
>'exit' as the final SQL statement when the user is closing the
>connection. Finally, a workaround is to issue 'revoke connect from
>username' rather 'drop user username'. The revoke will always work
>regardless of whether the user is currently logged on or not and will
>'lock' them out the next time they attempt to login to the RDBMS.
>
>HTH,
>
>Michael
><XXXX@XXXXX.COM>writes
>news:XXXX@XXXXX.COM...
>
>>Hi,
>>
>>i'm connecting via Listener (TNSnames.ora).
>>
>>
>>Michael Jacobs writes:
>>
>>
>>>Regarding the connection issue, are you connecting via Listener or is
>>>this local connection to the database?
>>>
>>><XXXX@XXXXX.COM>writes
>>>news:429c68bf$XXXX@XXXXX.COM...
>>>
>>>
>>>>Hi,
>>>>
>>>>i have 2 problems linked to Connections with ORACLE 9i and ADO (MDAC
>>>>2.7 and up).
>>>>
>>>>1) on my application i have made a limited tries login process. First a
>>>>successful login is done, then after closing the application, i test my
>>>>"security" process by making x wrong tries that will finally drop the
>>>>current user. I got then an Oracle error:
>>>>ORA-01940: cannot drop a user that is currently connected
>>>>I saw in the V$SESSION table that indeed the user i wish to drop is
>>>>still alive.
>>>>How can i kill sooner a connection?
>>>>
>>>>2) Another part of my application is using Global Temporary Tables,
>>>>design by sessions. When the session is closed, then all the data
>>>>inside disappear.
>>>>When i reconnect from the same user i got a connection that seems to be
>>>>dirty, i mean keeping inside some data from previous session.
>>>>How can i clean the connection when i close it or return it to the
>>>>pool?
>>>>
>>>>
>>>>I saw that there is more properties that can be add to a connection
>>>>string, like:
>>>>
>>>>Connection Lifetime
>>>>Connection Reset
>>>>
>>>>I try to use these but without great changes!
>>>>
>>>>I'm using Delphi 5 (update 1), Oracle 9i, MDAC 2.7, on Win2K SP4
>>>>workstation.
>>>>
>>>>Any direction of research will be welcomed.
>>>>
>>>>Tia
>>>>
>>>>Laurent
>>>
>>>
>