MySQL Data Source

MySQL Data Source
lac40
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.
In this article:
Install the MySQL JDBC Driver
CA Live API Creator
 includes the MariaDB JDBC driver. You can replace this driver with the MySQL JDBC driver and configure the URL to include specific properties.
If you have installed 
CA Live API Creator
 on Tomcat, copy the 
mysql-connector-java-<version>-bin
.jar
 JAR file (the JDBC driver) into the 
${CATALINA_HOME}/lib
 directory. After you have installed the JDBC driver, the MySQL data source is available as an option in the connection wizard when creating an API.
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.
For more information about the supported versions of the JDBC drivers, see Supported Platforms.
Connect to a MySQL Database
Prerequisites:
 You know your JDBC connection information.
Connect to your database by creating your API. For more information about how to create an API, see Create your API.
  1. In the connection wizard, select 
    MySQL
    .
  2. Complete the
    Host
    ,
    Port
    , and
    P
    assword
    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/MariaDB 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 
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.
tinyint
tinyint unsigned
number