Applying sql scripts from a file

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Applying sql scripts from a file

Andy Stevens-2
Hi,

Does anyone know of a simply utility class to read in a file
containing the database schema definition and apply it to a database?
I have the schema scripts used to create the live database, but have
some tests that run against an in-memory copy for speed; it helps if
the tables exist before dbunit populates them with the test data, so I
need something to apply the script.

I've found SimpleJdbcTestUtils.executeSqlScript() in the spring-test
library, but since the app doesn't use spring itself I'm a bit
reluctant to drag in nearly a dozen extra jars to the project (tried
it in a new empty project, and that's what maven downloaded after
resolving all the transitive dependencies...)

I could always write a function to do it myself, loop over a
bufferedreader on the file, build up the statement in a stringbuffer
and execute whenever I see "GO" or EOF, but I can't help but feel it's
such a common requirement there must be an existing (more flexible)
utility to do it so why reinvent the wheel?  To be honest, I was a
little surprised there was nothing to do this in the org.dbunit.util
package...

Any other suggestions?


Andy
--
http://pseudoq.sourceforge.net/  Open source java sudoku solver

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Applying sql scripts from a file

Jeff Jensen-2
How about the Maven SQL plugin?
http://mojo.codehaus.org/sql-maven-plugin/


On Thu, Apr 7, 2011 at 3:37 AM, Andy Stevens
<[hidden email]> wrote:

> Hi,
>
> Does anyone know of a simply utility class to read in a file
> containing the database schema definition and apply it to a database?
> I have the schema scripts used to create the live database, but have
> some tests that run against an in-memory copy for speed; it helps if
> the tables exist before dbunit populates them with the test data, so I
> need something to apply the script.
>
> I've found SimpleJdbcTestUtils.executeSqlScript() in the spring-test
> library, but since the app doesn't use spring itself I'm a bit
> reluctant to drag in nearly a dozen extra jars to the project (tried
> it in a new empty project, and that's what maven downloaded after
> resolving all the transitive dependencies...)
>
> I could always write a function to do it myself, loop over a
> bufferedreader on the file, build up the statement in a stringbuffer
> and execute whenever I see "GO" or EOF, but I can't help but feel it's
> such a common requirement there must be an existing (more flexible)
> utility to do it so why reinvent the wheel?  To be honest, I was a
> little surprised there was nothing to do this in the org.dbunit.util
> package...
>
> Any other suggestions?
>
>
> Andy
> --
> http://pseudoq.sourceforge.net/  Open source java sudoku solver
>
> ------------------------------------------------------------------------------
> Xperia(TM) PLAY
> It's a major breakthrough. An authentic gaming
> smartphone on the nation's most reliable network.
> And it wants your games.
> http://p.sf.net/sfu/verizon-sfdev
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Applying sql scripts from a file

Andy Stevens-2
On 7 April 2011 13:43, Jeff Jensen <[hidden email]> wrote:
> How about the Maven SQL plugin?
> http://mojo.codehaus.org/sql-maven-plugin/

Hi Jeff,

The biggest snag with that is the project doesn't use maven...
Also, I'm using an in-memory hsql database and the junit Ant task
forks a new jvm (in order to use specific jvmargs).  Any setup done in
the build.xml doesn't seem to be visible within the tests, otherwise
I'd just use Ant's own sql task.  I suspect the same would be the case
for Maven's Surefile plugin, since we'd need to use forkMode <>
"never" (http://maven.apache.org/plugins/maven-surefire-plugin/test-mojo.html)
 So I want to run the "create table" script in a @BeforeClass method
in the Test class.


Andy
--
http://pseudoq.sourceforge.net  Open source java sudoku solver

> On Thu, Apr 7, 2011 at 3:37 AM, Andy Stevens
> <[hidden email]> wrote:
>> Hi,
>>
>> Does anyone know of a simply utility class to read in a file
>> containing the database schema definition and apply it to a database?
>> I have the schema scripts used to create the live database, but have
>> some tests that run against an in-memory copy for speed; it helps if
>> the tables exist before dbunit populates them with the test data, so I
>> need something to apply the script.
>>
>> I've found SimpleJdbcTestUtils.executeSqlScript() in the spring-test
>> library, but since the app doesn't use spring itself I'm a bit
>> reluctant to drag in nearly a dozen extra jars to the project (tried
>> it in a new empty project, and that's what maven downloaded after
>> resolving all the transitive dependencies...)
>>
>> I could always write a function to do it myself, loop over a
>> bufferedreader on the file, build up the statement in a stringbuffer
>> and execute whenever I see "GO" or EOF, but I can't help but feel it's
>> such a common requirement there must be an existing (more flexible)
>> utility to do it so why reinvent the wheel?  To be honest, I was a
>> little surprised there was nothing to do this in the org.dbunit.util
>> package...
>>
>> Any other suggestions?
>>
>>
>> Andy

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Applying sql scripts from a file

Mike Kienenberger
In reply to this post by Andy Stevens-2
The easiest thing to do is write your own.  That's what we did.   It
has the advantage that you can take your sql scripts written for
another database, tweak it as you read it in, and apply the adjusted
sql to HSQLDB.

I somewhat suspect that the reason no one has provided patches to do
this because a) it's so easy to write yourself, and b) every
environment tends to require something different, especially for the
more complicated database systems.   So by the time you write up the
code, it isn't a generic solution any more.   Making it generic would
make it trivial.   The specifics being how you find the file, how get
a connection to the database, schema setup (if specified outside of
sql)., how you will deal with logging and errors.


By the time you're done, all you have left is this (and I suspect most
people wouldn't like how we dealt [or not dealt] with error handling).

protected String getInputStreamContentsAsString(InputStream inputStream)
        throws FileNotFoundException, IOException {
    StringBuilder stringBuilder = new StringBuilder();

    InputStreamReader inputStreamReader = new InputStreamReader(inputStream);
    BufferedReader reader = new BufferedReader(inputStreamReader);
    String line = reader.readLine();
    while (null != line)
    {
        stringBuilder.append(' ');
        stringBuilder.append(line);
        line = reader.readLine();
    }

    String value = stringBuilder.toString();
    return value;
}

protected void executeSql(Connection connection, String sqlString) {
    log.info("EXECUTE SQL: " + sqlString);

    try
    {

      // Get a statement from the connection
      Statement stmt = connection.createStatement() ;

      // Execute the query
      stmt.executeUpdate(sqlString) ;

      // Close the result set, statement and the connection
      stmt.close() ;
     }
        catch( SQLException se )
     {
  log.error("EXECUTE SQL: " + sqlString) ;
  log.error("  SQL Exception:" ) ;

      // Loop through the SQL Exceptions
      while( se != null )
         {
          log.error( "    State  : " + se.getSQLState()  ) ;
          log.error( "    Message: " + se.getMessage()   ) ;
          log.error( "    Error  : " + se.getErrorCode() ) ;

          se = se.getNextException() ;
         }
     }
                catch( Exception e )
     {
      log.error( e ) ;
     }
}


On Thu, Apr 7, 2011 at 4:37 AM, Andy Stevens
<[hidden email]> wrote:

> Hi,
>
> Does anyone know of a simply utility class to read in a file
> containing the database schema definition and apply it to a database?
> I have the schema scripts used to create the live database, but have
> some tests that run against an in-memory copy for speed; it helps if
> the tables exist before dbunit populates them with the test data, so I
> need something to apply the script.
>
> I've found SimpleJdbcTestUtils.executeSqlScript() in the spring-test
> library, but since the app doesn't use spring itself I'm a bit
> reluctant to drag in nearly a dozen extra jars to the project (tried
> it in a new empty project, and that's what maven downloaded after
> resolving all the transitive dependencies...)
>
> I could always write a function to do it myself, loop over a
> bufferedreader on the file, build up the statement in a stringbuffer
> and execute whenever I see "GO" or EOF, but I can't help but feel it's
> such a common requirement there must be an existing (more flexible)
> utility to do it so why reinvent the wheel?  To be honest, I was a
> little surprised there was nothing to do this in the org.dbunit.util
> package...
>
> Any other suggestions?
>
>
> Andy
> --
> http://pseudoq.sourceforge.net/  Open source java sudoku solver
>
> ------------------------------------------------------------------------------
> Xperia(TM) PLAY
> It's a major breakthrough. An authentic gaming
> smartphone on the nation's most reliable network.
> And it wants your games.
> http://p.sf.net/sfu/verizon-sfdev
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user