.::: Create Linked Server MSSQL to use Open Query (MariaDB/MySQL) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
system DSN > Add >> MySQL ODBC 5.3 ANSI Driver

Connection Parameters

Data Source Name: dbatka

TCP/IP Server: teguhth-otherserver port 3306

user: root

password: []

Database: teguhth

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server

General
Linked Server: maria
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: maria
Data Source: dbatka

Security
Be made using the seciroty context
remote login: root
With password: []

Server Options
RPC & RPC Out: True

B. Select Query Using Normal Query and Open Query
1. Normal Query - insert
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);

insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');

insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');

insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);

insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','2002-05-20',3);

select * from barang where KODE_BARANG ='ELK-99';
select * from suplier where KODE_SUPLIER ='EJ-99';
select * from customer where KODE_CUSTOMER ='J-0099';
select * from pasok where KODE_PASOK ='PAS-E099';
select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

2. Open Query - insert
insert into openquery (maria, 'select * from barang') VALUES  ('ELK-99','Barang - After Migration','BUAH',20); 
insert into openquery (maria, 'select * from suplier') values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into openquery (maria, 'select * from customer') values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into openquery (maria, 'select * from pasok') values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);
insert into openquery (maria, 'select * from pembelian') values('BEL-E099','ELK-99','J-0099','2002-05-20',3);
-- or 
select * from openquery(maria,'select * from barang') where KODE_BARANG ='ELK-99';
select * from openquery(maria,'select * from suplier') where KODE_SUPLIER ='EJ-99';
select * from openquery(maria,'select * from customer') where KODE_CUSTOMER ='J-0099';
select * from openquery(maria,'select * from pasok') where KODE_PASOK ='PAS-E099';
select * from openquery(maria,'select * from pembelian') where KODE_PEMBELIAN ='BEL-E099';
-- or
select * from openquery(maria,'select * from barang where KODE_BARANG =''ELK-99''');
select * from openquery(maria,'select * from suplier where KODE_SUPLIER =''EJ-99''');
select * from openquery(maria,'select * from customer where KODE_CUSTOMER =''J-0099''');
select * from openquery(maria,'select * from pasok where KODE_PASOK =''PAS-E099''');
select * from openquery(maria,'select * from pembelian where KODE_PEMBELIAN =''BEL-E099''');

C. Update Query
1. Normal Query - update
update barang set nama_barang='Barang OpenQuery' where KODE_BARANG ='ELK-99';
select * from barang

2. Open Query - update
update openquery(maria,'select * from barang where KODE_BARANG =''ELK-99''') set nama_barang='Barang OpenQuery'  ;
select * from openquery(maria,'select * from barang where KODE_BARANG =''ELK-99''');

D. Delete Query 
1. Normal Query - delete
delete from pembelian where KODE_BARANG ='ELK-99';
delete from pasok where KODE_PASOK ='PAS-E099';
delete from barang where KODE_BARANG ='ELK-99';

2. Open Query - delete
delete from openquery (maria,'select * from barang') ;
delete from openquery (maria,'select * from barang ') where NAMA_BARANG='KOMPUTER' ;

--foreign key must be delete first before delete primary key
-- table pembelian
delete from openquery (maria,'select * from pembelian ') where KODE_BARANG ='ELK-99' ;
-- or
delete from openquery (maria,'select * from pembelian where KODE_BARANG =''ELK-99''')  ;
-- table pasok
delete from openquery (maria,'select * from pasok ') where KODE_PASOK ='PAS-E099' ;
delete from openquery (maria,'select * from pasok where KODE_PASOK =''PAS-E099''') ;
--foreign key must be delete first before delete primary key
delete from openquery (maria,'select * from barang ') where KODE_BARANG ='ELK-99' ;
select * from openquery(maria,'select * from barang where KODE_BARANG =''ELK-99''');