.::: Create a dblink to a remote server MariaDB MySQL MarinaDB in Linux to MSSQL SQL Server 2022 in Windows Server 2022 using ha_connect,unixODBC,odbcinst.ini :::.

A. Install & Configuration DBLink
1. install haconnect in Linux Redhat

before 

http://teguhth.blogspot.com/2024/01/install-configure-odbcinstini-odbc-for.html
yum install MariaDB-connect-engine -y

[root@teguhth ~]# yum install MariaDB-connect-engine -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.cepatcloud.id
 * epel: mirror2.totbb.net
 * extras: mirror.cepatcloud.id
 * updates: mirror.cepatcloud.id
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-connect-engine.x86_64 0:10.11.6-1.el7.centos will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved

==================================================================================================================================================================
 Package                                      Arch                         Version                                       Repository                          Size
==================================================================================================================================================================
Installing:
 MariaDB-connect-engine                       x86_64                       10.11.6-1.el7.centos                          mariadb-main                       2.6 M

Transaction Summary
==================================================================================================================================================================
Install  1 Package

Total download size: 2.6 M
Installed size: 4.1 M
Downloading packages:
MariaDB-connect-engine-10.11.6-1.el7.centos.x86_64.rpm             27% [===============-                                        ] 2.3 kB/s | 740 kB  00:14:05 ETA
MariaDB-connect-engine-10.11.6-1.el7.centos.x86_64.rpm             29% [================-                                       ] 1.7 kB/s | 789 kB  00:17:54 ETA
MariaDB-connect-engine-10.11.6-1.el7.centos.x86_64.rpm             61% [==================================                      ] 2.7 kB/s | 1.6 MB  00:06:14 ETA
MariaDB-connect-engine-10.11.6-1.el7.centos.x86_64.rpm                                                                                     | 2.6 MB  00:09:10
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-connect-engine-10.11.6-1.el7.centos.x86_64                                                                                             1/1
unknown
  Verifying  : MariaDB-connect-engine-10.11.6-1.el7.centos.x86_64                                                                                             1/1

Installed:
  MariaDB-connect-engine.x86_64 0:10.11.6-1.el7.centos

Complete!
[root@teguhth ~]#


2. create linked server & check

CREATE SERVER dblink_mariamssql
                FOREIGN DATA WRAPPER `mssql`
                OPTIONS (
                USER 'admin',
                PASSWORD 'admin',
                HOST '10.10.10.7',
                port 1433,
                DATABASE 'teguhth');
                
or

CREATE SERVER dblink_mariamssql FOREIGN DATA WRAPPER `mssql` OPTIONS (USER 'admin',PASSWORD 'admin',HOST '10.10.10.7',port 1433,DATABASE 'teguhth');             
                
MariaDB [dblink]> select * from mysql.servers;
+-------------------+------------+---------+----------+----------+------+--------+---------+-------+
| Server_name       | Host       | Db      | Username | Password | Port | Socket | Wrapper | Owner |
+-------------------+------------+---------+----------+----------+------+--------+---------+-------+
| dblink_mariamssql | 10.10.10.7 | teguhth | admin    | admin    |    0 |        | mssql   |       |
+-------------------+------------+---------+----------+----------+------+--------+---------+-------+
1 row in set (0.000 sec)

MariaDB [dblink]>
 

3. create table link

CREATE TABLE dblink.barang_mssql1 ENGINE = CONNECT TABLE_TYPE=ODBC TABNAME='barang' CONNECTION='Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1;Server=10.10.10.7;Database=teguhth;UID=admin;PWD=admin;TrustServerCertificate=yes';

MariaDB [dblink]> show tables;
+------------------+
| Tables_in_dblink |
+------------------+
| barang_mssql     |
+------------------+
1 row in set (0.000 sec)

MariaDB [dblink]> show create table barang_mssql;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| barang_mssql | CREATE TABLE `barang_mssql` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(6,0) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1;Server=10.10.10.7;Database=teguhth;UID=sa;PWD=P@ssw0rd;TrustServerCertificate=yes;' `TABLE_TYPE`='ODBC' `TABNAME`='barang' |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [dblink]>
MariaDB [dblink]>
 

4. select 

select * from dblink.barang_mssql;
MariaDB [dblink]> select * from dblink.barang_mssql;
+-------------+-------------+---------------+-------------+
| 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 |
| ELK-07      | RICE COOKER | BUAH          |          20 |
| ELK-08      | RICE COOKER | BUAH          |          20 |
| ELK-09      | KIPAS ANGIN | BUAH          |          38 |
| ELK-10      | KIPAS ANGIN | BUAH          |          38 |
| ELK-11      | KIPAS ANGIN | BUAH          |          38 |
| ELK-12      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
12 rows in set (0.047 sec)

B. Testing After create  DBLink
1. test delete in datasource mssql

## from MSSQL Side
select @@servername,@@version
select * from teguhth.dbo.barang
delete from barang where KODE_BARANG='ELK-07'
select * from teguhth.dbo.barang
 

2. check from MariaDB
select @@hostname,@@version
select * from dblink.barang_mssql
 

3. test delete in datasource mssql

select @@servername,@@version
select * from teguhth.dbo.barang
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-20','KIPAS ANGIN','BUAH',38);
select * from teguhth.dbo.barang
 

4. check from MariaDB
select @@hostname,@@version
select * from dblink.barang_mssql