groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henson Sturgill <henson.re...@gmail.com>
Subject Re: groovy.sql.Sql - When is the connection actually made?
Date Fri, 01 May 2015 14:11:49 GMT
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> 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> 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
> >>
> >> On Thu, Apr 30, 2015 at 10:56 AM, Henson Sturgill <
> 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> 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>
> >>>> wrote:
> >>>>>
> >>>>>
> >>>>> On 29 Apr 2015 23:10, "Henson Sturgill" <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
>
>

Mime
View raw message