Create Function - UncategorizedScriptException - ArrayIndexOutOfBoundsException

Question

Description

I work as a new member of the Spring boot project, which uses 2 different properties files for two different configurations related to database settings:

• production mode: postgres SQL DB
• development mode: h2 in DB memory

As I try to minimize differences in the two scenarios, I started writing functions that will deal with the differences associated with handling date and time.

One example is adding hours, as postgres uses intervals, and h2 uses a similar function date_add, the oracle. A.

Unfortunately, I get an exception for function creation statements that work in the console.

Existing Files

Config / Properties

spring.profiles.active=pre-prod

spring.datasource.url=jdbc:postgresql://localhost:5432/db
spring.datasource.username=postgres
spring.datasource.password=root
spring.datasource.driver-class-name=org.postgresql.Driver

spring.datasource.data=classpath:db/migration/postgres/db_functions.sql,classpath:db/migration/postgres/data.sql
spring.jpa.hibernate.ddl-auto=create

db_functions.sql

--Adds a cast to date to the specific statement
--for h2 simply make a wrapper
CREATE OR REPLACE FUNCTION db_pgres_cast_varchar_to_date(d VARCHAR ) RETURNS date AS $$
        BEGIN
                RETURN d::date;
        END;
$$ LANGUAGE plpgsql;

--Common function for h2 and vct to add hours
--References: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days
CREATE OR REPLACE FUNCTION db_add_hours(d timestamp, hours int) RETURNS timestamp AS $$
        BEGIN
                RETURN d +  (hours || ' hours')::interval;
        END;
$$ LANGUAGE plpgsql;

Exception

NFO] org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor Shutting down ExecutorService 'createTaskExecutor'
Exception in thread "main" org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script from resource [class path resource [db/migration/postgres/vct_functions.sql]]; nested exception is java.lang.ArrayIndexOutOfBoundsException
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:509)

Update

, split/parser, 2 , 6 SQL-:

2

, , splitSqlScript, :

public static void splitSqlScript(EncodedResource resource, String script, String separator, String commentPrefix,
            String blockCommentStartDelimiter, String blockCommentEndDelimiter, List<String> statements)
            throws ScriptException

https://www.codatlas.com/github.com/spring-projects/spring-framework/HEAD/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/init/ScriptUtils.java?line=166

3

Spring Spring , :

--Adds a cast to date to the specific statement
--for h2 simply a wrapper
CREATE OR REPLACE FUNCTION vct_pgres_cast_varchar_to_date(VARCHAR ) RETURNS date
    AS 'select $1::date;'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;


--Common function for h2 and vct to add hours
--References: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days

CREATE OR REPLACE FUNCTION vct_add_hours(timestamp, integer) RETURNS timestamp
    AS 'select $1 +  ($2 || '' hours'')::interval'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;

Answer 1

SplitSqlScript spring: https://www.codatlas.com/github.com/spring-projects/spring-framework/HEAD/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/init/ScriptUtils.java?line=166

, create, $$ SQL .

.

CREATE OR REPLACE FUNCTION vct_pgres_cast_varchar_to_date(VARCHAR ) RETURNS date
    AS 'select $1::date;'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;