SQL Visual FoxPro Connection to SQL Server

Back to Tech Tips Index Page

Below I will demonstrate how to create a connection from Visual FoxPro to a SQL Server database instance.

Generally I code a seperate VFP .prg to perform the connection, testing logic, etc., to ensure I have a successful connection.  This .prg I can then call from my Main.prg "VFP startup program".
I also code a seperate VFP .prg disconnection .prg to disconnect from the SQL Server Database instanace.

Lets Look at the connection program first called SQLconnect.prg
* --- SQLconnect.prg
* --- Ping to see if the SQL Server is in fact even on line.
* --- Create connection into targeted SQL Server DB instance.
* --- Turn on transaction processing.
* --- Please note: The PING() is a seperate .prg shown at the end of this
* --- SQLconnect.prg.  There are two PING() function methods shown.

llPingVal = PING("ServerName")
IF llPingVal = .F.
  = MESSAGEBOX("Connection to the SQL Server failed at: " + TRANSFORM
  (DATETIME()) + "." + CHR(13) + "This application will automatically
  close down.",0+16,"SERVER - Connection Failure")
  * --- Clear event processing and return the user to Windows OS.
  CLEAR EVENTS
  QUIT
ENDIF

* --- If ping was successful then create my connection to the SQL Server database * --- using SQLStringConnect() function.

* --- Default connection always creates a shared connection.
cSQLConn = SQLStringConnect("Driver=SQL Server;Server=ServerName\DBInstanceName;UID=UserID;PWD=Password;
Database=Databasename")

* --- Test that the connection completed successfully.
IF cSQLConn <= 0
  = MESSAGEBOX("Connection to the SQL Server database failed at: " + TRANSFORM
  (DATETIME()) + "." + CHR(13) + "This application will automatically
  close down.",0+16,"DATABASE - Connection Failure")
  * --- Clear event processing and return the user to Windows OS.
  CLEAR EVENTS
  QUIT
ENDIF

* --- Set the transaction property to 2.
* --- 2 or DB_TRANSMANUAL (from FOXPRO.H). Transaction processing is handled 
* --- manually through SQLCOMMIT() and SQLROLLBACK(). Read/write.
= SQLSETPROP(cSQLConn, 'Transactions', 2)
* --- End of SQLconnect.prg

Ok, we now have a successful connection established from a Visual FoxPro program into a SQL Server database instance.
As mentioned, you place the above code in a seperate .prg and call it from your Main.prg VFP startup program.  This ensures you have a connection prior to loading up your entire application to have it fail with errors as the connection could not be established.

Lets look at the Ping() function code.
* --- Ping.prg
* --- Syntax: = PING("cServerName")
*--- Returns a .T., or a .F.
FUNCTION Ping
PARAMETERS mPc
LOCAL mCommand, Ret, mStr,cfile,RunOBJ
Ret = .F.
cfile=sys(3)
mCommand = Getenv("ComSpec") + " /C ping " + ALLTRIM(mPc)+ [ -n 1 -l 1 -w 1 > "]+cfile+["]
RunOBJ = Createobject("wscript.shell")
= RunOBJ.Run(mCommand,0,.T.)
IF FILE(cfile)
  mStr = FileToStr(cfile)
  Ret = IIF([Reply from] $ mStr,.T.,.F.)
ENDIF
* --- Remove the ping capture file.
DELETE FILE
RETURN Ret

Here is another version of a Ping() function using Windows API call and targeting the IP address instead of a server name.
* --- PingAPI.prg - Coded as a function.
* --- Format : = PingAPI("10.162.15.75")
LPARAMETERS lChkIP
DECLARE INTEGER GetRTTAndHopCount IN Iphlpapi;
INTEGER DestIpAddress, LONG @HopCount,;
INTEGER MaxHops, LONG @RTT

DECLARE INTEGER inet_addr IN ws2_32 STRING cp

LOCAL nHop, nRTT
nIPDest = inet_addr(lChkIP)
IF GetRTTAndHopCount(nIPDest, @nHop, 50, @nRTT) <> 0
  RETURN .T.
ELSE
  RETURN .F.
ENDIF

Notes on the PING() functions.  If the server name or IP is found both functions will return .T.  If the server name or IP is not found the functions will return .F.
In the situation where the server name or IP is not found there is a 3-4 second delay for the functions to return .F.

We now have all the logic in place to check that the SQL Server is up and running and that we can connect to the SQL Server database with error code logic in place.  I do make the double check to test that the server is up with the PING() function and then the error trapping on the return value of cSQLConn <= 0 so I know if the problem is a server down or an actual SQL Server database instance connection problem.

Now we can utilize the SQLEXEC() function in the Load event of any form to create a cursor of the SQL Server table to work with.  In fact we can now use code like shown below to create a temporary cursor anywhere in the VFP project and obtain the recordset we wish.
Notice that I am using the same connection handle cSQLConn that was used in the initial connection to the database cSQLConn = SQLStringConnect()

nConnRetval= SQLEXEC(cSQLConn,"Select * FROM Orders WHERE STATUS = 'INPROG' ORDER BY OrderNo","tmpOrders")
IF nConnRetval < 0
  = MESSAGEBOX("Obtaining data from the SQL Server database failed at: " +  
  TRANSFORM (DATETIME()) + "." + CHR(13) + "Problems may exist with the  
  system.",0+16,"QUERY - Failure")
  * --- Clear event processing and return the user to Windows OS.
  RETURN
ENDIF

Notice how each error message displayed advises me where the problem is.
SERVER - Connection Failure
DATABASE - Connection Failure
or
QUERY - Failure

Now lets look at the simple disconnect code.
* --- SQLdisconnect.prg
IF cSQLConn > 0      && If cSQLConn is not greater than 0 there is no connection.
  SQLDISCONNECT(0)   && Passing a 0 performs a database disconnect.
ENDIF
* --- Close all open tables or cursors.
CLOSE TABLES ALL
* --- End of SQLdisconnect.prg

I hope the code examples shown above will assist you with Visual FoxPro connections to a SQL Server database.

Pete,

Back to Tech Tips Index Page