Microsoft SQL Server Data Source

Microsoft SQL Server Data Source
lac42
This article includes information about how to install the Microsoft JDBC driver for SQL Server, a list of the SQL server options you can use in 
CA Live API Creator
, and how to troubleshoot your connection to a SQL Server database. With the JDBC driver installed, the SQL Server data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
Install the Microsoft JDBC Driver for SQL Server
The self-contained, single-user version of 
CA Live API Creator
 that is based on Jetty includes the Microsoft JDBC driver for SQL Server. The JDBC driver is located in the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory. If you have installed this version of 
CA Live API Creator
, no additional steps are required to install the JDBC driver.
If you have installed 
CA Live API Creator
 on another Java container, such as Apache Tomcat, copy the 
mssql-jdbc-<version>.jar
 JAR file (the JDBC driver) that is located in the 
databaseDrivers
 directory into the directory that is based on the Java container on which you have installed 
CA Live API Creator
:
Apache Tomcat
Copy the JDBC driver into the 
${CATALINA_HOME}/lib
 directory.
Oracle WebLogic
Copy the JDBC driver to the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
 
IBM WebSphere
Add the JDBC driver to your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation.
For more information about the versions of the JDBC drivers that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to SQL Server
You can connect to your SQL Server database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to an SQL Server data source.
    For more information about how to add a connection to a data source, see Database Connectivity
The following procedure details how to connect to your SQL Server database when creating your API.
 
Prerequisites:
 
  • You know your JDBC connection information.
  • In Microsoft SQL Server Management Studio, 
    Server authentication
     is set to 
    SQL Server and Windows Authentication mode
    .
    For more information, see the "Set the Server Authentication" section.
 
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select 
    SQL Server
     as the data source type.
  4. Complete the 
    Host
    Port
    Database
    , and 
    Password
     fields, including the following fields, and then click 
    Continue
    :
     
    Schema/Owner
     
    The schema name for this database. You can use double quotes (") for the schema name to accept the value of mixed-case (non-standard) schema names.
    Example:
     
    MySchema
    .
     
    Username
     
    The username that is used to connect to this database. You can use double quotes (") for the username to accept the value of mixed-case (non-standard) usernames.
You are connected to the SQL Server database.
SQL Server Options
You can use the following Microsoft SQL Server options in 
CA Live API Creator
:
  • Identity columns.
     For REST requests that perform insertion and provide a non-null value, 
    CA 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. 
    CA Live API Creator
     generates values for columns for all inserts using the sequences that you specify. You can use the same sequence for more than one table.
    For more information about how to associate entities (tables or views) that use sequences to the sequence, see Associate Entities to Sequences.
SQL Server is case-insensitive, but remembers case. The logic you specify for your API treats all SQL Server tables and columns as case-sensitive. 
CA Live API Creator
 emulates pagination at the database level.
Troubleshoot SQL Server Connection
The following are common issues that can prevent you from obtaining connectivity to your SQL Server 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 the server properties, 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.
SQL Server Datatypes
You can use read and write the following data types when you work with an Microsoft SQL Server database:
Data type
Modeled as...
Notes
char
string
varchar
string
nvarchar
nchar
string
numeric
decimal
float
number
timestamp
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
smallint
timyint
number
date
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
datetype
Timestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
datetype2
Timestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
datetimeoffset
TimestampOffset
image
binary
varbinary
bytes
time
Time
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
money
number
int
number
xml
string
sql_variant
bytes
bit
boolean
hierarchyid
string
uniqueidentifier
string
User-Defined Data Types
User-defined data types (UDTs) are mapped to the underlying type defined in the database. For instance, if you define a data type using the following:
CREATE TYPE [MyString] FROM nvarchar(15) NULL;
and you define a table using the following UDT:
create table RelParent (
name MyString not null primary key,
stuff varchar(200)
);
then all columns using this data type behave as being of type 
nvarchar(15)
.
Use Sequences in SQL Server Data Sources
SQL Server typically handles auto-generated primary keys using sequences, which are database objects that the sequence generator in the database uses to generate sequential values. When you connect to an SQL Server data source, 
CA Live API Creator
 detects the sequences that are defined in your schema.
You can have 
CA Live API Creator
 auto-generate primary keys using these sequences using the following methods:
Associate Entity Columns to Sequences
When you connect to a database that supports sequences, such as SQL Server, 
CA Live API Creator
 detects the sequences that are defined in your schema. 
CA Live API Creator
 cannot know which entity (table or view) uses which sequence. If your database includes sequences, you want it to generate unique primary keys during row inserts, and you want 
CA Live API Creator
 to auto-generate numbers for the entity's column using the sequence, associate the sequence to that entity's column.
For more information, see Associate Entities to Sequences.
Define Rules that Return a Unique Primary Key
You can have 
CA Live API Creator
 generate the following primary keys for the table using sequences and assign it to a column by way of event rules:
  •  
    Alphanumeric unique primary key:
     Define a 
    pre-insert event rule
    . Within the code for the rule, define how 
    CA Live API Creator
     computes the key. Pre-insert event rules fire only on client inserts. Rules of this type automatically handle inserting a parent and a set of children in a single transmission (Cascade Add).
     The 
    Oracle Sequence Example
     API sample illustrates an example of how you can have 
    CA Live API Creator
     insert a parent and a set of children in the same request.
    For more information about this example, see Oracle Sequence Example API Sample.
  •  
    Unique primary key:
     Define an 
    early event rule
    . Define a rule of this type for row inserts that are triggered from within your rule logic (for example, audit changes into child tables).
     The 
    Oracle Sequence Example
     API sample illustrates an example of how you can have 
    CA Live API Creator
     compute an alphanumeric primary key in an early event rule using sequences.
    For more information about this example, see Oracle Sequence Example API Sample.
For more information about how to define pre-insert and early event rule types, see Event Rule Types.