vfp远程视图如何才能共享同一odbc连接

在数据库中已建有到odbc的连接test,
另建了两个远程视图，用test连接，设好了共享连接，但是use两个远程视图后，用CURSORGETPROP（）查句柄，还是不同的正数，应该共享不成功，
建test连接时，分别用系统dsn、用户dsn、文件dsn都试过，还是不能共享，
困惑了几天了，有哪位高人指点一二，感激不尽！

8 个解决方案

#1

请参考我的一个例子

*使用SQL Server事务处理更新多个远程视图失败时回滚全部数据。

*------------------------------------------------------------------------------------

*---------------先代码建立并设置可更新远程视图
Public gcDSNLess,gnViewConn
gcDSNLess='DRIVER=Sql Server;SERVER=计算机名或IP;UID=用户名;PWD=密码;APP=Microsoft Visual FoxPro;WSID=计算机名或IP;DATABASE=数据库名;Network=DBMSSOCN;Address=计算机名或IP,1433'
Create Database MyData
Create Connection MyShareConn Connstring gcDSNLess

Create Sql View MyStaffInfoView Remote Connection MyShareConn Share;
	AS Select *;
	FROM dbo.StaffInfo Staffinfo;
	ORDER By Staffinfo.部门号, Staffinfo.分部门号, Staffinfo.工号	&&人员信息视图
ThisView="MyStaffInfoView"
DBSetProp(ThisView,"View","SendUpdates",.T.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",-1)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.T.)	&&共享连接
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","dbo.StaffInfo")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".部门号","Field","DataType","C(10)")
DBSetProp(ThisView+".部门号","Field","UpdateName","dbo.StaffInfo.部门号")
DBSetProp(ThisView+".部门号","Field","KeyField",.F.)
DBSetProp(ThisView+".部门号","Field","Updatable",.T.)

DBSetProp(ThisView+".部门名称","Field","DataType","C(20)")
DBSetProp(ThisView+".部门名称","Field","UpdateName","dbo.StaffInfo.部门名称")
DBSetProp(ThisView+".部门名称","Field","KeyField",.F.)
DBSetProp(ThisView+".部门名称","Field","Updatable",.T.)

DBSetProp(ThisView+".分部门名称","Field","DataType","C(20)")
DBSetProp(ThisView+".分部门名称","Field","UpdateName","dbo.StaffInfo.分部门名称")
DBSetProp(ThisView+".分部门名称","Field","KeyField",.F.)
DBSetProp(ThisView+".分部门名称","Field","Updatable",.T.)

DBSetProp(ThisView+".工号","Field","DataType","I")
DBSetProp(ThisView+".工号","Field","UpdateName","dbo.StaffInfo.工号")
DBSetProp(ThisView+".工号","Field","KeyField",.F.)
DBSetProp(ThisView+".工号","Field","Updatable",.T.)

DBSetProp(ThisView+".姓名","Field","DataType","C(20)")
DBSetProp(ThisView+".姓名","Field","UpdateName","dbo.StaffInfo.姓名")
DBSetProp(ThisView+".姓名","Field","KeyField",.F.)
DBSetProp(ThisView+".姓名","Field","Updatable",.T.)

DBSetProp(ThisView+".auto_id","Field","DataType","N(20)")
DBSetProp(ThisView+".auto_id","Field","UpdateName","dbo.StaffInfo.Auto_ID")
DBSetProp(ThisView+".auto_id","Field","KeyField",.T.)
DBSetProp(ThisView+".auto_id","Field","Updatable",.F.)

Use (ThisView) In 0
CursorSetProp("Buffering", 5, ThisView)
gnViewConn = CursorGetProp("CONNECTHANDLE",ThisView)


Create Sql View MySubDepartmentView Remote Connection MyShareConn Share;
	AS Select *;
	FROM dbo.SubDepartment SubDepartment;
	ORDER By SubDepartment.部门号, SubDepartment.分部门号
ThisView="MySubDepartmentView"
DBSetProp(ThisView,"View","SendUpdates",.T.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",-1)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.T.)	&&共享连接
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","dbo.SubDepartment")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".部门号","Field","DataType","C(10)")
DBSetProp(ThisView+".部门号","Field","UpdateName","dbo.SubDepartment.部门号")
DBSetProp(ThisView+".部门号","Field","KeyField",.F.)
DBSetProp(ThisView+".部门号","Field","Updatable",.T.)

DBSetProp(ThisView+".部门名称","Field","DataType","C(20)")
DBSetProp(ThisView+".部门名称","Field","UpdateName","dbo.SubDepartment.部门名称")
DBSetProp(ThisView+".部门名称","Field","KeyField",.F.)
DBSetProp(ThisView+".部门名称","Field","Updatable",.T.)

DBSetProp(ThisView+".分部门名称","Field","DataType","C(20)")
DBSetProp(ThisView+".分部门名称","Field","UpdateName","dbo.SubDepartment.分部门名称")
DBSetProp(ThisView+".分部门名称","Field","KeyField",.F.)
DBSetProp(ThisView+".分部门名称","Field","Updatable",.T.)

DBSetProp(ThisView+".auto_id","Field","DataType","N(20)")
DBSetProp(ThisView+".auto_id","Field","UpdateName","dbo.SubDepartment.Auto_ID")
DBSetProp(ThisView+".auto_id","Field","KeyField",.T.)
DBSetProp(ThisView+".auto_id","Field","Updatable",.F.)

Use (ThisView) In 0
CursorSetProp("Buffering", 5, ThisView)
gnViewConn = CursorGetProp("CONNECTHANDLE",ThisView)



*这里数据更改、删除、增加代码略


*--------------然后更新远程视图


*开始服务器上(如：SQL Server)的事务处理，个人习惯用此种，因为如果视图带有自增量字段，必须REQUERY视图后游标才能看到最新增量后数值


SQLExec( gnViewConn, 'BEGIN TRANSACTION' )
llSuccess= Tableupdate( 1, .F., 'MyStaffInfoView' )
If llSuccess
	llSuccess= Tableupdate( 1, .F., 'MySubDepartmentView' )
	If llSuccess
		SQLExec( gnViewConn, 'IF @@TRANCOUNT > 0 COMMIT' )
	Else
		SQLExec( gnViewConn, 'IF @@TRANCOUNT > 0 ROLLBACK' )
		Tablerevert(.T.,'MyStaffInfoView')
		Tablerevert(.T.,'MySubDepartmentView')
		Messagebox("保存失败！操作将还原。",48,"信息提示")
	Endif
Else

	SQLExec( gnViewConn, 'IF @@TRANCOUNT > 0 ROLLBACK' )
	Tablerevert(.T.,'MyStaffInfoView')
	Messagebox("保存失败！操作将还原。",48,"信息提示")
	Requery('MyStaffInfoView')
Endif
Requery('MyStaffInfoView')
Requery('MySubDepartmentView')

#2

十豆三掌柜，问一下，你这段代码中：
gnViewConn = CursorGetProp("CONNECTHANDLE",ThisView)
gnViewConn = CursorGetProp("CONNECTHANDLE",ThisView)
前后两个返回值是一样的吗？
我试着按你的代码，不过是建了一个命名通道连接（我的机子用tcp/ip不能连），然后带Connection *** Share语句建了两个视图，两个视图的DBSetProp(ThisView,"View","ShareConnection",.T.)也设好了，打开两个视图后，句柄还是不一样，怎么回事呢？
水平太菜，见笑，多谢回复!

#3

在DBC中修改视图，在系统菜单QUERY->ADVANCED OPTIONS中将SHARE CONNECTION勾上试试

#4

返回值应该不一样。
不过不影响你的更新。

#5

搜索了一下，共享连接应该是同一个视图在不同的工作区打开时，只建立一个连接，
而不是两个视图共用一个连接。

#6

用共享连接的视图：
多个视图虽然用一个共享连接，但句柄是不同，即连接是连接，句柄是句柄。
用共享连接的视图，注意视图的FetchSize属性要设为-1，即取回完整的结果集，否则会报连接忙

#7

连接与句柄不是一个概念，如用SQLSERVER，在企业管理器中可以看到，使用共享连接，只有一个连接。

#8

按楼上两位的说明，做了一下试验，基本上明白了，多谢兄弟们！