Subject Re: [Firebird-Java] Isn't possible to create many triggers/procedures by a executeBatch?
Author Edilmar
Roman Rokytskyy escreveu:
>> I made a gerenal code to run scripts, I think it is more complete than
>> code in this Jaybird page
>> <\
>> rt&s=set+term> because it accepts comments (/* ... */), changing
>> terminator character from default ";", checks size of buffer read when
>> comparing if the string starts with "SET TERM" and other things I didn't
>> find in the original link.
>> After solve my problem, I'd like to send my code to contrib with Jaybird
>> "Tips and Tricks".
> That will be great!
>> ...
>> The code commented for "foreign key" I think it doesn't have solution in
>> my case, because I don't know how to disconnected all other users of the
>> database to run it.
> First, you can always use FBMaintenanceManager to shutdown the database.
> There are few modes for it: deny new transactions, deny new connections,
> force shutdown after some timeout and shutdown immediate. When database is
> in shutdown mode, only SYSDBA can connect to it. This is exactly what you
> need.
> And starting from Firebird 2.0 (RC1 comming soon) you will no longer need to
> have exclusive mode to add foreign keys, so that won't be a problem soon.
>> The main problem occurs with "SET TERM" command.
>> I need it to allow creation of triggers/procedures/etc.
>> But, when I uncomment the code between the two SET TERM's Jaybird
>> 2.0.1/Firebird 1.5 arises this exception:
>> <pre>
>> java.sql.BatchUpdateException: Dynamic SQL Error
>> SQL error code = -104
>> Token unknown - line 1, char 5
>> at
>> org.firebirdsql.jdbc.AbstractStatement.executeBatch(AbstractStatement.ja\
>> va:982)
>> at SubMacroUtils.Versao.executarSQL(
>> at SubMacroUtils.Versao.atualizar(
>> </pre>
>> I think Jaybird doesn't understand "SET TERM" like a normal SQL command
>> to run into database.
> I do not know your Java code, but the "SET TERM" as well as "SET NAMES" and
> few others are only available in isql. Jaybird does not handle isql
> commands, it is not its job to do this, it only sends what client asks to
> server and returns data/errors back.
> To fix this problem, you have to filter the "SET TERM" commands and do not
> pass them to the server. As you can see from that example on our Wiki, the
> processCommand method checks whether it is "SET TERM" command and does not
> execute it, only changes the terminator string.
>> Isn't possible to create a script like this to run by Jaybird?
> Sure. Only ensure that you correctly handle the isql commands. Jaybird is
> capable to execute only DSQL statements. The rest must be interpreted by the
> application, not executed via Jaybird.
> Roman

But the problem is: the default terminator of SQL commands is ";".
And when creating triggers/procedures, it is needed to use ";" for
internal commands and other terminator for "CREATE XXX ... END"
Then, how can I run a script with a code like this?

Set Term ^ ;

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

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

Set Term ; ^