.::: Create table DbLink MariaDB MySQL using ODBC/DSN using metode SRCDEF base on query :::.

1. Install Connect Engine
yum install MariaDB-connect-engine -y

2. Check ODBC INI

[root@teguhth ~]# cat /etc/odbc.ini

[edbdb]
Description         = PostgreSQL connection to SallyDB
Driver              = PostgreSQL
Database            = teguhth
Servername          = 10.10.10.8
UserName            = admin
Password            = admin
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
#ConnSettings        =
 
[MariaDBDSN]
Description = My MariaDB DSN
Driver      = MariaDB
Server      = 10.10.10.8
Port        = 3306
Database    = teguhth
User        = admin
Password    = admin
Option      = 3

[root@teguhth ~]#

3. create database srcdefdb

MariaDB [edb]> create database srcdefdb;
Query OK, 1 row affected (0.002 sec)

MariaDB [edb]>
MariaDB [edb]> use srcdefdb
Database changed
MariaDB [srcdefdb]> show tables;
Empty set (0.000 sec)

MariaDB [srcdefdb]>

4. create table
CREATE TABLE srcdefdb.table_srcdef_maria ENGINE = CONNECT TABLE_TYPE=ODBC  CONNECTION='DSN=MariaDBDSN' SRCDEF='select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;';

5. show create table

CREATE TABLE `table_srcdef_maria` (
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `NAMA_SUPLIER` varchar(30) DEFAULT NULL,
  `TANGGAL_PASOK` datetime DEFAULT NULL,
  `JUMLAH_PASOK` decimal(4,0) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=MariaDBDSN' `TABLE_TYPE`='ODBC' `SRCDEF`='select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;'

6. select

select * from srcdefdb.table_srcdef_maria;

MariaDB [srcdefdb]> select * from srcdefdb.table_srcdef_maria;
+-------------+---------------------+---------------------+--------------+
| NAMA_BARANG | NAMA_SUPLIER        | TANGGAL_PASOK       | JUMLAH_PASOK |
+-------------+---------------------+---------------------+--------------+
| RICE COOKER | PT ACTRON           | 2002-01-01 00:00:00 |            8 |
| RICE COOKER | PT MULYA ELEKTRONIK | 2002-01-01 00:00:00 |            5 |
| LEMARI ES   | PT ACTRON           | 2002-02-01 00:00:00 |            2 |
| LEMARI ES   | PT MULYA ELEKTRONIK | 2002-02-02 00:00:00 |            3 |
| LEMARI ES   | PT SUPERTRON        | 2002-01-01 00:00:00 |            2 |
| TELEVISI    | PT ACTRON           | 2002-03-03 00:00:00 |            5 |
| TELEVISI    | PT ACTRON           | 2002-03-04 00:00:00 |            2 |
| TELEVISI    | PT ACTRON           | 2002-03-03 00:00:00 |            3 |
| TELEVISI    | PT ULTRASOUND       | 2002-03-13 00:00:00 |            4 |
| TELEVISI    | PT ULTRASOUND       | 2002-03-13 00:00:00 |            3 |
| RADIO/TAPE  | PT ULTRASOUND       | 2002-04-22 00:00:00 |           12 |
| RADIO/TAPE  | PT SUPERTRON        | 2002-04-30 00:00:00 |            9 |
+-------------+---------------------+---------------------+--------------+
12 rows in set (0.018 sec)

MariaDB [srcdefdb]>