Microsoft SQL Server Data Source

Microsoft SQL Server Data Source
calac41
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. After you have installed the JDBC driver for SQL Server, 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
 
Prerequisite: 
You have downloaded and extracted the JDBC driver for SQL Server file.
For more information about how to get the JDBC driver for SQL Server file, see the Microsoft site.
 
Follow these steps:
 
 
Apache Tomcat
If you have installed 
CA Live API Creator
 on Tomcat, complete the following:
  1. Copy the JAR file (the JDBC driver) into the 
    %{CATALINA_HOME}/lib
     directory.
  2. Restart the container.
If you have installed the self-contained, single-user version of 
CA Live API Creator
 that is based on Jetty, complete the following:
  1. Copy the JDBC driver into the 
    %JETTY_HOME%/caliveapicreator/lib/ext
     directory.
  2. Restart the container.
Connect to SQL Server
You can connect 
CA Live API Creator
 to SQL Server using only SQL Server Authentication mode.
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. 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.