Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

terminate connect

14 posts in General Discussion Last posting was on 2009-02-02 16:32:02.0Z
Alex Posted on 2009-01-28 14:00:37.0Z
Sender: 4332.498062ad.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: terminate connect
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49806505.437a.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jan 2009 06:00:37 -0800
X-Trace: forums-1-dub 1233151237 10.22.241.41 (28 Jan 2009 06:00:37 -0800)
X-Original-Trace: 28 Jan 2009 06:00:37 -0800, 10.22.241.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7322
Article PK: 5532

hi

what is right way to force terminate connection?

for instance, i've created stored procedure to delay for 5
min, called it and tried to terminate connection with
dbpp_disconnect().
Server returns
&#8211;298 Attempted two active database requests


David Fishburn Posted on 2009-01-28 14:39:41.0Z
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect
From: David Fishburn <fishburn_spam_off@ianywhere.com>
References: <49806505.437a.1681692777@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns9BA1624803C14fishburnsybasecom@10.22.241.106>
User-Agent: Xnews/2005.10.18
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 28 Jan 2009 06:39:41 -0800
X-Trace: forums-1-dub 1233153581 10.22.241.152 (28 Jan 2009 06:39:41 -0800)
X-Original-Trace: 28 Jan 2009 06:39:41 -0800, vip152.sybase.com
Lines: 45
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7324
Article PK: 5535

Alex wrote in news:49806505.437a.1681692777@sybase.com
of ianywhere.public.general:

A> what is right way to force terminate connection?
A>
A> for instance, i've created stored procedure to delay for 5
A> min, called it and tried to terminate connection with
A> dbpp_disconnect().
A> Server returns
A> &#8211;298 Attempted two active database requests

Please ALWAYS include version and MORE importantly BUILD number
with EACH post (dbeng11 -v).

1. Are you trying to disconnect your _current_ connection?
2. Are you trying to disconnect a _different_ connection?

1. Is solved by executing a DISCONNECT statement.

2. Is solved by executing a DROP CONNECTION statement, but you must
know the connection_id of the one you want to drop.
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng11 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/

Developer Community / Whitepapers
http://www.ianywhere.com/developer

Case-Express - to report bugs
http://case-express.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList

Documentation
http://dcx.sybase.com/


John Smirnios [Sybase] Posted on 2009-01-28 15:03:25.0Z
From: "John Smirnios [Sybase]" <smirnios_at_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect
References: <49806505.437a.1681692777@sybase.com> <Xns9BA1624803C14fishburnsybasecom@10.22.241.106>
In-Reply-To: <Xns9BA1624803C14fishburnsybasecom@10.22.241.106>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <498073bd@forums-1-dub>
Date: 28 Jan 2009 07:03:25 -0800
X-Trace: forums-1-dub 1233155005 10.22.241.152 (28 Jan 2009 07:03:25 -0800)
X-Original-Trace: 28 Jan 2009 07:03:25 -0800, vip152.sybase.com
Lines: 43
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7325
Article PK: 5534

In the case of #1 with a currently pending request, you need to issue a
cancel first. Cancel can be issued while a request is already in
progress. The request that is in progress will fail and _after_ that
failure is reported you can disconnect. See db_cancel_request() in the
documentation.

BTW, it's not a good idea to call dbpp_disconnect directly: the
interfaces to the dbpp_ functions can change in any major version
release of the product. Calls to the dbpp_ functions should only be
generated by sqlpp when processing an embedded SQL file. See EXEC SQL
DISCONNECT CURRENT in the docs.

-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

David Fishburn wrote:
> Alex wrote in news:49806505.437a.1681692777@sybase.com
> of ianywhere.public.general:
>
> A> what is right way to force terminate connection?
> A>
> A> for instance, i've created stored procedure to delay for 5
> A> min, called it and tried to terminate connection with
> A> dbpp_disconnect().
> A> Server returns
> A> &#8211;298 Attempted two active database requests
>
> Please ALWAYS include version and MORE importantly BUILD number
> with EACH post (dbeng11 -v).
>
> 1. Are you trying to disconnect your _current_ connection?
> 2. Are you trying to disconnect a _different_ connection?
>
> 1. Is solved by executing a DISCONNECT statement.
>
> 2. Is solved by executing a DROP CONNECTION statement, but you must
> know the connection_id of the one you want to drop.


Alex Posted on 2009-01-28 15:26:40.0Z
Sender: 4332.498062ad.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49807930.46dc.1681692777@sybase.com>
References: <498073bd@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jan 2009 07:26:40 -0800
X-Trace: forums-1-dub 1233156400 10.22.241.41 (28 Jan 2009 07:26:40 -0800)
X-Original-Trace: 28 Jan 2009 07:26:40 -0800, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7327
Article PK: 5537


> In the case of #1 with a currently pending request, you
> need to issue a cancel first. Cancel can be issued while
> a request is already in progress. The request that is in
> progress will fail and _after_ that failure is reported
> you can disconnect. See db_cancel_request() in the
> documentation.

tnx, will try.


Alex Posted on 2009-01-28 15:38:41.0Z
Sender: 4332.498062ad.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49807c01.4779.1681692777@sybase.com>
References: <498073bd@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jan 2009 07:38:41 -0800
X-Trace: forums-1-dub 1233157121 10.22.241.41 (28 Jan 2009 07:38:41 -0800)
X-Original-Trace: 28 Jan 2009 07:38:41 -0800, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7328
Article PK: 5538


> In the case of #1 with a currently pending request, you
> need to issue a cancel first. Cancel can be issued while
> a request is already in progress. The request that is in
> progress will fail and _after_ that failure is reported
> you can disconnect. See db_cancel_request() in the
> documentation.

same error, db_cancel_request() returns 1, but it has no
affect on the attempt terminate connection.

any idea more?


John Smirnios [Sybase] Posted on 2009-01-28 16:22:00.0Z
From: "John Smirnios [Sybase]" <smirnios_at_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
References: <498073bd@forums-1-dub> <49807c01.4779.1681692777@sybase.com>
In-Reply-To: <49807c01.4779.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49808628$1@forums-1-dub>
Date: 28 Jan 2009 08:22:00 -0800
X-Trace: forums-1-dub 1233159720 10.22.241.152 (28 Jan 2009 08:22:00 -0800)
X-Original-Trace: 28 Jan 2009 08:22:00 -0800, vip152.sybase.com
Lines: 39
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7330
Article PK: 5540

Did you wait until the original request completed? You can't just call
db_cancel_request( SQLCA );
EXEC SQL DISCONNECT CURRENT;
because you need to wait for the statement you are cancelling to finish
and get a response back to your application (though it will finish
earlier than it would without a cancel).

After you call cancel, your
EXEC SQL call myprocedure();
will soon fail with an error code of SQLSTATE_INTERRUPTED. Only then can
you call EXEC SQL DISCONNECT. Usually, the "main" thread (the one
calling the procedure needs to detect that the connection "should go
away" after its execution fails.

You can also register a callback to know when requests finish. See
db_register_a_callback().

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Alex wrote:
>> In the case of #1 with a currently pending request, you
>> need to issue a cancel first. Cancel can be issued while
>> a request is already in progress. The request that is in
>> progress will fail and _after_ that failure is reported
>> you can disconnect. See db_cancel_request() in the
>> documentation.
>
> same error, db_cancel_request() returns 1, but it has no
> affect on the attempt terminate connection.
>
> any idea more?


Alex Posted on 2009-01-29 10:43:52.0Z
Sender: 6bd1.49818723.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49818868.6bec.1681692777@sybase.com>
References: <49808628$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Jan 2009 02:43:52 -0800
X-Trace: forums-1-dub 1233225832 10.22.241.41 (29 Jan 2009 02:43:52 -0800)
X-Original-Trace: 29 Jan 2009 02:43:52 -0800, 10.22.241.41
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7331
Article PK: 5541


> Did you wait until the original request completed? You
> can't just call

no. my goal is to force connect be terminated. without any
condition. if i wait for request is completed i can
terminate connection without problem.

is it possible to terminate the connection immideately?

> db_cancel_request( SQLCA );
> EXEC SQL DISCONNECT CURRENT;
> because you need to wait for the statement you are
> cancelling to finish and get a response back to your
> application (though it will finish earlier than it would
> without a cancel).
>
> After you call cancel, your
> EXEC SQL call myprocedure();
> will soon fail with an error code of SQLSTATE_INTERRUPTED.
> Only then can you call EXEC SQL DISCONNECT. Usually, the
> "main" thread (the one calling the procedure needs to
> detect that the connection "should go away" after its
> execution fails.
>
> You can also register a callback to know when requests
> finish. See db_register_a_callback().
>
> -john.
>
> --
> John Smirnios
> Senior Software Developer
> iAnywhere Solutions Engineering
>
> Whitepapers, TechDocs, bug fixes are all available through
> the iAnywhere Developer Community at
> http://www.ianywhere.com/developer
>
> Alex wrote:
> >> In the case of #1 with a currently pending request, you
> >> need to issue a cancel first. Cancel can be issued
> while >> a request is already in progress. The request
> that is in >> progress will fail and _after_ that failure
> is reported >> you can disconnect. See db_cancel_request()
> in the >> documentation.
> >
> > same error, db_cancel_request() returns 1, but it has
> > no affect on the attempt terminate connection.
> >
> > any idea more?


John Smirnios [Sybase] Posted on 2009-01-29 21:57:52.0Z
From: "John Smirnios [Sybase]" <smirnios_at_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
References: <49808628$1@forums-1-dub> <49818868.6bec.1681692777@sybase.com>
In-Reply-To: <49818868.6bec.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49822660$1@forums-1-dub>
Date: 29 Jan 2009 13:57:52 -0800
X-Trace: forums-1-dub 1233266272 10.22.241.152 (29 Jan 2009 13:57:52 -0800)
X-Original-Trace: 29 Jan 2009 13:57:52 -0800, vip152.sybase.com
Lines: 67
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7332
Article PK: 5543

The cancel will generally take effect "very quickly" but a connection
with a request in progress cannot terminate itself "immediately". At the
very least you still have a thread running that has called into the
client libraries and has provided a SQLCA/SQLDA/buffers that the
libraries are still using. The SQLCA/SQLDA/buffers couldn't be reused or
freed until the thread using them returned back up to your program.

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Alex wrote:
>> Did you wait until the original request completed? You
>> can't just call
>
> no. my goal is to force connect be terminated. without any
> condition. if i wait for request is completed i can
> terminate connection without problem.
>
> is it possible to terminate the connection immideately?
>
>> db_cancel_request( SQLCA );
>> EXEC SQL DISCONNECT CURRENT;
>> because you need to wait for the statement you are
>> cancelling to finish and get a response back to your
>> application (though it will finish earlier than it would
>> without a cancel).
>>
>> After you call cancel, your
>> EXEC SQL call myprocedure();
>> will soon fail with an error code of SQLSTATE_INTERRUPTED.
>> Only then can you call EXEC SQL DISCONNECT. Usually, the
>> "main" thread (the one calling the procedure needs to
>> detect that the connection "should go away" after its
>> execution fails.
>>
>> You can also register a callback to know when requests
>> finish. See db_register_a_callback().
>>
>> -john.
>>
>> --
>> John Smirnios
>> Senior Software Developer
>> iAnywhere Solutions Engineering
>>
>> Whitepapers, TechDocs, bug fixes are all available through
>> the iAnywhere Developer Community at
>> http://www.ianywhere.com/developer
>>
>> Alex wrote:
>>>> In the case of #1 with a currently pending request, you
>>>> need to issue a cancel first. Cancel can be issued
>> while >> a request is already in progress. The request
>> that is in >> progress will fail and _after_ that failure
>> is reported >> you can disconnect. See db_cancel_request()
>> in the >> documentation.
>>> same error, db_cancel_request() returns 1, but it has
>>> no affect on the attempt terminate connection.
>>>
>>> any idea more?


Alex Posted on 2009-01-30 01:28:00.0Z
Sender: 7244.4981b385.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <498257a0.114a.1681692777@sybase.com>
References: <49822660$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Jan 2009 17:28:00 -0800
X-Trace: forums-1-dub 1233278880 10.22.241.41 (29 Jan 2009 17:28:00 -0800)
X-Original-Trace: 29 Jan 2009 17:28:00 -0800, 10.22.241.41
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7333
Article PK: 5749

in my example cancel doesn't work. sleep(1..10) has no
effect.
maybe reason is waitfor statement, is it?

does 'drop connection' immediately terminate 'alien'
connection ?


concerning thread. looks like in the client library
sqlca/da buffers are created in the thread ;) would it be
safe pthread_cancel that thread from application?
just checked, all clients threads are on cancellation
points: nanosleep,select,poll. sure, i'm out of internals
of server.

> The cancel will generally take effect "very quickly" but a
> connection with a request in progress cannot terminate
> itself "immediately". At the very least you still have a
> thread running that has called into the client libraries
> and has provided a SQLCA/SQLDA/buffers that the libraries
> are still using. The SQLCA/SQLDA/buffers couldn't be
> reused or freed until the thread using them returned back
> up to your program.
>
> -john.
>
> --
> John Smirnios
> Senior Software Developer
> iAnywhere Solutions Engineering
>
> Whitepapers, TechDocs, bug fixes are all available through
> the iAnywhere Developer Community at
> http://www.ianywhere.com/developer
>
> Alex wrote:
> >> Did you wait until the original request completed? You
> >> can't just call
> >
> > no. my goal is to force connect be terminated. without
> > any condition. if i wait for request is completed i can
> > terminate connection without problem.
> >
> > is it possible to terminate the connection immideately?
> >
> >> db_cancel_request( SQLCA );
> >> EXEC SQL DISCONNECT CURRENT;
> >> because you need to wait for the statement you are
> >> cancelling to finish and get a response back to your
> >> application (though it will finish earlier than it
> would >> without a cancel).
> >>
> >> After you call cancel, your
> >> EXEC SQL call myprocedure();
> >> will soon fail with an error code of
> SQLSTATE_INTERRUPTED. >> Only then can you call EXEC SQL
> DISCONNECT. Usually, the >> "main" thread (the one
> calling the procedure needs to >> detect that the
> connection "should go away" after its >> execution fails.
> >>
> >> You can also register a callback to know when requests
> >> finish. See db_register_a_callback().
> >>
> >> -john.
> >>
> >> --
> >> John Smirnios
> >> Senior Software Developer
> >> iAnywhere Solutions Engineering
> >>
> >> Whitepapers, TechDocs, bug fixes are all available
> through >> the iAnywhere Developer Community at
> >> http://www.ianywhere.com/developer
> >>
> >> Alex wrote:
> >>>> In the case of #1 with a currently pending request,
> you >>>> need to issue a cancel first. Cancel can be
> issued >> while >> a request is already in progress. The
> request >> that is in >> progress will fail and _after_
> that failure >> is reported >> you can disconnect. See
> db_cancel_request() >> in the >> documentation.
> >>> same error, db_cancel_request() returns 1, but it has
> >>> no affect on the attempt terminate connection.
> >>>
> >>> any idea more?


John Smirnios [Sybase] Posted on 2009-01-30 15:06:28.0Z
From: "John Smirnios [Sybase]" <smirnios_at_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
References: <49822660$1@forums-1-dub> <498257a0.114a.1681692777@sybase.com>
In-Reply-To: <498257a0.114a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49831774$1@forums-1-dub>
Date: 30 Jan 2009 07:06:28 -0800
X-Trace: forums-1-dub 1233327988 10.22.241.152 (30 Jan 2009 07:06:28 -0800)
X-Original-Trace: 30 Jan 2009 07:06:28 -0800, vip152.sybase.com
Lines: 119
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7334
Article PK: 5750

The documentation says "The WAITFOR statement wakes up periodically
(every 5 seconds by default) to check if it has been canceled or if
messages have been received. If neither of these has happened, the
statement continues to wait." Looking at the code, I think the doc is
slightly incorrect. A cancel should be recognized immediately. We only
wake up periodically to check for messages.

When I tried it in dbisqlc, hitting 'Stop' (which issues a cancel)
worked just fine. Check that your program isn't in a loop and just
decided to call you procedure again and check that you are canceling the
correct SQLCA.

Internally, DROP CONNECTION issues a cancel to that connection. Once the
current request is canceled, the connection itself is torn down. In
other words, it should be no more effective or immediate than
db_cancel_request though it will leave the connection in an unusable
state. DROP CONNECTION requires you to know the connection's id and
requires DBA authority to execute.

Programmatically, you don't always know what state the other thread is
in. Also, cancellation cleanup takes place asynchronously with the
thread that called pthread_cancel. How will you know when it is safe
release the SQLCA/SQLDA/buffers? What about the threads that were
created by the client libraries? They need to be shut down at a safe
time too (it usually happens when you call db_fini()).

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Alex wrote:
> in my example cancel doesn't work. sleep(1..10) has no
> effect.
> maybe reason is waitfor statement, is it?
>
> does 'drop connection' immediately terminate 'alien'
> connection ?
>
>
> concerning thread. looks like in the client library
> sqlca/da buffers are created in the thread ;) would it be
> safe pthread_cancel that thread from application?
> just checked, all clients threads are on cancellation
> points: nanosleep,select,poll. sure, i'm out of internals
> of server.
>
>> The cancel will generally take effect "very quickly" but a
>> connection with a request in progress cannot terminate
>> itself "immediately". At the very least you still have a
>> thread running that has called into the client libraries
>> and has provided a SQLCA/SQLDA/buffers that the libraries
>> are still using. The SQLCA/SQLDA/buffers couldn't be
>> reused or freed until the thread using them returned back
>> up to your program.
>>
>> -john.
>>
>> --
>> John Smirnios
>> Senior Software Developer
>> iAnywhere Solutions Engineering
>>
>> Whitepapers, TechDocs, bug fixes are all available through
>> the iAnywhere Developer Community at
>> http://www.ianywhere.com/developer
>>
>> Alex wrote:
>>>> Did you wait until the original request completed? You
>>>> can't just call
>>> no. my goal is to force connect be terminated. without
>>> any condition. if i wait for request is completed i can
>>> terminate connection without problem.
>>>
>>> is it possible to terminate the connection immideately?
>>>
>>>> db_cancel_request( SQLCA );
>>>> EXEC SQL DISCONNECT CURRENT;
>>>> because you need to wait for the statement you are
>>>> cancelling to finish and get a response back to your
>>>> application (though it will finish earlier than it
>> would >> without a cancel).
>>>> After you call cancel, your
>>>> EXEC SQL call myprocedure();
>>>> will soon fail with an error code of
>> SQLSTATE_INTERRUPTED. >> Only then can you call EXEC SQL
>> DISCONNECT. Usually, the >> "main" thread (the one
>> calling the procedure needs to >> detect that the
>> connection "should go away" after its >> execution fails.
>>>> You can also register a callback to know when requests
>>>> finish. See db_register_a_callback().
>>>>
>>>> -john.
>>>>
>>>> --
>>>> John Smirnios
>>>> Senior Software Developer
>>>> iAnywhere Solutions Engineering
>>>>
>>>> Whitepapers, TechDocs, bug fixes are all available
>> through >> the iAnywhere Developer Community at
>>>> http://www.ianywhere.com/developer
>>>>
>>>> Alex wrote:
>>>>>> In the case of #1 with a currently pending request,
>> you >>>> need to issue a cancel first. Cancel can be
>> issued >> while >> a request is already in progress. The
>> request >> that is in >> progress will fail and _after_
>> that failure >> is reported >> you can disconnect. See
>> db_cancel_request() >> in the >> documentation.
>>>>> same error, db_cancel_request() returns 1, but it has
>>>>> no affect on the attempt terminate connection.
>>>>>
>>>>> any idea more?


Alex Posted on 2009-01-30 21:07:52.0Z
Sender: 7244.4981b385.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49836c28.365a.1681692777@sybase.com>
References: <49831774$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Jan 2009 13:07:52 -0800
X-Trace: forums-1-dub 1233349672 10.22.241.41 (30 Jan 2009 13:07:52 -0800)
X-Original-Trace: 30 Jan 2009 13:07:52 -0800, 10.22.241.41
Lines: 149
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7335
Article PK: 5752

does "stop" in dbisql send db_cancel_request()? if so it's
not suitable. i didn't classify but there are some kind of
'long' requests when during execution hitting 'stop' has no
effect.

no loop in code. trust me , i'm green alien ;)

i saw syntax DROP CONNECTION. as far as connection is
created with the same login/pass it should be available for
termination, isn't it?
number of connection can be obtained from properties. but is
it the same mechanism of cancellation?

i'm not so far sure about internals of client library but
now i have the same task: cancel a thread based 'long'
requests. actually, it's not problem (in my app) to release
sqlca/da buffers on pthread_cancel, problem to take off
server load from 'long' request.
is any way to drop connection on tcp/ip layer?

btw... is any way just restrict time of the query execution
?

> The documentation says "The WAITFOR statement wakes up
> periodically (every 5 seconds by default) to check if it
> has been canceled or if messages have been received. If
> neither of these has happened, the statement continues to
> wait." Looking at the code, I think the doc is slightly
> incorrect. A cancel should be recognized immediately. We
> only wake up periodically to check for messages.
>
> When I tried it in dbisqlc, hitting 'Stop' (which issues a
> cancel) worked just fine. Check that your program isn't
> in a loop and just decided to call you procedure again
> and check that you are canceling the correct SQLCA.
>
> Internally, DROP CONNECTION issues a cancel to that
> connection. Once the current request is canceled, the
> connection itself is torn down. In other words, it should
> be no more effective or immediate than db_cancel_request
> though it will leave the connection in an unusable state.
> DROP CONNECTION requires you to know the connection's id
> and requires DBA authority to execute.
>
> Programmatically, you don't always know what state the
> other thread is in. Also, cancellation cleanup takes
> place asynchronously with the thread that called
> pthread_cancel. How will you know when it is safe release
> the SQLCA/SQLDA/buffers? What about the threads that were
> created by the client libraries? They need to be shut down
> at a safe time too (it usually happens when you call
> db_fini()).
>
> -john.
>
> --
> John Smirnios
> Senior Software Developer
> iAnywhere Solutions Engineering
>
> Whitepapers, TechDocs, bug fixes are all available through
> the iAnywhere Developer Community at
> http://www.ianywhere.com/developer
>
> Alex wrote:
> > in my example cancel doesn't work. sleep(1..10) has no
> > effect.
> > maybe reason is waitfor statement, is it?
> >
> > does 'drop connection' immediately terminate 'alien'
> > connection ?
> >
> >
> > concerning thread. looks like in the client library
> > sqlca/da buffers are created in the thread ;) would it
> > be safe pthread_cancel that thread from application?
> > just checked, all clients threads are on cancellation
> > points: nanosleep,select,poll. sure, i'm out of
> > internals of server.
> >
> >> The cancel will generally take effect "very quickly"
> but a >> connection with a request in progress cannot
> terminate >> itself "immediately". At the very least you
> still have a >> thread running that has called into the
> client libraries >> and has provided a SQLCA/SQLDA/buffers
> that the libraries >> are still using. The
> SQLCA/SQLDA/buffers couldn't be >> reused or freed until
> the thread using them returned back >> up to your program.
> >>
> >> -john.
> >>
> >> --
> >> John Smirnios
> >> Senior Software Developer
> >> iAnywhere Solutions Engineering
> >>
> >> Whitepapers, TechDocs, bug fixes are all available
> through >> the iAnywhere Developer Community at
> >> http://www.ianywhere.com/developer
> >>
> >> Alex wrote:
> >>>> Did you wait until the original request completed?
> You >>>> can't just call
> >>> no. my goal is to force connect be terminated.
> without >>> any condition. if i wait for request is
> completed i can >>> terminate connection without problem.
> >>>
> >>> is it possible to terminate the connection
> immideately? >>>
> >>>> db_cancel_request( SQLCA );
> >>>> EXEC SQL DISCONNECT CURRENT;
> >>>> because you need to wait for the statement you are
> >>>> cancelling to finish and get a response back to your
> >>>> application (though it will finish earlier than it
> >> would >> without a cancel).
> >>>> After you call cancel, your
> >>>> EXEC SQL call myprocedure();
> >>>> will soon fail with an error code of
> >> SQLSTATE_INTERRUPTED. >> Only then can you call EXEC
> SQL >> DISCONNECT. Usually, the >> "main" thread (the one
> >> calling the procedure needs to >> detect that the
> >> connection "should go away" after its >> execution
> fails. >>>> You can also register a callback to know when
> requests >>>> finish. See db_register_a_callback().
> >>>>
> >>>> -john.
> >>>>
> >>>> --
> >>>> John Smirnios
> >>>> Senior Software Developer
> >>>> iAnywhere Solutions Engineering
> >>>>
> >>>> Whitepapers, TechDocs, bug fixes are all available
> >> through >> the iAnywhere Developer Community at
> >>>> http://www.ianywhere.com/developer
> >>>>
> >>>> Alex wrote:
> >>>>>> In the case of #1 with a currently pending request,
> >> you >>>> need to issue a cancel first. Cancel can be
> >> issued >> while >> a request is already in progress.
> The >> request >> that is in >> progress will fail and
> _after_ >> that failure >> is reported >> you can
> disconnect. See >> db_cancel_request() >> in the >>
> documentation. >>>>> same error, db_cancel_request()
> returns 1, but it has >>>>> no affect on the attempt
> terminate connection. >>>>>
> >>>>> any idea more?


Stephen Rice [Sybase] Posted on 2009-02-02 16:20:13.0Z
From: "Stephen Rice [Sybase]" <stevedotriceatsybasedotcom>
Newsgroups: ianywhere.public.general
References: <49831774$1@forums-1-dub> <49836c28.365a.1681692777@sybase.com>
Subject: Re: terminate connect db_cancel
Lines: 165
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49871d3d@forums-1-dub>
Date: 2 Feb 2009 08:20:13 -0800
X-Trace: forums-1-dub 1233591613 10.22.241.152 (2 Feb 2009 08:20:13 -0800)
X-Original-Trace: 2 Feb 2009 08:20:13 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7342
Article PK: 5755

To limit the amount of time a request can run use the request_timeout option

--
/steve
Stephen Rice, Sybase iAnywhere
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Docs : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<Alex> wrote in message news:49836c28.365a.1681692777@sybase.com...
> does "stop" in dbisql send db_cancel_request()? if so it's
> not suitable. i didn't classify but there are some kind of
> 'long' requests when during execution hitting 'stop' has no
> effect.
>
> no loop in code. trust me , i'm green alien ;)
>
> i saw syntax DROP CONNECTION. as far as connection is
> created with the same login/pass it should be available for
> termination, isn't it?
> number of connection can be obtained from properties. but is
> it the same mechanism of cancellation?
>
> i'm not so far sure about internals of client library but
> now i have the same task: cancel a thread based 'long'
> requests. actually, it's not problem (in my app) to release
> sqlca/da buffers on pthread_cancel, problem to take off
> server load from 'long' request.
> is any way to drop connection on tcp/ip layer?
>
> btw... is any way just restrict time of the query execution
> ?
>
>
>
>> The documentation says "The WAITFOR statement wakes up
>> periodically (every 5 seconds by default) to check if it
>> has been canceled or if messages have been received. If
>> neither of these has happened, the statement continues to
>> wait." Looking at the code, I think the doc is slightly
>> incorrect. A cancel should be recognized immediately. We
>> only wake up periodically to check for messages.
>>
>> When I tried it in dbisqlc, hitting 'Stop' (which issues a
>> cancel) worked just fine. Check that your program isn't
>> in a loop and just decided to call you procedure again
>> and check that you are canceling the correct SQLCA.
>>
>> Internally, DROP CONNECTION issues a cancel to that
>> connection. Once the current request is canceled, the
>> connection itself is torn down. In other words, it should
>> be no more effective or immediate than db_cancel_request
>> though it will leave the connection in an unusable state.
>> DROP CONNECTION requires you to know the connection's id
>> and requires DBA authority to execute.
>>
>> Programmatically, you don't always know what state the
>> other thread is in. Also, cancellation cleanup takes
>> place asynchronously with the thread that called
>> pthread_cancel. How will you know when it is safe release
>> the SQLCA/SQLDA/buffers? What about the threads that were
>> created by the client libraries? They need to be shut down
>> at a safe time too (it usually happens when you call
>> db_fini()).
>>
>> -john.
>>
>> --
>> John Smirnios
>> Senior Software Developer
>> iAnywhere Solutions Engineering
>>
>> Whitepapers, TechDocs, bug fixes are all available through
>> the iAnywhere Developer Community at
>> http://www.ianywhere.com/developer
>>
>> Alex wrote:
>> > in my example cancel doesn't work. sleep(1..10) has no
>> > effect.
>> > maybe reason is waitfor statement, is it?
>> >
>> > does 'drop connection' immediately terminate 'alien'
>> > connection ?
>> >
>> >
>> > concerning thread. looks like in the client library
>> > sqlca/da buffers are created in the thread ;) would it
>> > be safe pthread_cancel that thread from application?
>> > just checked, all clients threads are on cancellation
>> > points: nanosleep,select,poll. sure, i'm out of
>> > internals of server.
>> >
>> >> The cancel will generally take effect "very quickly"
>> but a >> connection with a request in progress cannot
>> terminate >> itself "immediately". At the very least you
>> still have a >> thread running that has called into the
>> client libraries >> and has provided a SQLCA/SQLDA/buffers
>> that the libraries >> are still using. The
>> SQLCA/SQLDA/buffers couldn't be >> reused or freed until
>> the thread using them returned back >> up to your program.
>> >>
>> >> -john.
>> >>
>> >> --
>> >> John Smirnios
>> >> Senior Software Developer
>> >> iAnywhere Solutions Engineering
>> >>
>> >> Whitepapers, TechDocs, bug fixes are all available
>> through >> the iAnywhere Developer Community at
>> >> http://www.ianywhere.com/developer
>> >>
>> >> Alex wrote:
>> >>>> Did you wait until the original request completed?
>> You >>>> can't just call
>> >>> no. my goal is to force connect be terminated.
>> without >>> any condition. if i wait for request is
>> completed i can >>> terminate connection without problem.
>> >>>
>> >>> is it possible to terminate the connection
>> immideately? >>>
>> >>>> db_cancel_request( SQLCA );
>> >>>> EXEC SQL DISCONNECT CURRENT;
>> >>>> because you need to wait for the statement you are
>> >>>> cancelling to finish and get a response back to your
>> >>>> application (though it will finish earlier than it
>> >> would >> without a cancel).
>> >>>> After you call cancel, your
>> >>>> EXEC SQL call myprocedure();
>> >>>> will soon fail with an error code of
>> >> SQLSTATE_INTERRUPTED. >> Only then can you call EXEC
>> SQL >> DISCONNECT. Usually, the >> "main" thread (the one
>> >> calling the procedure needs to >> detect that the
>> >> connection "should go away" after its >> execution
>> fails. >>>> You can also register a callback to know when
>> requests >>>> finish. See db_register_a_callback().
>> >>>>
>> >>>> -john.
>> >>>>
>> >>>> --
>> >>>> John Smirnios
>> >>>> Senior Software Developer
>> >>>> iAnywhere Solutions Engineering
>> >>>>
>> >>>> Whitepapers, TechDocs, bug fixes are all available
>> >> through >> the iAnywhere Developer Community at
>> >>>> http://www.ianywhere.com/developer
>> >>>>
>> >>>> Alex wrote:
>> >>>>>> In the case of #1 with a currently pending request,
>> >> you >>>> need to issue a cancel first. Cancel can be
>> >> issued >> while >> a request is already in progress.
>> The >> request >> that is in >> progress will fail and
>> _after_ >> that failure >> is reported >> you can
>> disconnect. See >> db_cancel_request() >> in the >>
>> documentation. >>>>> same error, db_cancel_request()
>> returns 1, but it has >>>>> no affect on the attempt
>> terminate connection. >>>>>
>> >>>>> any idea more?


John Smirnios [Sybase] Posted on 2009-02-02 16:32:02.0Z
From: "John Smirnios [Sybase]" <smirnios_at_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect db_cancel
References: <49831774$1@forums-1-dub> <49836c28.365a.1681692777@sybase.com>
In-Reply-To: <49836c28.365a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49872002$1@forums-1-dub>
Date: 2 Feb 2009 08:32:02 -0800
X-Trace: forums-1-dub 1233592322 10.22.241.152 (2 Feb 2009 08:32:02 -0800)
X-Original-Trace: 2 Feb 2009 08:32:02 -0800, vip152.sybase.com
Lines: 61
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7343
Article PK: 5757

Yes, hitting 'Stop' in dbisql sends db_cancel_request. If there are
particular operations which don't respond to the cancel, please report
them. Note that there are some instances in which it is normal for the
cancel to take a long time but not in the one you reported so far
(WAITFOR DELAY). For example, suppose you execute "UPDATE
my_billion_row_table SET col=col+1" and let it run for an hour then hit
'cancel'. The server will immediately start undoing the portion of the
statement it has completed but it must be completely undone before the
client will be notified of the cancellation. Cancel rolls back the
current statement, not the whole transaction.

According to the doc, DBA permission is required to execute DROP
CONNECTION. As I mentioned earlier, it issues the same sort of cancel to
the connection before it can tear it down. If db_cancel_request isn't
working for you, DROP CONNECTION won't work for you either.

There is no way to simply close the TCPIP connection.

I don't think you can call db_fini on an active SQLCA. Also, if you
don't call db_fini you may orphan threads that were created inside the
client library.

Sorry, I don't know off-hand if there is a way to limit query execution
time. Check the doc or open another thread for that one.

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Alex wrote:
> does "stop" in dbisql send db_cancel_request()? if so it's
> not suitable. i didn't classify but there are some kind of
> 'long' requests when during execution hitting 'stop' has no
> effect.
>
> no loop in code. trust me , i'm green alien ;)
>
> i saw syntax DROP CONNECTION. as far as connection is
> created with the same login/pass it should be available for
> termination, isn't it?
> number of connection can be obtained from properties. but is
> it the same mechanism of cancellation?
>
> i'm not so far sure about internals of client library but
> now i have the same task: cancel a thread based 'long'
> requests. actually, it's not problem (in my app) to release
> sqlca/da buffers on pthread_cancel, problem to take off
> server load from 'long' request.
> is any way to drop connection on tcp/ip layer?
>
> btw... is any way just restrict time of the query execution
> ?
>
>
>


Alex Posted on 2009-01-28 15:23:45.0Z
Sender: 4332.498062ad.1804289383@sybase.com
From: Alex
Newsgroups: ianywhere.public.general
Subject: Re: terminate connect
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49807881.46bf.1681692777@sybase.com>
References: <Xns9BA1624803C14fishburnsybasecom@10.22.241.106>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jan 2009 07:23:45 -0800
X-Trace: forums-1-dub 1233156225 10.22.241.41 (28 Jan 2009 07:23:45 -0800)
X-Original-Trace: 28 Jan 2009 07:23:45 -0800, 10.22.241.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7326
Article PK: 5536


> Alex wrote in news:49806505.437a.1681692777@sybase.com
> of ianywhere.public.general:
>
> A> what is right way to force terminate connection?
> A>
> A> for instance, i've created stored procedure to delay
> for 5 A> min, called it and tried to terminate connection
> with A> dbpp_disconnect().
> A> Server returns
> A> &#8211;298 Attempted two active database requests
>
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng11 -v).
>
> 1. Are you trying to disconnect your _current_
> connection? 2. Are you trying to disconnect a _different_
> connection?
>
> 1. Is solved by executing a DISCONNECT statement.
>
> 2. Is solved by executing a DROP CONNECTION statement,
> but you must
> know the connection_id of the one you want to drop.

ver. 11.0.0.1511 (Linux)

i guess in your terms it's current connection. thus, i have

SQLCA *c;

and try to disconnect as

dbpp_disconnect( (c), SQLNULL );

concerning DROP CONNECTION, it's expanded to

dbpp_prepare_exec_drop( (sqlcaptr), __SQLV_cur_3, (SQLDA
*)0, (SQLDA *)0 );

looks like i need other connection to execute this command.
right? if so, it's not so convinient.