embedding a variable in sql select

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

embedding a variable in sql select

kelly prendergast
Groovy people...
I'm a relative newbie to groovy and java. I want to add a "where" clause
in my sql select statement and include a variable in the string but am
not sure how to do it. Basically I want to retrieve the previous months
records to write to a file.

Thanks for any help you can provide.
Kelly

// Perpare date string for select statement so the previous month is
retrieved
          def today= new Date()
          def dstring = today.format("yyyy-MM-dd")
          def yr = dstring[0..3]
       def mnth = dstring[5..6]
       def d = dstring[8..9]
          println mnth
          println dstring
          def new_mnth = mnth.toInteger()-1
          println new_mnth
          if (new_mnth < 10)
          {new_str_mnth = '0'+ new_mnth.toString()}
          else
            {new_str_mnth = new_mnth.toString()}
          println new_str_mnth
          def new_dstring =
yr.toString()+'-'+new_str_mnth.toString()+'-'+d.toString()
          println new_dstring

// delete any old files
          boolean fileSuccessfullyDeleted =  new
File("monthlyXray.txt").delete()
          println "return code: " + fileSuccessfullyDeleted

// create new output file
       File xrfile = new File("monthlyXray.txt")

//connect to E-SWDS database
          def
sql=Sql.newInstance("jdbc:sqlserver://swds-ex.swpc.noaa.gov:1433;databaseName=swdata;integratedSecurity=false","ngdctest",
'red4$Cda!e0809',"com.microsoft.sqlserver.jdbc.SQLServerDriver")
          // write results of query to file
           xrfile << "observatory begin_time max_time end_time begin_flux
max_flux integrated_flux\n"
           sql.eachRow('select observatory, begin_time, max_time, end_time,
begin_flux, max_flux, integrated_flux from goes_xray_edited_events where
begin_time > "${new_dstring.toString()}"')

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

kelly_prendergast.vcf (315 bytes) Download Attachment