Re: add column to table if not exists

Ok, thanks for info.

On Sat, May 4, 2019, 10:46 Fred Toussi via Hsqldb-user <
[email protected]> wrote:

> Although we can add IF NOT EXISTS to most database object creation
> statements, this is not possible for ALTER statements.
>
> It is also not possible to use DDL statements inside a PROCEDURE.
>
> We may add one of these capabilities to the next version, 2.5.0, in the
> coming days.
>
> Fred Toussi
>
> On Sat, May 4, 2019, at 06:50, [email protected] wrote:
>
> Trying to add column column1 to table1 if it doesn't exist yet:
>
> create table if not exists table1( column2 varchar(20));
>
> CREATE PROCEDURE column_present()
>         MODIFIES SQL DATA
>     BEGIN ATOMIC
>         DECLARE column_count integer;
>         set column_count = select COUNT(*) from
> information_schema.system_columns Where table_name = 'table1' and
> column_name = 'column1';
>         if column_count = 0 then alter table table1 ADD column1 integer;
> end if;
> END;
>
> call column_present();
>
> results in:
>
> [2019-05-03 22:28:13] [42581][-5581] unexpected token: ALTER : line: 6
> [2019-05-03 22:28:13] java.lang.RuntimeException:
> org.hsqldb.HsqlException: unexpected token: ALTER : line: 6
> [2019-05-03 22:28:13]   at org.hsqldb.error.Error.parseError(Unknown
> Source)
>
> what is the proper way to create the column in existing table (if it
> doesn't exist yet) in the HSQLDB?
>
> Please note: ignoring the error on creation if it already exists, is not
> an option for me.
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user