testcontainers-java: Invalid parsing of SQL Init script containing procedures

For those, who struggles with this issue and MS-SQL. As @vipulnewaskar7 suggested, I’ve tried to run init script from within container: .execInContainer("/opt/mssql-tools/bin/sqlcmd", "-S localhost -U SA -P 'A_Str0ng_Required_Password' -i /home/initMssql.sql"). This somehow causes TCP Provider: Error code 0x2AF9 error, while running the same command using native docker exec -i ... works fine. So the workaround is to run .sh script via .execInContainer API with sqlcmd inside:

#!/usr/bin/env sh
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -i /home/initMssql.sql

Finally, java-side config for singleton container looks like

    protected static final JdbcDatabaseContainer<?> MSSQL_CONTAINER;
    static {
        String localResourcesPath = "sql/";
        String dbInitScriptFileName = "initMssql.sh";
        String sqlInitScriptFileName = "initMssql.sql";
        MountableFile dbInitScript = MountableFile.forClasspathResource(localResourcesPath + dbInitScriptFileName);
        MountableFile sqlInitScript = MountableFile.forClasspathResource(localResourcesPath + sqlInitScriptFileName);
        String containerResourcesPath = "/home/";
        MSSQL_CONTAINER = new MSSQLServerContainer<>().withCopyFileToContainer(dbInitScript, containerResourcesPath)
                                                      .withCopyFileToContainer(sqlInitScript, containerResourcesPath);
        MSSQL_CONTAINER.start();
        try {
            MSSQL_CONTAINER.execInContainer("chmod", "+x", containerResourcesPath + dbInitScriptFileName);
            MSSQL_CONTAINER.execInContainer(containerResourcesPath + dbInitScriptFileName);
        } catch (IOException | InterruptedException e) {
            e.printStackTrace();
        }
    }

Was this really solved… can someone verify it, I am using 1.11.2 and still getting this issue when trying to create procedures…

A workaround I found was creating my own implementation of PostgreSQLContainer and including a constructor that accepts Future<String>, then I could use builder.from("postgres:9.5-alpine").env("POSTGRES_USER", "app_user").expose(5433) .env("POSTGRES_DB", "test").env("POSTGRES_PASSWORD", "test").copy("init.sql", "/docker-entrypoint-initdb.d/") and include the init script. Hope this helps anyone else who comes by this post.

I think you are right @rnorth. Using escape character would make script unusable elsewhere. I would prefer to use the production-ready, unmodified scripts during testing. So, using escape literal is not a good solution.

I tried the jitpack version as you mentioned and it seemed to work perfectly.

Here is my modified version of test procedure script -

DROP PROCEDURE IF EXISTS calculate_library_value;

CREATE PROCEDURE `calculate_library_value`(out totalValue decimal)
BEGIN

	-- Total Value of Library books
	select sum(price * total_quantity) into totalValue from books;
	
	/*
		Also get all the books.
	*/

	BEGIN 
		select * from books order by title;
	END;

END;

There I intentionally added a nested BEGIN ... END inside procedure body. The new code parsed it correctly as well as it was created successfully 👍. So Thank you very much for that!

Now for my usage, I am not sure when this fix will be released as new TestContainers-Java version. But I am sure, I am not the only one who would love to have this one as soon as possible released 😃.

If you think this will take a while to release, then I am thinking to extract out ScriptUtils of 1.6.0 (like this) in my code, add this code fix in it (current extract code has escape literal fix, but would remove it) and run init script after db container is started (something like this). I hope that would be ok with you and of course I would switch to the new version as soon as it is released and let TC handle that initialization.

Thank you for the help!

BTW if you could perhaps try it out, there’s a jitpack.io build available:

You’ll need the jitpack repository added to your POM:

<repositories>
	<repository>
	    <id>jitpack.io</id>
	    <url>https://jitpack.io</url>
	</repository>
</repositories>

and replace any testcontainers dependencies you have with this (leaving artifactId the same as before, though):

<dependency>
    <groupId>com.github.testcontainers.testcontainers-java</groupId>
    <artifactId>MODULE_NAME_GOES_HERE</artifactId>
    <version>experimental-sql-compound-statement-awareness-SNAPSHOT</version>
</dependency>