โค้ดการค้นหา Instance Name ของ MS SQL Server ผ่านทาง API (ไม่ใช้ SQLDMO)

"DSN=Sales;HOST=red;UID=Smith;PWD=Sesame;DATABASE=SalesOrders"

#define BRWS_LEN 100
SQLHENV  henv;
SQLHDBC  hdbc;
SQLHSTMT   hstmt;
SQLRETURN  retcode;
SQLCHAR  szConnStrIn[BRWS_LEN], szConnStrOut[BRWS_LEN];
SQLSMALLINT  cbConnStrOut;

/* Allocate the environment handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);       
   
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
   /* Set the version environment attribute. */
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0);

   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      /* Allocate the connection handle. */
      retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

      /* Call SQLBrowseConnect until it returns a value other than */
      /* SQL_NEED_DATA (pass the data source name the first time). */
      /* If SQL_NEED_DATA is returned, call GetUserInput (not  */
      /* shown) to build a dialog from the values in szConnStrOut. */
      /* The user-supplied values are returned in szConnStrIn,   */
      /* which is passed in the next call to SQLBrowseConnect.   */

         lstrcpy(szConnStrIn, "DSN=Sales");
         do {
            retcode = SQLBrowseConnect(hdbc, szConnStrIn, SQL_NTS,
                     szConnStrOut, BRWS_LEN, &cbConnStrOut);
            if (retcode == SQL_NEED_DATA)
               GetUserInput(szConnStrOut, szConnStrIn);
         } while (retcode == SQL_NEED_DATA);

         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){

            /* Allocate the statement handle. */
            retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            /* Process data after successful connection */
               ...;
               ...;
               ...;
               SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
            }
            SQLDisconnect(hdbc);
         }
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   }
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);

อันดับแรกที่ต้องกล่าวถึงซ่ะก่อนเลยว่า งานนี้เป็นการเรียกใช้งาน หรือ Interface ผ่านทาง ODBC หรือ Open Database Connectivity (ODBC) ซึ่งถือได้ว่าเจ้า ODBC นี้คือสิ่งที่เก่า เต่า โบราณล้าสมัยอย่างมาก 55555+ เพราะเป็นการเชื่อมต่อระบบฐานข้อมูล (DBMS) ที่จัดได้ว่า "ช้าที่สุด" เท่าที่ยังคงมีใช้งานกันอยู่ในยุคสมัยนี้ ... นั่นคือ ถึงมันจะเก่า แต่ยังไม่เก็บ เราก็นำมันมาใช้งานให้เกิดประโยชน์ได้ล่ะครับ ... พี่น้อง

เมื่อทำการระบุ SQL Server Driver (ในที่นี้คือ SQL Server เท่านั้น) และเรียกใช้งานฟังค์ชั่น SQLBrowseConnect ตัว Driver Manager จะทำการทดสอบการเชื่อมต่อผ่านทาง ODBC เพื่อทำการคืนค่า Attribute ต่างๆกลับมา ดังนี้ คือ Server, User name, Password, Application Name และ Workstation ID (API มันไปคุยกันเองในระดับล่างแล้วครับ เราสนใจเฉพาะค่าที่ส่งกลับมาให้ก็พอ) ... ในกรณีของ Server Attribute จะแสดงผลรายชื่อของ Server Name ออกมาเท่านั้นครับ ... ลักษณะของการคืนค่ากลับมาจะเป็นดังนี้ (กรณีมี Instance มากกว่า 1)
"SERVER:Server={Server1,Server2,Server3};UID:Login ID=?;PWD:Password=?;*APP:AppName=?;*WSID:WorkStation ID=?;"
ดังนั้นข้อมูลที่เราต้องการ (Instance Name) มันจะอยู่ในช่วงของวงเล็บปีกกาเปิด และ ปิด เท่านั้น ซึ่งเราจะใช้คำสั่ง (หรือ ฟังค์ชั่น) Mid$ เป็นตัวจัดการ ซึ่งจะทำให้ได้ออกมาเป็นชุดดังนี้ คือ
Server1,Server2,Server3
ดังนั้นต้องใช้คำสั่ง (หรือ ฟังค์ชั่น) ในการแยก (Split) Instance Name แต่ละชุดด้วยเครื่องหมาย Comma (,)

ประกอบไปด้วย 2 Machine แต่มี 3 Instance Name
• (local) เป็น MS SQL Server 2008 Professional ในเครื่องของผมเอง (เครื่องนี้มันชื่อนังหมูสับครับ ... 55555+)
• SERVER-XP เป็น Default Instance ของเครื่องทดสอบผม ติดตั้ง MS SQL Server 2000 Enterprise
• SERVER-XP\PAYROLL2008 เป็น Instance ของเครื่องทดสอบผม ติดตั้ง MS SQL Server 2008 Professional

Option Explicit

' การเรียกใช้งาน Win32 API (Application Programming Inteface) ผ่านทาง odbc32.dll

' http://msdn.microsoft.com/en-us/library/ms709270%28VS.85%29.aspx
Private Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv As Long) As Integer

' http://msdn.microsoft.com/en-us/library/ms712455%28v=VS.85%29.aspx
Private Declare Function SQLAllocHandle Lib "odbc32.dll" ( _
    ByVal hType As Integer, _
    ByVal hInput As Long, _
    ByRef phOutput As Long _
    ) As Integer

' http://msdn.microsoft.com/en-us/library/ms714565%28VS.85%29.aspx
Private Declare Function SQLBrowseConnect Lib "odbc32.dll" (ByVal hDbc As Long, _
    ByVal szConnStrIn As String, _
    ByVal cbConnStrIn As Integer, _
    ByVal szConnStrOut As String, _
    ByVal cbConnStrOutMax As Integer, _
    pcbconnstrout As Integer _
    ) As Integer

' พวกนี้ทำหน้าที่ตามชื่อฟังค์ชั่นของมันเลย ... เปิดดูในเว็บ MS หรือแผ่น MSDN ก็ได้ครับ
Private Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) As Integer
Private Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) As Integer
Private Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal hEnv As Long) As Integer

' http://msdn.microsoft.com/en-us/library/ms710123%28VS.85%29.aspx
Private Const SQL_SUCCESS = 0
Private Const SQL_HANDLE_ENV = 1
Private Const SQL_HANDLE_DBC = 2
Private Const SQL_NEED_DATA = 99
Private Const DEFAULT_RESULT_SIZE = 1024

Private Const SQL_DRIVER_STR As String = "DRIVER={SQL Server};"

' เมื่อมีการส่งค่าผ่าน SQLBrowseConnect  ก็จะสามารถค้นหา
' และ แสดงรายละเอียดต่างๆของการเชื่อมต่อ SQL Servers

' #####################################################
' โปรแกรมย่อยในการค้นหา Instance Name ของ SQL Server ... และมีการส่งค่ากลับเป็น String
' #####################################################
Private Function BrowseSQLServer() As String
    
    ' ตัวแปรแจ้งสถานะของการเกิด Error หรือไม่
    Dim Ret As Long
    
    ' Handle ของการ Connect และ Environment
    Dim hDbc As Long
    Dim hEnv As Long
    
    ' กำหนดการเชื่อมต่อแบบ ODBC (Open DataBase Connectivity)
    Dim strConnection As String
    
    ' รับค่า Instance name ของ SQL Server
    Dim strGetServer As String
    
    ' จำนวนความยาวของการ Connect ที่ได้จาก SQLBrowseConnect
    Dim LenConnOutput As Integer
    
    ' เลือกการติดต่อกับ SQL Server เท่านั้น และ เป็นการทดสอบผ่าน ODBC
    strConnection = SQL_DRIVER_STR
    
    ' จองพื้นที่เอาไว้ประมาณ 1024 ตัวอักขระ (น่าจะพอมั้งครับ ... หากไม่พอก็หยอดเหรียญเพิ่มเเอาน่ะ อิอิอิอิอิ)
    strGetServer = Space(DEFAULT_RESULT_SIZE)
    
    ' ประโยชน์ของตัวแปร Ret ที่ผมมักจะมีเสมอในการใช้งาน API คือ
    ' รับค่าจากฟังค์ชั่นของการปฏิบัติตามคำสั่ง แล้วตรวจสอบความผิดพลาดของการทำงานก่อนที่จะไปทำงานอื่นต่อ
    ' ในส่วนนี้ผมยกเลิกไม่ตรวจสอบน่ะครับ แต่ะมีเอาไว้เพราะเป็นความเคยชินในการเล่นกับไฟ API 55555+
    
    ' เริ่มต้นการตั้งค่าสภาพแวดล้อม (Environment Attribute) ให้กับ SQL Server ผ่านทาง ODBC
    Ret = SQLAllocEnv(hEnv)
    ' หรือแบบนี้ก็ได้ Call SQLAllocEnv(hEnv)
    ' ปกติเราต้องมาเขียนดัก Error ก่อนด้วย หากเกิดข้อผิดพลาดขึ้นมา เช่น
    'If Ret <> SQL_SUCCESS Then ... หากมันไม่ได้รับค่ากลับเป็น 0 แสดงว่าเกิด Error
    
    ' จับจองตำแหน่งของการเชื่อมต่อของ ODBC (หากค่า Ret <> 0 แสดงว่าเกิดปัญหา)
    Ret = SQLAllocHandle(SQL_HANDLE_DBC, ByVal hEnv, hDbc)
    
    ' เรียก SQLBrowseConnect เพื่อขอข้อมูลในการเชื่อมต่อผ่านทาง ODBC
    Ret = SQLBrowseConnect( _
        ByVal hDbc, _
        strConnection, _
        Len(strConnection), _
        strGetServer, _
        Len(strGetServer) + 2, _
        LenConnOutput _
        )
    ' ปกติเราต้องมาเขียนดัก Error ก่อนด้วย หากเกิดข้อผิดพลาดขึ้นมา เช่น
    'If Ret <> SQL_NEED_DATA Then ... หากมันไม่ได้รับค่ากลับเป็น 99 แสดงว่าเกิด Error
    
    ' เมื่อสั่ง SQLBrowseConnect จะส่งค่า Instance name และ ความยาว (LenConnOutput)
    strGetServer = Left(strGetServer, LenConnOutput)
    
    ' ตัวแปรที่ต้องทำการตัดค่าออกไปบางส่วน
    Dim chrFirst As Integer
    Dim chrLast As Integer
    
    ' ตัวอย่างของการรับค่ามาจาก strGerServer ... ลอง Debug ดูที่เครื่องคุณเองด้วยน่ะครับ
    ' SERVER:Server={(local),SERVER-XP,SERVER-XP\PAYROLL2008}; -->ต่อท้ายบรรทัดด้านล่าง
    ' UID:Login ID=?;PWD:Password=?;*APP:AppName=?;*WSID:WorkStation ID=?
    ' Debug.Print strGetServer
    
    ' เราต้องการข้อมูลเฉพาะตัวอักขระตามหลังเครื่องหมายปีกกาเปิด ... ในที่นี้คือ ก่อนวงเล็บ (local)
    ' เจอเครื่องหมายปีกกาเปิดที่ลำดับที่ 15 (ให้บวกขึ้น 1 เป็นจุดเริ่มต้นของการนับ)
    chrFirst = InStr(1, strGetServer, "{") + 1
    
    ' ตัวสุดท้ายที่ต้องการ คือ สิ้นสุดที่เครื่องหมายปีกกาปิด ตรง 2008}
    ' เจอเครื่องหมายปีกกาปิดที่ลำดับ 55
    chrLast = InStr(1, strGetServer, "}")
    
    ' เตรียมส่งค่าคืนกลับไป ... โดยผมเขียนโค้ดแบบให้มองเห็นง่ายๆ
    'BrowseSQLServer = Mid$(strGetServer, ตำแหน่งปีกกาเปิด, ตำแหน่งปีกกาปิด - ตำแหน่งปีกกาเปิด)
    BrowseSQLServer = Mid$(strGetServer, chrFirst, chrLast - chrFirst)
    ' หากเอาแบบยาก ... เขาจะได้เรียกเราว่าฝีมือ เอิ๊กๆๆๆๆ
    'BrowseSQLServer = Mid$(strGetServer, InStr(1, strGetServer, "{") + 1, _
        InStr(1, strGetServer, "}") - InStr(1, strGetServer, "{") - 1)
    
    ' ฟังค์ชั่น SQLDisconnect เรียกใช้งานเมื่อ SQLBrowseConnect ทำงานเสร็จสิ้น
    ' เพื่อตัดการเชื่อมต่อทาง ODBC ทั้งหมด แล้วคืนค่าหน่วยความจำกลับคืนระบบปฏิบัติการ
    Call SQLDisconnect(hDbc)
    Call SQLFreeEnv(hEnv)
    Call SQLFreeConnect(hDbc)

End Function

Private Sub cmdBrowse_Click()
    Dim Count As Integer
    
    ' ตัวแปรแยก Instance name ออกจากกันเข้าสู่ Array
    Dim strField() As String
    
    ' ไปฟังค์ชั่นตรวจสอบการ Connect ของ SQL Server
    ' แล้วแยกแต่ละส่วน หรือ SQL Server Instance นั่นเอง ออกจากเครื่องหมาย Comma (,)
    strField() = Split(BrowseSQLServer, ",")

    cmbSQLServer.Clear
    
    For Count = 0 To UBound(strField)
        cmbSQLServer.AddItem Trim$(strField(Count))
    Next
    
    cmbSQLServer.ListIndex = 0
    
    MsgBox "ค้นหา SQL Server Instance พบ " & cmbSQLServer.ListCount & _
        " รายการ.", vbOKOnly + vbInformation, "รายงานสถานะ"

End Sub

Private Sub Form_Load()
    Me.Move (Screen.Width - Me.Width) \ 2, (Screen.Height - Me.Height) \ 2
    cmbSQLServer.Clear
End Sub