MySQL Data Source

MySQL Data Source
lac42
This article includes information about how to install the MySQL JDBC driver, how to connect to a MySQL database, and a list of the MySQL data types that 
CA Live API Creator
 supports. With the JDBC driver installed, the MySQL data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
Install the MySQL JDBC Driver
The self-contained, single-user version of 
CA Live API Creator
 that is based on Jetty includes the MySQL JDBC driver. The 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 license file and the 
mysql-connector-java-<version>-bin
 
.jar
 JAR file (the JDBC driver) 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 into the 
<Your WebLogic Domain Directory>/lib
 directory.
For more information about how to add the MySQL JDBC driver, see the MySQL site.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver into 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 supported versions of the JDBC drivers, see Installation Requirements and Supported Platforms.
Connect to a MySQL Database
You can connect to your MySQL 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 a MySQL 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 MySQL database when creating your API.
 
Prerequisites:
 You know your JDBC connection information.
 
Follow these steps:
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
     as the approach that you want to use to create your API.
  3. Select 
    MySQL
     as the data source.
  4. Complete the 
    Host
    Port
    , and 
    Password
     fields, including the following fields, and then click 
    Continue
    :
    Database
    The database name.
    Example:
     DATABASE
    Case sensitive:
     Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    Username
     
    The user name under which you access your database. You database administrator assigns you a user name.
    Example:
     DBADMIN
    Case Sensitive: 
    Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
You are connected to the database.
: Recent versions of MySQL might require that you connect to MySQL using Secure Sockets Layer (SSL). The following error message indicates this requirement:
javax.net.ssl.SSLException: Unsupported record version Unknown-0.0
You can avoid this MySQL SSL exception.
For more information about how to avoid this exception, see Troubleshooting.
MySQL Supported Data Types
When you are setting up a connection to an external data source, 
CA Live API Creator
 must map the data types in the data source to data types in 
CA Live API Creator
. The following table lists the different data type mappings applicable when working with a MySQL database.
You can use the full range of MySQL data types in 
CA Live API Creator
.
For more information about the data types, see the MySQL documentation.
CA Live API Creator
 supports read and write of the following MySQL data types:
Data type
Modeled as...
Notes
smallint
smallint unsigned
number
integer
integer unsigned
number
bigint
bigint unsigned
number
decimal
number
float    
number 
tinyint
tinyint unsigned
number
If you define the data type as tinyint(1) and it returns as a boolean value, use the following JDBC URL parameter:
jdbc:mysql://server:5432/database?
tinyInt1IsBit=false
varchar
string
mediumint    
number
timestamp
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
varbinary
 bytes
longblob
 bytes            
mediumblob    
bytes             
longtext        
bytes
date
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
time
SimpleTime
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
If you are using a time change to the JDBC URL connection (data source):
jdbc:mysql://server:5432/database?
noDatetimeStringSync=true
For more information about how to connect to the database, including information about 
noDatetimeStringSync
, see the MySQL JDBC driver documentation.