B4J Tutorial Trigger B4J Script with MySQL on Linux via udf_sys

This Tutorial save you a lot of time and is an Important Feature for Remote Database Apps!

Integrated in this Turtorial:
-install udf_sys on MySQL (Ubutnu 16.04)
-call B4J Script (.jar) over MySQL Trigger
-example for a Trigger that call this script
-example for a B4J script (Firebase Push Notification)

Let the learning begin!

We can make it all over the console/terminal

Install udf_sys on Linux/Ubuntu 16.04:

open the Console on GUI or go to the Terminal

It is recommended to be logged in as a sudo user.

1. Download the files

enter this into the console:

wget https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip

or Download it manually

2. Unzip

enter this to unzip the files

unzip master.zip

when it is finished then go to the extracted folder:

cd lib_mysqludf_sys-master/

3. Compile the UDF_SYS

with this command we can compile files

sudo gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. \

when you run it then he wanted the file to compile and the destination directory + filename + file ending in this case we need a .so file

here is the command you need, check the path before you press Enter

-shared lib_mysqludf_sys.c -o /usr/lib/mysql/plugin/lib_mysqludf_sys.so

if a error show like "The program 'gcc' is currently not installed." then put easy

Spoiler: input

sudo apt install gcc

and then we need the dev paket from MySQL

sudo apt-get install
libmysqlclient-dev

and now... we have compile the udf_sys successful!

4. Create the required functions on MySQL

Open the MySQL Shell or the Workbench

login as root

MySQL shell:

mysql -u root -p

put this into the shell or open a SQL Tab on the Workbench

use mydatabase;

then we create these 2 Functions:

CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_eval RETURNS int SONAME 'lib_mysqludf_sys.so';

And that's all!


Test .jar from B4J to call Firebase Push

We create a non ui B4J project.

Sub Process_Globals
    Private API_KEY As String = "XXXXXXXXXXXX"
End Sub

Sub AppStart (Args() As String)
    SendMessageToTopic(Args(0)) 'this are our arguments, if we call the script with arguments
    StartMessageLoop
End Sub

Private Sub SendMessageToTopic(Topic As String)
    Dim Job As HttpJob
    Job.Initialize("fcm", Me)
    Dim m As Map = CreateMap("to": $"/topics/${Topic}"$)
        
        Dim data As Map = CreateMap("title": "The Title" , "body": "The Body of this Notification")
      
        m.Put("priority", 10)

    m.Put("data", data)
    Dim jg As JSONGenerator
    jg.Initialize(m)
    Job.PostString("https://fcm.googleapis.com/fcm/send", jg.ToString)
    Job.GetRequest.SetContentType("application/json;charset=UTF-8")
    Job.GetRequest.SetHeader("Authorization", "key=" & API_KEY)
End Sub

Sub JobDone(job As HttpJob)
    Log(job)
    If job.Success Then
        Log(job.GetString)
    End If
    job.Release
    StopMessageLoop
End Sub

Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Now we compile this script in Release Mode and put it to a folder on our server and go to the Next step.


Create Trigger to call the .jar script

go to the MySQL Workbench open your table and go to the triggers.

in this example i want to create a Trigger for triggers event "After Update"

I put my .jar in the folder "home/admin/myfolder_for_mynotifications"

CREATE DEFINER = CURRENT_USER TRIGGER `orders`.`delivery_AFTER_UPDATE` AFTER UPDATE ON `delivery` FOR EACH ROW
BEGIN

DECLARE cmd CHAR(255);
 DECLARE result int;

SET cmd=CONCAT('#!/bin/bash
cd /home/admin/myfolder_for_mynotifications
java -jar mynotification.jar ', NEW.user_token); #or you can set your own message 'mymessageAsArgument'
 
 SET result = sys_exec(cmd);

END

And now ever we update the delivery table, then is this trigger triggered and call our B4J .jar script and send a push notification to the topic.


I am available for any questions, if I forgot something, I will add it.


why did I do this tutorial?

I spent 3 weeks installing it (maybe more), at first I was on Windows Server 2016 and had to realize that it just will not work (2 weeks wasted). That's why I had to switch to Ubuntu, there I also had some difficulties (1 more wasted week)

Greetings

 

[alwaysbusy]

Great tutorial!