|
การทำ Link Server จาก SQL Server เพื่ออ่านเขียนข้อมูลจาก MySQL Database |
การทำ Link Server จาก SQL Server เพื่ออ่านเขียนข้อมูลจาก MySQL Database ในการใช้งานหลาย Database ร่วมกับหลาย ๆ Application ปัญหาที่เราพบอย่างหนึ่งคือ ข้อมูลอยู่คนล่ะชนิดของ Database เมื่อต้องการนำข้อมูลมาใช้งานร่วมกันนั้น ในหลาย ๆ ระบบจะใช้การ Interface ข้อมูลเข้าออกระหว่างกัน เช่น การเขียน โปรแกรมเพื่อสร้าง Text file แล้วค่อยนำไป Import กับอีกระบบหนึ่ง โดยอาจจะใช้การตั้งเป็น Schedule หรือนำไป Import แบบ Manual ปัญหาที่ตามมาก็คือ ข้อมูลเกล่านั้นไม่อัพเดดทันที ต่อความต้องการใช้งาน การตรวจสอบข้อมูลหรือนำข้อมูลไปใช้งานในระบบต่าง ๆ นั้นทำได้ยาก และ ไม่ค่อยสะดวก ซึ่งปัญหานี้ ในกรณีที่เราใช้ Database ของ SQL Server เราสามารถทำ Link Server ไปยัง Database ต่าง ๆ ได้หลายประเภทมาก ไม่ว่าจะเป็น MySQL, Oracle , DB2 หรือ Database อื่น ๆ ที่รองรับการเชื่อมต่อผ่าน Provider ต่าง ๆ เช่น ODBC
SQL Server Link Server to MySQL Database
โดยรูปแบบการทำงานของ Link Server บน SQL Server Database คือ จะสร้าง Connection ผ่าน Driver ต่าง ๆ ที่ใช้กับ Database นั้น ๆ เมื่อเราได้ Link Server เรียบร้อยแล้ว เราจะสามารถทำการ Query ข้อมูลของ Database นั้น ๆ โดยการอ้างถึง Connection และ Schema ของ Database จากนั้นจะทำการ SELECT / INSERT / UPDATE / DELETE ข้อมูลของ Database ปลายทางก็สามารถทำได้ผ่าน Query ที่เขียนบน SQL Server ได้
นอกจากนี้ใน SQL Server ยังสามารถประยุกต์ใช้งาน Link Server ร่วมกับ Stored Procedure ได้ เช่นในระบบ ๆ หนึ่งอยู่บน MySQL Database เราสามารถเขียน Stored Procedure เพื่อดึงข้อมูลแบบ Real-time โดยการ Query หรือจะเป็นพวก Schedule Job ในการ Transfer ข้อมูลระหว่างกันก็ทำได้
สามารถดูตัวอย่างการตั้ง Schedule บน SQL Server ได้จากบทความนี้
ตัวอย่างการทำ Link Server จาก SQL Server ไปยัง MySQL Database
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145213.jpg?v=1001)
เป็น Table ตารางที่อยู่บน MySQL Database
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145218.jpg?v=1001)
ในการเชื่อมต่อจาก SQL Server ไปยัง MySQL จะต้องติดตั้ง MyODBC ให้เรียบร้อยก่อน ส่วน Version นั้นใช้อะไรก็ได้ แต่ตอนที่เรียกใช้จะต้องอ้างชื่อ Driver ให้ถูกต้อง
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145223.jpg?v=1001)
ในการดูว่า Driver ที่ติดตั้งชื่ออะไร สามารถเปิดดูได้ที่ Odbc แล้วลอง Add ดูว่ามีรายการ Driver ที่เราได้ทำการติดตั้งลงไปหรือไม่
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145228.jpg?v=1001)
กลับมาที่ SQL Server ให้คลิกขวาที่ Link Server -> New Link Server
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145233.jpg?v=1001)
สิ่งที่จะต้องกรอกข้อมูลคือ
Link Server: ชื่อ Link Server เป็นชื่อ Connection ที่เราจะเรียกใช้งาน
Provider: Microsoft OLE DB Provider for ODBC Drivers
Project name: MySQL
Provider string: DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=mydatabase;USER=root;PASSWORD=;
ให้ระบุชื่อ Server , User, Password และ Database ของ MySQL ให้ถูกต้อง
ในการสร้าง Link Server สามารถใช้คำสั่ง SQL สร้างได้เช่นเดียวกัน
EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL_DB',
@srvproduct=N'MySQL',
@provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.3 Unicode Driver};
SERVER=localhost;
DATABASE=mydatabase;
USER=root;
PASSWORD=;'
นำคำสั่งนี้ไปใช้ได้เลย
และเมื่อสร้างเรียบร้อยแล้ว
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145240.jpg?v=1001)
เราจะได้ชื่อ Link Server : MYSQL_DB ตามที่ได้ตั้งไว้
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145323.jpg?v=1001)
สามารถทดสอบการเชื่อมต่อ
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145327.jpg?v=1001)
ถ้าสามารถเชื่อมต่อได้ ก็จะแสดงผลดังรูป
Note! การเชื่อมต่อสามารถเชื่อมได้ทั้งภายใน Local (เครื่องเดียวกัน) หรือจะเชื่อมต่อไปยังเครื่องอื่น ๆ ใน Network ทั้งแบบ Intranet (Lan) หรือ Internet แต่จะต้องดูในเรื่องของสิทธิ์การเข้าถึง เครือข่าย / Firewall ด้วย
Sample 1 : เขียน Query บน SQL Server เพื่ออ่านข้อมูล SELECT จาก MySQL
SELECT * FROM OPENQUERY (MYSQL_DB, 'SELECT * FROM mydatabase.customer')
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145333.jpg?v=1001)
จากตัวอย่างเป็นการเชื่อมต่อและ Query ไปยัง MYSQL_DB (Link Server) และการอ้างถึง Database และ Table
Sample 3 การ Insert ข้อมูลลงใน Database ของ MySQL ที่เป็น Link Server
INSERT OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer ')
VALUES ('Column1','Column1','Column1','Column1','Column1','Column1');
INSERT INTO OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer ')
SELECT 'Column1','Column1','Column1','Column1','Column1','Column1';
สามารถทำการ Insert และ Select ค่าที่เป็น Value ได้เลย เช่น SELECT 'C005','Name...',... หรือจะอ้างจาก Table
Sample 3 : การเขียน Query เพื่อ Copy (Insert) ข้อมูลจาก MySQL มาเก็บไว้ที่ SQL Server
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145339.jpg?v=1001)
ในตอนนี้ที่ SQL Server มีตาราง CUSTOMER และมีข้อมูลแค่รายการเดียว
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
SELECT * FROM OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer');
เขียน Query เพื่อ Insert ข้อมูลลงใน SQL Server โดยอ่านจาก Link Server ของ MySQL
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145344.jpg?v=1001)
ข้อมูลที่ถูก Insert ลงใน SQL Server Database
Sample 4 : การเขียน Query เพื่อ Copy (Insert) ข้อมูลจาก SQL Server มาเก็บไว้ที่ MySQL
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145352.jpg?v=1001)
ในขณะเดียวกัน เราก็สามารถที่จะเขียน Query ข้อมูลจาก SQL Server แล้วไป Insert ที่ MySQL Database ได้
INSERT INTO OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer')
SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM CUSTOMER WHERE CUSTOMER_ID = 'C006';
เขียน Query เพื่อเลือกข้อมูลในรหัสที่ต้องการ
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145431.jpg?v=1001)
ข้อมูลใหม่ที่ถูก Insert เข้าไป
นอกจากนี้ยังสามารถใช้การเขียนแบบ SUB QUERY เช่น IN / NOT IN เพื่อดูว่าข้อมูลระหว่าง 2 ตัวนี้มี Diff กันหรือไม่
INSERT INTO OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer')
SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM CUSTOMER
WHERE CUSTOMER_ID NOT IN (SELECT * FROM OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID FROM mydatabase.customer'));
![SQL Server Link Server to MySQL SQL Server Link Server to MySQL](https://www.thaicreate.com/upload/stock/20160504145438.jpg?v=1001)
เลือก Insert ข้อมูล ที่ไม่มีอยู่ในอีกฝั่ง
Sample 5 : การเขียน Query เพื่อ DELETE ลบที่อยู่บน MySQL (Link Server)
DELETE OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer ') WHERE CUSTOMER_ID = 'C005'
หรือ
DELETE OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer WHERE CUSTOMER_ID = ''C006'' ')
Sample 6 : การเขียน Query เพื่อ UPDATE แก้ไขที่อยู่บน MySQL (Link Server)
UPDATE OPENQUERY (MYSQL_DB, 'SELECT NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer WHERE CUSTOMER_ID = ''C005'' ') SET NAME = 'Toon Sangsuree';
Sample 7 : การเขียน Stored Procedure เพื่อทำงานร่วมกับ Cursor
-- Declare Variable
DECLARE @sCustomer VARCHAR(4);
DECLARE @sName VARCHAR(100);
DECLARE @sEmail VARCHAR(100);
DECLARE @sCountryCode VARCHAR(2);
DECLARE @sBudget DECIMAL(18,2);
DECLARE @sUsed DECIMAL(18,2);
-- Declare cursor
DECLARE cursor_customer CURSOR FOR
SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED
FROM OPENQUERY (MYSQL_DB, 'SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer')
-- Open cursor
OPEN cursor_customer
FETCH NEXT FROM cursor_customer
INTO @sCustomer, @sName, @sEmail, @sCountryCode, @sBudget, @sUsed;
-- Loop From Cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '@sCustomer = ' + @sCustomer;
PRINT '@sName = ' + @sName;
PRINT '@sEmail = ' + @sEmail;
PRINT '@sCountryCode = ' + @sCountryCode;
PRINT '@sBudget = ' + CAST(@sBudget AS VARCHAR);
PRINT '@sUsed = ' + CAST(@sUsed AS VARCHAR);
FETCH NEXT FROM cursor_customer -- Fetch next cursor
INTO @sCustomer, @sName, @sEmail, @sCountryCode, @sBudget, @sUsed;
END
-- Close cursor
CLOSE cursor_customer;
DEALLOCATE cursor_customer;
ในกรณีที่ใช้ Cursor ร่วมกับ ตัวแปรที่ส่งไปกับ WHERE ให้ใช้
-- Declare cursor
DECLARE @TSQL NVARCHAR(1000);
SET @TSQL = 'DECLARE cursor_customer CURSOR FOR SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM OPENQUERY(MYSQL_DB,''SELECT CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED FROM mydatabase.customer WHERE COUNTRY_CODE = ''''' + @sCountry + ''''' '')'
EXEC SP_EXECUTESQL @TSQL
-- Open cursor
OPEN cursor_customer
FETCH NEXT FROM cursor_customer
INTO @sCustomer, @sName, @sEmail, @sCountryCode, @sBudget, @sUsed;
|
|
|
|
![](/images/point.jpg) |
|