Returning a list of SQL Servers in VFP

Posted on January 31, 2005. Filed under: Visual FoxPro |

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

Advertisements

Make a Comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: