Hi Jim, Thanks a lot. It would be very useful for exactly the reasons you outline.
Raymond On 5/14/07, Jim Newsham <[EMAIL PROTECTED]> wrote:
> -----Original Message----- > From: Bryan Pendleton [mailto:[EMAIL PROTECTED] > 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
