Microsoft SQL Server

Live API Creator supports the following Microsoft SQL Server options:

  • Identity Columns. For REST requests that perform insertion and provide a non-null value, Live API Creator correctly enables identity_insert before performing the insertion.
  • SQL Sequences. For example, you can specify that column A in table FOO should use the MySequence sequence. Live API Creator generates values for columns for all inserts using the sequences you specify. You can use the same sequence for more than one table.
  • Case Sensitivity. SQL Server is case-insensitive, but remembers case. Live API Creator logic treats all SQL Server tables and columns as case-sensitive.
  • Pagination. SQL Server does not provide simple support for pagination. Live API Creator emulates pagination at the database level. For most users, this provides reasonable performance.

Install Microsoft SQL Server and Azure SQL Server Drivers

  1. Download the Microsoft JDBC driver for SQL Server file from the Microsoft site.
  2. Extract the file using the following command:
    tar -xf <JDBC file name>
  3. Locate the sqljdbc_4.2/enu/sqljdbc42.jar JAR file (the JDBC driver) and copy it into the CALiveApiCreator/lib/ext directory, relative to the directory with Start.sh in it.
  4. Start the demonstration package.

Connect to SQL Server

Microsoft SQL Server can present some unique challenges. The following are common issues that may prevent you from obtaining connectivity to your database server.

Set the Server Authentication

In Microsoft SQL Server Management Studio, ensure that Server authentication is set to SQL Server and Windows Authentication mode. To view this page, in SQL Server Management Studio, right-click server, select Properties. The following image shows the Server Properties window in Microsoft SQL Server Management Studio:

Set the Port Number

In Sql Server Configuration Manager, ensure that the TCP/IP protocol is enabled and that IP6 and IPAll are assigned a value for TCP Port, typically 1433. The following image shows the TCP/IP Properties window in Sql Server Configuration Manager:

Restart the Service

If you make changes to the server authentication or port number, restart the SQL Server service. To connect to the database, use the value you specified for IP6 and IPAll TCP Port, typically 1433.