db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject RE: Executing command from a file via JDBC
Date Tue, 15 May 2007 03:31:50 GMT


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@referentia.com]
> 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);
>         }
>       }
>     }
>   }
> 
> 




Mime
View raw message