Accessing SQL Server database with SPT in VFP

VFP because it is simple and easy to learn, can quickly set up application software by the vast number of programmers love, but its database system is not safe also makes the majority of users very headache. With the application of MS SQL Server database system, its powerful security can be widely praised. In the long-term programming practice, I found that the use of VFP SQL pass-through (SPT) technology combined with MS SQL Server database system, can also be like VB, Delphi, PowerBuilder as easy to develop excellent customer/server (c/s) Application software. Now through the preparation of a simple communication record examples and the vast number of VFP enthusiasts to explore.
　　

　server-side MS SQL Server database design

1. Establish a database named "SFXT" in MS SQL Server.

2. Create a data table called "Communications Record" in the SFXT database, with the following table structure:

Column name (field name) data type length
Name CHR 8
Birthday DateTime 8
Telephone CHR 11
Email CHR 30
and set the name as the primary key.

3. Create a stored procedure that queries out all records in the "SFXT" database. Sp_searchall

CREATE PROCEDURE [Sp_searchall]
As
SELECT * FROM communication record ORDER by name
Return

4. Create a stored procedure sp_insertdata that inserts a new record in the "SFXT" database.
CREATE PROCEDURE [Sp_insertdata]
@name [Char] (10),
@birthday [datetime],
@telephone [Char] (11),
@email [Char] (30)
As
INSERT into communication record (name, birthday, phone, email) VALUES (@name, @birthday, @telephone, @email)
Return

　　second, the client VFP SQL pass-through technology design

1. The establishment of such as (^15020603b^) VFP form interface.

2. Establish an ODBC data source named "SFXT" to connect to the SFXT database in MS SQL Server.

This can be done by executing the button "Establish OBDC data source" above VFP form, or by running "OBDC data source" in Windows Control Panel. The main settings include selecting SQL Server Driver, communication protocol, login ID and password, etc.

The Create OBDC data source button. Click event:

* Function Description Sqlstringconnect ([cconnectstring])

* The SQL Data Source dialog box is displayed when the connection string cconnectstring is omitted, and you can select or create a new data source

Sqlstringconnect ()

3. Two ways to connect to an MS SQL Server data source.

Establish a connection using an existing data source name, "Establish a connection mode 1" button. Click event.
Public Vodbc,vuser,vpwd,vconn
Vodbc= ' SFXT ' && connection SQL Server database ODBC data source name
vuser= ' sa ' && access to the SQL Server database login username, SA for system user
Vpwd= ' 5213 ' && user login password, password set for SA system user
Vconn=sqlconnect (VODBC, Vuser,vpwd)
If vconn>0
MessageBox (' Connection successful! ',, ' ODBC data source ')
Else
MessageBox (' Connection failed! ',, ' ODBC data source ')
endif
Use the connection string to establish a data source connection, "Establish connection Mode 2" button. Click event.
Public Vconn
Vconn=sqlstringconnect (' dsn=sfxt:uid=sa:pwd=5213 ')
If vconn>0
MessageBox (' Connection successful! ',, ' ODBC data source ')
Else
MessageBox (' Connection failed! ',, ' ODBC data source ')
endif

4. The main parameter settings of the data source connection, the active Connection property settings button. Click event.

* Note: Use function Sqlgetprop () to return the set parameter value
Sqlsetprop (Vconn, "Asynchronous" ",. F.) && Returns the result set synchronously when taking false values: Returns asynchronously when the truth is taken
Sqlsetprop (Vconn, "ConnectTimeout" ",") && connection timeout Wait seconds set, the value of 0 to 600
Sqlsetprop (Vconn, "IdleTimeout" ", 0) && idle timeout interval seconds, value 0 for indefinite timeout wait
Sqlsetprop (Vconn, "" Transactions "", 1) && to automatically process remote transactions when value 1: value 2 for manual processing

5. "Execute Stored Procedure query data" button. Click event.

* Function Description SqlExec (Nconnectionhandle, [Csqlcommand, [Cursorname]])
*nconnectionhandle Current data source active connection handle
*csqlcommand An SQL statement expression that executes a SQL Server stored procedure
*cursorname returns the name of the execution result temp table
SqlExec (Vconn, "" Execute Sp_searchall "", "" My Correspondence Record ")
Browse

6. "Execute SQL Statement query data" button. Click event.

* Function Description SqlExec (Nconnectionhandle, [Csqlcommand, [Cursorname]])
*nconnectionhandle Current data source active connection handle
*csqlcommand need to send an SQL statement expression
*cursorname returns the name of the execution result temp table
SqlExec (Vconn, ' SELECT * from correspondence ', ' My Correspondence record ')
Browse

7. Insert new record for parameter to stored procedure button. Click event.

Local Vname,vbirthday,vtelephone,vemail,vsql
* Random generation of new records examples
VName = ' name ' +sys (3) && name
Vbirthday = Dtoc (Date ()-int (rand () *10000)) && birthday
Vtelephone = SYS (3) && phone
Vemail = SYS (3) + ' @hotmail. com ' && e-mail
* Converts parameters passed to stored procedures into strings and quotes to form SQL statements.
Vsql= "" "" "" Sp_insertdata "" "" + "" "" "+vname+" "," "" "+vbirthday+" "," "" +vtelephone+ "" "" "
If SqlExec (vconn,vsql) >0 && Send SQL statements
MessageBox (' Insert new record success! ',, ' information ')
Else
MessageBox (' Insert new record not successful! ',, ' information ')
Endi
SqlExec (Vconn, "" Execute Sp_searchall "", "" My Correspondence Record ")
Browse

8. "Set the properties of the current table modify data" button. Click event.

* Note: Use function Cursorgetprop () to return the set parameter value
Cursorsetprop (' Batchupdatecount ') && number of update instructions for remote data sources sent to the buffer table
Cursorsetprop (' Buffering ', 3) && set the current table to open row buffering
Cursorsetprop (' fetchsize ', -1) && extract all query records from remote tables
Cursorsetprop (' keyfieldlist ', ' name ') && Specify the primary key field for the remote table
Cursorsetprop (' Sendupdates ',. T.) && send SQL statement to update remote table when current buffer table changes content
Cursorsetprop (' Tables ', ' Communication Records ') && Specify the remote table name for the connection
* The local buffer table corresponds to the Remote table field
Cursorsetprop (' updatenamelist ', ' name Correspondence record.) name, birthday correspondence record, birthday, telephone correspondence record, telephone, email communication, email '
* Specify an updatable field list
Cursorsetprop (' updatablefieldlist ', ' name, birthday, phone, email ')
Cursorsetprop (' Updatetype ', 1) && remote table update mode, replacement method
Cursorsetprop (' Wheretype ', 3) && update SQL statement WHERE clause contains primary key and modified field
Browse && Modify Buffer table data, move record pointer, automatically send SQL statement to update remote table

9. Disconnect the connection to data Source button. Click event.

SQLDisconnect (Vconn)

10. "Close" button. Click event.

Thisform.release

Above we use the VFP SQL pass-through technology to write a simple communication record management program, realizes to the SQL Server database data inserts, the inquiry, the modification and so on basic function, is a typical client/server (c/s) structure application. Hope to help readers to develop software in the future.