Chapter 8. Connecting clients to the virtual database

Procedure

1. From the sqlline prompt, type the following command to connect to the Portfolio database that you created earlier.

   !connect jdbc:teiid:portfolio@mm://portfolio:31000;

2. When prompted type a user name and password to log in:

   Enter username for jdbc:teiid:portfolio@mm://portfolio:31000;: ANY_USER_NAME
   Enter password for jdbc:teiid:portfolio@mm://portfolio:31000;: ANY_PASSWORD

   The credentials that you supply at this point are only dummy values. You can type any value.

   The command responds with the following output:

   Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_READ_COMMITTED) will be used instead.
   0: jdbc:teiid:portfolio@mm://portfolio:31000>

   You are now connected to the virtual database service. You can submit SQL queries at prompt.

   For information about some sample queries that you can try, see Sample queries.

3. Type !quit to end the SQLLine session.

   The SQLLine pod is deleted.

   You must re-run the installation command to run SQLLine again.

Procedure

1. Add a load balancer service for the virtual database by setting the value of spec.expose in your virtual database CR to LoadBalancer.

   To provide flexibility in exposing other resources in the future, precede the value with a hyphen (-) to indicate that it is an element in an array, as in the following example:

   apiVersion: teiid.io/v1alpha1
   kind: VirtualDatabase
   metadata:
     name: portfolio
   spec:
     replicas: 1
     expose:
       - LoadBalancer
   ....

2. After you deploy the virtual database, you can run the following command from a terminal window to identify the exposed host and port:

   oc get svc portfolio-external

   The command returns network information for the service, including the cluster IP address, external host name, and port number and type. For example:

   NAME               TYPE          CLUSTER-IP     EXTERNAL-IP                            PORT(S)         AGE
   portfolio-external  LoadBalancer  172.30.22.226  ad42f5d8b303045-487804948.example.com  3306:30357/TCP  15m

1. From a terminal window, type the following command to obtain the name of the portfolio pod:

   oc get pods

   The command returns the list of available pods. For example:

   NAME                              READY   STATUS      RESTARTS    AGE
   accounts-1-deploy                 0/1     Completed   0           21h
   accounts-1-q5z6m                  1/1     Running     0           21h
   portfolio-1-build                 0/1     Completed   0           21h
   portfolio-6bbf99fb8d-hgh9d        1/1     Running     0           21h
   teiid-operator-598874685b-c8m9q   1/1     Running     0           21h
   virtualdatabase-builder-1-build   0/1     Completed   0           21h

2. Using the name of the virtual database pod, type the following command to forward the cluster port to your local computer:

    oc port-forward <dv-pod-name> 31000:31000

   For example:

   oc port-forward portfolio-6bbf99fb8d-hgh9d 31000:31000

   The command returns the following response:

   Forwarding from 127.0.0.1:31000 -> 31000
   Forwarding from [::1]:31000 -> 31000

   You can now access the pod from applications running on your computer. Install and run the SQuirreL SQL client to test it out.

Procedure

1. Download the SQuirreL installation JAR from the following page: http://squirrel-sql.sourceforge.net/#installation.

2. To install SQuirreL, open a terminal window to the directory that contains the downloaded file, and type the following command:

   java -jar squirrel-sql-<version>-install.jar

3. Follow the prompts in the installation wizard to complete the installation process.

Procedure

1. If you enabled external client access by adding a load balancer to the OpenShift cluster, retrieve the connection information for the virtual database by running the following command, otherwise skip to Step 2.

   oc get service portfolio

   The command returns the cluster IP address, and the available ports, for example:

   NAME       TYPE       CLUSTER-IP    EXTERNAL-IP  PORT(S)                                                              AGE
   portfolio  ClusterIP  172.30.25.27  <none>       8080/TCP,8778/TCP,9779/TCP,31000/TCP,35432/TCP,31443/TCP,35443/TCP   8m23s

2. Download the Teiid Java driver from the following page: http://teiid.io/teiid_wildfly/downloads/

   For more information about using a JDBC driver to connect external Java programs to virtual databases on OpenShift, see https://github.com/teiid/teiid-openshift-examples/blob/master/jdbc.adoc.

3. Open SquirreL, and add the Teiid driver.

   1. From the menu, click Drivers > New Driver.
   2. In the Add Driver dialog box, type Teiid in Name field.

   3. In the Example URL field, type:

      jdbc:teiid:<vdb-name>@mms://<host>:<port>

      Note

      Port 31000 is a non-secure port. If you connect to the virtual database on port 31000, type mm instead of mms in the Example URL field.

   4. In the Website URL field, type:

      `http://teiid.org`

   5. Click Extra Class Path and then click Add.
   6. Navigate to the directory where you downloaded the Teiid driver, click the driver JAR file, and then click Open.
   7. Click List Drivers. The Class Name field displays org.teiid.jdbc.TeiidDriver.
   8. Click OK.

4. Add an Alias for the connection.

   1. Click the Aliases tab, and from the menu click Aliases > New Alias.
   2. In the Name field, type portfolio.
   3. In the Driver field, select Teiid

   4. In the URL field, type jdbc:teiid:portfolio.1@mm://IPADDRESS:PORT_NUMBER in place of the example URL.

      For example, if you used port forwarding to provide external access, type the following localhost URL:

      jdbc:teiid:portfolio.1@mm://127.0.0.1:31000

   5. In the User Name field, type any value.

      Note

      The user name, and the password that you provide in the next step, are dummy values. Although you must supply a user name and password to create the alias, the values that you provide are not used in authenticating with the virtual database.

   6. In the Password field, type any value.
   7. To test your connection, click Test, and then click Connect.
   8. Click OK to confirm that the test was successful, and then click OK again to save the alias.

Procedure

1. From the Aliases tab, right-click the alias that you created, and then click Connect to connect to the virtual database.
2. After the connection is established, click the SQL tab.
3. Type a SQL query and and press Ctrl+Enter to see the results.