Microsoft SQL Server Data Source

Microsoft SQL Server Data Source
lac51
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)
.
Manage Database Functions and Stored Procedures
CA Live API Creator
exposes the stored procedures and database functions as RESTful resource endpoints.
View Stored Procedures with Same Name
You can view stored procedures that have the same name within your schema in API Creator. To view your schema, with your API open, in the Create section, click
Schema
.
If your SQL Server database contains multiple stored procedures, SQL Server names the stored procedures using a semi-colon delimiter syntax:
<procedure name>;1
<procedure name>;2
...
For example, if you have two stored procedures called
myProc
, SQL Server names your stored procedures as follows:
myProc;1
myProc;2
If your SQL Server database contains multiple stored procedures,
CA Live API Creator
lists them using the period delimiter syntax:
<prefix>:<procedure name>.1
<prefix>:<procedure name>.2
...
For example, if you have two stored procedures called
myProc
,
CA Live API Creator
lists your stored procedures as follows:
<prefix>:myProc.1
<prefix>:myProc.2
Identify and Reference Database Functions and Stored Procedures
Database functions and stored procedures with unique names appear as the REST endpoint.
The following example shows how you can identify or reference the
myProcedure
stored procedure and identify or reference the
myFunction
database function in
CA Live API Creator
:
<prefix>:myProcedure
<prefix>:myFunction
Test Database Functions and Stored Procedures
Test your functions and stored procedures in the REST Lab. Issue a POST request while passing the
IN
argument values.
For more information:
Use Sequences in SQL Server Data Sources
SQL Server typically handles auto-generated primary keys using the IDENTITY column and by setting the column as the primary key. You can also have
CA Live API Creator
auto-generate values for the primary key of an entity (table or view).
For more information about the approaches to generating sequential values, see Associate Entities to Sequences.