As part of a Jenkins pipeline script, I need to execute some SQL scripts to initialize a database (mariadb). This job runs on a Jenkins slave that I own. I maintain the Jenkins master instance, but not the host it runs on.
My first try was simply having inline code in the script to call "Sql.newInstance(...)" and "sql.execute(...)". This fails because the mariadb jar is not in the classpath.
I then tried setting the CLASSPATH env var for the slave, to add the mariadb client jar to it, then disconnecting and reconnecting the slave. This appeared to have no effect.
I then tried the @GrapeConfig/@Grab approach. I used @GrapeConfig to set "systemClassLoader=true". This fails with "Unable to find suitable classloader". So, I then tried removing the @GrapeConfig, and this fails with "RuntimeException: Provider for class javax.xml.parsers.SAXParserFactory cannot be created".
Next, I guess I'm going to try to directly run "mysql" from a "sh" command and pipe in the contents of my sql scripts. This seems plausible, but I'm not sure if this will work.
I've seen notes talking about various attempts to do this, but I've never heard of someone successfully doing this.
Although it seems logical to implement this in a "java way", I found that it was more straightforward to simply run 'sh("mysql ... < file.sql")'. It avoids all the gnarly classpath problems. It does require that the database client be installed on the slave box, and it doesn't allow for database independence (not really that important). In reality, what I actually did was run mariadb in a container, so the resulting command line was more like "docker exec -i container mysql ... < file.sql" (notice the "-i", not the usual "-it", as that won't work if piping in a file).
Update:
From the elbow from macg33zr, I found it was pretty easy to add tasks to my existing Gradle build script to manipulate the database. I haven't fully implemented what I need to with it, I just verified that it could work with a simple "select" statement.
I already had the JDBC driver jars in a separate Gradle configuration, as I needed to store those artifacts in the container's lib directory.
The following is a good summary of the required pieces: https://discuss.gradle.org/t/jdbc-driver-class-cannot-be-loaded-with-gradle-2-0-but-worked-with-1-12/2277 . The key subtle thing is adding the JDBC driver jars to the classloader.
