VFP and SQL remote heterogeneous database

Data | database <P> </P> Friends familiar with VFP know that in VFP we can use Remote view and SPT (SQL pass through) technology to control remote heterogeneous databases. These techniques are actually the encapsulation of the ODBC API, so it is as easy for users to access a remote database as it is to operate a traditional dbf. With regard to the use of these two technologies, it is entirely possible to write the next book, given the subject and length of this article, only enumerate the applications of SPT technology to access remote data.

Many people think that with the intuitive, simple tools such as remote views, why do you need an SPT? It's hard to understand that SPT is more difficult to master than remote view, but you'll find that remote view is actually a visual tool for SPT! The SPT is more powerful than the remote view, and the functionality provided by the remote view is only a subset of the SPT. Its advantages and disadvantages are mainly embodied in the following aspects:

L, SPT advantages

1) Get multiple cursor at a time;

2 Execute other SQL statements except Select, such as Insert, Update, delete, etc.

3 Execute the remote database stored procedure;

4 The implementation of remote database special functions, commands, etc.;

5) transaction Management.

2. The disadvantage of SPT

1 no graphical user interface;

2 The connection must be maintained manually;

3) obtained Cursor by default is "readable and writable" Cursor, to make it "updatable" Cursor must be set before the line.

Follow our knowledge of SPT to see this great tool! (Note: All routines in this article use the SQL Server Northwind database demo).

Manage Connections

L, establish a connection

Note: If the code for all the examples in this article uses a connection, the connection handle "CON" is generated by default in "Establish Connection" code.

Wait \ ' Connect to SQL Server ' nowait noclear WINDOW

Sqlsetprop (0, "Displogin", 3) &&&& set environment to "Never Show ODBC logon dialog box."

Con=sqlstringconnect ("Driver=sql Server; Server=boe; uid=sa;pwd=;d Atabase=northwind ")

* Assuming the SQL Server server name is BOE, the user ID is SA and the password is an empty string

* If your SQL Server name, user ID, password and the different, please modify the relevant parts of the above code to meet the settings in your system

Wait clear

IF con<=0

MESSAGEBOX (\ ' connection failed \ ', 64,\ ' connected to SQL Server ')

ELSE

MESSAGEBOX (\ ' connection succeeded \ ', 64,\ ' connected to SQL Server ')

ENDIF

2. Disconnect the connection

SQLDisconnect (CON)

Get multiple cursor at a time

We can return multiple cursor with an SPT at once, as follows:

Csql= "SELECT * from EMPLOYEES" +CHR (a) + "select * from CUSTOMERS" +CHR (a) + "SELECT * FROM Products"

? SqlExec (Con,csql, "TEMP")

The return value of SqlExec () represents the number of cursor, which returns 3. These three cursor are named after the TEMP, TEMP1 and TEMP2 respectively.

Execute other SQL statements

Here we try to execute an SQL statement other than SQL Server:

Csql= "IF EXISTS (SELECT * from Customersswheres customerid=\ ' test\ ')"

csql=csql+ "DELETE from Customersswheres customerid=\ ' test\ '" csql=csql+ "ELSE INSERT CUSTOMERS (customerid,companyname VALUES (\ ' test\ ', \ ' This is a test!) \')"

IF SqlExec (con,csql) <=0

MESSAGEBOX (\ ' execute failed \ ', 64,\ ' send statement to SQL Server ')

ELSE

MESSAGEBOX (\ ' execution successful \ ', 64,\ ' send statement to SQL Server ')

ENDIF

In this case, a friend might ask: What if CUSTOMERID is a variable in the SQL statement? In fact, we can solve the problem through two common solutions:

1. Stitching string

Custid=\ ' test\ '

Csql= "IF EXISTS (SELECT * from Customersswheres customerid=\ '" +custid+ "\") "

csql=csql+ "DELETE from CUSTOMERS swherescustomerid=\ '" +custid+ "\"

csql=csql+ "ELSE INSERT CUSTOMERS (customerid,companyname) VALUES (\ ' +custid+" \ ', \ ' This is a test!) \')"

? SqlExec (Con,csql)

2. SPT standard variable Transfer method

Custid=\ ' test\ '

Csql= "IF EXISTS" (SELECT * from customersswherescustomerid=? CustID) "

csql=csql+ "DELETE from customersswherescustomerid=? CustID "

csql=csql+ "ELSE INSERT CUSTOMERS (customerid,companyname) VALUES (?) Custid,\ ' This is a test! \')"

? SqlExec (Con,csql)

Special functions and commands

If you have sufficient permissions in SQL Server to use the special functions and commands of the remote database through SPT, you have full control of SQL Server, and here we demonstrate "How to get the database server time":

? SqlExec (Con, "select GETDATE () as Serverdatetime", "Temp1")

? temp1.serverdatetime

Use in ("Temp1")

Transaction management

In some complex applications, there is often an operation that affects several tables. As far as the client is concerned, the data to be sent to the remote database can be varied from multiple rows of table buffers to a single record of row buffering, as well as the data maintenance that we demonstrated directly with SQL statements. How to control these update behavior in a transaction! Either succeed together or roll back together.

Csql= "DELETE from customersswherescustomerid=\ ' blaus\ '" +CHR (10)

csql=csql+ "INSERT CUSTOMERS (customerid,companyname) VALUES (\ ' test1\ ', \ ' This is a test!) \')"

Sqlsetprop (CON, "Transactions", 2) &&&& start a transaction

Ireturn=sqlexec (Con,csql)

IF Ireturn=1

Sqlcommit (CON) &&&& transaction delivery

ELSE

Sqlrollback (CON) &&&& transaction rollback

ENDIF

Sqlsetprop (CON, "Transactions", 1) &&&& back to automatic transaction processing status

&&&& for the purposes of this example, "DELETE from Customersswheres customerid=\ ' blaus\" is always unable to execute, and SQL Server returns an error revealing:

&&&&delete statement conflicted with COLUMN REFERENCE constraint \ ' fk_orders_customers\ '.

&&&&the conflict occurred in database \ ' northwind\ ', table \ ' orders\ ', column \ ' customerid\ '.

&&&& so this transaction is always rolled back!!

As you can see from the routines, the transactions that we open are actually for connection, meaning that all data updates through the connection are included in the transaction until the transaction is rolled back or delivered.

Sqlsetprop (CON, "Transactions", 2), in fact, turned on the human transaction, that is, the user must explicitly give the delivery or rollback instructions, the transaction will end. So the author thinks: After completing a transaction, should execute Sqlsetprop (CON, "Transactions", 1) Set the "Connection" transaction mode as the default "Automatic", which can prevent the user from falling into unknown transactions.

The basic operation of SPT is more than that, and later we will introduce some other basic operations for our readers. If friends can master these basic operations, you can write a good c/s program. Although this article uses SQL Server as a remote database, if you use DB2 and Oracle, you can do the same with VFP.

This article began to mention VFP in this aspect of the content is very broad, a few thousand words of course can not speak, interested friends can visit www.boeworks.com, in order to check more content. OK, I would like to have the opportunity to discuss this issue with you.