Marathon Griffin Computers and Service - Computer and Laptop Sales - Computer Repairs and Service - Network & Accounting Specialist

 

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.
You can then call the connection .prg in the Load event of a form and call the disconnect .prg in the QueryUnload event of a form.

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.

* --- The ping test is only an option you may wish to consider.
* --- Option only. This is a UDF and code is shown later below.
* --- Only use this command if the client's firewall/router is
* --- pingable.  Some firewalls are configure not to reply to a
* --- ping request.

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 the SQLStringConnect() function.
* --- Default connection always creates a shared connection.
lnSQLConn = SQLStringConnect("Driver=SQL Server;Server=ServerName\DBInstanceName;UID=UserID;PWD=Password;
Database=Databasename")

* --- Test that the connection completed successfully.
IF lnSQLConn <= 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(lnSQLConn, '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 &cfile 
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.
There are other Visual FoxPro Ping code that you can find, the ping examples above are only examples to demonstrate how to use a ping call to ensure a SQL Server is actually alive and running.

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

lnConnRetval= SQLEXEC(lnSQLConn,"Select * FROM Orders WHERE STATUS = 'INPROG' ORDER BY OrderNo","tmpOrders")
IF
lnConnRetval < 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 let's look at the simple disconnect code.
* --- SQLdisconnect.prg
IF lnSQLConn > 0    && If lnSQLConn 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.
* --- You would close local cursors down only if
* --- this is part of your required logic flow
.
CLOSE TABLES ALL
* --- End of SQLdisconnect.prg

 

REMOTE CONNECTIONS TO SQL SERVER AND HOW TO HANDLE
Below is an example of code right out of one of my projects connecting to a remote SQL Server down in the US. You will notice in the code example below where I am performing a SQLSETPROP() call to kill the popup of a SQL Server login authentication window in the event of a connection failure. This generally you do not want to have happen in a production system and you want to handle the logic in code of what to do with any failure or errors.
As well, you will see the lcSQLString for the SERVER= paramater has an IP address and a ,1433 after the IP address. ie: 212.123.53.253,1433
The 212.123.53.253 is the static IP address of generally the customer's external IP address that would be setup as a static IP address that never changes. This is arranged by the client's ISP.
The client would then in his/her firewall-router configure port forwarding of port 1433 to the internal IP address of the SQL Server within the client's LAN.
The SQL Server is configured to allow remote connections and 1433 is the SQL Server's default listening port awaiting incoming connections. This listening port can be changed, but if you do this then of course you have to ensure whatever listening port is used is also opened up in your firewall-router.

I am providing to you the resource link below showing you how to configure your SQL Server to accept incoming remote connections.
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx  

* --- The value in this lcClientID memory variable was
* --- was stored in it via the user inputting the client's
* --- ID number into a TextBox on the form.  Then something
* --- like the code snippet below can be called to perform
* --- a connection to the SQL Server table holding the required
* --- data and select it down into a local Visual FoxPro
* --- cursor to be worked with.  ie: View it or print it!

* --- Disable the SQL Server's auto login
* --- screen if the connection failed.
= SQLSETPROP(0, "DispLogin", 3)

* --- SQL Server ADO string is working as well.
gnSQLConn = SQLStringConnect("DRIVER=SQL Server;SERVER=212.123.53.253,1433;UID=pete;PWD=pete;DATABASE=QBaccounts")lcSQLsting = "SELECT * FROM PAYMENTTRANSACTIONS WHERE CLIENTID = '&lcCientID'"
lnSQLSuccess = SQLEXEC(gnSQLConn,"SELECT * FROM PAYMENTTRANSACTIONS WHERE CLIENTID = '&lcCientID' ORDER BY TRANSDATETIME","curPaymentTrans")
IF lnSQLSuccess < 0
 
 = MESSAGEBOX("Obtaining data from the SQL Server database failed at: " +  
   TRANSFORM
 (DATETIME()) + "." + CHR
(13) + "Problems may exist with the  
   system.",0+16,"Data Extraction Failure")
   * --- Clear event processing and return the user to Windows OS.
   RETURN
Endif

* --- Select your local cursor for viewing of printing, etc.
SELECT
curPaymentTrans
* --- Star out in productions system this is only for testing purposes.
BROWSE

 

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

Pete,

Back to Tech Tips Index Page

SystemsLaptop 
Tower 
Monitor 
Office