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, click Create New API
.

The connection wizard opens.

Click Database First
as the approach that you want to use to create your API.

Select MySQL
as the data source.

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,

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: 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), use the following JDBC URL parameter: 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.
timestamp	SimpleTimeStampOffset	For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.

Database Connectivity

Layer7 Live API Creator 5.4

MySQL Data Source