How to use the Oracle Database Tools Service to provide MySQL data to APEX

Last week I posted an article on How to use the Oracle Database Tools Service to provide data to APEX. That was for an Oracle Database. Another thing that happened last week was the announcement of MySQL Support in Database Tools in OCI. Here’s the obvious follow up article showing accessing data from an Oracle Cloud hosted MySQL Database Service in an APEX application.

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 application
Adding a chart with REST Enabled SQL source for the MySQL Sakila database
The 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.

One thought on “How to use the Oracle Database Tools Service to provide MySQL data to APEX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s