SQL Server JDBC - cancelled at the user's request

Author

cancelled at the user's request

Mikee

2005-05-01, 11:23 am

Hi

I'm writng a servlet to maintain a database of users.

One functionality is to add/amend a user. The sql
steps I go through are

1) A simple select to see if the supplied community exists

2) delete the user if exists

3) add user with new attributes (passwd,firstname,la
stname etc)

I'm turning off autocommit and only committing at the end
if no exceptions encountered

I'm inconsistently getting the exception
" [Microsoft][SQLServe
r 2000 Driver for JDBC]The operation
was cancelled at the user's request"

Most of the time it's working but occaisonally it doesn't.
Part of the problem might be that I'm setting the querytimeout
but it's set to 10 minutes and the process finishes in under
a second. If I remove the querytimeout I've yet to encounter the
exception but not sure if this is luck. Ideally I'd like
to keep the timeout in and/or understand what's happening.

Code snippets below. During a run of getting the exceptions
yesterday it was occuring at the con.commit point. Worryingly
the excpetion was thrown but the database was updated, is this
possible.

I tried doing the sql as separate executes rather than batch
but moving to batch seems to reduce the frequency of errors.

thanks
Mike

con. setAutoCommit(false)
;
Statement stmt = con.createStatement();
stmt. setQueryTimeout(90);

......
stmt.execute("select * from wsa_community where community='"
+ community + "'");
ResultSet rs = stmt.getResultSet();
boolean communityExists = false;
if (rs.next()) {
communityExists = true;
}
rs.close();
if (communityExists) {
String sqlAddUser;
String sqlDelUser;
sqlDelUser = "delete from wsa_users where username='"
+ userName+ "'and community='"+ community+ "'";
stmt. addBatch(sqlDelUser)
;
sqlAddUser = "insert into wsa_users
(lastName,firstName,
username,community,p
assword) values ( "
+ "'"+ lastName+ "',"
+ "'"+ firstName+ "',"
+ "'"+ userName+ "',"
+ "'"+ community + "'," + "'" + passwd1 + "')";
stmt. addBatch(sqlAddUser)
;
int resCount[];
resCount=stmt.executeBatch();
.......
stmt.close();
con.commit();
.....
con.close();

Shelby Goerlitz [MSFT]

2005-05-03, 8:23 pm

Mike:

When you say that you have setQueryTimeout at 10 minutes, what value did you
have? 600 or 10? In the code below it looks like you have it set for 90
seconds..

-shelby

Shelby Goerlitz
Microsoft, SQL Server

"Mikee" <mar@roe.ac.uk> wrote in message
news:1114961600.938560.118200@o13g2000cwo.googlegroups.com...
> Hi
>
> I'm writng a servlet to maintain a database of users.
>
> One functionality is to add/amend a user. The sql
> steps I go through are
>
> 1) A simple select to see if the supplied community exists
>
> 2) delete the user if exists
>
> 3) add user with new attributes (passwd,firstname,la
stname etc)
>
> I'm turning off autocommit and only committing at the end
> if no exceptions encountered
>
> I'm inconsistently getting the exception
> " [Microsoft][SQLServe
r 2000 Driver for JDBC]The operation
> was cancelled at the user's request"
>
> Most of the time it's working but occaisonally it doesn't.
> Part of the problem might be that I'm setting the querytimeout
> but it's set to 10 minutes and the process finishes in under
> a second. If I remove the querytimeout I've yet to encounter the
> exception but not sure if this is luck. Ideally I'd like
> to keep the timeout in and/or understand what's happening.
>
> Code snippets below. During a run of getting the exceptions
> yesterday it was occuring at the con.commit point. Worryingly
> the excpetion was thrown but the database was updated, is this
> possible.
>
> I tried doing the sql as separate executes rather than batch
> but moving to batch seems to reduce the frequency of errors.
>
> thanks
> Mike
>
> con. setAutoCommit(false)
;
> Statement stmt = con.createStatement();
> stmt. setQueryTimeout(90);

> .....
> stmt.execute("select * from wsa_community where community='"
> + community + "'");
> ResultSet rs = stmt.getResultSet();
> boolean communityExists = false;
> if (rs.next()) {
> communityExists = true;
> }
> rs.close();
> if (communityExists) {
> String sqlAddUser;
> String sqlDelUser;
> sqlDelUser = "delete from wsa_users where username='"
> + userName+ "'and community='"+ community+ "'";
> stmt. addBatch(sqlDelUser)
;
> sqlAddUser = "insert into wsa_users
> (lastName,firstName,
username,community,p
assword) values ( "
> + "'"+ lastName+ "',"
> + "'"+ firstName+ "',"
> + "'"+ userName+ "',"
> + "'"+ community + "'," + "'" + passwd1 + "')";
> stmt. addBatch(sqlAddUser)
;
> int resCount[];
> resCount=stmt.executeBatch();
> ......
> stmt.close();
> con.commit();
> ....
> con.close();
>

Mikee

2005-05-04, 7:23 am

Hi Shebly

>When you say that you have setQueryTimeout at 10 minutes, what value
did you
>have? 600 or 10? In the code below it looks like you have it set for
90
>seconds..

Sorry for the confusion, I'd originally set it at
stmt.setQueryTimeout(90) as 90 seconds
is about the length I want to allow it to run but
when I was getting the errors I bumped it up to
stmt. setQueryTimeout(600)
but it
(understandably) made no difference.
The query's return in less than a second so I can't see the value is
really affecting
anything. I've yet to get the error if I remove the setQueryTimeout.

Thanks
Mike

Kamil Sykora [MSFT]

2005-05-10, 11:23 am

Hello Mike,

I have tested your code with some tables I constructed and some blocking
situations. I.e. I would run a second transaction in QA to cause blocking
on the two tables to try to reproduce the behavior. I have been unable to
do so yet. Every time I get the timeout it's at the time specified by
setQueryTimeout().

Can you let me know which version of the driver you are using? You can get
that by running something like:

DatabaseMetaData d = con.getMetaData();
System.out.println(d. getDriverVersion());

There was some work done on setQueryDriver before RTW. I don't think you're
using the Beta version but just wanted to double check.

Can you also let me know if you have observed any conditions when you are
more likely to experience the problem? For example, do you think it's
happening with more concurrent users on the system? Could you have some
transient blocking issues happening at that time?

I would recommend running a SQL profiler at the same time as running your
app and try to correlate the occurrences of the exceptions to some events
in the profiler. For example, could it be that when you get the exception,
you see a certain other event on a different SPID occurring? Are they on
related tables? Etc.

Thanks,
Kamil

Kamil Sykora
Microsoft Developer Support - Web Data

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/secur_ity.

--------------------
| From: "Mikee" <mar@roe.ac.uk>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: cancelled at the user's request
| Date: 4 May 2005 02:43:07 -0700
|
| Hi Shebly
|
| >When you say that you have setQueryTimeout at 10 minutes, what value
| did you
| >have? 600 or 10? In the code below it looks like you have it set for
| 90
| >seconds..
|
| Sorry for the confusion, I'd originally set it at
| stmt.setQueryTimeout(90) as 90 seconds
| is about the length I want to allow it to run but
| when I was getting the errors I bumped it up to
| stmt. setQueryTimeout(600)
but it
| (understandably) made no difference.
| The query's return in less than a second so I can't see the value is
| really affecting
| anything. I've yet to get the error if I remove the setQueryTimeout.
|
| Thanks
| Mike
|
|

Mikee

2005-05-11, 7:23 am

Hi Kamil

Thanks for looking at this

The version I'm using is 2.2.0040.

>Can you also let me know if you have observed any conditions when you
are
>more likely to experience the problem? For example, do you think it's
>happening with more concurrent users on the system? Could you have
some
>transient blocking issues happening at that time?

I don't think concurrency is an issue as during testing this was the
only external connection
doing a query (though I did have QueryAnalyser and EnterpriseManager
open but doing nothing).

I've had the profiler running when the exception has been thrown but
couldn't see anything relevant
there.

Still no errors if I remove the statement.setQueryTimeout

Thanks
Mike

Kamil Sykora [MSFT]

2005-05-17, 11:23 am

Hi Mike,

sounds like it should be reproducible on other systems too, since you can
get it to happen with just one user.

Can you provide me with a table script, and some data that you're using,
along with any constraints, triggers, etc that you have on that table? Does
it happen with a local SQL Server (application on the same box) or with the
application being on a different box?

Thanks,
Kamil

Kamil Sykora
Microsoft Developer Support - Web Data

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/secur_ity.

--------------------
| From: "Mikee" <mar@roe.ac.uk>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: cancelled at the user's request
| Date: 11 May 2005 04:45:49 -0700
|
| Hi Kamil
|
| Thanks for looking at this
|
| The version I'm using is 2.2.0040.
|
| >Can you also let me know if you have observed any conditions when you
| are
| >more likely to experience the problem? For example, do you think it's
| >happening with more concurrent users on the system? Could you have
| some
| >transient blocking issues happening at that time?
|
| I don't think concurrency is an issue as during testing this was the
| only external connection
| doing a query (though I did have QueryAnalyser and EnterpriseManager
| open but doing nothing).
|
| I've had the profiler running when the exception has been thrown but
| couldn't see anything relevant
| there.
|
| Still no errors if I remove the statement.setQueryTimeout
|
| Thanks
| Mike
|
|

Mikee

2005-05-20, 9:23 am

Hi Kamil

Script for the two tables below. I can't give you my actual data as it
contains real usernames and passwords
some dummy data are appended as a csv. Don't waste much time on this
for now as I removed the timeout
and it's working.

Mike

CREATE TABLE [dbo].[wsa_community] (
[community] [varchar] (64) COLLATE Latin1_General_CI_AS
NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[wsa_users] (
[firstName] [varchar] (32) COLLATE Latin1_General_CI_AS
NOT NULL ,
[lastName] [varchar] (32) COLLATE Latin1_General_CI_AS
NOT NULL ,
[username] [varchar] (32) COLLATE Latin1_General_CI_AS
NOT NULL ,
[password] [varchar] (32) COLLATE Latin1_General_CI_AS
NOT NULL ,
[community] [varchar] (64) COLLATE Latin1_General_CI_AS
NOT NULL ,
[email] [varchar] (32) COLLATE Latin1_General_CI_AS
NULL ,
[ipaddress] [varchar] (32) COLLATE Latin1_General_CI_AS
NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[wsa_community] WITH NOCHECK ADD
CONSTRAINT & #91;PK_wsa_community
] PRIMARY KEY CLUSTERED
(
[community]
) ON [PRIMARY]
GO

data for wsa_community
testcommunity
test1community

data for wsa_users
oierg,ojrht,eohg,joe
g,testcommunity,rtyr
t,3874
uijhge,jioegr,joieg,
ju9ger,testcommunity
,89789,3874
uerg,ioerg,jioerg,we
i,testcommunity,ryrt
yrty,3874
jhuierg,uiowf,oierg,
eogi,testcommunity,r
urtyrt,3874
opegr,iopjeg,pegor,g
kepo,testcommunity,r
turtu,3874
ioerg,epgk,egrpo,egr
po,testcommunity,rtu
rtutr,3874
io,kogtr,ejior,bfv,t
estcommunity,rturur,
3874
jnhuer,erjhuiv,iougr
e,jweor,testcommunit
y,urur6tu,3874
hid,poijerg,erpgok,e
rgpo,testcommunity,r
turur,3874
ioerg,jorihjk,thgbi,
hjti,testcommunity,r
turtu,3874
ijoge,ioh,fytwd,whei
fu,testcommunity,rtu
ryru,3874
huewrf,wiu,uhbsc,hiu
vw,testcommunity,urt
urt,3874
vghsc,nvdoj,jokb,ejo
iv,testcommunity,rur
rt,3874
reaxp06.roe.ac.uk> more ~mar/dummy.csv
aas,asas,fsgsdfgsd,d
fgdg,testcommunity,a
saas,132114
da,sdfsdf,sdfsdf,sfd
sdrew,testcommunity,
sdsds,3874
klsfk,sedfs,sdfsd,ew
r,testcommunity,awaq
,3874
gegerg,ergerg,ergerg
,egrerg,testcommunit
y,kg,3874
eger,rtytry,tyery,er
yery,testcommunity,e
dl,3874
uoritiwre,eryer,erte
rtdfb,dfgjk,testcomm
unity,loefkgj,3874
okj,rf,wef,wef,testc
ommunity,ldkg,3874
wsdcfvb,5tgh,yhkjl,y
tjhtrg,testcommunity
,yukuyj,3874
kergj,oytihk,gyeroig
,egjgrfe,testcommuni
ty,ttut,3874
ldjgld,kejrgoie,eoig
j,jorpt,testcommunit
y,truhrh,3874
kegplk,kglk,ohijrpo,
pieitr,testcommunity
,rtyrty,3874
riwrui,utojdf,topgor
e,eroito,testcommuni
ty,rtyrtyh,3874
keptoie,erter,ertert
y,tyytoi,testcommuni
ty,rtyry,3874
pirpotyop,rtpoyi,oir
tyi,iotryo,testcommu
nity,r5tyrt,3874
iwepir,tyqdfty,fghwe
,weriewy,testcommuni
ty,rtyrty,3874
uwriuw,fdre,ertreg,f
tgfdgd,testcommunity
,rtrtt,3874
eporeri,ererge,reter
,gtyt,testcommunity,
rthtry,3874
ergre,ergerg,ergeg,y
ukut,testcommunity,r
tyrt,3874
opokpok,kpkp,k0pkpol
,k0k,testcommunity,r
tyrtyr7u,3874
gfaed,ohrt,ihueg,hge
,testcommunity,yuurt
y,3874
oierg,ojrht,eohg,joe
g,testcommunity,rtyr
t,3874
uijhge,jioegr,joieg,
ju9ger,testcommunity
,89789,3874
uerg,ioerg,jioerg,we
i,testcommunity,ryrt
yrty,3874
jhuierg,uiowf,oierg,
eogi,testcommunity,r
urtyrt,3874
opegr,iopjeg,pegor,g
kepo,testcommunity,r
turtu,3874
ioerg,epgk,egrpo,egr
po,testcommunity,rtu
rtutr,3874
io,kogtr,ejior,bfv,t
estcommunity,rturur,
3874
jnhuer,erjhuiv,iougr
e,jweor,testcommunit
y,urur6tu,3874
hid,poijerg,erpgok,e
rgpo,testcommunity,r
turur,3874
ioerg,jorihjk,thgbi,
hjti,testcommunity,r
turtu,3874
ijoge,ioh,fytwd,whei
fu,testcommunity,rtu
ryru,3874
huewrf,wiu,uhbsc,hiu
vw,testcommunity,urt
urt,3874
vghsc,nvdoj,jokb,ejo
iv,testcommunity,rur
rt,3874

Sponsored Links