Camel SQL Stored Procedure Example in Red Hat Fuse on Karaf

$ podman run -p 3306:3306 -d --name=mariadb -e MYSQL_ROOT_PASSWORD=mypassword mariadb/server
f63034a1f205d00882cba109f4a131bcc8d24858113a2b58dc3c746535339211

$ podman ps -a
CONTAINER ID  IMAGE                            COMMAND  CREATED        STATUS            PORTS                   NAMES
f63034a1f205  docker.io/mariadb/server:latest  mysqld   5 seconds ago  Up 3 seconds ago  0.0.0.0:3306->3306/tcp  mariadb

$ podman exec -it mariadb bash
root@f63034a1f205:/# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.5.10-MariaDB-1:10.5.10+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database testdb
    -> ;
Query OK, 1 row affected (0.000 sec)


MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table item(id INT NOT NULL AUTO_INCREMENT, title VARCHAR(50) NOT NULL, description VARCHAR(200), price INT NOT NULL, create_date DATE, PRIMARY KEY(id)); 
Query OK, 0 rows affected (0.197 sec)

MariaDB [testdb]> insert into item values(1, "pencilbox", "plastic one side box", 40, '2021-06-12');
Query OK, 1 row affected (0.126 sec)
MariaDB [testdb]> 
MariaDB [testdb]> insert into item values(2, "eraser", "eraset", 5, '2021-06-11');
Query OK, 1 row affected (0.126 sec)

MariaDB [testdb]> insert into item values(3, "sharpener", "sharpener", 6, '2021-06-11');
Query OK, 1 row affected (0.352 sec)

MariaDB [testdb]> insert into item values(4, "pencil", "pencil", 3, '2021-06-13');
Query OK, 1 row affected (0.010 sec)

MariaDB [testdb]> select * from item;
+----+-----------+----------------------+-------+-------------+
| id | title     | description          | price | create_date |
+----+-----------+----------------------+-------+-------------+
|  1 | pencilbox | plastic one side box |    40 | 2021-06-12  |
|  2 | eraser    | eraset               |     5 | 2021-06-11  |
|  3 | sharpener | sharpener            |     5 | 2021-06-11  |
|  4 | pencil    | pencil               |     3 | 2021-06-13  |
+----+-----------+----------------------+-------+-------------+
4 rows in set (0.000 sec)


MariaDB [testdb]> select * from item where price >= 5;
+----+-----------+----------------------+-------+-------------+
| id | title     | description          | price | create_date |
+----+-----------+----------------------+-------+-------------+
|  1 | pencilbox | plastic one side box |    40 | 2021-06-12  |
|  2 | eraser    | eraset               |     5 | 2021-06-11  |
|  3 | sharpener | sharpener            |     5 | 2021-06-11  |
+----+-----------+----------------------+-------+-------------+
3 rows in set (0.001 sec)

# Create Stored-Procedure
MariaDB [testdb]> DELIMITER &&
MariaDB [testdb]> CREATE PROCEDURE testdb.GetItems(IN cost INT)
    -> BEGIN
    -> SELECT * from item where price > cost;
    -> END &&
Query OK, 0 rows affected (0.062 sec)

# This is how we can call stored-procedure from MariaDB console.
MariaDB [testdb]> call GetItems(4);
+----+-----------+----------------------+-------+-------------+
| id | title     | description          | price | create_date |
+----+-----------+----------------------+-------+-------------+
|  1 | pencilbox | plastic one side box |    40 | 2021-06-12  |
|  2 | eraser    | eraset               |     5 | 2021-06-11  |
|  3 | sharpener | sharpener            |     6 | 2021-06-11  |
+----+-----------+----------------------+-------+-------------+
3 rows in set (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

MariaDB [testdb]> call GetItems(5);
+----+-----------+----------------------+-------+-------------+
| id | title     | description          | price | create_date |
+----+-----------+----------------------+-------+-------------+
|  1 | pencilbox | plastic one side box |    40 | 2021-06-12  |
|  3 | sharpener | sharpener            |     6 | 2021-06-11  |
+----+-----------+----------------------+-------+-------------+
2 rows in set (0.001 sec)

# This is how we can view stored-procedure.
MariaDB [(none)]> select * from  information_schema.routines where SPECIFIC_NAME="GetItems";
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+---------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+--------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                                | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                               | ROUTINE_COMMENT | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+---------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+--------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------+----------------------+--------------------+
| GetItems      | def             | testdb         | GetItems     | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | BEGIN
	SELECT * from item where price > cost;
END | NULL          | NULL              | SQL             | NO               | CONTAINS SQL    | NULL     | DEFINER       | 2021-06-12 07:23:43 | 2021-06-12 07:23:43 | IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |                 | root@%  | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+---------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+--------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.004 sec)

<?xml version="1.0" encoding="UTF-8"?>

<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="              http://www.osgi.org/xmlns/blueprint/v1.0.0 https://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd              http://camel.apache.org/schema/blueprint https://camel.apache.org/schema/blueprint/camel-blueprint.xsd">

    <camelContext id="cbr-example-context" xmlns="http://camel.apache.org/schema/blueprint">

        <route id="cbr-route">
            <from id="_from1" uri="timer://foo?fixedRate=true&period=20000"/>
            <setHeader headerName="num1"><constant>6</constant></setHeader>
            <to uri="sql-stored:GetItems(INTEGER ${headers.num1})?dataSource=#dbcp"/>
            <log id="_log5" message="headers: ${headers} Body: ${body}"/>
        </route>
    </camelContext>
    <bean id="dbcp" destroy-method="close"
		class="org.apache.commons.dbcp2.BasicDataSource">
		<property name="driverClassName" value="org.mariadb.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://0.0.0.0:3306/testdb" />
		<property name="username" value="root" />
		<property name="password" value="mypassword" />
	</bean>
</blueprint>

# We have to first build this project with following command, we should run this from the location of pom.xml in project.
[chandrashekhar@localhost Camel-SqlStoredProcedure-Example]$ mvn clean install

# Within [FUSE_HOME]/bin start Red Hat Fuse 7.8
[chandrashekhar@localhost bin]$ ./start
[chandrashekhar@localhost bin]$ ./status
karaf: JAVA_HOME not set; results may vary
Running ...
[chandrashekhar@localhost bin]$ cd ../etc
[chandrashekhar@localhost etc]$ vi users.properties
# uncomment following two lines
admin = admin,_g_:admingroup
_g_\:admingroup = group,admin,manager,viewer,systembundles,ssh
[chandrashekhar@localhost etc]$ cd ../bin
[chandrashekhar@localhost bin]$ ./client
client: JAVA_HOME not set; results may vary
Logging in as admin

 ____          _   _   _       _     _____                
|  _ \ ___  __| | | | | | __ _| |_  |  ___|   _ ___  ___  
| |_) / _ \/ _` | | |_| |/ _` | __| | |_ | | | / __|/ _ \ 
|  _ <  __/ (_| | |  _  | (_| | |_  |  _|| |_| \__ \  __/ 
|_| \_\___|\__,_| |_| |_|\__,_|\__| |_|   \__,_|___/___| 

  Red Hat Fuse (7.8.0.fuse-780038-redhat-00001)
  http://www.redhat.com/products/jbossenterprisemiddleware/fuse/

Hit '<tab>' for a list of available commands
and '[cmd] --help' for help on a specific command.

Open a browser to http://localhost:8181/hawtio to access the management console

Hit '<ctrl-d>' or 'shutdown' to shutdown Red Hat Fuse.

admin@root()> 

admin@root()> install -s mvn:org.apache.commons/commons-dbcp2/2.1.1
Bundle ID: 248
admin@root()> install -s mvn:org.apache.commons/commons-pool2/2.6.2
Bundle ID: 249
admin@root()> feature:install camel-sql
admin@root()> install -s mvn:org.mariadb.jdbc/mariadb-java-client/2.7.3
Bundle ID: 251
admin@root()> install -s mvn:com.mycompany/sql-storedprocedure/1.0
Bundle ID: 252
admin@root()>