Subject Re: [firebird-support] Re: ISQL & trigger
Author Martijn Tonies

> > Show the complete command you're trying to run.
> >
> > Are you running this as a script, or as a single command?
> >
> Hi Martin !
> Having to create the table trought a JDBC connection, I would test the
> statements trought a SQL Editor, like Mitec IB Query.
> So, theese are the statements that I passed trought the editor, and
> that I expect to send in he future trought the JDBC connection:
> CREATE TABLE my_table (
> my_number integer not null primary key
> ) ;
> CREATE GENERATOR my_generator;
> set term !!;
> CREATE TRIGGER my_before_trigger FOR my_table
> AS
> IF (NEW.my_number IS NULL)
> THEN NEW.my_number = GEN_ID(my_generator, 1);
> set term ; !!

As explained to you before, "set term" is only needed for
the command line tool "isql" if you want to:
1) run commands from a script
2) input a multi-statement command

Other tools will need the SET TERM for scripts as well.

But, when running as a single statement, you should be
able to run the CREATE TRIGGER without the need
for SET TERM... Many Firebird related tools make a
difference between running single SQL commands (where
a colon (":") stands for a parameter) and running scripts,
where ";" is the normal statement terminator, unless changed
by SET TERM. The Script-parser then parses the script
and cuts it up into single statement - it does this by using
the current statement terminator (";") or the one defined

In your JDBC connection, you can run a CREATE TRIGGER
without the need for SET TERM, as long as you don't use
the JDBC parameter character ("?", I believe).

> by the way ... I would like the java application to work with firebird
> and Mysql..
> Is it a nightmare or the problems arise only in the creation statement
> (e.g: Innodb & forward key) and data type creation ( e.g: counter &
> generator) ??

As long as you don't use engine-specific SQL, you should be able
to go a long way.

Obviously, the engine specific statements (like tabletype = InnoDB etc)
will cause you some trouble.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions