Thursday, 14 November 2013

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

No comments: