This page explains how Portrait can be connected to a given SQL database.

Prerequisites

Before you can use SQL as a source for Portrait, we need to make sure that the following prerequisites are met.

SQL Server

Portrait will access the SQL instance via JDBC. Make sure the Portrait-App Instance has direct access to the SQL server - this especially relevant if the Portrait runs in a DMZ or external network.

Depending on the given SQL database and connection properties, the protocol and ports differ.

SQL User

Create a specific user for Portrait - we recommend the name portraitservice -, since it is only used for Portrait as a service user. Depending on your use-case, you should consider read-only writes.

Set up a connection

In the application.yml file, we need to add a new entry inside the connections section:

See https://portrait.atlassian.net/wiki/spaces/PA7/pages/1029112502 to see all available driverClass options. How the url is formatted, is depending on the used driver. Check the documentation of the given JDBC driver.

This example shows a connection to MS SQL Server with a named instance and port:

connections:
  connection:
    - id: NAVDEMO
      type: SQL
      driverClass: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url: "jdbc:sqlserver://SQL01\\NAVDEMO:48981;database=Demo Database NAV (7-1);trustServerCertificate=true"
      username: "portraitservice"
      password: "s3cur3d"

You can have several connections, each of them must start with a “-”.

The yml-file is formatted with whitespaces. Ensure you have the correct intentation: first level = 2 whitespaces, second level = 4 whitespaces, and so on. You will get an error in the logs at startup if the yml is malformed.

After you added the connection, you can jump to the next chapter: “Adding an SQL source”.

Examples

This chapter provides you with examples for connections via JDBC URL’s.

Microsoft SQL Server

This example connects to a Microsoft Dynamics Business Central database. The database runs on a named instance.

    - id: NAVDEMO
      type: SQL
      driverClass: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url: "jdbc:sqlserver://SQL01\\NAVDEMO:48981;database=Demo Database NAV (7-1);trustServerCertificate=true"
      username: "portraitservice"
      password: "s3cur3d"

Since the upgrade to the latest official SQL JDBC driver (10.x.x), you have to add the trustServerCertificate=true option.

SQLite

This example uses a SQLite database. We ship a SQLite database for demo uses or small use-cases.

    - id: internal
      type: SQL
      driverClass: "org.sqlite.JDBC"
      url: "jdbc:sqlite:./config/sample.db"

Portrait - 7.x

SQL connections