CODEWITHSUNDEEP
postgresqljdbcgroovy
lospejos
lospejos

Reputation: 1986

How to get server messages (raise notice) from PostgreSQL function with Groovy Sql

I have stored function in PostgreSQL, in which I have PL/PGSQL statement like this:

raise notice 'Message text';

I have also Groovy application, which uses default Sql class to call this function. I want to get messages (raise notice) from function are displayed in stdout or logged in my Groovy application.

I created PoC project to test this: https://github.com/lospejos/groovy-jdbc-get-server-messages

Please find comment in Groovy file: https://github.com/lospejos/groovy-jdbc-get-server-messages/blob/master/call_db_function.groovy

I also found this: https://stackoverflow.com/a/23087861/1828296 But I can't get how to get Statement object from Sql instance.

Upvotes: 0

Views: 1688

Answers (1)

lospejos
lospejos

Reputation: 1986

For the benefit of the others. To get server messages from stored function, call SQL like this:

def sql = Sql.newInstance('jdbc:postgresql://localhost/postgres', 'postgres', 'postgres')

final String paramValue = "Param value"

sql.query("select * from testme(param => :paramValue)", [paramValue: paramValue]) { resultSet ->
    def rsRows = [:]
    while (resultSet.next()) {
        rsRows << resultSet.toRowResult()
    }
    def warning = resultSet.getStatement().getWarnings()
    while (warning) {
        println "[${LocalDateTime.now()}] [${warning.getSQLState()}] ${warning.message}"
        warning = warning.nextWarning
    }
    println rsRows
}

I also updated repository code.

Upvotes: 2

Related Questions