Emulating a BOOLEAN Data Type in Pivotal SQLFire

Not all SQL databases provide a Boolean data type and SQLFire which uses Derby has no BOOLEAN data type. In order to use BOOLEAN you can use a SMALLINT using values as 0 or 1 and then make API calls with the JDBC driver calling ResultSet.getBoolean() to give you TRUE or FALSE as shown below.

1. Create table as shown below with some sample rows.

  
sqlf> run './sql/boolean.sql';
sqlf> drop table boolean_test;
0 rows inserted/updated/deleted
sqlf> create table boolean_test (col1 smallint);
0 rows inserted/updated/deleted
sqlf> insert into boolean_test values (1);
1 row inserted/updated/deleted
sqlf> insert into boolean_test values (1);
1 row inserted/updated/deleted
sqlf> insert into boolean_test values (0);
1 row inserted/updated/deleted
sqlf> insert into boolean_test values (1);
1 row inserted/updated/deleted
sqlf> insert into boolean_test values (0);
1 row inserted/updated/deleted
sqlf> select * from boolean_test;
COL1  
------
0     
1     
0     
1     
1     

5 rows selected

2. Sample JDBC code to query table using ResultSet.getBoolean().

  
public void run() throws SQLException
 {
  Connection conn = null;
  Statement stmt = null;
  ResultSet rset = null;
  
  logger.log (Level.INFO, String.format("Connecting to SQLFire with url %s", url));
  
  try
  {
   conn = DriverManager.getConnection(url);
   logger.log(Level.INFO, conn.toString());
   stmt = conn.createStatement();
   rset = stmt.executeQuery("select * from boolean_test");
   while (rset.next())
   {
    System.out.println("col1 = " + rset.getBoolean(1));
   }
  }
  catch (SQLException se)
  {
   logger.log(Level.SEVERE, se.getMessage());
  }
  finally
  {
   if (conn != null)
   {
    conn.close();
   }
   
   if (stmt != null)
   {
    stmt.close();
   }
   
   if (rset != null)
   {
    rset.close();
   }
  }
  
 }

Output

Nov 14, 2013 12:37:24 PM pas.au.apples.sqlfire.types.BooleanDemo run

INFO: Connecting to SQLFire with url jdbc:sqlfire://127.0.0.1:1527/

Nov 14, 2013 12:37:26 PM pas.au.apples.sqlfire.types.BooleanDemo run

INFO: NetConnection@12401369,agent: NetAgent@2cba5bdb:127.0.0.1[1528]

col1 = false

col1 = true

col1 = false

col1 = true

col1 = true