Thread: SQL Script using JDBC

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

Ravi,

AFAIK, there's no easy way to execute an SQL script directly through
JDBC, you will have to write your own parser to split the script in
individual statements and execute them one by one.

Actually the splitting in statements is the biggest challenge here, it's
not as simple as for e.g. using a regular expression. We do this kind of
stuff here (i.e. executing SQL scripts through JDBC, with the added
condition that the same script must execute fine in psql), and first we
used the simple rule of "end of statement = a semicolon at the end of
the line". This works fine until your script doesn't contain stored
procedures, in that case the stored procedure body is to be sent as one
statement, but it surely will contain lines ending in semicolon. So
either you start tracking quoted strings, or you use some other
statement delimiter. You could use an SQL comment as statement
delimiter, something like:

-- execute

This will be ignored by psql, and used by our parser code to execute the
current statement...

We actually still use the "line ended with semicolon" delimiter, and put
some comments at the end of line in stored procedures to not have the
semicolons at line ends... kind of awkward, but it works...

HTH,
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

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");

    }