Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

 Show me a simple GSQL application?

Here you are:

Code Block

import groovy.sql.Sql

sql = Sql.newInstance("jdbc:hsqldb:mem", "sa","", "org.hsqldb.jdbcDriver")
sql.execute('create table TableA (FirstName varchar(40),  LastName varchar(40))')
sql.execute('INSERT INTO TableA (FirstName,LastName) values (?,?)',\['Stan','Juka'\])


sql.eachRow('select * from TableA') {
        println "TableA row: ${it.firstName}, ${it.lastName}"
}

The output should be:

No Format

TableA row: Stan, Juka

Why does my statement doesn't work? (GString Special Treatment)

Why the INSERT in this code fails?

Code Block

...
values = "'Stan','Juka'"
insertSQL = "INSERT INTO TableA (FirstName,LastName) values ($values)"

sql.execute(insertSQL)

Because the insertSQL is a GString.  if you make it a String like this

Code Block

String insertSQL = "INSERT INTO TableA (FirstName,LastName) values ($values)"

 it will work.  Or you can do it like this:

Code Block

firstName = 'Stan'
lastName = 'Juka'
insertSQL = "INSERT INTO TableA (FirstName,LastName) values ($firstName,$lastName)"

...

I modified the code to get it to work with Oracle and for readability.
The Sql.newInstance will connect to an Oracle database SID called XE installed on localhost
at port 1521. The database user is "username" and the password is "password".
In order to get this to work you will have to install the the latest jdbc.jar file from Oracle's website.  You need to install the .jar file into the
lib directory in your GROOVY_HOME directory:

Code Block

import groovy.sql.Sql

sql = Sql.newInstance( "jdbc:oracle:thin:@localhost:1521:XE", "username", "password", "oracle.jdbc.OracleDriver" )

createTable	= 'CREATE TABLE TableA ( FirstName varchar2( 40 ), LastName varchar2( 40 ) )'
insertIntoTable = 'INSERT INTO TableA ( FirstName, LastName ) values ( ?, ? )'
selectStatement = 'SELECT * FROM TableA'

sql.execute( createTable )
sql.execute( insertIntoTable, [ 'Bill', 'Lyons' ] )

// the eachRow method is an Iterator
sql.eachRow( selectStatement ) {

  println "TableA row: ${ it.firstName }, ${ it.lastName }" }
}

...