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. It is a great post. Keep sharing such kind of useful information.

    Oracle Training in Chennai | Oracle course in Chennai

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

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

    ReplyDelete
  4. 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
  5. Really GREAT post. many-many thanks! This is a great example of kwnoledge sharing. Well done!

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

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

    ReplyDelete
  8. 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
  9. Great read! Thank you for such useful insights. Visit here for advanced technical courses on ORACLE APEX ONLINE TRAINING

    ReplyDelete
  10. http://www.offshorededi.com offers high quality DMCA Ignored Hosting. We make offshore hosting simple for you to use with reliable servers and one-click installers.

    ReplyDelete
  11. 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
  12. Looking forward to getting more updates and we play a small role in upskilling

    people providing the latest tech courses. Join us to upgrade on ORACLE APEX ONLINE

    TRAINING


    ReplyDelete
  13. Infycle Technologies offers the Best Data training in chennai and is widely known for its excellence in giving the best Data Science Certification course in Chennai. Providing quality software programming training with 100% placement & to build a solid career for every young professional in the software industry is the ultimate aim of Infycle Technologies. Apart from all, the students love the 100% practical training,
    which is the specialty of Infycle Technologies. To proceed with your career with a solid base, reach Infycle Technologies through 7502633633.

    ReplyDelete
  14. Grab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for a free demo.
    Best Oracle PLSQL Training in Chennai | Infycle Technologies

    ReplyDelete

  15. I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

    Devops Training in Hyderabad

    Hadoop Training in Hyderabad

    Python Training in Hyderabad

    Tableau Training in Hyderabad

    Selenium Training in Hyderabad

    ReplyDelete
  16. Grab Data Science Certification in Chennai for skyrocketing your career with Infycle Technologies, the best Software Training & Placement institutes in and around Chennai. In addition to the Certification, Infycle also gives the best placement training for personality tests, interview preparation, and mock interviews for leveling up the candidate's grades to a professional level.

    ReplyDelete
  17. Are you interested in doing Data Science Training in Chennai with a Certification Exam? Catch the best features of Data Science training courses with Infycle Technologies, the best Data Science Training & Placement institutes in and around Chennai. Infycle offers the best hands-on training to the students with the revised curriculum to enhance their knowledge. In addition to the Certification & Training, Infycle offers placement classes for personality tests, interview preparation, and mock interviews for clearing the interviews with the best records. To have all it in your hands, dial 7504633633 for a free demo from the experts.

    ReplyDelete
  18. Infycle Technologies, the
    No.1 software training institute in Chennai
    offers the leading Python course in Chennai for tech professionals and students at the best offers. In addition to the Python course, other in-demand courses such as Data Science, Selenium, Oracle, Java, Power BI, Digital Marketing also will be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

    ReplyDelete
  19. Reach to the best Python Training institute in Chennai for skyrocketing your career, Infycle Technologies. It is the best Software Training & Placement institute in and around Chennai, that also gives the best placement training for personality tests, interview preparation, and mock interviews for leveling up the candidate's grades to a professional level.

    ReplyDelete
  20. Grab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for having a free demo.

    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

New plugins to store files outside your database!

The plugins we release helps you easly upload files to Amazon AWS S3. Below we have simple installation tutorial and for more you can always visit our DEMO application. Actually, this plugin is in 2 parts: - "FMcomponent" part is APEX item. Something like native filebrowser item but fully configurable. You can always customize how it with a sparkle of CSS. - "FMproviders" part serves as connection to one of the file storage service (eg. Amazon S3, Dropbox, etc) To use it, you have to grand DBMS_CRYPTO privilege to your Oracle schema: grant execute on sys.dbms_crypto to <schema>; Next, install both plugins into your APEX application: FMcomponent and specific FMprovider (currently we provide AWS S3 FMprovider). At the end you have to install additional  package . As a schema owner open your favorite SQL editor and run as first apexutil_fm_aws.pls and second apexutil_fm_aws.plb. Now you can try to add plugins in your application. Go to Apex Ap