VFP Access SQL Server Database in SPT
VFP can quickly establish application software for quick easy learning, and it is very popular among the majority of programmers, but its database system is not safe to make the majority of users very headache. With the promotion and application of the MS SQL Server database system, its powerful security performance is generally praised. In long-term programming practice, I found that using VFP's SQL Pass-Through (SPT) technology combines the MS SQL Server database system, it can also easily develop excellent customer / server (C / S) applications like VB, Delphi, Powerbuilder. software. Now, by writing a simple communication example and the majority of VFP enthusiasts.
First, the server side MS SQL Server database design
1. Create a database called "sfxt" in MS SQL Server.
2. Create a data sheet named "communication" in the SFXT database, the table structure is as follows:
And set the name as the primary key.
3. Establish a stored procedure for all records in the "SFXT" database. Sp_searchall
Create Procedure [sp_searchall]
AS
SELECT * FROM communication otter by name
Return
4. Create a stored procedure sp_insertdata inserted into the "SFXT" database.
CREATE Procedure [sp_insertdata]
@name [char] (10),
@BIRTHDAY [DATETIME],
@TELEPHONE [CHAR] (11),
@Email [char] (30)
AS
INSERT INTO Communications (Name, Birthday, Phone, Email) Values (@ name, @ Birthday, @ Televhone, @ email)
Return
Second, the client VFP SQL Pass-THROUGH technology design
1. Establish a VFP form interface such as (^ 15020603b ^).
2. Establish an ODBC data source named "sfxt" to connect the SFXT database in MS SQL Server.
It can be done by performing the button "Establish OBDC Data Source" on the above VFP form; or by running the "OBDC data source" in the Windows Control Panel. The main settings include selecting SQL Server drivers, communication protocols, login identity, and passwords.
"Establish OBDC Data Source" button. Click event:
* Function Description SqlstringConnect ([CConnectString])
* Display "SQL Data Source" dialog box when connecting string cconnectString, select or create a new data source
SQLStringConnect ()
3. Ways to connect MS SQL Server data sources.
Connection with an existing data source name, "establish a connection method 1" button. Click event.
Public VODBC, Vuser, VPWD, Vconn
VODBC = 'sfxt' && Connect SQL Server Database ODBC Data Source Name
Vuser = 'SA' && Access SQL Server Database Login User Name, sa is a system user
VPWD = '5213' && user login password, password set for SA system users
vconn = SqlConnect (VODBC, Vuser, VPWD)
IF vconn> 0
MessageBox ('connection success!', 64, 'ODBC data source')
Else
Messagebox ('connection failed!', 64, 'ODBC Data Source') Endif
Use the connection string to establish a data source connection, "establish a connection mode 2" button. Click event.
Public vconn
vconn = sqlstringconnect ('DSN = SFXT: UID = SA: PWD = 5213')
IF vconn> 0
MessageBox ('connection success!', 64, 'ODBC data source')
Else
MessageBox ('connection failed!', 64, 'ODBC data source')
ENDIF
4. The main parameter setting of the data source connection, "Active Connection Properties Settings" button. Click event.
* Note: Use the function sqlgetprop () to return the parameter value of the settings
SQLSETPROP (vconn, "asynchronous",. f.) && gives the results set synchronous back: Take the true value for asynchronous return
SQLSETPROP (vconn, "" ConnectTimeout ", 15) && connection timeout waiting for the number of seconds, can be 0 to 600
SQLSETPROP (vconn, "" idletimeout ", 0) && idle time intervals, value 0 is indefinite timeout waiting
SQLSETPROP (Vconn, "Transactions", 1) && value 1 is automatically handled for remote transactions:
5. "Execute the Stored Procedure Query Data" button. Click event.
* Function Description SQLEXEC (nConnectionHandle, [csqlcommand, [cursorname]])
* NConnectionHandle Current Data Source Activity Connection Handle
* csqlcommand executes SQL statement expressions for SQL Server stored procedures
* CURSORNAME Returns the name of the execution result temporary table
SQLEXEC (vconn, "" execute sp_searchall "", "My Communication")
Browse
6. "Execute SQL Statement Query Data" button. Click event.
* Function Description SQLEXEC (nConnectionHandle, [csqlcommand, [cursorname]])
* NConnectionHandle Current Data Source Activity Connection Handle
* CSQLCommand needs to send SQL statement expressions
* CURSORNAME Returns the name of the execution result temporary table
SQLEXEC (vConn, 'SELECT * FROM communication ",' my communication record ')
Browse
7. "Insert a New Record to the Storage Procedure" button. Click event.
Local vname, vTelephone, Vemail, VSQL
* Randomly generate new records
VNAME = 'Name' SYS (3) &&
VbirdHDAY = DTOC (Date () - int (Rand () * 10000)) && birthday
vtelephone = sys (3) && phone
Vemail = SYS (3) '@ Hotmail.com' && Email * Transfer to the parameter of the stored procedure into a string, and the quotation number forms a SQL statement.
vsql = "" EXECUTE SP_INSERTDATA "" "" "" " vbirthday " "" "" " vtelephone " "" "" " vemail " "" ""
If SQLEXEC (vconn, vsql)> 0 && Send SQL statement
MessageBox ('insert new record success!', 64, 'information')
Else
Messagebox ('inserting new records is unsuccessful!', 64, 'information')
endi
SQLEXEC (vconn, "" execute sp_searchall "", "My Communication")
Browse
8. "Set the Properties of the current table" button. Click event.
* Note: Use the function cursorgetprop () to return the parameter value of the settings.
The number of update instructions for the remote data source of CURSORSETPROP ('BatchUpdateCount', 100) && Send to the buffer table
CursorsetProp ('Buffering', 3) && Settings the current table as an open line buffer
CursorsetProp ('fetchsize', - 1) && extracts all inquiry records from remote tables
CursorsetProp ('KeyfieldList', 'Name') && Specifies the primary key field of the remote table
CursorsetProp ('sendupdates',. t.) && current buffer table When changing content, send SQL statements to update remote tables
CursorSetProp ('Tables', 'Communication') && Specifies the remote table name of the connection
* Local buffer table corresponds to remote table fields
CursorsetProp ('updatenamelist', 'name communication). Name, birthday communication signal. Birthday, telephone communication signal, telephone, email communication. Email')
* Specify a list of updated fields
CursorSetProp ('Updatablefieldlist', 'Name, Birthday, Phone, Email')
CursorSetProp ('UpdateType', 1) && Remote Table Update, Replacement
CursorsetProp ('WHERETYPE', 3) && update the WHERE clause in the SQL statement contains the primary keyword and the modified field
Browse && modifies the buffer table data, and automatically sends SQL statements to update the remote table after moving the record pointer.
9. "Disconnect the connection" button. Click event.
Sqldisconnect (vconn)
10. "Close" button. Click event.
ThisForm.Release We use the VFP's SQL Pass-Through technology to prepare a simple communication management program to implement the basic functions such as insertion, query, modification, such as data in the SQL Server database, is a typical client / server (C / S) structure application. I hope that the readers will help the development software in the future.
