Home arrow SQL Server arrow How to Connect to a SQL Server from Visual FoxPro
SQL SERVER

How to Connect to a SQL Server from Visual FoxPro


Sayed explains how one could connect from Visual Foxpro to a SQL Server, and the problems related to making this connection.

Author Info:
By: Sayed Geneidy
Rating: 5 stars5 stars5 stars5 stars5 stars / 61
August 24, 2003
TABLE OF CONTENTS:
  1. · How to Connect to a SQL Server from Visual FoxPro
  2. · Conclusion

print this article
SEARCH DEVARTICLES

How to Connect to a SQL Server from Visual FoxPro
(Page 1 of 2 )

In Microsoft public newsgroups, I've noticed a recent increase in the number of questions that deal with how to connect from Visual Foxpro to SQL Server, and the problems related to making this connection. So I've decided to write this article to cover such an important topic.

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:

OptionDescription
DSNReferences an ODBC DSN.
DriverSpecifies the name of the ODBC driver to use.
ServerSpecifies the name of the SQL Server to connect to.
UIDSpecifies the login ID or username.
PWDSpecifies the password for the given login ID or username.
DatabaseSpecifies the initial database to connect to.
APPSpecifies the name of the application making the connection.
WSIDThe name of the workstation making the connection.
Trusted_ConnectionSpecifies 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=SQL Server;Server=<SQL2000>;"+ ;
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 = 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().


blog comments powered by Disqus
SQL SERVER ARTICLES

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables – New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials