pb pipe管道同步数据

$PBExportHeader$gf_pipeline.srf
global type gf_pipeline from function_object
end type
 
forward prototypes
global function long gf_pipeline (transaction as_oritransaction, transaction as_taransaction, string as_ori_tablename, string as_ori_sql, string as_pipe_type)
end prototypes
 
global function long gf_pipeline (transaction as_oritransaction, transaction as_taransaction, string as_ori_tablename, string as_ori_sql, string as_pipe_type);//==============================================================================
// 函数: f_pipeline()
//------------------------------------------------------------------------------
// 描述: 动态数据管道
//------------------------------------------------------------------------------
// 参数: 
//		value	transaction	as_oritransaction		源事务
//		value	transaction	as_taransaction  		目的事务
//		value	string     	as_ori_tablename 		表名
//		value	string     	as_ori_tablename 		语句，没有语句 select * from 表名
//		value	string     	as_pipe_type     		类型  Refresh
//------------------------------------------------------------------------------
// 返回值:  long   <>1错误
//------------------------------------------------------------------------------
// 作者:	牛三斤 QQ719297063		日期: 2018-09-28
//------------------------------------------------------------------------------
// 修改历史: 
//	f_pipeline(sqlca,sql_tmp,'供货商表','Refresh')
//==============================================================================
 
if as_pipe_type = '' then as_pipe_type = 'Refresh'
datastore lds_1
integer li_return,li_columncount 
long ll_i
string ls_sel,ls_errors,ls_pipeline1,ls_pipeline2,ls_pipeline3,ls_pipeline4,ls_inital
string ls_colname,ls_foxtype1,ls_foxtype,ls_foxdbtype,ls_dbtype
string ls_pipetype[] = {""},ls_pipesyn
string gs_dsn,gs_tardsn
string ls_err
gs_dsn = 'A'  //gs_dsn//源数据库      gs_tardsn目标数据库
gs_tardsn = 'B'
if len(as_ori_sql)=0 then
	ls_sel = 'select * from ' + as_ori_tablename
else
	ls_sel=as_ori_sql
end if 
 
 
lds_1 = create datastore
lds_1.create(as_oritransaction.syntaxfromsql(ls_sel,"style(type=grid)",ls_errors))
if len(ls_errors) > 0 then 
	messagebox('','创建数据窗口失败'+ls_errors)
	return -99
end if 
 
//计算数据存储总的列数
li_columncount = integer(lds_1.object.datawindow.column.count)
 
//定义数据管道对象
pipeline pipe_data
pipe_data = create pipeline
 
//构造数据管道语法
ls_pipeline1 = "PIPELINE(source_connect="+gs_dsn+",destination_connect="+gs_tardsn+",type="+as_pipe_type+",commit=10000,errors=100)"
ls_pipeline2 = "SOURCE(name=~""+as_ori_tablename+"~","
ls_pipeline3 = "RETRIEVE(statement=~"pbselect(version(400) table(name=~~~""+as_ori_tablename+"~~~")"
ls_pipeline4 = "DESTINATION(name=~""+as_ori_tablename+"~","
//对总的列数做循环
for ll_i = 1 to li_columncount
	//获取该列对应的数据库列的名称
	ls_colname = string(lds_1.describe('#'+string(ll_i)+".dbname"))
	if pos(ls_colname,'.') > 0 then ls_colname = mid(ls_colname,pos(ls_colname,'.') +1)
	//获取该列的数据类型
	ls_foxtype = string(lds_1.describe('#'+string(ll_i)+".coltype"))
	if pos(ls_foxtype,'(') > 0 then
		ls_dbtype = left(ls_foxtype,pos(ls_foxtype,'(') -1)
		if ls_dbtype = 'char' then ls_dbtype = 'varchar'
	else
		ls_dbtype = ls_foxtype
	end if
	if lower(mid(ls_foxtype,1,4)) = 'char' then ls_foxtype = replace(ls_foxtype,1,4,'varchar')
	if lower(ls_foxtype) = 'image' then continue
	if lower(ls_foxtype) = 'text' then continue
	
	//对不同类型进行不同的处理
	//choose case upper(left(ls_foxtype,3))
	//case "chr"
	
	//case "dec"
	
	//
	//case "int"
	//  //
	//  end choose
	if lower(ls_colname) = 'num' then
		ls_pipeline2+= "COLUMN(type=long,name=~""+ls_colname+"~",dbtype='int',nulls_allowed=no')"+"~r~n"
		ls_pipeline3+= "COLUMN(name=~~~""+as_ori_tablename+"."+ls_colname+"~~~")"
		ls_pipeline4+= "COLUMN(type=long,name=~""+ls_colname+"~",dbtype='identity int',nulls_allowed=no,initial_value=~"exclude~")"+"~r~n"
	else
		ls_pipeline2+= "COLUMN(type="+ls_dbtype+",name=~""+ls_colname+"~",dbtype=~""+ls_foxtype+"~",nulls_allowed=yes)"+"~r~n"
		ls_pipeline3+= "COLUMN(name=~~~""+as_ori_tablename+"."+ls_colname+"~~~")"
		ls_pipeline4+= "COLUMN(type="+ls_dbtype+",name=~""+ls_colname+"~",dbtype=~""+ls_foxtype+"~",nulls_allowed=yes)"+"~r~n"
	end if
next
ls_pipeline2+= +")"
ls_pipeline3+= ")~")"
ls_pipeline4+= +")"
ls_pipesyn = ls_pipeline1+"~r~n"+ls_pipeline2+"~r~n"+ls_pipeline3+"~r~n"+ls_pipeline4
pipe_data.syntax = ls_pipesyn
li_return = pipe_data.start(as_oritransaction,as_taransaction,lds_1)
if li_return<0 then 
	choose case li_return
		case -1
			ls_err='管道打开失败'
		case -2
			ls_err='列太多'
		case -3
			ls_err='目标表已经存在'
		case -4
			ls_err='目标表不存在'
		case -5
			ls_err='联结错误'
		case -6
			ls_err='检索变量错误'
		case -7
			ls_err='列不匹配'
		case -8
			ls_err='源SQL错误'
		case -9
			ls_err='目标SQL错误'
		case -10
			ls_err='超出了最大的错误数'
		case -11
			ls_err='未知错误'
		case -12
			ls_err='表语法错误'
		case -13
			ls_err='没有提供必须的主键'
		case -14
			ls_err='未知错误'
		case -15
			ls_err='管道操作已经进行'
		case -16
			ls_err='源数据库中有错误'
		case -17
			ls_err='目标数据库中有错误'
		case -18
			ls_err='目标数据库是只读的'
		case else
			ls_Err='未知错误'
	end choose	
end if 
destroy lds_1;
destroy pipe_data;
if li_return<0 then 
	messagebox('pipeline','错误原因:'+ls_err+'~r~n~错误代码:'+string(li_return))
end if 
return li_return
/*
返回值 含义
1 函数执行成功
-1 管道打开失败
-2 列太多
-3 目标表已经存在
-4 目标表不存在
-5 联结错误
-6 检索变量错误
-7 列不匹配
-8 源中有致命的SQL错误
-9 目标中有致命的SQL错误
-10 超出了最大的错误数
-12 表语法错误
-13 没有提供必须的主键
-15 管道操作已经进行
-16 源数据库中有错误
-17 目标数据库中有错误
-18 目标数据库是只读的
*/
end function