how do you use a liquibase parameter in an insert statement?

Question

There is a liquibase parameter in Spring boot, let's say:

 spring.liquibase.parameters.val1 = value1

I want to use this parameter in a sql file like this:

insert into table1 (name, value) values ("nameOfValue", ${val1});

unfortunately, the only combination that so far worked was putting 3 single quotes - '''${val1}''' (which gives 'value1') and substring removing the first and last single quote.

Is there a more clean way of using liquibase parameters in an INSERT statement in SQL changeset files?

What about `insert into table1 (name, value) values ("nameOfValue", '${val1}');` *note* the quotes around **'${val1}'** — Dirk Deyne, Jan 31 '23 at 09:54
Please, could you provide the actual use case? What is the parameter you are trying to set? `val1`? — jccampanero, Jan 31 '23 at 22:52

score 3 · Accepted Answer · answered Jan 31 '23 at 11:11

3

It looks like you don't have to do anything special to insert a parameter from the properties no matter the chosen format of the changeset.

All of the following will result in valid insert statements.

SQL changeset

--changeset me:2
insert into test1 (id, name) values (1, 'name 1');
insert into test1 (id, name) values (3, '${val1}');

YAML changeset

- changeSet:
    id: 2
    author: me
    changes:
      - sql:
          endDelimiter: ;
          sql: >-
            insert into test1 (id, name) values (1, 'name 1');
            insert into test1 (id, name) values (3, '${val1}');

XML changeset:

<changeSet id="2" author="me">
    <sql endDelimiter=";">
        insert into test1 (id, name) values (1, 'name 1');
        insert into test1 (id, name) values (3, '${val1}');
    </sql>
</changeSet>

answered Jan 31 '23 at 11:11

dekkard

6,121
1
16
26

Have you tried the SQL variant? it did not work for me. – Greg Jan 31 '23 at 20:06
Yes, I did try all of them. – dekkard Jan 31 '23 at 20:49
How exactly it didn't work, were there any errors in the logs? Try enabling debug by adding `logging.level.liquibase=debug` to your application properties and see if there are any clues. – dekkard Jan 31 '23 at 21:00
if the value of val1 was "actualValue" I got the error: "actualValue is not a column name" – Greg Feb 01 '23 at 13:42
Do you get this error even while using `'${val1}'` (in single quotes)? Can you post the relevant part of your actual changeset? Also, what version of Spring boot are you using? – dekkard Feb 01 '23 at 16:50
The Liquibase version you use might help too. – dekkard Feb 01 '23 at 17:00
it actually works. I have no idea why it did not work before. 3 devs struggled with it for an hour. – Greg Feb 02 '23 at 08:12
@Greg Does it work without `--liquibase formatted SQL` in your `.sql` files? – Dirk Deyne Feb 02 '23 at 15:26
@Dirk without that no inserts would have been executed at all and there would have been no "actualValue is not a column name" error. Above I posted only the relevant excerpts of changelog examples not the full ones. – dekkard Feb 02 '23 at 15:52
@dekkard If you leave out `--liquibase formatted SQL` with `insert into test1 (id, name) values (3, '${val1}');` in your `.sql`-file then _'${val1}'_ is inserted instead of the actual value. – Dirk Deyne Feb 02 '23 at 16:02
Ok, you're correct, although in the logs it won't show the actual inserts and says `0 row(s) affected`. Anyway this is not causing the mentioned error. – dekkard Feb 02 '23 at 16:07

Dirk Deyne · Answer 2 · 2023-02-01T14:10:07.170

Assuming your inserts are done in a xxx.sql file then it is IMPORTANT you tell liquibase your SQL is formatted, you can do that by adding --liquibase formatted sql at the top of your file

example: inserts.sql

--liquibase formatted SQL
--changeset Greg:1
insert into table1 (name, value) values ('nameOfValue', '${val1}');

References:

Example Changelogs: SQL Format and Liquibase Works with Plain Old SQL
Github demo liquibase-jpa-parameters

how do you use a liquibase parameter in an insert statement?

2 Answers2