Automation for sql query execution

dheerajb84

Hello,

I would like to know which redgate tool to be used for to run custom database query from Jenkins and we are using Oracle database and queries manually executing from SQL developer. We would like automation it with flyway. Please suggest us which product to be used ?

Thank you.

Peter_Laws

Hello dheerajb84,

Yes Flyway CLI could certainly do that and then spool the output potentially return the response to the Jenkins runner depending on what you needed.

If you didn't need any SQL*plus functionality the free open source version might be sufficient for your needs.
https://documentation.red-gate.com/flyway/getting-started-with-flyway/installers

dheerajb84

Thank you for your reply. I have 50  sql queries in bitbuket repo. If any change in the sql, I need to get the diff output and that sql queries should be executed using SQL plus. How this to be achieved using Flyway and Jenkins? Please kindly help me to understand in detailed. Is this can also be achieved with out SQL plus ?  Do we need groovy script with flyway plugins  just in jenkins pipeline?

Peter_Laws

Oh I see, that's a trickier proposition, flyway won't extract the diff, it is able to detect the files have changed but isn't designed to calculate a delta from there.

However, if you're able to get the required delta then flyway can happily run the script.
SQL*Plus isn't required as mentioned, I just offered it as an extension option.

As for how it works in Jenkins, much the same as any of the supported platforms, you populate the config file to point to where your scripts are and also define database environments and then call whichever verbs you need to perform the desired action, in this case migrate.