Help

Page tree

Custom Integrations with JDBC

Connect  Agiloft to external systems and database with the generic JDBC driver. This acts as a wrapper for the REST - Search operation.

Prerequisites

The Advanced or Premium edition of Agiloft is required to use REST and the JDBC driver.

JDBC Driver

This driver can be used in any application. It only supports SELECT SQL operations.

You can access the current version of the driver here: https://www.agiloft.com/utils/agiloft-jdbc-driver.jar

Settings and Parameters

The driver has these settings:

  • driver class name: com.agiloft.jdbc.adapter.Agiloftdriver 
  • URL for connection: jdbc:agiloft:schema.https={is_https};schema.loginDomain={server_url};schema.kb={KB_name};schema.user={user_name};schema.password={password} 

Where the parameters are: 

  • is_https: true if the connection is through HTTPS, false otherwise  
  • server_url: address of the server (if HTTPS is used, there is no need to add 443 port) 
  • KB_name: name of the Agiloft KB 
  • user_name: username of  Agiloft account
  • password: password for  Agiloft account

For example, you might use: jdbc:agiloft:schema.https=true;schema.loginDomain=sandbox2.agiloft.com;schema.kb=Demo;schema.user=admin;schema.password=qwerty

For subtables, the table name must be placed inside quotation marks (""). Post-processing of query conditions is done using Apache Calcite. Make sure to measure acceptable performance early, especially if you use operators, Calcite functions, or a combination of the two. Note that this post-processing does not support fields whose logical name begins with an underscore (_), and if you have any such fields, you need to transfer their contents to a supported field in order to use this method.


This example driver is used in the following Java sample program:


Java Example
public class driverUsecase { 
    public static void main(String[] args) throws SQLException { 
         String url = "jdbc:agiloft:schema.https=true;schema.loginDomain=sandbox2.agiloft.com;schema.kb=Demo;schema.user=admin;schema.password=qwerty"; 
         Connection connection = driverManager.getConnection(url); 
  String sql = "select count(*) from company"; 
PreparedStatement statement = connection.prepareStatement(sql); 
 
ResultSet rs = statement.executeQuery(); 
while (rs.next()) { 
    int value = rs.getInt(1); 
    System.out.println("count(*) of company = " + value); 
} 
   } 
} 

Here's an example using Python instead:

Python Example
import os
import jaydebeapi

def run_sql(sql):
    '''Sample function to run SQL query'''     driver_class = 'com.agiloft.jdbc.adapter.AgiloftDriver'
    jar_file = 'C:\\agiloft-jdbc-driver.jar' 
    jdbc_url = ("jdbc:agiloft:schema.https=false;schema.loginDomain=sandbox2.agiloft.com;"
                "schema.kb=Demo;schema.user=admin;schema.password=qwerty")
    conn = jaydebeapi.connect(jclassname=driver_class, url=jdbc_url, jars=jar_file)
    cursor = conn.cursor()
    cursor.execute(sql)
    return cursor.fetchall()

company_count = run_sql("select count(*) from company")[0][0]
print("Total companies = %s" % (company_count,))