Skip to main content

ORACLE APEX with MySQL !

About 10 years ago I tried to build APEX application on MS SQLServer. It was possible but performance was not satisfactory. (You can read this post here)...
Today I will describe how to use APEX with MySQL. Obviusly I don't think it is good idea to build APEX app only on MySQL. It can be used as part of Hybryd solution (Oracle + MySQL or Postrgres).



My description covers integration with MySQL. As a result will be APEX application with Interactive Report and CRUD form based on MySQL data.
Description contains two parts: First part is a MySQL site, Second part is a APEX application part


Prerequisites:
  • Oracle 11g+
  • APEX 18.1+
  • MySQL/Postgres
  • RestSQL

I. MySQL Part


1. MySQL contains simple apexdb database with one apexutil table.



2. To use this database install RestSQL. What is RestSQL? RestSQL is an open-source, ultra-lightweight data access layer for HTTP clients. RestSQL is a persistence framework or engine in the middle tier of a classic three tier architecture: client, application server and database. It may also be embedded in any middle-tier as a Java library. On the project home page you can find few distribution types. I choose WAR because I can deploy it on Tomcat, the same one I use for ORDS.
Instalation:
1. Edit  $TOMCAT_HOME/conf/context.xml file, as in (add this line):


<Parameter name="org.restsql.properties" value="/etc/opt/restsql/restsql.properties" override="false" />


2. Create file restsql.properties in /etc/opt/restsql/ path and paste inside this information:
database.password = MySQL_Pass
database.user = root
sqlresources.dir = /etc/opt/restsql
database.url = jdbc:mysql://localhost:3306
3. Next place restql-X.X.XX.war file with new name restql.war in the $TOMCAT/webapps directory and bounce the Tomcat server.
Now try connect to RestSQL module: go to http://localhost:8080/restsql. If you connected successful create Resources. Go to Tools menu and clean Sub-directory field and use database name in Database field, in my case apexdb. Afterward click Generate button. During this process RestSQL scans MySQL database and creates webservice definitions based on the tables.
Now is time to check specific http endpoints. In RestSQL module go to Resources menu http://localhost:8080/restsql/res/ and click "query/json" link.
If you see the json text below this as a result then you're there. You got MySQL site ready. Next we'll move to Oracle side:

{ "apexutils": [
  { "apx_id": 1, "apx_service": "Development", "apx_percentage": 75 },
  { "apx_id": 2, "apx_service": "Apex hosting", "apx_percentage": 3 },
  { "apx_id": 3, "apx_service": "Apex system sustain", "apx_percentage": 15 },
  { "apx_id": 4, "apx_service": "Other", "apx_percentage": 7 },
 ]
}


II Oracle Apex side


To use Web Source as Interactive Report Source prepare "Remote Server" and "Web Source Module". Go to Shared components and click Remote Servers on right sidebar. Next use Create button and fill in fields:





Afterward create Web Source Module. Go to Shared component and click Web Source Module link. Use Create button and follow the wizard steps:


Step 1

Step 2

Step 3

Step 4
Click Advanced !

Step 5

Step 6

Now you can create Interactive Report. There are no special steps to build IR based on external data. In the last wizard step set Data source "Web Source" and select "APEXUTIL" in Web Source Module field.




Report is ready, but how to manipulate MySQL data from APEX context ?
In APEX 18.1 there is no Form wizard to build Form based on Web Source Modules. There is only wizard to build Form/Report based on "Legacy Web Service References" : ( I hope it's temporary state and in the future it will be enhanced. (or maybe I didn't find it... maybe you know how to use new "Web Source Modules" as a Form source ?). In this case the best solution is to create form manualy. Go to page which was used to build IR. And add Static Content region with 4 Items and 1 Button like this:

P2_ID - hidden
P2_SERVICE - text field
P2_PERCENTAGE - text field
P2_START_DATE - data picker

ADDEDIT (button) - Action (submit page)





Also IR region must be changed. Add one new column (with dynamic item /checkbox/) and DELETE button. APX_ID column must be changed as well, make it as a link.

Change APX ID column.
Go to APX_ID column attribute -> Link and set like this:



Add "CHECKS" new column
Go to Interactive report region -> Local Post Processing -> Type: SQL Query and paste this query:

SELECT
 APEX_ITEM.CHECKBOX2(1,APX_ID)  as CHECKS,
 APX_ID                         as APX_ID,
 APX_SERVICE                    as APX_SERVICE,
 APX_PERCENTAGE                 as APX_PERCENTAGE,
 APX_START_DATE                 as APX_START_DATE
from #APEX$SOURCE_DATA#

Change Security section for CHECKS column -> Escape special characters -> NO

Add DELETE button - Action (submit page)

Save changes and select CHECKS column from IR Action menu. Now page looks like on the screenshot:





UI is ready now add Processes:

Create 3  processes:

1. MySQL CRU operations
Source: PL/SQL Code
Condition: Server-side Condition -> When Button Pressed -> ADDEDIT

declare
l_json        clob;
l_response    clob;
l_http_method varchar2(128);
parsed_json   apex_json.t_values;
begin
/* Start JSON request preparation */
apex_json.initialize_clob_output;
apex_json.open_object;
        apex_json.open_array('apexutils');
            apex_json.open_object;
                 if :P2_ID is not null then
                   /* Update process */
                   l_http_method := 'PUT';
                   apex_json.write('apx_id', :P2_ID);
                 else
                   /* Insert process */
                   l_http_method := 'POST';
                 end if;
                 apex_json.write('apx_service', :P2_SERVICE);
                 apex_json.write('apx_percentage', :P2_PERCENTAGE);
                 apex_json.write('apx_start_date', to_char(to_date(:P2_START_DATE,'DD-MON-YY'),'YYYY-MM-DD'));
            apex_json.close_object;
        apex_json.close_array;
apex_json.close_object;
           
l_json := apex_json.get_clob_output;
/* HTTP request preparation. ACL must be set */
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url                  =>'http://192.168.56.101:8080/restsql/res/apexutil',
    p_http_method          =>l_http_method,
    p_body                 =>l_json)  ;
end;

2. MySQL D operation
Source: PL/SQL Code
Condition: Server-side Condition -> When Button Pressed -> DELETE

declare
l_json        clob;
l_response    clob;
l_http_method varchar2(128);
parsed_json   apex_json.t_values;
begin
/* Start JSON request preparation */
apex_json.initialize_clob_output;
apex_json.open_object;
        apex_json.open_array('apexutils');
                /* Start process dynamic items. Read checked checkboxes */
                FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP 
                    apex_json.open_object;
                    apex_json.write('apx_id', APEX_APPLICATION.G_F01(i));
                    apex_json.close_object;
                END LOOP;
        apex_json.close_array;
apex_json.close_object;
            
l_json := apex_json.get_clob_output;
/* HTTP request preparation. ACL must be set */
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url                  =>    'http://192.168.56.101:8080/restsql/res/apexutil',
    p_http_method          =>    'DELETE',
    p_body                 =>    l_json)  ;
end;

3. Clear session
Type: Clear all items on the Current Page
Condition: Server-side Condition -> When Button Pressed -> ADDEDIT


DONE ! : )

If you want to see how it works watch video:




Comments

  1. Thx : ) Glad to hear you... btw: let's follow me on Twitter @APEXUTIL_andrew there is more useful info : )

    ReplyDelete
  2. Very good post. Nice example and excellent detail.

    ReplyDelete
  3. Do you have to have Tomcat installed or can this be done without ??

    ReplyDelete
    Replies
    1. You can ommit tomcat but in this case you have to use other type of RESTSQL (f.ex. docker)

      Delete
  4. Hi, I got : ORA-20987: The requested URL has been prohibited, creating Web Source Module

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. Could anybody please help me provide restSQL war file ?
    The download links were removed from the official website and couldn't be found anywhere else in the internet.. Please..

    ReplyDelete
  7. When was this ability available with APEX. I.e. to connect to non-Oracle DBs? Is it a new functionality or was it available from 2010 onwards?

    ReplyDelete

Post a Comment

Popular posts from this blog

TWO-Factor Authentication with APEX application.

Two-factor authentication means that you must have two factors, to access your account. The first factor is something you know, like your login and password combination. The second is something you have, like a mobile device with a security app installed. That second factor — the mobile device with a security app — adds an extra layer of protection to your account. Even if hackers steal your password, they can’t log in, because they don't have your mobile device. Try 2FA on our SANDBOX environment! It is the most popular 2FA option based on Times-based One-Time Password (TOTP) alghoritm described in RFC 6238 and RFC 4226. TOTP is supported by Google Authenticator, Microsoft Authenticator, Authy 2-Factor Authentication and many other mobile apps. The most popular online services like Facebook, Google, Twitter use TOTP to protect user's accounts. TOTP is a standard algorithm based on cryptographic hash method SHA-1. SHA-1 algorithm's implementation is available sin

How to return json with null value using apex_json.write(ref_cursor)

Few days ago I had to develop dynamic query json export module. As a sandbox I prepared PLSQL block: DECLARE c sys_refcursor; l_sql varchar2(4000) := 'select null as "col1", 100 as "col2" from dual union all select 200 as "col1", null as "col2" from dual'; l_file clob; BEGIN open c for l_sql; apex_json.initialize_clob_output; apex_json.open_object; apex_json.write('rowset', c); apex_json.close_object; l_file := apex_json.get_clob_output; apex_json.free_output; DBMS_OUTPUT.PUT_line(l_file); END; { "rowset": [{ "col2": 100 }, { "col1": 200 } ] } As you can see above PLSQL code doesn't return null value pairs : ( apex_json.write(p_name IN VARCHAR2, p_cursor IN OUT NOCOPY sys_refcursor) procedure Signature 14 doesn't support null. How to fix it... ? APEX API provides apex_js