How to execute a set of related SQL instructions with JDBC?

Question

I'm trying to execute this script through JDBC (it's SQL Server):

DECLARE @var VARCHAR(10)
SET @var = "test"
INSERT INTO foo (name) VALUES (@var)

It's just an example, in my business case I have a big collection of SQL statements in one script.

I'm trying this:

final Statement stmt = connection.createStatement();
for (String sql : lines) {
  stmt.executeUpdate(sql);
}
stmt.close();

SQL Server is saying on the second line:

java.sql.SQLException: Must declare the scalar variable "@var".

Looks like I'm doing something wrong...

Why don't you create stored procedures and call them instead? — Chandu, May 31 '11 at 19:49
This is a possible workaround, yes. But I'm trying to find a way to do this without the stored procedure. — yegor256, May 31 '11 at 19:50
http://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc — jmj, May 31 '11 at 19:51

score 2 · Accepted Answer · answered May 31 '11 at 19:52

2

You're executing it one line at a time, so of course the second line fails as it depends on the first line. Why not execute it all at once?...

answered May 31 '11 at 19:52

Fosco

1 Answers1