B4J Tutorial Trigger B4J Script with MySQL on Linux via udf_sys

Discussion in 'B4J Tutorials' started by Alexander Stolte, Jan 26, 2018.

  1. Alexander Stolte

    Alexander Stolte Well-Known Member Licensed User

    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:
    Code:
    wget https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip
    001.PNG

    or Download it manually

    2. Unzip

    enter this to unzip the files
    Code:
    unzip master.zip
    when it is finished then go to the extracted folder:
    Code:
    cd lib_mysqludf_sys-master/
    002.PNG

    3. Compile the UDF_SYS

    with this command we can compile files
    Code:
    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 ;)
    Code:
    -shared lib_mysqludf_sys.c -o /usr/lib/mysql/plugin/lib_mysqludf_sys.so
    004.PNG


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

    003.PNG

    Code:
    sudo apt install gcc
    and then we need the dev paket from MySQL

    Code:
    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:
    Code:
    mysql -u root -p
    005.PNG

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

    Code:
    use mydatabase;
    then we create these 2 Functions:

    Code:
    CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
    CREATE FUNCTION sys_eval RETURNS int SONAME 'lib_mysqludf_sys.so';
    006.PNG

    And that's all!


    Test .jar from B4J to call Firebase Push


    We create a non ui B4J project.

    Code:
    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 StringAs 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"

    Code:
    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
     
    Don Oso, udg, Anser and 4 others like this.
  2. alwaysbusy

    alwaysbusy Expert Licensed User

    Great tutorial!
     
    Alexander Stolte likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice