Executing command from a file via JDBC

[raykroeker]

I was wondering if anyone knows of a way I can get derby to execute a series of statements from file, for example to upgrade my db schema from one version to another without resorting to parsing the file for statements myself?

Thanks.

--
---------------------------------------------------------
Raymond Kroeker

[francois.orsini]

You mean like feeding some script(s) to IJ connected to a Derby instance:
http://db.apache.org/derby/docs/dev/tools/tools-single.html#ttoolsij98878

Not sure if that's what you're asking for....

On 5/10/07, Raymond Kroeker <raymond@...> wrote:

I was wondering if anyone knows of a way I can get derby to execute a series of statements from file, for example to upgrade my db schema from one version to another without resorting to parsing the file for statements myself?

Thanks.

--
---------------------------------------------------------
Raymond Kroeker

[raykroeker]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Hi Francois,
   Thanks for your response.  That's similar to what I'm looking for but I didn't want to distribute derbytools.jar with my application.  I guess was looking for something that came with the core jdbc driver.

Ray

On 5/10/07, Francois Orsini <francois.orsini@...> wrote:

You mean like feeding some script(s) to IJ connected to a Derby instance:
http://db.apache.org/derby/docs/dev/tools/tools-single.html#ttoolsij98878

Not sure if that's what you're asking for....

On 5/10/07, Raymond Kroeker <raymond@...> wrote:

I was wondering if anyone knows of a way I can get derby to execute a series of statements from file, for example to upgrade my db schema from one version to another without resorting to parsing the file for statements myself?

Thanks.

--
---------------------------------------------------------
Raymond Kroeker

--
---------------------------------------------------------
Raymond Kroeker

[Bryan Pendleton]

> I didn't want to distribute derbytools.jar with my application.  I guess
> was looking for something that came with the core jdbc driver.

You could put something together yourself pretty easily:

   BufferedReader rdr = ...;
   String sql;
   Statement stmt = conn.createStatement();

   while ( (sql = rdr.readLine()) != null)
      stmt.executeUpdate(sql);

Would that serve your purposes?

thanks,

bryan

[Jim Newsham-2]

> -----Original Message-----
> From: Bryan Pendleton [mailto:bpendleton@...]
> Sent: Thursday, May 10, 2007 9:58 AM
> To: Derby Discussion
> Subject: Re: Executing command from a file via JDBC
>
> > I didn't want to distribute derbytools.jar with my application.  I guess
> > was looking for something that came with the core jdbc driver.
>
> You could put something together yourself pretty easily:
>
>    BufferedReader rdr = ...;
>    String sql;
>    Statement stmt = conn.createStatement();
>
>    while ( (sql = rdr.readLine()) != null)
>       stmt.executeUpdate(sql);
>
> Would that serve your purposes?
>
> thanks,
>
> bryan
>

I had a need to run sql scripts into our database at runtime as well.  I
didn't find anything in the Derby API, so I whipped the following up.  We
use it to create the database structure from a .sql resource, the first time
our application runs (and also within unit tests, since we want a newly
initialized copy of the database for each test).

It's not bullet-proof (read the comments), but it was sufficient for our
purposes.  If something like this doesn't already exist in Derby, it sure
would be useful... with the documented deficiencies remedied, of course.

Jim


  /**
   * Executes the sql commands in the given script against the database
whose
   * data source is given.  The script is executed in a transaction, which
is
   * committed or rolled back based on success or failure.  The script
should
   * follow derby sql syntax.
   * @param dataSource a derby data source
   * @param script a derby sql script
   * @throws IOException if an error occurs while reading from the script
   * @throws SQLException if an error occurs while executing a command
against
   *         the database
   */
  public static void runScript(DataSource dataSource, InputStream script)
    throws IOException, SQLException {
    if (dataSource == null) {
      throw new IllegalArgumentException("dataSource is null");
    }
    if (script == null) {
      throw new IllegalArgumentException("script is null");
    }
    // this is currently pretty basic; it doesn't do anything special for
    // quotes, and treats a single leading dash as a comment (instead of
    // looking for 2)
    Connection conn = null;
    try {
      conn = dataSource.getConnection();
      Statement stmt = conn.createStatement();
      StringBuffer buf = new StringBuffer();
      boolean inComment = false;
      boolean endOfLine = true;
      int in;
      while ((in = script.read()) != -1) {
        char c = (char) in;
        if (inComment) {
          // consume all characters until end of line
          if (c == '\n') {
            inComment = false;
          }
        }
        else if (c == '-' && endOfLine) {
          // found comment on new line
          inComment = true;
        }
        else if (c == ';') {
          // found end of sql expression
          stmt.execute(buf.toString());
          buf.setLength(0);
        }
        else {
          // new character in current sql expression
          buf.append(c);
        }
        endOfLine = (c == '\n');
      }

      // process final sql statement, if any
      String s = buf.toString().trim();
      if (s.length() > 0) {
        stmt.execute(s);
      }
    }
    finally {
      if (conn != null) {
        try {
          conn.close();
        }
        catch(SQLException sqle) {
          LOGGER.warn("failed to close connection", sqle);
        }
      }
    }
  }

[Jim Newsham-2]

P.S.  As you can see, the code is pretty basic... surely there are other
aspects of the Derby SQL syntax (besides comments and quotes) that aren't
even considered.  It would be cool if Derby exposed a similar utility method
which depended directly on Derby's internal parser, so that it would be
guaranteed to parse correctly.
 

> -----Original Message-----
> From: Jim Newsham [mailto:jnewsham@...]
> Sent: Monday, May 14, 2007 5:22 PM
> To: 'Derby Discussion'
> Subject: RE: Executing command from a file via JDBC
>
> I had a need to run sql scripts into our database at runtime as well.  I
> didn't find anything in the Derby API, so I whipped the following up.  We
> use it to create the database structure from a .sql resource, the first
> time
> our application runs (and also within unit tests, since we want a newly
> initialized copy of the database for each test).
>
> It's not bullet-proof (read the comments), but it was sufficient for our
> purposes.  If something like this doesn't already exist in Derby, it sure
> would be useful... with the documented deficiencies remedied, of course.
>
> Jim
>
>
>   /**
>    * Executes the sql commands in the given script against the database
> whose
>    * data source is given.  The script is executed in a transaction, which
> is
>    * committed or rolled back based on success or failure.  The script
> should
>    * follow derby sql syntax.
>    * @param dataSource a derby data source
>    * @param script a derby sql script
>    * @throws IOException if an error occurs while reading from the script
>    * @throws SQLException if an error occurs while executing a command
> against
>    *         the database
>    */
>   public static void runScript(DataSource dataSource, InputStream script)
>     throws IOException, SQLException {
>     if (dataSource == null) {
>       throw new IllegalArgumentException("dataSource is null");
>     }
>     if (script == null) {
>       throw new IllegalArgumentException("script is null");
>     }
>     // this is currently pretty basic; it doesn't do anything special for
>     // quotes, and treats a single leading dash as a comment (instead of
>     // looking for 2)
>     Connection conn = null;
>     try {
>       conn = dataSource.getConnection();
>       Statement stmt = conn.createStatement();
>       StringBuffer buf = new StringBuffer();
>       boolean inComment = false;
>       boolean endOfLine = true;
>       int in;
>       while ((in = script.read()) != -1) {
>         char c = (char) in;
>         if (inComment) {
>           // consume all characters until end of line
>           if (c == '\n') {
>             inComment = false;
>           }
>         }
>         else if (c == '-' && endOfLine) {
>           // found comment on new line
>           inComment = true;
>         }
>         else if (c == ';') {
>           // found end of sql expression
>           stmt.execute(buf.toString());
>           buf.setLength(0);
>         }
>         else {
>           // new character in current sql expression
>           buf.append(c);
>         }
>         endOfLine = (c == '\n');
>       }
>
>       // process final sql statement, if any
>       String s = buf.toString().trim();
>       if (s.length() > 0) {
>         stmt.execute(s);
>       }
>     }
>     finally {
>       if (conn != null) {
>         try {
>           conn.close();
>         }
>         catch(SQLException sqle) {
>           LOGGER.warn("failed to close connection", sqle);
>         }
>       }
>     }
>   }
>
>

[Daniel John Debrunner-2]

Jim Newsham wrote:

>
>> -----Original Message-----
>> From: Bryan Pendleton [mailto:bpendleton@...]
>> Sent: Thursday, May 10, 2007 9:58 AM
>> To: Derby Discussion
>> Subject: Re: Executing command from a file via JDBC
>>
>>> I didn't want to distribute derbytools.jar with my application.  I guess
>>> was looking for something that came with the core jdbc driver.
>> You could put something together yourself pretty easily:
>>
>>    BufferedReader rdr = ...;
>>    String sql;
>>    Statement stmt = conn.createStatement();
>>
>>    while ( (sql = rdr.readLine()) != null)
>>       stmt.executeUpdate(sql);
>>
>> Would that serve your purposes?
>>
>> thanks,
>>
>> bryan
>>
>
>
> I had a need to run sql scripts into our database at runtime as well.  I
> didn't find anything in the Derby API, so I whipped the following up.  We
> use it to create the database structure from a .sql resource, the first time
> our application runs (and also within unit tests, since we want a newly
> initialized copy of the database for each test).
>
> It's not bullet-proof (read the comments), but it was sufficient for our
> purposes.  If something like this doesn't already exist in Derby, it sure
> would be useful... with the documented deficiencies remedied, of course.

How about the runscript() method in ij?

runScript

public static int runScript(java.sql.Connection conn,
                             java.io.InputStream sqlIn,
                             java.lang.String inputEncoding,
                             java.io.OutputStream sqlOut,
                             java.lang.String outputEncoding)
                      throws java.io.UnsupportedEncodingException

     Run a SQL script from an InputStream and write the resulting output
to the provided PrintStream. SQL commands are separated by a semi-colon
';' character.

     Parameters:
         conn - Connection to be used as the script's default connection.
         sqlIn - InputStream for the script.
         inputEncoding - Encoding of the script.
         sqlOut - OutputStream for the script's output
         outputEncoding - Output encoding to use.
     Returns:
         Number of SQLExceptions thrown during the execution, -1 if not
known.
     Throws:
         java.io.UnsupportedEncodingException

Dan.

[Jim Newsham-2]

Yes something like that. ;)  However, I would expect such a method to throw
an exception the first time an error occurs.  I don't see the purpose of
blindly continuing in the face of errors.  

It was some time ago, so I don't recall if I didn't use this method because
it didn't exist at the time, or I didn't know it existed, or perhaps because
it doesn't quit when an error occurs.

Thanks for the pointer,
Jim
 

> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@...]
> Sent: Monday, May 14, 2007 5:42 PM
> To: Derby Discussion
> Subject: Re: Executing command from a file via JDBC
>
> Jim Newsham wrote:
> >
> >> -----Original Message-----
> >> From: Bryan Pendleton [mailto:bpendleton@...]
> >> Sent: Thursday, May 10, 2007 9:58 AM
> >> To: Derby Discussion
> >> Subject: Re: Executing command from a file via JDBC
> >>
> >>> I didn't want to distribute derbytools.jar with my application.  I
> guess
> >>> was looking for something that came with the core jdbc driver.
> >> You could put something together yourself pretty easily:
> >>
> >>    BufferedReader rdr = ...;
> >>    String sql;
> >>    Statement stmt = conn.createStatement();
> >>
> >>    while ( (sql = rdr.readLine()) != null)
> >>       stmt.executeUpdate(sql);
> >>
> >> Would that serve your purposes?
> >>
> >> thanks,
> >>
> >> bryan
> >>
> >
> >
> > I had a need to run sql scripts into our database at runtime as well.  I
> > didn't find anything in the Derby API, so I whipped the following up.
> We
> > use it to create the database structure from a .sql resource, the first
> time
> > our application runs (and also within unit tests, since we want a newly
> > initialized copy of the database for each test).
> >
> > It's not bullet-proof (read the comments), but it was sufficient for our
> > purposes.  If something like this doesn't already exist in Derby, it
> sure
> > would be useful... with the documented deficiencies remedied, of course.
>
>
> How about the runscript() method in ij?
>
> runScript
>
> public static int runScript(java.sql.Connection conn,
>                              java.io.InputStream sqlIn,
>                              java.lang.String inputEncoding,
>                              java.io.OutputStream sqlOut,
>                              java.lang.String outputEncoding)
>                       throws java.io.UnsupportedEncodingException
>
>      Run a SQL script from an InputStream and write the resulting output
> to the provided PrintStream. SQL commands are separated by a semi-colon
> ';' character.
>
>      Parameters:
>          conn - Connection to be used as the script's default connection.
>          sqlIn - InputStream for the script.
>          inputEncoding - Encoding of the script.
>          sqlOut - OutputStream for the script's output
>          outputEncoding - Output encoding to use.
>      Returns:
>          Number of SQLExceptions thrown during the execution, -1 if not
> known.
>      Throws:
>          java.io.UnsupportedEncodingException
>
> Dan.
>

[raykroeker]

Hi Jim,
  Thanks a lot.  It would be very useful for exactly the reasons you outline.

Raymond

On 5/14/07, Jim Newsham <jnewsham@...> wrote:

> -----Original Message-----
> From: Bryan Pendleton [mailto: bpendleton@...]
> Sent: Thursday, May 10, 2007 9:58 AM
> To: Derby Discussion
> Subject: Re: Executing command from a file via JDBC
>
> > I didn't want to distribute derbytools.jar with my application.  I guess
> > was looking for something that came with the core jdbc driver.
>
> You could put something together yourself pretty easily:
>
>    BufferedReader rdr = ...;
>    String sql;
>    Statement stmt = conn.createStatement();
>
>    while ( (sql = rdr.readLine()) != null)
>       stmt.executeUpdate(sql);
>
> Would that serve your purposes?
>
> thanks,
>
> bryan
>

I had a need to run sql scripts into our database at runtime as well.  I
didn't find anything in the Derby API, so I whipped the following up.  We
use it to create the database structure from a .sql resource, the first time
our application runs (and also within unit tests, since we want a newly
initialized copy of the database for each test).

It's not bullet-proof (read the comments), but it was sufficient for our
purposes.  If something like this doesn't already exist in Derby, it sure
would be useful... with the documented deficiencies remedied, of course.

Jim


  /**
   * Executes the sql commands in the given script against the database
whose
   * data source is given.  The script is executed in a transaction, which
is
   * committed or rolled back based on success or failure.  The script
should
   * follow derby sql syntax.
   * @param dataSource a derby data source
   * @param script a derby sql script
   * @throws IOException if an error occurs while reading from the script
   * @throws SQLException if an error occurs while executing a command
against
   *         the database
   */
  public static void runScript(DataSource dataSource, InputStream script)
    throws IOException, SQLException {
    if (dataSource == null) {
      throw new IllegalArgumentException("dataSource is null");
    }
    if (script == null) {
      throw new IllegalArgumentException("script is null");
    }
    // this is currently pretty basic; it doesn't do anything special for
    // quotes, and treats a single leading dash as a comment (instead of
    // looking for 2)
    Connection conn = null;
    try {
      conn = dataSource.getConnection();
      Statement stmt = conn.createStatement();
      StringBuffer buf = new StringBuffer();
      boolean inComment = false;
      boolean endOfLine = true;
      int in;
      while ((in = script.read()) != -1) {
        char c = (char) in;
        if (inComment) {
          // consume all characters until end of line
          if (c == '\n') {
            inComment = false;
          }
        }
        else if (c == '-' && endOfLine) {
          // found comment on new line
          inComment = true;
        }
        else if (c == ';') {
          // found end of sql expression
          stmt.execute(buf.toString());
          buf.setLength(0);
        }
        else {
          // new character in current sql expression
          buf.append (c);
        }
        endOfLine = (c == '\n');
      }

      // process final sql statement, if any
      String s = buf.toString().trim();
      if (s.length() > 0) {
        stmt.execute (s);
      }
    }
    finally {
      if (conn != null) {
        try {
          conn.close();
        }
        catch(SQLException sqle) {
          LOGGER.warn("failed to close connection", sqle);
        }
      }
    }
  }

--
---------------------------------------------------------
Raymond Kroeker

[raykroeker]

Hi Daniel,
  I'm just throwing around ideas at this point, but it would be awesome if the code from runscript could be made into a system stored procedure.  Then it's always there and runscript from ij could re-use the same functionality.

  Just a thought.

Raymond

On 5/14/07, Daniel John Debrunner <djd@...> wrote:

Jim Newsham wrote:

>
>> -----Original Message-----
>> From: Bryan Pendleton [mailto:bpendleton@...]
>> Sent: Thursday, May 10, 2007 9:58 AM
>> To: Derby Discussion
>> Subject: Re: Executing command from a file via JDBC
>>
>>> I didn't want to distribute derbytools.jar with my application.  I guess
>>> was looking for something that came with the core jdbc driver.
>> You could put something together yourself pretty easily:
>>
>>    BufferedReader rdr = ...;
>>    String sql;
>>    Statement stmt = conn.createStatement();
>>

>>    while ( (sql = rdr.readLine()) != null)

>>       stmt.executeUpdate(sql);
>>
>> Would that serve your purposes?
>>
>> thanks,
>>
>> bryan
>>
>
>
> I had a need to run sql scripts into our database at runtime as well.  I
> didn't find anything in the Derby API, so I whipped the following up.  We
> use it to create the database structure from a .sql resource, the first time
> our application runs (and also within unit tests, since we want a newly
> initialized copy of the database for each test).
>
> It's not bullet-proof (read the comments), but it was sufficient for our
> purposes.  If something like this doesn't already exist in Derby, it sure
> would be useful... with the documented deficiencies remedied, of course.

How about the runscript() method in ij?

runScript

public static int runScript(java.sql.Connection conn,
                             java.io.InputStream sqlIn,
                             java.lang.String inputEncoding,
                             java.io.OutputStream sqlOut,
                             java.lang.String outputEncoding)
                      throws java.io.UnsupportedEncodingException

     Run a SQL script from an InputStream and write the resulting output
to the provided PrintStream. SQL commands are separated by a semi-colon
';' character.

     Parameters:
         conn - Connection to be used as the script's default connection.
         sqlIn - InputStream for the script.
         inputEncoding - Encoding of the script.
         sqlOut - OutputStream for the script's output
         outputEncoding - Output encoding to use.
     Returns:
         Number of SQLExceptions thrown during the execution, -1 if not
known.
     Throws:
         java.io.UnsupportedEncodingException

Dan.

--
---------------------------------------------------------
Raymond Kroeker