Roman Rokytskyy escreveu:

>> But the problem is: the default terminator of SQL commands is ";".
>
> Correct.
>
>> And when creating triggers/procedures, it is needed to use ";" for
>> internal commands and other terminator for "CREATE XXX ... END"
>> command.
>
> Correct.
>
>> Then, how can I run a script with a code like this?
>
> Your application has to take into account the terminator specified in the
> script (the SET TERM command), use this terminator to extract the statements
> (complete CREATE ... END blocks that can span multiple lines and contain
> semicolons but without the current terminator, ^ in your case), submit that
> command to the engine, proceed to the next statement.
>
> That is exactly the task that the example on the wiki does.
>
> But now I am confused. I understood that you have extended that example to
> handle comments, buffer end, etc. Right? If yes, what had happened to the
> processCommand method?
>
> Roman
>
>

Thanks Roman!

Now the method to run scripts is running fine... I have put it below for
better analysis of the code. It was made with JDK 1.5 (it uses
StringBuilder), but it is easy to change for StringBuffer...

some comments about it:
- I change \r\n to white space
- look at comments controlling
- look at buffer length checks
- I run all SQL code in a batch

boolean executarSQL(String buf) {
int i;
final String CMDTERMINATOR = "SET TERM ";
final int SIZETERMINATOR = CMDTERMINATOR.length();
final char TERMINATOR = ';';
char terminator = TERMINATOR, newterminator = TERMINATOR;
boolean comment, hassql = false;
StringBuilder sql;
String onesql;
Statement stmt;
try {
comment = false;
sql = new StringBuilder();
stmt = conn.createStatement();
for (i = 0; i < buf.length(); i++) {
if (buf.charAt(i) == '\r' || buf.charAt(i) == '\n') {
// change ENTER (Windows/Linux/...) to white space
sql.append(' ');
continue;
}
if (comment == false) {
if (buf.charAt(i) == terminator) {
onesql = sql.toString().trim();
if (onesql.length() > 0) {
hassql = true;
stmt.addBatch(onesql);
System.out.println(onesql);
}
sql = new StringBuilder();
if (newterminator != terminator)
terminator = newterminator;
}
else if (buf.charAt(i) == '/' && buf.charAt(i+1) == '*') { //
comment start
comment = true;
i++;
}
else {
if (i+SIZETERMINATOR < buf.length() &&
buf.substring(i,i+SIZETERMINATOR).equalsIgnoreCase(CMDTERMINATOR)) {
i += SIZETERMINATOR;
while (i < buf.length()) {
if (buf.charAt(i) == ' ') // jump out white spaces
between "SET TERM" and new terminator
i++;
else
break;
}
newterminator = buf.charAt(i);
}
else {
sql.append(buf.charAt(i));
}
}
}
else { // comment
if (buf.charAt(i) == '*' && buf.charAt(i+1) == '/') { //
comment end
comment = false;
i++;
}
}
}
if (hassql)
stmt.executeBatch();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}

=====================

The SQL script to test the method is here:

Drop Table tabestr;
Drop Index XPKtab;
Drop Index XAKtab;
Drop Trigger Novocodtab;
Drop Procedure Novocodtab2;
Drop Generator codtab_Gen;
Drop Table tab;

Create Table tab(
codtab int not null,
descr varchar(50),
primary key(codtab)
);

CREATE UNIQUE INDEX XPKtab ON tab
(
codtab
);

CREATE INDEX XAKtab ON tab
(
descr
);

Alter Table tab
add descr2 char(1);

Create Table tabestr(
codtabestr int not null,
descr varchar(50),
codtab int,
primary key(codtabestr)
);

/*
ALTER TABLE tabestr
ADD CONSTRAINT R_1000
FOREIGN KEY (codtab)
REFERENCES tab;
*/

Create Generator codtab_Gen;

Set Term ^ ;

Create Trigger Novocodtab For tab
Before Insert As
Begin
if (New.codtab is null) then
begin
New.codtab = Gen_Id(codtab_Gen,1);
end
End ^

Create Procedure Novocodtab2
Returns (PCod integer)
As
Begin
Select Gen_Id(codtab_Gen, 1)
From rdb$database
Into PCod;
End ^

Set Term ; ^

Alter Table tab
add descr3 char(1);