Returning a list of SQL Servers in VFP

R

Here’s a handy bit of code for returning a list of available SQL Servers.

I grabbed it out of the VFP9 Data Sources task pane code.

It always works if I’m on a network, but I’ve had mixed results if working standalone.



#define SQL_HANDLE_ENV 1

#define SQL_HANDLE_DBC 2

#define SQL_ATTR_ODBC_VERSION 200

#define SQL_OV_ODBC3 3

#define SQL_SUCCESS 0

#define SQL_NEED_DATA 99

#define DEFAULT_RESULT_SIZE 2048

#define SQL_DRIVER_STR "DRIVER=SQL SERVER";

LOCAL hEnv

LOCAL hConn

LOCAL cInString

LOCAL cOutString

LOCAL nLenOutString

LOCAL ARRAY aServerList[1]

DECLARE SHORT SQLBrowseConnect IN odbc32 ;

    INTEGER ConnectionHandle, ;

    STRING InConnectionString, ;

    INTEGER StringLength1, ;

    STRING @ OutConnectionString, ;

    INTEGER BufferLength, ;

    INTEGER @ StringLength2Ptr

DECLARE SHORT SQLAllocHandle IN odbc32 ;

    INTEGER HandleType, ;

    INTEGER InputHandle, ;

    INTEGER @ OutputHandlePtr

DECLARE SHORT SQLFreeHandle IN odbc32 ;

    INTEGER HandleType, ;

    INTEGER Handle

   

DECLARE SHORT SQLSetEnvAttr IN odbc32 ;

    INTEGER EnvironmentHandle, ;

    INTEGER Attribute, ;

    INTEGER ValuePtr, ;

    INTEGER StringLength

hEnv = 0

hConn = 0

cInString = SQL_DRIVER_STR

cOutString = SPACE(DEFAULT_RESULT_SIZE)

nLenOutString = 0

LOCAL cServerList

cServerList = ”

TRY

    IF SQLAllocHandle(SQL_HANDLE_ENV, hEnv, @hEnv) == SQL_SUCCESS

        IF (SQLSetEnvAttr(hEnv,

SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0)) == SQL_SUCCESS

            IF

SQLAllocHandle(SQL_HANDLE_DBC, hEnv, @hConn) == SQL_SUCCESS

               

IF (SQLBrowseConnect(hConn, @cInString, LEN(cInString), @cOutString,

DEFAULT_RESULT_SIZE, @nLenOutString)) == SQL_NEED_DATA

                   

nCnt = ALINES(aServerList, STREXTRACT(cOutString, ‘{‘, ‘}’), .T., ‘,’)

                   

FOR i = 1 TO nCnt

                       

cServerList = cServerList + ‘,’ + ALLTRIM(aServerList[i])

                   

ENDFOR

               

ENDIF

            ENDIF

        ENDIF

    ENDIF

CATCH TO oException

    * ignore error, just return an empty string of servers

FINALLY

    IF hConn <> 0

        SQLFreeHandle(SQL_HANDLE_DBC, hConn)

    ENDIF

    IF hEnv <> 0

        SQLFreeHandle(SQL_HANDLE_ENV, hConn)

    ENDIF

ENDTRY

?cServerList

Add comment

About Craig

I'm the co-host of HubShots and the CEO of XEN - helping mid-large B2B companies with their digital marketing and lead generation.

Craig Bailey

Archives

Posts