Pivotal GemFireXD adding an INITCAP function to the distributed system

We recently released Pivotal GemFireXD BETA with the latest release of Pivotal HD. Pivotal GemFire XD is a memory-optimized, distributed data store that is designed for applications that have demanding scalability and availability requirements. With GemFire XD you can manage data entirely using in-memory tables, or you can persist very large tables to local disk store files or to a Hadoop Distributed File System (HDFS) for big data deployments.

In this post we show how to add the popular INITCAP function to GemFireXD or SQLFire.

1. Create a class with a public static method as shown below , this class method will do the INITCAP for us in SQL terms.

  
package pivotal.au.accelarator.gemfirexd.sql.functions;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.text.WordUtils;

public class GemFireXdSqlFunctions
{
    public static String initcap (String input)
    {
        return WordUtils.capitalizeFully(input);
    }
} 

2. Add the class to the classpath of the distributed system.

3. Create a stored function called INITCAP to use the method defined at #1

  
CREATE FUNCTION INITCAP (data VARCHAR(32672)) RETURNS VARCHAR(32672)
LANGUAGE JAVA
EXTERNAL NAME 'pivotal.au.accelarator.gemfirexd.sql.functions.GemFireXdSqlFunctions.initcap'
PARAMETER STYLE JAVA; 

4. Test as shown below.

  
sqlf> connect client 'localhost:1527';
sqlf> select initcap('pas APICELLA') from sysibm.sysdummy1;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
Pas Apicella                                                                                                                    

1 row selected  

For more information on Pivotal GemFireXD see the link below.

http://gopivotal.com/products/pivotal-hd

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