Many of the world’s largest and fastest-growing organizations rely on MySQL to save time and money powering their high-volume web sites, business-critical systems and packaged software.
Brian Spendolini already covers the steps for defining a MySQL connection in Database Tools so I won’t repeat them here. Also covered in my previous article is how to determine the REST Enabled SQL URL for the Database Tools connection and how to define the API key so that APEX can access that service.
What’s different now in APEX 22.1 is that it can determine the database product behind the connection. So when you add a REST Enabled SQL service for a MySQL database it will ask you for the default database to use for all queries. That is quite literal, APEX will send a ‘use <database>’ statement as part of the query script sent over REST Enabled SQL.
APEX does something special when adding a REST Enabled SQL service URL for a MySQL connection
Using a query on your MySQL data
The connection defined is to a database which has the sample sakila database so lets have a chart in APEX to show the Sales by Film Category data.
Create an APEX applicationAdding a chart with REST Enabled SQL source for the MySQL Sakila databaseThe query for getting category and total_sales from the sales_by_film_category
Note in the above image the Column Mapping for the series specifies the Label and Value to use.
Chart showing data from the hosted MySQL Data Service
Conclusion
Now you can, with APEX 22.1, query data from your Oracle Cloud hosted MySQL database. The use of ORDS REST Enabled SQL service in this manner is only supported when used with Oracle Cloud hosted MySQL databases.
Or at least the majority of data types. In a previous post, MySQL Sakila Structure, I went through the structure of the example sakila database and mentioned that it does not cover all supported MySQL data types. That means the example database is not sufficient on its own to test the behaviour of the MySQL Connector/J JDBC driver and how it works with different data types. In an attempt to bridge that gap I have produced a script to create a single table with all the supported data types along with modifiers that have an impact on how MySQL Connector/J represents them. See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html for more details on the nuances of the implementation’s handling of particular data types.
MySQL has a sample database which is useful for tutorials, samples and a reference for testing integration scenarios. The scripts to create the sakila sample schema put tables, views, procedures, triggers and data in place.
The sample also includes the sakila.mwb file which is a MySQL Workbench data model that you can open within MySQL Workbench to examine the database structure. For more information, see MySQL Workbench.
Data model of sakila sample database
Not all MySQL data types are used in the sample schema but it does cover the most common as well as a BLOB for a picture and GEOMETRY for location data. Here’s a description of each table in a convenience text structure which is searchable.
describe actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe actor_info;
+------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id | smallint unsigned | NO | | 0 | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| film_info | text | YES | | NULL | |
+------------+-------------------+------+-----+---------+-------+
describe address;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| address_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| address | varchar(50) | NO | | NULL | |
| address2 | varchar(50) | YES | | NULL | |
| district | varchar(20) | NO | | NULL | |
| city_id | smallint unsigned | NO | MUL | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| location | geometry | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe category;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| category_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
describe city;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| city_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| city | varchar(50) | NO | | NULL | |
| country_id | smallint unsigned | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe country;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| country_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| country | varchar(50) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| email | varchar(50) | YES | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| create_date | datetime | NO | | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer_list;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| ID | smallint unsigned | NO | | 0 | |
| name | varchar(91) | YES | | NULL | |
| address | varchar(50) | NO | | NULL | |
| zip code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| notes | varchar(6) | NO | | | |
| SID | tinyint unsigned | NO | | NULL | |
+----------+-------------------+------+-----+---------+-------+
describe film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(128) | NO | MUL | NULL | |
| description | text | YES | | NULL | |
| release_year | year | YES | | NULL | |
| language_id | tinyint unsigned | NO | MUL | NULL | |
| original_language_id | tinyint unsigned | YES | MUL | NULL | |
| rental_duration | tinyint unsigned | NO | | 3 | |
| rental_rate | decimal(4,2) | NO | | 4.99 | |
| length | smallint unsigned | YES | | NULL | |
| replacement_cost | decimal(5,2) | NO | | 19.99 | |
| rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
| special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
describe film_actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id | smallint unsigned | NO | PRI | NULL | |
| film_id | smallint unsigned | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe film_category;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | |
| category_id | tinyint unsigned | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| staff_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| picture | blob | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| username | varchar(16) | NO | | NULL | |
| password | varchar(40) | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff_list;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID | tinyint unsigned | NO | | 0 | |
| name | varchar(91) | YES | | NULL | |
| address | varchar(50) | NO | | NULL | |
| zip code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| SID | tinyint unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
describe store;
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| store_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| manager_staff_id | tinyint unsigned | NO | UNI | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
That’s it. All the tables and views in the sakila sample database described.
Notice that sakila does not cover all MySQL data types such as TINYBLOB, VARBINARY etc. The next blog post will examine how the MySQL Connector/J JDBC driver represents that data and introduce a new table for testing all the supported data types.
Peter's Random Topic Library 