Re: SQL Script using JDBC - Mailing list pgsql-jdbc

Ravi,

I have included our parsing code at the end. Note that we have an
additional requirement that the parser must work with scripts written
for oracle and for postgres command line clients. And most of our
scripts are generated from XML, and they have to work either only with
pstgres or oracle CL clients, or both, and they always have to work
through this parser... so we have basically 3 types of scripts, for
oracle only, for postgres only, and for both. Each of them has different
statement end rules... see the comments in the code.

Cheers,
Csaba.

On Mon, 2006-02-06 at 10:41, Ravi Chaddha wrote:
> Please let me know how can we execute a SQL file using
> JDBC from a remote computer. The script creates
> tables, functions in PostgreSQL database.
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org


    /**
     * Executes the SQL statements found in "file".<br>
     * This method breaks the provided SQL file in executable statements
     * on:<ul>
     * <li>semicolon on the end of the line, like in:<br>
     *     <code>SELECT * FROM table<br>...<br>ORDER BY
id<b>;</b></code><br>
     *     The semicolon will be skipped (causes error in JDBC), but
it's
     *     needed by the command line clients;</li>
     * <li>a slash alone on a line, like in:<br>
     *     <code>CREATE TRIGGER ... <br>...<br>BEGIN<br>...<br>END;
     *     /* hack to hide the semicolon */
<br><b>/</b></code><br>
     *     Note the SQL comment needed to hide the semicolon at the end
of the
     *     line (which is part of the SQL statement) from being
interpreted as
     *     statement separator.
     * </ul>
     */
    protected void setupDB(Connection connection, File file,
                                PrintWriter log)
        throws IOException, SQLException
    {
        Assert.pre(file.length() < Integer.MAX_VALUE, "file too long");

        log.println("Reading from file " + file.getAbsolutePath());
        System.out.println("Reading from file " +
file.getAbsolutePath());

        BufferedReader reader = new BufferedReader(new
FileReader(file));
        StringBuffer sqlBuf = new StringBuffer();
        String line;
        boolean statementReady = false;
        int count = 0;
        while ((line = reader.readLine()) != null) {
            // different continuation for oracle and postgres
            line = line.trim();
            if (line.equals("--/exe/--"))   //execute finished statement
for postgres
            {
                sqlBuf.append(' ');
                statementReady = true;
            }
            else if (line.equals("/")) //execute finished statement for
oracle
            {
                sqlBuf.append(' ');
                statementReady = true;
            }
            else if (line.startsWith("--") || line.length() == 0)  //
comment or empty
            {
                continue;
            }
            else if (line.endsWith(";"))
            {
                sqlBuf.append(' ');
                sqlBuf.append(line.substring(0, line.length() - 1));
                statementReady = true;
            }
            else
            {
                sqlBuf.append(' ');
                sqlBuf.append(line);
                statementReady = false;
            }
            /*
            if (line.startsWith("--")) continue; // comment ?
            line = line.trim();
            if (line.length() == 0) continue;
            sqlBuf.append(' ');
            statementReady = false;
            if (line.endsWith(";")) {
                sqlBuf.append(line.substring(0, line.length() - 1));
                statementReady = true;
            } else if (line.equals("/")) {
                statementReady = true;
            } else {
                sqlBuf.append(line);
            }
            */

            if (statementReady) {
                if (sqlBuf.length() == 0) continue;
                executeQuery(connection, sqlBuf.toString());
                count ++;
                sqlBuf.setLength(0);
            }
        }

        log.println("" + count + " statements processed");
        log.println("Import done sucessfully");
        System.out.println("" + count + " statements processed");
        System.out.println("Import done sucessfully");

    }