MySQL Data Source
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
Layer7 Live API Creator
supports.lac52
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
Layer7 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
Layer7 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 Layer7 Live API Creator
, no additional steps are required to install the JDBC driver.If you have installed
Layer7 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 Layer7 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:
- In API Creator, on the APIs page, clickCreate New API.The connection wizard opens.
- ClickDatabase Firstas the approach that you want to use to create your API.
- SelectMySQLas the data source.
- Complete theHost,Port, andPasswordfields, including the following fields, and then clickContinue:DatabaseThe database name.Example:DATABASECase 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 (").UsernameThe user name under which you access your database. You database administrator assigns you a user name.Example:DBADMINCase 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,
Layer7 Live API Creator
must map the data types in the data source to data types in Layer7 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
Layer7 Live API Creator
.For more information about the data types, see the MySQL documentation.
Layer7 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 | |
json | object | Only the MySQL JDBC driver supports this data type. |
set | number or string | set(1,2,3) or set('a','b','c') a single valid value |
enum | number or string | enum('small','medium','large') a single valid value |
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:
|
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), use the following JDBC URL parameter:
For more information about how to connect to the database, including information about noDatetimeStringSync , see the MySQL JDBC driver documentation. |
timestamp | SimpleTimeStampOffset | For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset. |