Description

This artefact describes how to :

• Connect API Gateway to an SQL Server database
• call a stored procedure

API Management Version Compatibilty

This artefact was successfully tested for the following versions:

• 7.4.1

Install : SQL connection setup

Introduction

This section describes how to connect an API Gateway to an SQL Server database. It is not a best practices but more a how-to guide to connect quickly an SQL Server database (for a POC for example) In this guide, the API Gateway is installed on a Windows

Driver JDBC installation

• Download JDBC driver from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
• Unzipp package(sqljdbc6.0)
• Copy “enu/sqljdbc42.jar” in the directory “apigateway/ext/lib”
• Copy ”enu/auth/x86/sqljdbc_auth.dll” in the directory “apigateway/win32/lib”
• Restart API Gateway instance

Reference drivers in Policy Studio

• Drill down to Window > Preferences > Runtime Deps.
• Do not forget to restart Policy Studio with the option “-clean”

“mixed mode” authentication activation

• Launch SQL Server Management Studio (Start > Run… > ssms > OK)
• Right click on Server and properties
• Open the “Security” section
• Choose “SQL Server and …”
• Refer to https://msdn.microsoft.com/en-us/library/ms188670.aspx

sa user activation

• “Security” > ”Logins”
• Double click on “sa”
• Enter a new password and confirm it
• Select “Enabled” in the tab ”Status”

TCP activation

• Run SQL Server Configuration Manager : Start > Run… > SQLServerManager11.msc > OK
• in "SQL Server Network Configuration" section, select “Protocols for SQLEXPRESS" and click on “TCP/IP”

• In the tab “Protocol” turn "Enabled” to “Yes”
• In the tab "IP Addresses”, go directly at the bottom and in the section “IPAll” select "TCP port" to 1433
• “TCP Dynamic Port” must stay empty

Start “SQL Server” service

• In SQL Server Management Studio, right click on the server
• Click on "Restart"

JDBC connection setup in Policy Studio

• In Policy Studio, Drill down to “External Connections”
• Click on “Database Connections”
• Click on “Add a Database Connection”

• Add a name
• fill in JDBC URL: jdbc:sqlserver://:1433;databaseName=
• Username: sa
• Password:
• Click on “Test Connection”
• If the test is OK, click on “OK”

Usage : Call the stored procedure

Introduction

This section describes how to call a SQL Server stored procedure. It is not a best practices but more a how-to guide to connect quickly an SQL Server database (for a POC for example) In this guide, the API Gateway is installed on a Windows

Context

The API Gateway owns a filter “Read/Write Database” which can be used to request a SQL database with a request (such as SELECT, INSERT, DELETE, UPDATE) or a stored procedure. Unfortunately, only the first result is returned from a stored procedure.

Solution

To read all the resultsets, the filter "Scripting" is used. A JavaScript is used to :

• Retrieve the database connection from the one configured in the configuration
• Call the stored procedure
• Return all data contained in the resultsets in a JSON formatRetourner

Script interface

• Configuration :
  • Connection data are setup in the configuration
• Input :
  • Attribute “db_connection” : object name for the database connection (ex: “MS SQL Server”)
  • Attribute “db_statement” : Request to call the stored procedure
  • Attribute “db_param_n” (n : integer > 0) : if the stored procedure needs input parameters, they are set thanks to those attributes
• Output :
  • Attribute “content” : JSON structure containing all the data

Configuration

• Configuration object declaration
  • Parameter “Name” is used to retrieve the connection object from the script

• Attribute “db_connection” setup

Input : db_statement et db_param_n

• db_statement contained the stored procedure call

  • Either using the standard JDBC format : { call MyStoredProcedure(?, ?) }
  • Either using a underneath engine specific format : EXECUTE MyStoredProcedure @param1 = ?, @param2 = ?

• Parameters are declared with “?” and their values are passed with the attributes ”db_param_n”

  • n = 1 for the first parameter, 2 for the second, etc.

Output : content

The attribute “content” contains all data in a JSON format:

Policy structure

Script structure

Bug and Caveats

Nothing identified

Contributing

Please read Contributing.md for details on our code of conduct, and the process for submitting pull requests to us.

Team

Axway Team

License

Apache License 2.0)