Build HubDB-Connected Web Apps in Servoy

Servoy is a rapid application development and deployment platform. When paired with the CData JDBC Driver for HubDB, users can build HubDB-connected apps that work with live HubDB data. This article describes how to connect to HubDB from Servoy and build a simple web app to display and search HubDB data.

With built-in optimized data processing, the CData JDBC Driver offers unmatched performance for interacting with live HubDB data. When you issue complex SQL queries to HubDB, the driver pushes supported SQL operations, like filters and aggregations, directly to HubDB and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations). Its built-in dynamic metadata querying lets you work with HubDB data using native data types.

Connect to HubDB in Servoy Developer

To build HubDB-connected apps, you need to first create a data provider in Servoy Developer using the CData JDBC Driver for HubDB.

1. Install the JDBC Driver.
2. Copy the JDBC Driver JAR file. (cdata.jdbc.hubdb.jar) to the /application_server/drivers/ directory in the installation directory for Servoy.
3. Open Servoy Developer.
4. In the Solution Explorer, right-click Database Server (under Resources) and choose "Connect to existing database" -> "empty."
   1. Name the server.
   2. Click to show the advanced server settings.

      • Set the URL, for example: jdbc:hubdb:AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH

        Built-In Connection String Designer

        For assistance in constructing the JDBC URL, use the connection string designer built into the HubDB JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

        java -jar cdata.jdbc.hubdb.jar

        Fill in the connection properties and copy the connection string to the clipboard.

        There are two authentication methods available for connecting to HubDB data source: OAuth Authentication with a public HubSpot application and authentication with a Private application token.

        Using a Custom OAuth App

        AuthScheme must be set to "OAuth" in all OAuth flows. Be sure to review the Help documentation for the required connection properties for you specific authentication needs (desktop applications, web applications, and headless machines).

        Follow the steps below to register an application and obtain the OAuth client credentials:

        1. Log into your HubSpot app developer account.
           • Note that it must be an app developer account. Standard HubSpot accounts cannot create public apps.
        2. On the developer account home page, click the Apps tab.
        3. Click Create app.
        4. On the App info tab, enter and optionally modify values that are displayed to users when they connect. These values include the public application name, application logo, and a description of the application.
        5. On the Auth tab, supply a callback URL in the "Redirect URLs" box.
           • If you're creating a desktop application, set this to a locally accessible URL like http://localhost:33333.
           • If you are creating a Web application, set this to a trusted URL where you want users to be redirected to when they authorize your application.
        6. Click Create App. HubSpot then generates the application, along with its associated credentials.
        7. On the Auth tab, note the Client ID and Client secret. You will use these later to configure the driver.

        8. Under Scopes, select any scopes you need for your application's intended functionality.

           A minimum of the following scopes is required to access tables:

           • hubdb
           • oauth
           • crm.objects.owners.read
        9. Click Save changes.
        10. Install the application into a production portal with access to the features that are required by the integration.
            • Under "Install URL (OAuth)", click Copy full URL to copy the installation URL for your application.
            • Navigate to the copied link in your browser. Select a standard account in which to install the application.
            • Click Connect app. You can close the resulting tab.

        Using a Private App

        To connect using a HubSpot private application token, set the AuthScheme property to "PrivateApp."

        You can generate a private application token by following the steps below:

        1. In your HubDB account, click the settings icon (the gear) in the main navigation bar.
        2. In the left sidebar menu, navigate to Integrations > Private Apps.
        3. Click Create private app.
        4. On the Basic Info tab, configure the details of your application (name, logo, and description).
        5. On the Scopes tab, select Read or Write for each scope you want your private application to be able to access.
        6. A minimum of hubdb and crm.objects.owners.read is required to access tables.
        7. After you are done configuring your application, click Create app in the top right.
        8. Review the info about your application's access token, click Continue creating, and then Show token.
        9. Click Copy to copy the private application token.

        To connect, set PrivateAppToken to the private application token you retrieved.

      • Select the Driver class you just copied, for example, cdata.jdbc.hubdb.HubDBDriver

Build a HubDB-Connected Web App

Once you have configured the connection to HubDB in the Servoy Developer resources, you are ready to build apps with access to live HubDB data.

Create a New Solution

1. In the Server Explorer, right-click "All solutions" and select "Create new solution."
2. Name the solution.
3. Select the checkbox to include the "search" module.
4. Click "Finish."

Create a New Form

Right-click "Forms" and select "Create new form."

1. Name the form.
2. Select a Datasource.
3. Set the type (e.g., Simple) and click "Finish."

Add a Data Grid to the Form

1. Drag a Data Grid component (from Servoy NG-Grids) onto the form.

2. Drag a column component onto the Data Grid and set the "dataprovider" property for each column component to a column from the HubDB "table" (e.g., PartitionKey from the NorthwindProducts table).

   Continue adding columns as desired.

Add Searching to the App

Note that the "svySearch" extension is required to add search functionality (included by default when you create a new solution). If you did not add the extension when you created the solution or you are modifying an existing solution, you can add the search module by right-clicking Modules (in the solution) and selecting "Add Module." Select "svySearch" and click "OK."

1. Drag a Text Field component onto the Form.
2. Right-click the Form and select "Open in Script Editor."
3. Create a new variable (JavaScript) to hold the search value:

   var searchText = '';
   

4. Back on the Form, in the Text Field properties:
   1. Set the "dataprovider" property to the Form variable you just created.
   2. Double-click to add a method for the onAction event.
   3. Click to create the method in "Form," name the method (e.g., onEnter), and click "Create private."
   4. Click "OK & Show."
5. Add the following JavaScript to the JavaScript file to use the Servoy framework to implement searching bound data based on the text in the Text Field:

   var search = scopes.svySearch.createSimpleSearch(foundset).setSearchText(searchText);
   search.setSearchAllColumns();
   search.loadRecords(foundset);
   

Save and Launch the App

Save the form and JavaScript file, then click Run -> Launch NGClient to start the web app.

Download a free, 30-day trial of the CData JDBC Driver for HubDB and start building HubDB-connected apps with Servoy. Reach out to our Support Team if you have any questions.