How to Connect to SQL Server from Visual FoxPro

There are two functions that can be used to establish a connection with the a remote SQL Server from Visual FoxPro:

* SQLConnect()
* SQLStringConnect()
The SQLConnect() Function

There are two ways to use the SQLConnect() function to connect to a remote data source, such as SQL Server. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the Control Panel.

The following example creates a connection to a remote server using the ODBCNorthwind DSN:

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

The second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command. The CREATE CONNECTION command stores the metadata that Visual FoxPro needs to connect to a remote data source.

The following example creates a Visual FoxPro connection named Northwind and then connects to the database described by the connection:

LOCAL hConn
CREATE DATABASE cstemp
CREATE CONNECTION Northwind DATASOURCE "ODBCNorthwind" USERID "sa" PASSWORD ""
hConn = SQLConnect("Northwind")

SQLStringConnect() Function

The other function that can be used to establish a connection to a remote data source, such as SQL Server, is SQLStringConnect(). Unlike SQLConnect(), SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings.

The valid options are determined by the requirements of the ODBC driver. Specific requirements for each ODBC driver can be found in that ODBC driver's documentation.

The following table lists some commonly used connection string options for SQL Server:

DSN - References an ODBC DSN.
Driver - Specifies the name of the ODBC driver to use.
Server - Specifies the name of the SQL Server to connect to.
UID - Specifies the login ID or username.
PWD - Specifies the password for the given login ID or username.
Database - Specifies the initial database to connect to.
APP - Specifies the name of the application making the connection.
WSID - The name of the workstation making the connection.
Trusted_Connection - Specifies whether the login is being validated by the Windows NT Domain.

Not all of the options listed in the above table have to be used for each connection.

For instance, if you specify the Trusted_Connection option and connect to SQL Server using NT Authentication, there is no reason to use the UID and PWD options since SQL Server would invariably ignore them. The following code demonstrates some examples of using SQLStringConnect().

Note: You can use the name of your server instead of the string.

SQL Server 2000 code example:

LOCAL hConn
hConn=SQLStringConnect("Driver=SQLServer;Server=;UID=sa;PWD=;Database=Northwind");
hConn=SQLStringConnect("DSN=ODBCNorthwind;UID=sa;PWD=;Database=Northwind")
hConn=SQLStringConnect("DSN=ODBCNorthwind;Database=Northwind;Trusted_Connection=Yes")

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 = 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().

Extreme Programming

A discipline of software development that follows a specific structure that is designed to simplify and expedite the process of developing new software. Kent Beck developed Extreme Programming to be used with small teams of developers who need to develop software quickly in an environment of rapidly-changing requirements.

XP teams design software for specific functionalities without adding any functionalities that are not specifically requested that may slow down the process, keeping the development course simple through systematic and regular testing and design improvements.

Extreme Programming is based on 12 principles:
* The Planning Process -- The desired features of the software, which are communicated by the customer, are combined with cost estimates provided by the programmers to determine what the most important factors of the software are. This stage is sometimes called the Planning Game.
* Small Releases -- The software is developed in small stages that are updated frequently, typically every two weeks.
* Metaphor -- All members on an XP team use common names and descriptions to guide development and communicate on common terms.
* Simple Design -- The software should include only the code that is necessary to achieve the desired results communicated by the customer at each stage in the process. The emphasis is not on building for future versions of the product.
* Testing -- Testing is done consistently throughout the process. Programmers design the tests first and then write the software to fulfill the requirements of the test. The customer also provides acceptance tests at each stage to ensure the desired results are achieved.
* Refactoring -- XP programmers improve the design of the software through every stage of development instead of waiting until the end of the development and going back to correct flaws.
* Pair Programming -- All code is written by a pair of programmers working at the same machine.
* Collective Ownership -- Every line of code belongs to every programmer working on the project, so there are no issues of proprietary authorship to slow the project down. Code is changed when it needs to be changed without delay.
* Continuous Integration -- The XP team integrates and builds the software system multiple times per day to keep all the programmers at the same stage of the development process at once.
* 40-Hour Week -- The XP team does not work excessive overtime to ensure that the team remains well-rested, alert and effective.
* On-Site Customer -- The XP project is directed by the customer who is available all the time to answer questions, set priorities and determine requirements of the project.
* Coding Standard -- The programmers all write code in the same way. This allows them to work in pairs and to share ownership of the code.

How to change SQL Server Authentication Mode

During installation, SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. This topic describes how to change the security mode, after installation.

If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login, use the ALTER LOGIN command.

Security Note:

It is very important to choose a strong password for the sa login.

The sa login can only connect to the server using SQL Authentication.

To change security authentication mode:
* In SQL Server Management Studio Object Explorer, right-click your server, and then click Properties.
* On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
* In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.

To restart SQL Server from SQL Server Management Studio

In Object Explorer, right-click your server, and then click Restart. If running, SQL Server Agent must also be restarted.

To enable the sa login

Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '';
GO