Getting Started

By default, Sierra allows five concurrent SQL connections to the database per Sierra user. Innovative can increase the number of concurrent connections allowed for a specific Sierra user, but note that running multiple SQL queries simultaneously can have serious performance consequences, depending on the structure and scope of the queries. Contact Innovative if you need to adjust the concurrent session limit for one or more Sierra users.

To begin using Sierra Direct SQL Access:

  1. Create a Sierra user, or edit an existing user, for staff who can access the database views.

Due to restrictions of the PostgreSQL database, user login names authorized for Sierra Direct SQL Access (assigned the Sierra SQL Access application) must not contain capital letters. Login names that include capital letters are unable to authenticate against the database.

Internationalized Data

The language assigned to the user determines the language of any internationalized data retrieved from the database views of multi-lingual systems.

  1. Assign the "Sierra SQL Access" application to the user.
  2. Acknowledge the license statement.
  3. Verify that the appropriate port (1032 in most cases) is open between staff and the database server.
  4. Ensure that there are no firewall restrictions impeding communication between client and the database server.
  5. When you are ready to create SQL queries, view the SierraDNA on Innovative's TechDocs website.
  6. Choose an SQL application to create and run queries against the database.
SQL Applications

Innovative staff use freeware programs such as PGAdmin and Jaspersoft Studio. Libraries are welcome to use any program they prefer.

If you have trouble accessing and using Sierra Direct SQL Access, see the Troubleshooting pages for assistance with common issues.

Setting Up SQL Client Connections

To connect an SQL client to the database:

  1. Install PostgreSQL database drivers on the PC. You can download ODBC or JDBC drivers, as needed by your chosen client application, from the PostgreSQL website.
  2. In the client application, create a connection to the database server, using the DNS name, on port 1032.
  3. Set the connection to use SSL.
  4. Connect to the "iii" database.
  5. Apply the username and password created for Sierra Direct SQL Access.

Setting Up Access for a Report-writing Client via JDBC

To connect a report-writing client to the database via JDBC, you must use an SSL connection string. The following is an example SSL connection string for Jaspersoft Studio:

jdbc:postgresql://hills-sierra-pg.iii.com:1032/iii?characterEncoding=UTF-8&autoReconnect=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory