How to Connect to SQL Server from Visual FoxPro

Handling Connection Errors

Both the SQLConnect() and SQLStringConnect() functions return a connection handle. If
the connection is established successfully, the handle will be a positive integer. If Visual FoxPro failed to make the connection, the handle will contain a negative integer. A simple
call to the AERROR() function can be used to retrieve the error number and  message. The following example traps for a failed connection and displays the error number and message using the Visual FoxPro MESSAGEBOX() function.

Visual FoxPro returns error 1526 for all errors against a remote data source. The fifth element of the array returned by AERROR() contains the remote data source-specific error.

#define MB_OKBUTTON 0

#define MB_STOPSIGNICON 16

LOCAL hConn

hConn = SQLConnect("ODBCNorthwind", "falseuser", "")

IF (hConn < 0)

LOCAL ARRAY laError[1]

AERROR(laError)

MESSAGEBOX( ;

laError[2], ;

MB_OKBUTTON + MB_STOPSIGNICON, ;

"Error " + TRANSFORM(laError[5]))

ENDIF

Disconnecting From SQL Server

It is very important that a connection be released when it is no longer needed by the application because connections consume valuable resources on the server, and the number of connections may be limited by licensing constraints.

You break the connection to the remote data source using the SQLDisconnect() function. SQLDisconnect() takes one parameter, the connection handle created by a call to either SQLConnect() or SQLStringConnect(). SQLDisconnect() returns a 1 if the connection was correctly terminated and a negative value if an error occurred.

The following example establishes a connection to SQL Server, and then drops the connection:

LOCAL hConn,lnResult

*hConn = SQLStringConnect("Driver=SQL Server;Server=<SQL2000>;"+ ;

UID=sa;PWD=;Database=Northwind")

hConn = SQLConnect("ODBCNorthwind", "sa", "")

IF (hConn > 0)

MESSAGEBOX("Connection has done")

lnResult = SQLDisconnect(hConn)

IF lnResult < 0

MESSAGEBOX("Disconnect failed")

ENDIF && lnResult < 0

ENDIF && hConn > 0

If the parameter supplied to SQLDisconnect() is not a valid connection handle, Visual FoxPro will return a run-time error (#1466). Currently there is no way to determine whether a connection handle is valid without attempting to use it.

To disconnect all SQL pass through connections, you can pass a value of zero to SQLDisconnect().

Published with the express written permission of the author. Copyright 2003.