Using Flyway set variable statement throws error

Viewed 88

In my EBevar SQL is working well:

enter image description here

BUT ....

Spring boot flyway application runs, it throws an error:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V1__obsolete.sql failed -------------------------------------- SQL State : 42601 Error Code : 0 Message : ERROR: syntax error at or near "@" Position: 1 Location : db/migration/pgsql/_env/V1__obsolete.sql (C:\workspace\t-flyway\target\classes\db\migration\pgsql_env\V1__obsolete.sql) Line : 1 Statement : @set TESTINGE = te

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796) ~[spring-beans-5.2.7.RELEASE.jar:5.2.7.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.7.RELEASE.jar:5.2.7.RELEASE] at org.springframe

I also check with different cases with JDBC.

CASE 1: I got error when I execute this code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MysqlCon {
    public static void main(String args[]) throws Exception{  
        Class.forName("org.postgresql.Driver");  
        Connection con=DriverManager.getConnection(  
                "jdbc:postgresql://localhost:5432/test1?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true","postgres","postgres");  
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("@set TESTINGE = te");

    }

}

Error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "@" Position: 1 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273) at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:226) at com.techgeeknext.MysqlCon.main(MysqlCon.java:14)

enter image description here

I also checked another way:

CASE 2:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MysqlCon {
    public static void main(String args[]) throws Exception{  
        Class.forName("org.postgresql.Driver");  
        Connection con=DriverManager.getConnection(  
                "jdbc:postgresql://localhost:5432/test1?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true","postgres","postgres");  
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("SET TESTINGE TO te");

    }

}

Error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter "testinge" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273) at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:226) at com.techgeeknext.MysqlCon.main(MysqlCon.java:14)

enter image description here

Thanks in advance

1 Answers

This is because @set is a DBeaver specific command. It‘s not handled on the server but on your DBeaver client.

see DBeaver handbook (https://dbeaver.com/doc/dbeaver.pdf) section Client Side Commands:

You can use special commands in the SQL scripts. These commands are executed on DBeaver's side, not on the server-side.

If you want to use Variables in Flyway you may want to have a look into Flyway placeholders: https://flywaydb.org/documentation/configuration/placeholder

Related