SQL Connector

Owned by Kimberly Cruz (Unlicensed)
Last updated: Feb 11, 2020
5 min read

My SQL

FIN supports acquiring data from a wide range of sources including MySQL Databases

  • An SQL connection is accomplished using a Java Data Base Connectivity (JDBC) connector

  • Almost all commercially available SQL-type databases support a JDBC driver

Where is Java?

This step is helpful if there are multiple versions of Java on the machine in question

  1. On the command prompt (where FIN is launched from), you can type

fan -version

  • The full command may look like C:\Program Files (x86)\FIN Framework\FIN Framework 5.0.3.2752\bin\fan -version

  1. It is also possible to look at the

Debug section in the Host app

JDBC Connector Setup

The JDBC driver for MySQL is a single file that allows a Java-based application to work with an Oracle (MySQL) back end.

When this document was written, the link to download the latest JDBC connector for MySQL was available at the following link: http://www.mysql.com/products/connector

  • The page will ask the developer to log in or signup, but below those two options, the developer can ask to just start the download

  • If the developer has access to the MySQL installation, the driver is most likely already present. The file might be in a directory that looks like this: C:\Program Files (x86)\MySQL\Connector J 5.1.26

 Once downloaded this file must be copied into the Java\jre7\lib\ext directory. The full path often looks like this: C:\Program Files (x86)\Java\jre7\lib\ext

Using SQL Connectors

  1. Edit the “config.props” file that can be found in the “\etc\sql” directory. The full path will depend on where the instance of FIN was installed.

  • One possibility is this: C:\Program Files (x86)\FIN Framework\etc\sql. Add this line under the JDBC drivers section: java.drivers=com.mysql.jdbc.Driver if no other java.drivers are present

  • Note that multiple java.drivers are fine, but be sure to separate them with commas as indicated in the config.props file itself

  • Note that it should be on one line and only say java.drivers= once. Here is an example: java.drivers=com.microsoft.sqlserver.jdbc.SQLServerDriver,com.mysql.jdbc.Driver,org.sqlite.JDBC. It is necessary to restart SkySpark after making this change.

  1. Once FIN is running, the user will need to go to the Setting App (which can be found by selecting the Application Launcher located on the top left) then select the Extension option

  1. The user will look for the following pod:

SQL

  1. Once found, the user will need to enable the pod by selecting on the SQL > Enable

  • If the pod is enabled, the version number will turn green (red signifies disabled)

  1. After the pod was been enabled, the user will need to go to DB Builder and expand on the

Connector Tree

  • The user will notice that there is an SQL connection available

  1. Select on the

Add button which is located on the bottom of the grid view

  1. Once selected, the user will be displayed with the following form:

AddSqlConn

  1. The “dis” tag is merely a display name, so its exact name is of little significance

  2. The URI is of this format: jdbc:mysql://IP_ADDRESS:PORT/PUT_DATABASE_NAME_HERE

    • Note: that the IP_ADDRESS can be localhost and the :PORT is optional, though the MySQL default is 3306

  3. It is important to also note that a login and password will be required based on how the database was set up

    • Note: When trying to re-edit the URI, it will look like there is a password, even if there really is not. This is intentional.

  1. After the record is added and shown in the grid view, select on the connection and select the

Ping button

  • This will allow the user to see if the connector is ready

SQL Connectors in Folio

  1. The user can check on the connectors and read them in Folio by the following Axon code:

readAll(sqlConn)

  1. Clicking on the “i” on the left of a given connector gives its unique ID, which can be then copied and pasted into a query

  1. Once the SQL Connector is set up, the user can verify operation by making queries against the database.

Advanced Topics

Fantom and MySQL

A Fantom script can be used to test the SQL Connection. In the following example, the code logs in as “username” with “password” as the password. A table called tblUnit is queried

  • Note: How echo is needed to return the actual data. It is important to note that “localhost” can be an IP address or have a port number like the default port for MySQL as seen below: localhost:3306.

using sql class Test { static Void main() { db := SqlConn.open("jdbc:mysql://localhost:3306/db", "username", "password") rows := db.sql("select * from tblUnit").query rows.each |row| { echo("row:") row.cols.each |col| { echo(" " + col.name + " = " + row.get(col)) } } } }

Fantom code can be run by passing the name of the .fan file you want to run to the Fan.exe application in the DOS Prompt/Command Prompt

  • In this case, C:\Program Files (x86)\FIN Framework\bin\fan mySQL.fan would run the file and return results directly from the mySQL Server.