PostgresSQL : sql modification ignored after first error

[daja93]

Hello,

When writing an sql with an error and executing it on PostgreSQL it displays the error.
If I correct the error.

Example :

No sql will ever afterward unless I reopen the connection.

I've tried with 4.7.1 and squirrel-sql-snapshot-20240617_2003

driver is : postgresql-42.7.3.jar

Thanks for your help

[gerdwagner]

Sorry, I can't reproduce your problem with the same JDBC driver version as yours on PostgreSQL version 14.4.

If you know some Java you may try out the following code to reproduce the problem:

public class TestPostgreSQL3
{
   public static void main(String[] args) throws ClassNotFoundException, SQLException
   {
      Class.forName("org.postgresql.Driver");
      Connection con = DriverManager.getConnection("<yourJdbcUrl>", "<yourUser>", "<yourPassword>");

      try
      {
         con.createStatement().execute("SELECT 1 where 1 =1 and");
      }
      catch(SQLException e)
      {
         // Ignore
      }

      con.createStatement().execute("SELECT 1 where 1 =1");
   }
}

If this code reproduces the problem you may contact your database vendor.

[daja93]

Hello @gerdwagner , thank you for the time you took to try it out.

I tried a few things, and it seems to be related to the commit . If i remember correctly Squirrel set autocommit by default but that's something I disable.

If I reproduce the steps and get stuck on the same error despite correcting it, but I rollback, then I can once again issue passing statements.

If I use autocommit it works seamlessly. I'm not really versed in how PostgreSql regarding how transactions work since we're migrating to it. I'm using an other client for the moment and can't reproduce the issue..

Thank you again

[gerdwagner]

Without autocommit I'm able to reproduce your problem. It can also be reproduced with the appropriately modified Java code:

public class TestPostgreSQL3
{
   public static void main(String[] args) throws ClassNotFoundException, SQLException
   {
      Class.forName("org.postgresql.Driver");
      Connection con = DriverManager.getConnection("<yourJdbcUrl>", "<yourUser>", "<yourPassword>");

      con.setAutoCommit(false);
      
      try
      {
         con.createStatement().execute("SELECT 1 where 1 =1 and");
      }
      catch(SQLException e)
      {
         // Ignore
      }

      con.createStatement().execute("SELECT 1 where 1 =1");
   }
}

With this reproduction code you may contact your JDBC driver and/or database vendor.

By the way, using the executeQuery instead of the execute method does not affect the problem.