| |
การทำ 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
เป็น Table ตารางที่อยู่บน MySQL Database
ในการเชื่อมต่อจาก SQL Server ไปยัง MySQL จะต้องติดตั้ง MyODBC ให้เรียบร้อยก่อน ส่วน Version นั้นใช้อะไรก็ได้ แต่ตอนที่เรียกใช้จะต้องอ้างชื่อ Driver ให้ถูกต้อง
ในการดูว่า Driver ที่ติดตั้งชื่ออะไร สามารถเปิดดูได้ที่ Odbc แล้วลอง Add ดูว่ามีรายการ Driver ที่เราได้ทำการติดตั้งลงไปหรือไม่
กลับมาที่ SQL Server ให้คลิกขวาที่ Link Server -> New Link Server
สิ่งที่จะต้องกรอกข้อมูลคือ
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=;'
นำคำสั่งนี้ไปใช้ได้เลย
และเมื่อสร้างเรียบร้อยแล้ว
เราจะได้ชื่อ Link Server : MYSQL_DB ตามที่ได้ตั้งไว้
สามารถทดสอบการเชื่อมต่อ
ถ้าสามารถเชื่อมต่อได้ ก็จะแสดงผลดังรูป
Note! การเชื่อมต่อสามารถเชื่อมได้ทั้งภายใน Local (เครื่องเดียวกัน) หรือจะเชื่อมต่อไปยังเครื่องอื่น ๆ ใน Network ทั้งแบบ Intranet (Lan) หรือ Internet แต่จะต้องดูในเรื่องของสิทธิ์การเข้าถึง เครือข่าย / Firewall ด้วย
Sample 1 : เขียน Query บน SQL Server เพื่ออ่านข้อมูล SELECT จาก MySQL
SELECT * FROM OPENQUERY (MYSQL_DB, 'SELECT * FROM mydatabase.customer')
จากตัวอย่างเป็นการเชื่อมต่อและ 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 มีตาราง 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
ข้อมูลที่ถูก Insert ลงใน SQL Server Database
Sample 4 : การเขียน Query เพื่อ Copy (Insert) ข้อมูลจาก SQL Server มาเก็บไว้ที่ MySQL
ในขณะเดียวกัน เราก็สามารถที่จะเขียน 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 เพื่อเลือกข้อมูลในรหัสที่ต้องการ
ข้อมูลใหม่ที่ถูก 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'));
เลือก 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;
|
Load balance : Server 05
