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

Raymond

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


> -----Original Message-----
> From: Bryan Pendleton [mailto: bpendleton@amberpoint.com]
> 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