VFP 的 SPT 起跳 -- 陈纯(BOE数据网络工作室)
细节描述 Visual FoxPro 的 SPT 技术快速入门
说在前面
熟悉 Fox 的朋友都知道,在 VFP 里我们可以使用远程视图 (Remote View) 和 SPT(SQL Pass Through) 技术控制远程异构数据库。这些技术其实是 VFP 对 ODBC 的 API 的封装,所以对于用户来说访问远程数据库就像操作传统的DBF一样简单。关于这两种技术的使用,完全可以洋洋洒洒地写下一本书,鉴于本文主题及篇幅,这里仅枚举 SPT 技术访问远程数据的应用。
SPT 与远程视图
很多人搞不懂有了远程视图这样直观、简单的工具,为什么还需要 SPT 呢?确实 SPT 较远程视图难以掌握,但细细体会你会发现:远程视图其实是对 SPT 的可视化工具!SPT 较远程视图更具威力,远程视图提供的功能只是 SPT 的一个子集。仔细探索两者优劣,我们发现:
SPT 的优势:
1. 一次得到多个Cursor
2. 执行除 Select 以外的其他 SQL 语句,如 Insert、Update、Delete等
3. 执行远程数据库的存储过程
4. 执行远程数据库的特殊函数、命令
5. 事务管理
SPT 的劣势:
1. 没有图形用户界面
2. 必须人工维护连接
3. 得到的Cursor默认是"可读写"Cursor,要使它成为"可更新"Cursor必须经过设定
下面就顺着我们对 SPT 的认识,来浏览一下这种伟大的工具吧!(注意:本文所有例程均使用 SQL Server的NorthWind 数据库演示)
管理连接:
建立连接:(注意:本文所有示例代码若用到连接的,默认采用"建立连接"代码中产生的连接句柄"con")
WAIT ' 连接到 SQL Server 上去 ' NOWAIT NOCLEAR WINDOW
SQLSETPROP(0,"DispLogin" ,3) && 设置环境为:"从不显示 ODBC 登陆对话框"
con=SQLSTRINGCONNECT("driver=SQL Server;Server=BOE;Uid=sa;pwd=;database=northwind")
*假定 SQL Server 服务器名为 BOE, 用户 ID 是sa, 口令是空串
*如果你的 SQL Server 的服务器名, 用户 ID, 口令与上不同,请修改以上代码中的相关部分以符合你系统中的设置
WAIT clear
IF con<=0
MESSAGEBOX(' 连接失败 ',64,' 连接到 SQL Server 上去 ')
ELSE
MESSAGEBOX(' 连接成功 ',64,' 连接到 SQL Server 上去 ')
ENDIF
断开连接:
SQLDISCONNECT(con)
一次得到多个Cursor
我们可以用一次 SPT 传回多个Cursor,如下:
cSQL="SELECT * FROM EMPLOYEES"+CHR(10)+"SELECT * FROM CUSTOMERS"+CHR(10)+"SELECT * FROM PRODUCTS"
?SQLEXEC(con,cSQL,"TEMP")
SQLEXEC( ) 的返回值表示Cursor的数量,这里返回 3 。这三个Cursor分别以: TEMP,TEMP1,TEMP2 命名。
执行除 SQL-Select 以外的 SQL 语句
cSQL="IF EXISTS(SELECT * FROM CUSTOMERS WHERE CUSTOMERID='TEST')"
cSQL=cSQL+" DELETE FROM CUSTOMERS WHERE CUSTOMERID='TEST'"
cSQL=cSQL+" ELSE INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES('TEST',' 这是一个测试! ')"
IF SQLEXEC(CON,cSQL)<=0
MESSAGEBOX(' 执行失败 ',64,' 发送语句到 SQL Server 上去 ')
ELSE
MESSAGEBOX(' 执行成功 ',64,' 发送语句到 SQL Server 上去 ')
ENDIF
行文至此,也许有朋友会问:如果 SQL 语句中 CUSTOMERID 是一个变量怎么办呢?有两个常用的解决方案:
拼接字符串
CUSTID='TEST'
cSQL="IF EXISTS(SELECT * FROM CUSTOMERS WHERE CUSTOMERID='"+CUSTID+"')"
cSQL=cSQL+" DELETE FROM CUSTOMERS WHERE CUSTOMERID='"+CUSTID+"'"
cSQL=cSQL+" ELSE INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES('"+CUSTID+"',' 这是一个测试! ')"
?SQLEXEC(CON,cSQL)
SPT 标准变量传递法
CUSTID='TEST'
cSQL="IF EXISTS(SELECT * FROM CUSTOMERS WHERE CUSTOMERID=?CUSTID)"
cSQL=cSQL+" DELETE FROM CUSTOMERS WHERE CUSTOMERID=?CUSTID"
cSQL=cSQL+" ELSE INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES(?CUSTID,' 这是一个测试! ')"
?SQLEXEC(CON,cSQL)
执行远程数据库的存储过程
存储过程的好处自是不必多言,下面就让我们看看怎样用 SPT 调用远程数据库的存储过程。下面我们演示的是 NorthWind 数据库中的存储过程" CustOrderHist ",它的作用是返回指定客户关于产品的消费数量合计。据我所知,这里有两种书写格式供大家选择:
使用 T-SQL 的写法:
CUSTID='VINET'
?SQLEXEC(CON,'EXEC CustOrderHist ?CUSTID','TEMP1')
使用 ODBC 的写法:
CUSTID='VINET'
?SQLEXEC(CON,'{CALL CustOrderHist(?CUSTID)}','TEMP2')
存储过程常常会需要返回一些变量,通用的方法就是使用输出参数。在演示之前,我们先用 SPT 在 SQL Server 建立一个包含输入、输出参数的存储过程。
cSQL="IF EXISTS(select * from sysobjects where id=object_id('MY_PROC') and OBJECTPROPERTY(id,'IsProcedure')=1)"
cSQL=cSQL+" drop procedure MY_PROC " &&如果存储过程My_proc已经存在,就删除它
?SQLEXEC(con,cSQL)
cSQL="CREATE PROCEDURE MY_PROC @EmployeeID int,@Desc varchar(100) output as /* 只支持寻找直接下属 */"+chr(10)
cSQL=cSQL+" DECLARE @ROW INT"+chr(10)
cSQL=cSQL+" SELECT * FROM EMPLOYEES WHERE REPORTSTO=@EMPLOYEEID"+chr(10)
cSQL=cSQL+" SELECT @ROW=@@ROWCOUNT"+chr(10)
cSQL=cSQL+" IF @ROW>0"+chr(10)
cSQL=cSQL+" SELECT @Desc=' 找到了 '+CAST(@ROW AS VARCHAR(4)) +' 位下属 '"+chr(10)
cSQL=cSQL+" ELSE SELECT @Desc=' 这是一位普通员工 '"
?SQLEXEC(con,cSQL)
使用 T-SQL 的写法:
EMPID=2
DESCRIPTION=""
?SQLEXEC(CON,'EXEC MY_PROC ?EMPID,?@DESCRIPTION','TEMP1')
?DESCRIPTION
使用 ODBC 的写法:
EMPID=2
DESCRIPTION=""
?SQLEXEC(CON,'{CALL MY_PROC(?EMPID,?@DESCRIPTION)}','TEMP2')
?DESCRIPTION
执行远程数据库的特殊函数、命令
如果在 SQL Server 中你有足够的权限,通过 SPT 你可以完全控制 SQL Server ,这里我们演示"怎样取得数据库服务器的时间":
?SQLEXEC(con,"select getdate() as serverdatetime","temp1")
?temp1.serverdatetime
USE IN ("temp1")
事务管理
在一些复杂的应用中,往往会有一项操作影响好几个表的情况。就客户端来说,发送到远程数据库的数据变动可能来源很多:表缓冲的多行记录的变动,行缓冲的单行记录变化,以及前文我们演示的直接用 SQL 语句传递的数据维护,林林总总……怎样把这些更新行为控制在一个事务中要么--一起成功,要么一起回滚。
cSQL="DELETE FROM CUSTOMERS WHERE CUSTOMERID='BLAUS'"+CHR(10)
cSQL=cSQL+"INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES('TEST1',' 这是一个测试! ')"
SQLSETPROP(CON,"Transactions" ,2)&& 开始一个事务
IRETURN=SQLEXEC(CON,cSQL)
IF IRETURN=1
SQLCOMMIT(CON)&& 事务交付
ELSE
SQLROLLBACK(CON)&& 事务回滚
ENDIF
SQLSETPROP(CON,"Transactions" ,1)&& 重新回到自动事务处理状态
&&就本例而言,"DELETE FROM CUSTOMERS WHERE CUSTOMERID='BLAUS'"总是不能执行的,SQL Server会返回错误:
&&DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'.
&&The conflict occurred in database 'Northwind', table 'Orders', column 'CustomerID'.
&&所以这笔事务总是被回滚的!!
从例程中我们看到,我们开启的事务其实是针对"连接"的,也就是说通过该"连接"的所有数据更新都包含于事务中,直到事务被回滚或交付。
SQLSETPROP(CON,"Transactions" ,2 ), 其实是开启了人工事务处理,也就是说必须由用户明确的给出交付或者回滚指令,事务才会结束。所以笔者以为:完成一笔事务以后,应执行 SQLSETPROP(CON,"Transactions" ,1 ) 将"连接"的事务模式设为默认的"自动",这样可以防止用户陷入未知的事务中去。
设为可更新
到目前为止,我们已经演示了 6 个 SPT 专题了,除了第一个"连接管理"在远程视图中能够实现之外,其余的远程视图都无法实现。下面我们要讨论一下怎样把 SPT 传回的结果集合设为"可更新",总的来说远程视图提供的就是这个功能。
在默认状况下, SPT 从远程数据库得到的Cursor是"可读写"的,即:可以对它进行" Select 、 Update 、 Insert 、 Delete "的操作,但数据的变化不会反映到数据源。"可更新"Cursor的特色就是可以直接将客户端的数据变动,自动生成一系列 SQL 描述更新远程数据库。
实现"可读写"Cursor到"可更新"Cursor必须经历以下五步的设置:
CURSORSETPROP("TABLES", 数据源表名 , 可更新Cursor名 )
此步骤设定的是数据源里(SQL Server)待更新的表名,如果涉及多个表就这样写: CURSORSETPROP("TABLES","T1,T2","MyCursor") 。
CURSORSETPROP("KEYFIELDLIST", 关键字段 , 可更新Cursor名 )
此步骤是设定关键字段的,这个关键字段是可更新Cursor的字段,而不是数据源里的字段。
CURSORSETPROP("UPDATABLEFIELDLIST", 可更新字段列表 , 可更新Cursor名 )
此步骤设定的是在可更新Cursor里哪些字段的变动要被反映到数据源,即哪些字段时可更新的。
CURSORSETPROP("UPDATENAMELIST", 前后段字段对应关系列表 , 可更新Cursor名 )
此步骤设定数据源字段与可更新Cursor字段的对应关系。
CURSORSETPROP("SENDUPDATES",.T., 可更新Cursor名 )
这个步骤是打开 SQL 发送开关,最关键的一步。
为便于大家理解,现将以上五步实例化:
例一:
SQLEXEC(con,"select categoryid as id ,categoryname,description from categories","mycursor")
SELECT mycursor
CURSORSETPROP("Tables","categories","mycursor")
CURSORSETPROP("KeyFieldList","id","mycursor")
CURSORSETPROP("UpdatableFieldList" ,"id,categoryname,description","mycursor")
CURSORSETPROP("UpdateNameList","id categories.categoryid,categoryname categories.categoryname,"+;
"description categories.description","mycursor")
CURSORSETPROP("SendUpdates" ,.t.,"mycursor")
例二:
SQLEXEC(con,"select a.productid,a.productname,a.unitprice,b.categoryid,b.categoryname,c.supplierid,"+;
"c.companyname as suppliername,c.contactname"+;
" from (products a inner join categories b on a.categoryid=b.categoryid)"+;
" inner join suppliers c on a.supplierid=c.supplierid","mycursor")
SELECT mycursor
CURSORSETPROP("Tables","products,categories,suppliers","mycursor")
CURSORSETPROP("KeyFieldList","productid,categoryid,supplierid","mycursor")
CURSORSETPROP("UpdatableFieldList",+;
"productid,productname,unitprice,categoryid,categoryname,supplierid,suppliername,contactname","mycursor")
CURSORSETPROP("UpdateNameList","productid products.productid,productname "+;
"products.productname,unitprice products.unitprice,"+;
"categoryid categories.categoryid,categoryname categories.categoryname,"+;
"supplierid suppliers.supplierid,suppliername suppliers.companyname,contactname suppliers.contactname","mycursor")
CURSORSETPROP("SendUpdates" ,.t.,"mycursor")
行笔匆匆,终于把我认识的 SPT 基本操作写完了,掌握这些,已能编写不错的 C/S 程序。虽然,本文是用 SQL Server 作为远程数据库,但是如果你使用 DB2 、 Oracle ,在 VFP 中也是一样处理。