$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