RE: Microsoft SQL Server validation query
These queries all seem like an awful lot of unnecessary processing. Here's what I use: SELECT 1+1 -Original Message- From: Antonio Fiol Bonnín [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:25 AM To: Tomcat Users List Subject: Re: Microsoft SQL Server validation query For any database server I can think of: CREATE TABLE validation ( dummy char(1) ); INSERT INTO validation VALUES ( 'X' ); -- and make sure you do this only once And then use as a validation query: SELECT * FROM validation; Well. Yes. I am ashamed of reimplementing Oracle's DUAL, but if you really want to know of a table that will always be there, provide it yourself. And anyway, I mostly agree with Mike's statement about server-specific params in the config files: Adding one more ... is not too much of a burden. Only if you set the validation query from outside the config files, which is very unlikely, Mike's statement would not strictly apply IMHO. Antonio Fiol Derek Mahar wrote: Thank you to all of you for your quick replies. It seems that the connection pool validation query is not specific to any database server implementation unless the query statement itself is server-specific (that is, it refers to a special server system database, table, or function). I like the idea of querying the server for the date, but I'm not sure that the date function is standard SQL and portable across all server implementations. Does anyone know otherwise? Derek -Original Message- From: Peter Lin [mailto:[EMAIL PROTECTED] Sent: January 8, 2004 10:35 AM To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query in the past I just select the date from sql server. unless you want to test a specific table, but that has potential performance impact. the safe simple query to see if sql server is alive is to just select the date. peter lin Allistair Crossley [EMAIL PROTECTED] wrote: I would not do that because that would return as many 1s as there are rows in the table. Something like count(*) may not be the most efficient but it returns just 1 row always. Also with using 1, you cannot guarantee a row will come back. Allistair Crossley __ Intranet Senior Developer New Media Group, QAS Ltd Telephone: 020 7819 5343 __ -Original Message- From: Michael Duffy [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:25 To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query Or even SELECT 1 FROM TABLE. No COUNT overhead, if any. - MOD --- Allistair Crossley wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Microsoft SQL Server validation query
Hooper, Brian wrote: These queries all seem like an awful lot of unnecessary processing. Here's what I use: SELECT 1+1 Indeed. If your server supports it, there is an even simpler one: select 1 or select 0. But this is not correct SQL for an Oracle server. Equivalent Oracle syntax is select 1 from dual. But on other servers, dual does not exist by default. Well... you see what I mean... Antonio smime.p7s Description: S/MIME Cryptographic Signature
RE: Microsoft SQL Server validation query
Oops, I didn't realize those types of queries don't fly on other databases. Thanks for the tip. -Original Message- From: Antonio Fiol Bonnín [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:03 PM To: Tomcat Users List Subject: Re: Microsoft SQL Server validation query Hooper, Brian wrote: These queries all seem like an awful lot of unnecessary processing. Here's what I use: SELECT 1+1 Indeed. If your server supports it, there is an even simpler one: select 1 or select 0. But this is not correct SQL for an Oracle server. Equivalent Oracle syntax is select 1 from dual. But on other servers, dual does not exist by default. Well... you see what I mean... Antonio - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
How about one of these: Select @@VERSION Or Select getdate() as CurrentDate -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: Thursday, January 08, 2004 8:15 AM To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
How about one of these: Select @@VERSION Or Select getdate() as CurrentDate I would rather use: SELECT CURRENT_DATE since CURRENT_DATE (no parenthesis) is standard SQL, I am not aware if this suffers the same problems as SELECT 1 on Oracle. Another function is COUNT(), and if that is not present on a system... Giuliano -- H U M P H || ||| software Java C++ Server/Client/Human Interface applications on MacOS - MacOS X http://www.humph.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
Any, such as if you have a user table, Select lastname from user where userid = 1 It's just a query which is going to return results. -Art D'Alessandro CBE Technologies Office: 617-514-1785 Cell: 617-905-5917 -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: Thursday, January 08, 2004 10:15 AM To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] FONT SIZE=1 FACE=VERDANA,ARIAL COLOR=BLUE --- QAS Ltd. Developers of QuickAddress Software a href=http://www.qas.com;www.qas.com/a Registered in England: No 2582055 Registered in Australia: No 082 851 474 --- /FONT - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
Or even SELECT 1 FROM TABLE. No COUNT overhead, if any. - MOD --- Allistair Crossley [EMAIL PROTECTED] wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] FONT SIZE=1 FACE=VERDANA,ARIAL COLOR=BLUE --- QAS Ltd. Developers of QuickAddress Software a href=http://www.qas.com;www.qas.com/a Registered in England: No 2582055 Registered in Australia: No 082 851 474 --- /FONT - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
I would not do that because that would return as many 1s as there are rows in the table. Something like count(*) may not be the most efficient but it returns just 1 row always. Also with using 1, you cannot guarantee a row will come back. Allistair Crossley __ Intranet Senior Developer New Media Group, QAS Ltd Telephone: 020 7819 5343 __ -Original Message- From: Michael Duffy [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:25 To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query Or even SELECT 1 FROM TABLE. No COUNT overhead, if any. - MOD --- Allistair Crossley [EMAIL PROTECTED] wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] FONT SIZE=1 FACE=VERDANA,ARIAL COLOR=BLUE --- QAS Ltd. Developers of QuickAddress Software a href=http://www.qas.com;www.qas.com/a Registered in England: No 2582055 Registered in Australia: No 082 851 474 --- /FONT - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
in the past I just select the date from sql server. unless you want to test a specific table, but that has potential performance impact. the safe simple query to see if sql server is alive is to just select the date. peter lin Allistair Crossley [EMAIL PROTECTED] wrote: I would not do that because that would return as many 1s as there are rows in the table. Something like count(*) may not be the most efficient but it returns just 1 row always. Also with using 1, you cannot guarantee a row will come back. Allistair Crossley __ Intranet Senior Developer New Media Group, QAS Ltd Telephone: 020 7819 5343 __ -Original Message- From: Michael Duffy [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:25 To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query Or even SELECT 1 FROM TABLE. No COUNT overhead, if any. - MOD --- Allistair Crossley wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- QAS Ltd. Developers of QuickAddress Software www.qas.com Registered in England: No 2582055 Registered in Australia: No 082 851 474 --- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
RE: Microsoft SQL Server validation query
If that takes to long, you can limit the search, if you have a table with an indexed column where you know that there is certain id SELECT 1 FROM TABLE WHERE ID = known id (We always have such tables) --- Allistair Crossley [EMAIL PROTECTED] wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
Thank you to all of you for your quick replies. It seems that the connection pool validation query is not specific to any database server implementation unless the query statement itself is server-specific (that is, it refers to a special server system database, table, or function). I like the idea of querying the server for the date, but I'm not sure that the date function is standard SQL and portable across all server implementations. Does anyone know otherwise? Derek -Original Message- From: Peter Lin [mailto:[EMAIL PROTECTED] Sent: January 8, 2004 10:35 AM To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query in the past I just select the date from sql server. unless you want to test a specific table, but that has potential performance impact. the safe simple query to see if sql server is alive is to just select the date. peter lin Allistair Crossley [EMAIL PROTECTED] wrote: I would not do that because that would return as many 1s as there are rows in the table. Something like count(*) may not be the most efficient but it returns just 1 row always. Also with using 1, you cannot guarantee a row will come back. Allistair Crossley __ Intranet Senior Developer New Media Group, QAS Ltd Telephone: 020 7819 5343 __ -Original Message- From: Michael Duffy [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:25 To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query Or even SELECT 1 FROM TABLE. No COUNT overhead, if any. - MOD --- Allistair Crossley wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- QAS Ltd. Developers of QuickAddress Software www.qas.com Registered in England: No 2582055 Registered in Australia: No 082 851 474 --- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Microsoft SQL Server validation query
off hand I know selecting the date from sybase, sql server and oracle are all different. one way around it is to use a stored procedure, that way you can have the same stored proc in each RDBMS and the java call is the same. I'm sure others have done it a different way, but that's how I've tackled the problem in the past. peter Derek Mahar [EMAIL PROTECTED] wrote: Thank you to all of you for your quick replies. It seems that the connection pool validation query is not specific to any database server implementation unless the query statement itself is server-specific (that is, it refers to a special server system database, table, or function). I like the idea of querying the server for the date, but I'm not sure that the date function is standard SQL and portable across all server implementations. Does anyone know otherwise? Derek - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
RE: Microsoft SQL Server validation query
Does it really matter if it's different? You are setting up a datasource in a configuration file, the contents of that configuration are pretty much guaranteed to be different for every database anyways. (JDBC URL and Driver come to mind). Adding one more (the validation query) is not too much of a burden. -Original Message- From: Peter Lin [mailto:[EMAIL PROTECTED] Sent: Thursday, January 08, 2004 10:32 AM To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query off hand I know selecting the date from sybase, sql server and oracle are all different. one way around it is to use a stored procedure, that way you can have the same stored proc in each RDBMS and the java call is the same. I'm sure others have done it a different way, but that's how I've tackled the problem in the past. peter Derek Mahar [EMAIL PROTECTED] wrote: Thank you to all of you for your quick replies. It seems that the connection pool validation query is not specific to any database server implementation unless the query statement itself is server-specific (that is, it refers to a special server system database, table, or function). I like the idea of querying the server for the date, but I'm not sure that the date function is standard SQL and portable across all server implementations. Does anyone know otherwise? Derek - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Microsoft SQL Server validation query
For any database server I can think of: CREATE TABLE validation ( dummy char(1) ); INSERT INTO validation VALUES ( 'X' ); -- and make sure you do this only once And then use as a validation query: SELECT * FROM validation; Well. Yes. I am ashamed of reimplementing Oracle's DUAL, but if you really want to know of a table that will always be there, provide it yourself. And anyway, I mostly agree with Mike's statement about server-specific params in the config files: Adding one more ... is not too much of a burden. Only if you set the validation query from outside the config files, which is very unlikely, Mike's statement would not strictly apply IMHO. Antonio Fiol Derek Mahar wrote: Thank you to all of you for your quick replies. It seems that the connection pool validation query is not specific to any database server implementation unless the query statement itself is server-specific (that is, it refers to a special server system database, table, or function). I like the idea of querying the server for the date, but I'm not sure that the date function is standard SQL and portable across all server implementations. Does anyone know otherwise? Derek -Original Message- From: Peter Lin [mailto:[EMAIL PROTECTED] Sent: January 8, 2004 10:35 AM To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query in the past I just select the date from sql server. unless you want to test a specific table, but that has potential performance impact. the safe simple query to see if sql server is alive is to just select the date. peter lin Allistair Crossley [EMAIL PROTECTED] wrote: I would not do that because that would return as many 1s as there are rows in the table. Something like count(*) may not be the most efficient but it returns just 1 row always. Also with using 1, you cannot guarantee a row will come back. Allistair Crossley __ Intranet Senior Developer New Media Group, QAS Ltd Telephone: 020 7819 5343 __ -Original Message- From: Michael Duffy [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:25 To: Tomcat Users List Subject: RE: Microsoft SQL Server validation query Or even SELECT 1 FROM TABLE. No COUNT overhead, if any. - MOD --- Allistair Crossley wrote: i think you could use anything .. maybe SELECT COUNT(*) FROM table The dual table is an oracle dummy table and is quite handy, but I think the validation query can just be any old select statement that should return true a result always. ADC -Original Message- From: Derek Mahar [mailto:[EMAIL PROTECTED] Sent: 08 January 2004 15:15 To: Tomcat Users List Subject: Microsoft SQL Server validation query Does anyone happen to know which validation query I should use for Microsoft SQL Server? Derek smime.p7s Description: S/MIME Cryptographic Signature
