作为一个dblink事务执行多个查询 [英] Execute multiple queries as one dblink transaction
问题描述
我正在一个Java应用程序中工作，我需要同时执行这两个查询（作为Java中的Strings），并在出现错误的情况下回滚事务。
SELECT dblink_exec（'hostaddr = xxx.xx.xxx.xxx port = 5432 dbname = bdname user = myuser password = mypass connect_timeout = 2'，
'INSERT INTO table3 （field4）
值（5）'）作为结果；
SELECT dblink_exec（'hostaddr = xxx.xx.xxx.xxx port = 5432 dbname = bdname user = myuser password = mypass connect_timeout = 2'，
'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436'）AS结果；
更新
我创建了一个字符串，其中两个查询用; 分隔，如注释建议
UPDATE
我已经尝试使用JDBC原子事务作为Java中的代码。我强迫第二个sql失败，但是即使我指定.setAutoCommit（false）; dblink通过第一个查询影响了另一个数据库。我在没有dblink事务的情况下尝试了相同的代码，并且回滚效果很好。 dblink是问题。
Java UPDATE
public static boolean ejecutarTransaccionDblink（String sql）{
boolean estado = false;
try {
statement sentencia = conexion.createStatement（）;
conexion.setAutoCommit（false）;
if（sql.length（）！= 0）{
if（sentencia.execute（sql））{
conexion.commit（）;
estado = true;
}
}
} catch（SQLException ex）{
System.out.println（ex.toString（））;
try {
estado = false;
conexion.rollback（）;
} catch（SQLException ex1）{
}
}最后{
try {
conexion.setAutoCommit（true）;
estado返还；
} catch（SQLException ex）{
return estado;
}
}
}
感谢您的帮助。
要在事务中运行查询，只需设置 auto-commit 功能在连接上为false（记住在完成后将其设置回true，特别是如果从连接池中检索连接-从而可以重用）。
代码相对简单：
ResultSet resultado = null;
字符串statement1 = SELECT dblink_exec（'hostaddr = xxx.xx.xxx.xxx port = 5432 dbname = bdname user = myuser password = mypass connect_timeout = 2'，'INSERT INTO table3（field4）VALUES（5）' ） 结果;
字符串statement2 = SELECT dblink_exec（'hostaddr = xxx.xx.xxx.xxx port = 5432 dbname = bdname user = myuser password = mypass connect_timeout = 2'，'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436'）AS结果;
try {
//将自动提交设置为false，以指示交易开始
conexion.setAutoCommit（false）;
//在事务中运行您想在连接上进行的任何查询，例如：
语句sentencia = conexion.createStatement（）;
resultado = sentencia.executeQuery（sql）;
//完成后手动提交交易
conexion.commit（）;
返回结果；
} catch（SQLException ex）{
System.out.println（ Error Consulta： + ex）;
//确保在发生错误的情况下回滚事务。 （注意：您可能要在此处添加NPE检查
con.rollback（）;
返回null;
}最后{
//关闭任何语句/ prepareStatements等。注意，您必须在finally块中执行此操作，以确保您的连接不会停留在事务中。
con.setAutoCommit（true）;
}
希望有帮助
更新
正如@a_horse_with_no_name指出的那样，dblink_exec连接到远程数据库，因此以上操作并不完整，因为它仅处理第一个数据库中的事务。
我相信答案应该在于使用命名连接和 dblink_exec ，其中该过程涉及：
- 使用
dblink_connect打开新连接 - 使用
dblink_exec - 使用上一个
dblink_exec执行查询1大大打开的连接 - 在先前打开的连接中使用
dblink_exec执行查询2 - 在以下位置提交事务以前打开的连接
因此，代码如下所示：
SELECT dblink_connect（'myconn'，'hostaddr = xxx.xx.xxx.xxx port = 5432 dbname = bdname user = myuser password = mypass connect_timeout = 2'）;
SELECT dblink_exec（'myconn'，'BEGIN'）;
SELECT dblink_exec（'myconn'，'INSERT INTO table3（field4）VALUES（5）'）;
SELECT dblink_exec（'myconn'，'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436'）;
SELECT dblink_exec（'myconn'，'COMMIT'）;
问题是，这一切都未经测试，所以@KazMiller您可以尝试一下吗？ / p>
I am working in a Java application where I need to execute these the two queries (as Strings in java) at the same time and rollback the transaction if there where errors.
SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',
'INSERT INTO table3(field4)
VALUES (5)') AS result;
SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',
'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436') AS result;
UPDATE
I created one String with the two queries separated with ; as in the comments suggest
UPDATE
I have tried JDBC atomic transactions as the code in java. I force the second sql fail but even if I am specifying .setAutoCommit(false); the dblink affected the other database with the first query. I tried the same code with NO dblink transactions and the rollback works well. dblink is the problem.
Java UPDATE
public static boolean ejecutarTransaccionDblink(String sql) {
boolean estado = false;
try {
Statement sentencia = conexion.createStatement();
conexion.setAutoCommit(false);
if (sql.length() != 0) {
if (sentencia.execute(sql)) {
conexion.commit();
estado = true;
}
}
} catch (SQLException ex) {
System.out.println(ex.toString());
try {
estado = false;
conexion.rollback();
} catch (SQLException ex1) {
}
} finally {
try {
conexion.setAutoCommit(true);
return estado;
} catch (SQLException ex) {
return estado;
}
}
}
Thanks for your help.
In order to run the queries in a transaction, you simply need to set the auto-commit feature to false on the connection (remembering to set it back to true when you're done, especially if the connection is retrieved from a connection pool - and therefore reused).
The code is relatively simple:
ResultSet resultado = null;
String statement1 = "SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2','INSERT INTO table3(field4) VALUES (5)') AS result";
String statement2 = "SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2','UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436') AS result";
try {
// set auto-commit to false, to indicate start of transaction
conexion.setAutoCommit(false);
// run whatever queries you want on the connection, in a transaction, e.g. :
Statement sentencia = conexion.createStatement();
resultado = sentencia.executeQuery(sql);
//manually commit the transaction when you're done
conexion.commit();
return resultado;
} catch (SQLException ex) {
System.out.println("Error Consulta:" + ex);
// ensure transaction is rolled-back in case of error. (note: you might want to add an NPE check here
con.rollback();
return null;
} finally {
// close any statements / preparedStatements, etc. Note you MUST do this in the finally block, to ensure your connection won't stay in transaction.
con.setAutoCommit(true);
}
Hope that helps
UPDATE
As @a_horse_with_no_name pointed out, dblink_exec connects to a remote db, so the above is not complete, as it only handles transactions in the first db.
I believe the answer should lie with using named connections with dblink_exec where the process involves:
- opening a new connection with
dblink_connect - starting a transaction in new named connection with
dblink_exec - executing query 1 with
dblink_execin previously opened connection - executing query 2 with
dblink_execin previously opened connection - committing transaction in previously opened connection
Therefore, the code would look like this:
SELECT dblink_connect('myconn','hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2');
SELECT dblink_exec('myconn','BEGIN');
SELECT dblink_exec('myconn', 'INSERT INTO table3(field4) VALUES (5)');
SELECT dblink_exec('myconn', 'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436');
SELECT dblink_exec('myconn','COMMIT');
The thing is, this is all untested, so @KazMiller could you please give this a try?
这篇关于作为一个dblink事务执行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
