Pivotal Knowledge Base

Follow

Using SQuirreL SQL with GemFire XD

SQuirreL SQL is a graphical Java client program that you can use to view the structure of a JDBC-compliant database, browse data in tables, issue SQL commands, and so forth. The GemFire XD JDBC thin client driver works with SQuirreL SQL and similar JDBC tools to offer simple administrative functions in a graphical tool. SQuirreL SQL provides an editor with code completion and syntax highlighting for standard SQL statements.

These are some of the key capabilities of SQuirreL SQL:

  • Browsing the objects in a database using the session window.
  • Executing, editing, and running ad-hoc SQL scripts and commands.
  • SQL statement editing with code completion.
  • Generating DDL.

The SQuirreL SQL home page provides more details about the client.

Prerequisites

To access GemFire XD using SQuirreL SQL you require:

Install and start SQuirreL SQL using the product installation instructions. Then use the following sections to configure SQuirreL with the GemFire XD thin client driver.

Create a thin client GemFire XD driver configuration

Follow these steps to create an entry for the JDBC thin client GemFire XD driver in SQuirreL SQL:

  1. Start SQuirreL SQL if it is not already running.
  2. Select the Drivers tab in the main window.
  3. Select Drivers > New Driver... to display the Add Driver window.
  4. Fill in the following fields of the Add Driver window:
    • Name: Enter "GemFire XD thin client driver" or another descriptive name.
    • Example URL: Enter jdbc:gemfirexd://hostname:port/
  5. Click the Extra Class Path tab.
  6. Click Add again and choose the gemfirexd-client.jar file from the lib directory of your GemFire XD installation.
  7. Select the gemfirexd-client.jar entry and click List drivers. This populates the Class Name drop-down list.
  8. In the Class Name list, verify that com.pivotal.gemfirexd.jdbc.ClientDriver is selected. The Add Driver window should resemble this screenshot:
  9. Click OK.

Create an alias for a GemFire XD thin client connection

A SQuirreL SQL alias contains the connection details that are required to log into a particular database. Follow these steps to create an alias to connect using the GemFire XD thin client driver:

  1. Select the Aliases tab in the main SQuirreL SQL window.
  2. Select Aliases > New Alias... to display the Add Alias window.
  3. Fill in the following fields of the Add Alias window:
    • Name: Enter "GemFire XD client connection" or another descriptive name.
    • Driver: Select the GemFire XD thin client driver that you created in the previous procedure. For example, select "GemFire XD thin client driver."
    • URL: The URL field is automatically populated with the jdbc:gemfirexd://hostname:port/ URL that you specified in the driver configuration. Replace the host name and port number with the address and port of the running GemFire XD member to which you want to connect.
    • User Name, Password: Enter a username and password value for the connection. If the GemFire XD server or locator enables authentication (using the -auth-provider boot property), then enter a valid username and password combination to connect to the distributed system. If authentication is disabled, then you can specify any temporary username and password value into these fields.

      Note: GemFire XD uses the username value as the schema name when you do not provide the schema name for a database object. GemFire XD uses "APP" as the default schema. If your system does not enable authentication, you can specify "APP" in the User Name field to maintain consistency with the default schema behavior.
  4. Click Test.
  5. Click Connect to test the new alias. SQuirreL SQL indicates whether the connection was successful.
  6. Click OK to close the connection test result.
  7. Click OK to create the new alias.

Start a session to execute SQL commands

Follow this procedure to use the new thin client alias and execute SQL commands in GemFire XD:

  1. Select the Aliases tab in the main SQuirreL SQL window.
  2. Double-click the alias that you created in the previous section. For example, double-click "GemFire XD thin client driver." This brings up the Connect to: window.
  3. Click the Connect button to connect to the database and launch the session window. The session window displays the alias name and the username you provided for the connection. Note: GemFire XD leverages Apache Derby, and the product name is displayed as "Apache Derby" to maintain interoperability with other DRDA drivers.
  4. Use the Objects tab to navigate through the database schema. For example, clicking Data Types displays:

  5. Click the SQL tab to execute SQL statements. Click the run button or press Control-Enter to execute the statement. For example:

The SQuirreL SQL help provides more information about using the client.

Comments

  • Avatar
    DANIEL BARALE

    I am using GFXD on a AWS instance that use SSH keys for authentication. You connect to AWS using either a public IP or a Public DNS string. For example, here's the current Public DNS connection string for gfxd-demo:

    jdbc:gemfirexd://ec2-54-22-33-101.us-west-2.compute.amazonaws.com:1527

    Somehow works out that port 1528 & 1529 are members (all possible servers are called):

    jdbc:gemfirexd://ec2-54-184-202-187.us-west-2.compute.amazonaws.com:1527/

    I get an error thou that generates this stack:

    java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLNonTransientConnectionException: A communications error has been detected: Failed after trying all available servers: [gfxd1.zdatainc.local[1529], gfxd1.zdatainc.local[1528], gfxd1.zdatainc.local[1527], ec2-54-184-202-187.us-west-2.compute.amazonaws.com[1527]], for control host[port]: ec2-54-184-202-187.us-west-2.compute.amazonaws.com[1527].
    at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:262)
    at java.util.concurrent.FutureTask.get(FutureTask.java:119)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
    ... etc etc

    The SQuirrel SQL client seems to be attempting to connect to all members of the gfxd cluster but is failing because of DNS name resolution errors. This is due to the fact that the gfxd services only have knowledge of the internal domain (zdatainc.local) and are not mapped to the external DNS hostnames. This is the expected behavior because of the transient nature of the training image. There may be a way to set it up through the client so that it connects to just the locator or a config file where you can manually enter the external addresses:ports of the services.

    Do you know it?

    Daniel Barale
    ZDATA INC

  • Avatar
    DANIEL BARALE

    Fixed the issue. You may want to try editing the local hosts file on your SQuirrel SQL client machine to include the following line:

    54.184.202.187 ec2-54-184-202-187.us-west-2.compute.amazonaws.com gfxd1.zdatainc.local

    This may be good enough workaround to enable connectivity to all the cluster members from the client tool. Inbound ports 1528 & 1529 are already open in the security group.

Powered by Zendesk