Thread

1. Understanding JDBC Behaviour

   Manav Kumar <mkumar@yugabyte.com> — 2025-05-17T12:35:00Z

   Hi Team,
   I'm writing to clarify a syntax to pass the guc options in the startup
   packt of the connection via JDBC.
   
   
   Wrote below small java program:
   
   Properties props = new Properties();
   props.setProperty("options", "-c DateStyle=Postgres,DMY");
   props.setProperty("user", "postgres");
   props.setProperty("password", "postgres");
   connection = DriverManager.getConnection(
   "jdbc:postgresql://localhost:5432/postgres", props);
   
   stmt1 = connection.createStatement();
   ResultSet rs = stmt1.executeQuery("show DateStyle");
   while (rs.next()) {
   System.out.println(rs.getString(1));
   }
   stmt1.execute("reset DateStyle");
   rs = stmt1.executeQuery("show DateStyle");
   while (rs.next()) {
   System.out.println(rs.getString(1));
   }
   
   The output I'm getting is:
   ISO, DMY
   ISO, DMY.
   
   Rather shouldn't the expected output should be
   Postgres, DMY
   Postgres, DMY //*Please correct me - *Even after the reset the default
   value should be Postgres, DMY ?
    I checked the official docs, and found the syntax above.
   Looking forward to seeing your response! Please let me know if I should
   post elsewhere.
   
   Thanks
   Manav
   

2. Re: Understanding JDBC Behaviour

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-05-19T07:15:56Z

   On Sat, 2025-05-17 at 18:05 +0530, Manav Kumar wrote:
   > I'm writing to clarify a syntax to pass the guc options in the startup packt of the connection via JDBC. 
   > 
   > 
   > Wrote below small java program:
   > 
   > Properties props = new Properties();
   >             props.setProperty("options", "-c DateStyle=Postgres,DMY");
   >             props.setProperty("user", "postgres");
   >             props.setProperty("password", "postgres"); 
   >             connection = DriverManager.getConnection(
   >                 "jdbc:postgresql://localhost:5432/postgres", props);
   > 
   >             stmt1 = connection.createStatement();
   >             ResultSet rs = stmt1.executeQuery("show DateStyle");
   >             while (rs.next()) {
   >                 System.out.println(rs.getString(1));
   >             }
   >             stmt1.execute("reset DateStyle");
   >             rs = stmt1.executeQuery("show DateStyle");
   >             while (rs.next()) {
   >                 System.out.println(rs.getString(1));
   >             }
   > 
   > The output I'm getting is:
   > ISO, DMY
   > ISO, DMY.
   > 
   > Rather shouldn't the expected output should be 
   > Postgres, DMY
   > Postgres, DMY //Please correct me - Even after the reset the default value should be Postgres, DMY ?
   >  I checked the official docs, and found the syntax above. 
   > Looking forward to seeing your response! Please let me know if I should post elsewhere.
   
   The correct list would have been pgsql-jdbc, but I'll answer here.
   
   There is no bug involved.  The behavior can be explained as follows:
   
   1. The PostgreSQL JDBC driver forces "DateStyle" to "ISO", because it needs that setting
      to handle dates and timestamps correctly.  If you manage to change the setting, the
      driver will fail with the error message:
   
         The server''s DateStyle parameter was changed to ....
         The JDBC driver requires DateStyle to beegin with ISO for correct operation.
   
   2. RESET will reset the parameter to what it was when the session started.
   
   Yours,
   Laurenz Albe