How to execute an external *.sql/*.ddl script file?

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

How to execute an external *.sql/*.ddl script file?

BenXS
Assume I have two external script files:

One (mycreate.sql) for creating tables and another (myfill.sql) to fill the tables (by INSERT statements).

Both are placed in the root directory of my Eclipse project.

How can I call now such a *.sql script from my jUnit java test class?

Thank you
Ben
Reply | Threaded
Open this post in threaded view
|

Re: How to execute an external *.sql/*.ddl script file?

Mike Kienenberger
On Thu, Jan 27, 2011 at 5:30 AM, BenXS <[hidden email]> wrote:
>
> Assume I have two external script files:
>
> One (mycreate.sql) for creating tables and another (myfill.sql) to fill the
> tables (by INSERT statements).
>
> Both are placed in the root directory of my Eclipse project.


Here's one way.  This assumes that your tests with your working
directory set the root directory of your project (which is the default
for running tests in eclipse).

=============
                        JdbcDatabaseTester jdbcDatabaseTester = new JdbcDatabaseTester(driver, url,
                                        userid, password);
                }
               
                IDatabaseConnection connection = jdbcDatabaseTester.getConnection();
[...]
                        Connection conn = dbunit.getConnection().getConnection();
[...]

[...]
  String localSqlFile = "yourFile.sql";

    FileInputStream inputStream = new FileInputStream(localSqlFile);
[...]
    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 sqlString = stringBuilder.toString();
[...]
      Statement stmt = connection.createStatement() ;

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

      // Close the result set, statement and the connection
      stmt.close() ;

------------------------------------------------------------------------------
Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
Finally, a world-class log management solution at an even better price-free!
Download using promo code Free_Logger_4_Dev2Dev. Offer expires
February 28th, so secure your free ArcSight Logger TODAY!
http://p.sf.net/sfu/arcsight-sfd2d
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: How to execute an external *.sql/*.ddl script file?

BenXS
Thank you.

Your suggested way is however the traditional way of jdbc.

So why is dbunit existing/invented at all?

I thought dbunit will offer a convenient way of executing such external script with statements like

dbunit.connect(driver, url, userid, passwd);
dbunit.executescript("D:\test\proj123\myscript.sql");

Am I wrong? What else is the purpose of dbunit if it does not provide such a basic feature?

Ben

Mike Kienenberger wrote
On Thu, Jan 27, 2011 at 5:30 AM, BenXS <bxstover@yahoo.co.uk> wrote:
>
> Assume I have two external script files:
>
> One (mycreate.sql) for creating tables and another (myfill.sql) to fill the
> tables (by INSERT statements).
>
> Both are placed in the root directory of my Eclipse project.


Here's one way.  This assumes that your tests with your working
directory set the root directory of your project (which is the default
for running tests in eclipse).

=============
                        JdbcDatabaseTester jdbcDatabaseTester = new JdbcDatabaseTester(driver, url,
                                        userid, password);
                }
               
                IDatabaseConnection connection = jdbcDatabaseTester.getConnection();
[...]
                        Connection conn = dbunit.getConnection().getConnection();
[...]

[...]
  String localSqlFile = "yourFile.sql";

    FileInputStream inputStream = new FileInputStream(localSqlFile);
[...]
    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 sqlString = stringBuilder.toString();
[...]
      Statement stmt = connection.createStatement() ;

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

      // Close the result set, statement and the connection
      stmt.close() ;

------------------------------------------------------------------------------
Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
Finally, a world-class log management solution at an even better price-free!
Download using promo code Free_Logger_4_Dev2Dev. Offer expires
February 28th, so secure your free ArcSight Logger TODAY!
http://p.sf.net/sfu/arcsight-sfd2d
_______________________________________________
dbunit-user mailing list
dbunit-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: How to execute an external *.sql/*.ddl script file?

Sébastien LE CALLONNEC
Hi Ben,


Good question, and I am not sure I have a convincing explanation to this
-- maybe the others will come up with something better! ;)

DbUnit uses formats such as XML, csv, etc. to define test data quickly
and in a database-independent fashion.  The same formats can be used to
build the “expected” datasets, which obviously would not be as
straightforward to do with a bunch of db-specific queries.  I guess
that’s the gist of it.

Ironically, DbUnit’s own unit tests partly provide the features you are
referring to through the *Environment classes (as far as I remember
HypersonicEnvironment does just what you want to do).



Regards,
Seb


On 02/02/2011 14:50, BenXS wrote:

>
> Thank you.
>
> Your suggested way is however the traditional way of jdbc.
>
> So why is dbunit existing/invented at all?
>
> I thought dbunit will offer a convenient way of executing such external
> script with statements like
>
> dbunit.connect(driver, url, userid, passwd);
> dbunit.executescript("D:\test\proj123\myscript.sql");
>
> Am I wrong? What else is the purpose of dbunit if it does not provide such a
> basic feature?
>
> Ben
>
>
> Mike Kienenberger wrote:
>>
>> On Thu, Jan 27, 2011 at 5:30 AM, BenXS<[hidden email]>  wrote:
>>>
>>> Assume I have two external script files:
>>>
>>> One (mycreate.sql) for creating tables and another (myfill.sql) to fill
>>> the
>>> tables (by INSERT statements).
>>>
>>> Both are placed in the root directory of my Eclipse project.
>>
>>
>> Here's one way.  This assumes that your tests with your working
>> directory set the root directory of your project (which is the default
>> for running tests in eclipse).
>>
>> =============
>> JdbcDatabaseTester jdbcDatabaseTester = new JdbcDatabaseTester(driver,
>> url,
>> userid, password);
>> }
>>
>> IDatabaseConnection connection = jdbcDatabaseTester.getConnection();
>> [...]
>> Connection conn = dbunit.getConnection().getConnection();
>> [...]
>>
>> [...]
>>    String localSqlFile = "yourFile.sql";
>>
>>      FileInputStream inputStream = new FileInputStream(localSqlFile);
>> [...]
>>      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 sqlString = stringBuilder.toString();
>> [...]
>>        Statement stmt = connection.createStatement() ;
>>
>>        // Execute the query
>>        stmt.executeUpdate(sqlString) ;
>>
>>        // Close the result set, statement and the connection
>>        stmt.close() ;
>>
>> ------------------------------------------------------------------------------
>> Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
>> Finally, a world-class log management solution at an even better
>> price-free!
>> Download using promo code Free_Logger_4_Dev2Dev. Offer expires
>> February 28th, so secure your free ArcSight Logger TODAY!
>> http://p.sf.net/sfu/arcsight-sfd2d
>> _______________________________________________
>> dbunit-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>
>>
>

------------------------------------------------------------------------------
Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
Finally, a world-class log management solution at an even better price-free!
Download using promo code Free_Logger_4_Dev2Dev. Offer expires
February 28th, so secure your free ArcSight Logger TODAY!
http://p.sf.net/sfu/arcsight-sfd2d
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: How to execute an external *.sql/*.ddl script file?

Mike Kienenberger
In reply to this post by BenXS
I guess I'll take a stab at this as a dbunit user rather than
developer.  Especially as one who needs these functions.

Responses inline.

On Wed, Feb 2, 2011 at 9:50 AM, BenXS <[hidden email]> wrote:
> Your suggested way is however the traditional way of jdbc.
>
> So why is dbunit existing/invented at all?

Well, if there's already a way to do it with jdbc, why not use that
tool?  Why reinvent the wheel?


> I thought dbunit will offer a convenient way of executing such external
> script with statements like
>
> dbunit.connect(driver, url, userid, passwd);

This part really should be done in jdbc.   It already does what is
necessary for every kind of connection type.


> dbunit.executescript("D:\test\proj123\myscript.sql");

This would certainly be useful.   But does it really have much to do
with database unit testing?  Not directly.
But it'd still be nice to see this included as a utility class for dbunit.


> Am I wrong? What else is the purpose of dbunit if it does not provide such a
> basic feature?

Dbunit isn't for managing databases -- it's for testing them.   So
it's an extra extended feature, not a basic feature of the framework.


All that said, if you posted a patch providing this functionality,
using either the code I sent you or the code included in dbunit's
testing environment, it seems likely that it'd make it into a future
release.


> Mike Kienenberger wrote:
>>
>> On Thu, Jan 27, 2011 at 5:30 AM, BenXS <[hidden email]> wrote:
>>>
>>> Assume I have two external script files:
>>>
>>> One (mycreate.sql) for creating tables and another (myfill.sql) to fill
>>> the
>>> tables (by INSERT statements).
>>>
>>> Both are placed in the root directory of my Eclipse project.
>>
>>
>> Here's one way.  This assumes that your tests with your working
>> directory set the root directory of your project (which is the default
>> for running tests in eclipse).
>>
>> =============
>>                       JdbcDatabaseTester jdbcDatabaseTester = new JdbcDatabaseTester(driver,
>> url,
>>                                       userid, password);
>>               }
>>
>>               IDatabaseConnection connection = jdbcDatabaseTester.getConnection();
>> [...]
>>                       Connection conn = dbunit.getConnection().getConnection();
>> [...]
>>
>> [...]
>>   String localSqlFile = "yourFile.sql";
>>
>>     FileInputStream inputStream = new FileInputStream(localSqlFile);
>> [...]
>>     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 sqlString = stringBuilder.toString();
>> [...]
>>       Statement stmt = connection.createStatement() ;
>>
>>       // Execute the query
>>       stmt.executeUpdate(sqlString) ;
>>
>>       // Close the result set, statement and the connection
>>       stmt.close() ;
>>
>> ------------------------------------------------------------------------------
>> Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
>> Finally, a world-class log management solution at an even better
>> price-free!
>> Download using promo code Free_Logger_4_Dev2Dev. Offer expires
>> February 28th, so secure your free ArcSight Logger TODAY!
>> http://p.sf.net/sfu/arcsight-sfd2d
>> _______________________________________________
>> dbunit-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>
>>
>
> --
> View this message in context: http://old.nabble.com/How-to-execute-an-external-*.sql-*.ddl-script-file--tp30775968p30826682.html
> Sent from the DBUnit - Users mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
> Finally, a world-class log management solution at an even better price-free!
> Download using promo code Free_Logger_4_Dev2Dev. Offer expires
> February 28th, so secure your free ArcSight Logger TODAY!
> http://p.sf.net/sfu/arcsight-sfd2d
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
Finally, a world-class log management solution at an even better price-free!
Download using promo code Free_Logger_4_Dev2Dev. Offer expires
February 28th, so secure your free ArcSight Logger TODAY!
http://p.sf.net/sfu/arcsight-sfd2d
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: How to execute an external *.sql/*.ddl script file?

BenXS
In reply to this post by Sébastien LE CALLONNEC
I would really appreciate such commands for dbunit.
Is there something like an official feature request email address for dbunit?
If I would open a poll for this feature I am sure it will rank at pos one.

Nearly all of my colleagues/friends who use dbunit miss that.
Instead they have to fiddle around with such odd operations like db backup+restore
to refresh start scenario.

Ben

Sébastien LE CALLONNEC wrote
Hi Ben,


Good question, and I am not sure I have a convincing explanation to this
-- maybe the others will come up with something better! ;)

DbUnit uses formats such as XML, csv, etc. to define test data quickly
and in a database-independent fashion.  The same formats can be used to
build the “expected” datasets, which obviously would not be as
straightforward to do with a bunch of db-specific queries.  I guess
that’s the gist of it.

Ironically, DbUnit’s own unit tests partly provide the features you are
referring to through the *Environment classes (as far as I remember
HypersonicEnvironment does just what you want to do).



Regards,
Seb


On 02/02/2011 14:50, BenXS wrote:
>
> Thank you.
>
> Your suggested way is however the traditional way of jdbc.
>
> So why is dbunit existing/invented at all?
>
> I thought dbunit will offer a convenient way of executing such external
> script with statements like
>
> dbunit.connect(driver, url, userid, passwd);
> dbunit.executescript("D:\test\proj123\myscript.sql");
>
> Am I wrong? What else is the purpose of dbunit if it does not provide such a
> basic feature?
>
> Ben
>
>>> Assume I have two external script files:
>>>
>>> One (mycreate.sql) for creating tables and another (myfill.sql) to fill
>>> the
>>> tables (by INSERT statements).
>>>
>>> Both are placed in the root directory of my Eclipse project.
>>