db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Raymond Kroeker" <raym...@raykroeker.com>
Subject Re: Executing command from a file via JDBC
Date Tue, 15 May 2007 15:41:25 GMT
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

Mime
View raw message