Issue with NULL varchars

Hi,

I'm relatively knew to PostgreSQL, but have a fair amount of
experience with JDBC and Oracle/SQL Server.

I'm running PostgreSQL 8.3.0 on Mac OSX 10.5.2, am using the 8.3-603
JDBC drivers, and using Java 1.5.0_13.

I'm a bit confused by the results of the following test case code:

----

im****t java.sql.*;

public class jdbcLocaleTest
{
  public static void main(String args[])
  {
    try
    {
      Driver driver = (Driver)
Class.forName("org.postgresql.Driver").newInstance();
      DriverManager.registerDriver(driver);
      Connection c =
DriverManager.getConnection("jdbc:postgresql://localhost/testdb",
"testdb", "");

      int count, id = 1;
      PreparedStatement p;
      ResultSet rs;

      String language = "en";
      String country  = "GB";
      String variant  = null;

      // Create a test table:
      p = c.prepareStatement("CREATE TABLE testing (id int, language
varchar(2), country varchar(2), variant varchar(64))");
      p.execute();

      // Create a new Locale in the database:
      p = c.prepareStatement("INSERT INTO testing (id, language,
country, variant) VALUES (?, ?, ?, ?)");
      p.setInt(1, id);
      p.setString(2, language);
      p.setString(3, country);
      p.setNull(4, java.sql.Types.VARCHAR);  // or p.setString(4,
variant);
      count = p.executeUpdate();
      System.out.println("INSERT: inserted " + count + " rows.");

      // Retrieve that Locale by its ID:
      p = c.prepareStatement("SELECT language, country, variant FROM
testing WHERE id = ?");
      p.setInt(1, id);
      rs = p.executeQuery();
      while (rs.next())
      {
        System.out.println("SELECT by id: language=" + rs.getString(1)
+ ", country=" + rs.getString(2) + ", variant=" + rs.getString(3));
      }
      rs.close();

      // Retrieve that Locale's ID by its Data:
      p = c.prepareStatement("SELECT id FROM testing WHERE language =
? AND country = ? AND variant = ?");
      p.setString(1, language);
      p.setString(2, country);
      p.setNull(3, java.sql.Types.VARCHAR);  // or p.setString(3,
variant);
      rs = p.executeQuery();
      while (rs.next())
      {
        System.out.println("SELECT by data: Got id=" + rs.getInt(1));
      }
      rs.close();

      // Retrieve that Locale's ID by its Data:
      p = c.prepareStatement("SELECT COUNT(*) FROM testing WHERE variant =
?");
      p.setNull(1, java.sql.Types.VARCHAR);    // or p.setString(1,
variant);
      rs = p.executeQuery();
      while (rs.next())
      {
        System.out.println("SELECT COUNT: count=" + rs.getInt(1));
      }
      rs.close();

      // Drop the test table:
      p = c.prepareStatement("DROP TABLE testing");
      p.execute();

      p.close();
    }
    catch (Exception e)
    {
      System.out.println("Error: " + e);
    }
  }
}

----

The output, when I run this, is

INSERT: inserted 1 rows.
SELECT by id: language=en, country=GB, variant=null
SELECT COUNT: count=0

which implies that the table is created, the row is inserted, the row
is retrieved when selecting by its Id, but when we try and search for
anything with a NULL varchar value, nothing is returned.

I've also tried swapping the setNull statements with setString(n,
null) - same result.

What am I missing?!

Many thanks,

Ant.

-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc