groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul King <pa...@asert.com.au>
Subject Re: groovy.sql.Sql - When is the connection actually made?
Date Fri, 01 May 2015 22:34:58 GMT
On 2/05/2015 2:27 AM, Søren Berg Glasius wrote:
> But isn't it true, that if you use Sql with some database pool (and a connection instead)
it will take a connection from the pool and return it when done?

Most database pools will give you the datasource option, e.g. see the example for Apache Commmons
DBCP in the Groovy documentation:

http://docs.groovy-lang.org/next/html/documentation/#_connecting_with_a_datasource

Hence the connection will be obtained from the datasource for each operation and
closed after each operation (without any of the other closure connection retention
methods in play). Closing a connection from a datasource will make it available
for other calls (hence back in the pool). Closing an already closed connection
(from a datasource) is benign.

Cheers, Paul.

> Best regards / Med venlig hilsen,
> Søren Berg Glasius
>
> 40 Stevenson Ave, Berkeley, CA 94708
> Mobile: (+1)510 984 8362, Skype: sbglasius
> --- Press ESC once to quit - twice to save the changes.
>
> On 1 May 2015 at 07:11, Henson Sturgill <henson.reset@gmail.com <mailto:henson.reset@gmail.com>>
wrote:
>
>     Ah, thanks Paul & Dinko! That makes sense. I actually hadn't used withInstance()
before. Aren't closures amazing? Makes me really want to go back and learn some JavaScript.
>
>     Anyway, here's my connection time-out test code now:
>
>     /* Example Code */
>     import groovy.sql.Sql
>
>     String query = "select count(*) cnt from spriden where upper(spriden_last_name) =
'STURGILL'"
>     int minuteDelay = 0
>
>     Sql.withInstance("jdbc:oracle:thin:@SOMESERVER:15215:SOMETNS",
>                       "henson", "PASSWORD", "oracle.jdbc.driver.OracleDriver")
>                       {
>                           while (minuteDelay <= 120) {
>                               // Sleep increasing number of minutes
>                               Thread.sleep(1000 * 60 * minuteDelay)
>                               Date date = new Date()
>                               it.eachRow(query) {
>                                   println "After ${minuteDelay} minutes - ${it.cnt} (${date})"
>                               }
>                               minuteDelay += 5
>                           }
>                       }
>     /* End Example */
>
>
>     On Fri, May 1, 2015 at 8:17 AM Paul King <paulk@asert.com.au <mailto:paulk@asert.com.au>>
wrote:
>
>
>         Yes, what Dinko said ... the second time! :-)
>
>         In summary, if you create your Sql instance using the DataSource
>         constructor, then a new connection will be obtained from the
>         datasource (frequently a pool) for each operation and closed
>         after each operation. There are cacheConnection(Closure) and
>         withTransaction(Closure) methods which retain the connection
>         across the scope of the closure call before closing it.
>
>         If you use any of the withInstance(*args, Closure) methods then the
>         connection will be created at the start of the withInstance closure
>         block and closed automatically at the end of the block.
>
>         Otherwise, it is up to you to  manually close the connection.
>
>         Cheers, Paul.
>
>         On 1/05/2015 8:49 PM, Dinko Srkoč wrote:
>          > Henson, sorry! It seems that I was wrong. If you made an Sql instance
>          > by calling newInstance() then there would be only one JDBC connection
>          > and it would be alive until close() is called explicitly, either
>          > directly on the connection or on the Sql object itself, or when the
>          > program terminates.
>          >
>          > So, calling eachRow() actually uses existing connection, it does not
>          > create another one. What gets closed is the ResultSet and Statement
>          > objects.
>          >
>          > When I answered your question it was 5 am in my time zone, and that is
>          > my excuse. ;-)
>          >
>          > Cheers,
>          > Dinko
>          >
>          > On 30 April 2015 at 21:46, Owen Rubel <orubel@gmail.com <mailto:orubel@gmail.com>>
wrote:
>          >> That was why I asked about a connection pool. With a connection pool,
it
>          >> won't get dropped as long as it is active. but inactive connections
go back
>          >> into the pool. You can use a connection pool without having the overhead
of
>          >> having to create a connection everytime you need to make a call and
without
>          >> having to manage the connections yourself.
>          >>
>          >> Owen Rubel
>          >> 415-971-0976
>          >> orubel@gmail.com <mailto:orubel@gmail.com>
>          >>
>          >> On Thu, Apr 30, 2015 at 10:56 AM, Henson Sturgill <henson.reset@gmail.com
<mailto:henson.reset@gmail.com>>
>          >> wrote:
>          >>>
>          >>> Thanks Owen, and Dinko.
>          >>>
>          >>> I was just asking how connections were made. My goal was to keep
a single
>          >>> connection open and test it, a growing number of minutes between
tries, to
>          >>> see when/if the connection was being dropped. But since the connection
is
>          >>> made on each execution, my code will most likely never fail.
>          >>>
>          >>> Thanks so much,
>          >>> Henson
>          >>>
>          >>> On Thu, Apr 30, 2015 at 1:04 AM Owen Rubel <orubel@gmail.com
<mailto:orubel@gmail.com>> wrote:
>          >>>>
>          >>>> Just in case you are asking about connection pools, you can
use grails
>          >>>> datasource for defining the pool.
>          >>>>
>          >>>> here's a good thread on that...
>          >>>>
>          >>>>
>          >>>> http://stackoverflow.com/questions/12291930/connection-pooling-and-prepared-statements-with-groovy-sql-sql-or-jdbc-in-grails
>          >>>>
>          >>>>
>          >>>> On Wed, Apr 29, 2015 at 8:22 PM, Dinko Srkoč <dinko.srkoc@gmail.com
<mailto:dinko.srkoc@gmail.com>>
>          >>>> wrote:
>          >>>>>
>          >>>>>
>          >>>>> On 29 Apr 2015 23:10, "Henson Sturgill" <henson.reset@gmail.com
<mailto:henson.reset@gmail.com>> wrote:
>          >>>>>>
>          >>>>>> Been playing around with a hosted Oracle database that
*seems* to be
>          >>>>>> dropping connections. I made the following script to
test (call to
>          >>>>>> Sql.newInstance() not shown) but I'm curious -- does
Groovy create the
>          >>>>>> connection to the database on newInstance(), or every
time eachRow() is
>          >>>>>> called?
>          >>>>>>
>          >>>>>
>          >>>>> Every time eachRow is called, and the connection is closed
upon
>          >>>>> consuming the dataset.
>          >>>>>
>          >>>>> Cheers,
>          >>>>> Dinko
>          >>>>>
>          >>>>>> --- Example Code ---
>          >>>>>>
>          >>>>>> String query = "select count(*) cnt from spriden where
>          >>>>>> upper(spriden_last_name) = 'STURGILL'"
>          >>>>>> int minuteDelay = 0
>          >>>>>>
>          >>>>>> while (minuteDelay <= 120) {
>          >>>>>>      Thread.sleep(1000 * 60 * minuteDelay); // Sleep
increasing number
>          >>>>>> of minutes
>          >>>>>>      Date date = new Date();
>          >>>>>>      sql.eachRow(query) {
>          >>>>>>          println "After ${minuteDelay} minutes - ${it.cnt}
(${date})"
>          >>>>>>      }
>          >>>>>>      minuteDelay += 5;
>          >>>>>> }
>          >>>>>>
>          >>>>>> --- End Example ---
>          >>>>>>
>          >>>>>> Thanks so much you wonderful programmers!
>          >>
>          >>
>          >
>
>
>         ---
>         This email has been checked for viruses by Avast antivirus software.
>         http://www.avast.com
>
>


---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com


Mime
View raw message