JDBC : How to link Fast2 and SQL DB

blog-image

To extract from or inject in a SQL DataBase, Fast2 use JDBC as a connector with SQL.

Requirements

To use JDBC you must download the library related to your SQL DataBase.

JDBC and SQLServer 2019

To begin, download the JDBC Driver for SQLServer

⚠️ You must choose the right version for your JDK !

To check it you have two ways:

  • From a command line:
> echo %JAVA_HOME%
  • Or, check it in your Fast2: >Fast2>config>env.properties

After that you can add:

  • The .jar file in your Fast2: >Fast2/Worker.libs
  • The .dll in you java folders >java/java-version/jre/bin

Thanks to the SQL Server Configuration Manager, start the TCP port.

πŸ‘ Congrats, the JDBC requierements are done !

Example: Injection on a SQL DataBase

For the educational aspect of this topic, let us take this map as an example

Map Fast2

and consider this table to inject in:

CREATE TABLE table_test_connection
(
    column_A int primary key,
    column_B varchar(100)
)

How to configure the SQLStatementTask

Basically, two fields are mandory:

  • The JDBC link to establish the connection between Fast2 and SQLServer. To generate it, use the Microsoft Documentation
    SQLGenericCaller

For example, you can use :

jdbc:sqlserver://localhost;databaseName=demo;encrypt=false;integratedSecurity=true;

If you do not want to put your credentials in the login URL, you can encrypt your password by entering your credentials by filling in the fields below.

SQLGenericCaller
  • The query to insert or update your database.
INSERT INTO table_test_connection (column_A, column_B) VALUES ('${punnetId}', "test");
SQLStatement

Now let’s start your Fast2 map and check that your table has new values !

SELECT * from table_test_connection;
SQLStatement

πŸ‘ Fast2: 1, JDBC & SQL: 0

Congrats, you’ve made it !

Now you can also use the JDBC connection for other tasks such as SQLMultiQueryTask or SQLSource.
In addition to inserting or updating your database, you can also use these tasks to remove the contents of your tables !