2

I'm not able to get named/unamed dblink connections to persist in my springboot "java" (actually kotlin) application. I am writing my code in kotlin. The code looks like this.

var result = jdbcTemplate.queryForObject<String>("SELECT dblink_connect('myTestConnectionName'," + myCloudServerConnectionString + ");")

println(result)  // prints "OK" meaning that it ran okay I believe.

var result2 = jdbcTemplate.queryForObject<String>("SELECT dblink_get_connections();")

println(result2) // prints "null" meaning there are no dblink connections.

I run the SQL manually in both PSQL and DBeaver and it works fine. I will see the named connection when I run "dblink_get_connections" sql. It only doesn't work in my springboot application.

Does anyone know what I can do to fix it?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Philip Nguyen
  • 871
  • 2
  • 10
  • 29

1 Answers1

1

Calling dblink_connect() requires a transaction.
Your code opens and immediately closes it. dblink_get_connections is executed in another transaction.
You can use @Transactional on your method to change this behavior.

Alexey Soshin
  • 16,718
  • 2
  • 31
  • 40
  • Well I actually ended up not using the JDBCTemplate, and I used createStatement().executeQuery("my sql query") on the connection variable instead. It worked! – Philip Nguyen Aug 10 '18 at 14:47