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.
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.
Note in the above image the Column Mapping for the series specifies the Label and Value to use.
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.