.::: Create a dblink to a remote server MariaDD MySQL to PostgreSQL EDB and using metode SRCDEF :::.

1. install ODBC postgresql * configure

http://teguhth.blogspot.com/2024/01/install-postgresql-odbc-driver-on-linux.html

A. using dblink

1. create table dblink example table barang
CREATE TABLE table_edb_barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG)) ENGINE = CONNECT TABLE_TYPE=ODBC TABNAME='barang' CONNECTION='DSN=edbdb';

 
2. check output table_edb_barang

select * from edb.table_edb_barang;

MariaDB [edb]> select * from edb.table_edb_barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01      | RICE COOKER | BUAH          |          20 |
| ELK-02      | LEMARI ES   | UNIT          |           8 |
| ELK-03      | TELEVISI    | UNIT          |          30 |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 |
| ELK-05      | KOMPUTER    | UNIT          |          28 |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.014 sec)

MariaDB [edb]>
 

B. create table SRCDEF to connect edb

1. create table srcdef
CREATE TABLE table_edb_srcdef ENGINE = CONNECT TABLE_TYPE=ODBC  
CONNECTION='DSN=edbdb' 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;';

show create table table_edb_srcdef;
 

2. check output

select * from edb.table_edb_srcdef;
MariaDB [edb]> select * from edb.table_edb_srcdef;
+-------------+---------------------+---------------------+--------------+
| 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.014 sec)

MariaDB [edb]>