Returning a list of SQL Servers in VFP

Posted on January 30, 2005 20:45 by Craig Bailey
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



Digg It!DZone It!StumbleUponTechnoratiRedditDel.icio.usNewsVineFurlBlinkList


Categories:
Actions: E-mail | Comments (0)

Add comment


(Will show your Gravatar icon)  

  Country flag

biuquote
  • Comment
  • Preview
Loading