利用动态数据管道技术迁移不同版本或者不同类型数据库数据

本场景案例是Informix数据库从7.3版本往11.0版本迁移数据，由于版本跨度过大，数据库文件系统结构不同，导致无法通过服务器打包数据方式来迁移数据，因此用到动态数据管道来实现数据迁移

global type f_pipeline_full from function_object
end type

forward prototypes
global function string f_pipeline_full (transaction atrans_source, transaction atrans_destination, string as_tablename, integer ai_pipe_type)
end prototypes

global function string f_pipeline_full (transaction atrans_source, transaction atrans_destination, string as_tablename, integer ai_pipe_type);//====================================================================
// 事件: .f_pipeline_full()
//--------------------------------------------------------------------
// 描述:用于Informix数据库版本跨度过大，无法通过服务器打包恢复数据，故通过构造动态数据管道来迁移数据
//			该函数目前待完善处：1.无法获取column的null属性，暂时把管道中该属性全部填充nulls_allowed=yes，以免空字段传输异常
//--------------------------------------------------------------------
// 参数:
// 	value	transaction	atrans_source     	
// 	value	transaction	atrans_destination	
// 	value	string     	as_tablename      	
// 	value	integer    	ai_pipe_type      	
//--------------------------------------------------------------------
// 返回:  string
//--------------------------------------------------------------------
// 作者:	JGM		日期: 2018年03月10日
//--------------------------------------------------------------------
//	Copyright (c) 2017 JGM (TM), All rights reserved.
//--------------------------------------------------------------------
// 修改历史:
//
//====================================================================

Int i ,li_columncount , li_return
String ls_pipeline1,ls_pipeline2,ls_pipeline3,ls_pipeline4,ls_pipesyn

//定义数据管道操作方式
String ls_pipetype[5] = {"create" , "replace" ,"refresh" , "append" , "update" }

String ls_sourcetype,ls_destype,ls_colname,ls_sourcedbtype,ls_desdbtype,ls_inital,ls_sourcetype_temp,ls_source_key
String ls_insertcol,ls_sel,errors
pipeline pipe_sourcetodes
datastore lds_source

ls_sel = "SELECT * FROM " + as_tablename
lds_source = Create datastore

//动态创建datastore，用以获取字段名及数据类型
lds_source.Create(atrans_source.SyntaxFromSQL ( ls_sel ,"style(type=grid)",errors))

If Len(errors) > 0 Then Return '动态数据存储创建失败'

li_columncount = Integer(lds_source.Object.DataWindow.Column.Count)

pipe_sourcetodes = Create pipeline

If ai_pipe_type <= 2 Then
	ls_pipeline1 = 'PIPELINE(source_connect=lybh,destination_connect=jdbh,type='+ls_pipetype[ai_pipe_type]+',commit=100,errors=100,keyname="' + as_tablename + '_x")'
Else
	Return '数据管道传输方式暂未写'
End If

ls_pipeline2 = 'SOURCE(name="' + as_tablename + '",'

ls_pipeline3 = "RETRIEVE(statement=~"PBSELECT(VERSION(400)TABLE ( NAME = ~~~""+as_tablename + "~~~")"

//"~""双引号中"字符串需转义~"，等同于'"'，两者并无区别，就像单纯引用字符串用单引号''，和双引号""，并无区别

ls_pipeline4 = "DESTINATION(name=~""+as_tablename+"~","

//循环获取字段名及数据类型，构造动态数据管道语法
For i = 1 To li_columncount
	ls_colname = String(lds_source.Describe("#"+String(i)+".dbname"))
	ls_sourcetype_temp = String(lds_source.Describe("#"+String(i)+".coltype"))
	ls_source_key = String(lds_source.Describe("#"+String(i)+".key"))
	
	//判断字段数据类型，根据数据类型构造动态数据管道语法	
	Choose Case Upper(Left(ls_sourcetype_temp,3))
	
		Case "CHA"
			ls_sourcetype = "char"
			ls_sourcedbtype = "~"CHAR"+Right(ls_sourcetype_temp,Len(ls_sourcetype_temp)-Pos(ls_sourcetype_temp,"(")+1)+"~""
			ls_destype = "char"
			ls_desdbtype = "~"CHAR"+Right(ls_sourcetype_temp,Len(ls_sourcetype_temp)-Pos(ls_sourcetype_temp,"(")+1)+"~""
			ls_inital = "~"spaces~""
		Case "DEC"
			ls_sourcetype = "decimal"
			ls_sourcedbtype = "~"DECIMAL"+"(20,"+Right(ls_sourcetype_temp,Len(ls_sourcetype_temp)-Pos(ls_sourcetype_temp,"("))+"~""
			ls_destype = "decimal"
			ls_desdbtype = "~"DECIMAL"+"(20,"+Right(ls_sourcetype_temp,Len(ls_sourcetype_temp)-Pos(ls_sourcetype_temp,"("))+"~""
			ls_inital = "~"0~""
		Case "DAT"
			If Upper(ls_sourcetype_temp) = "DATE" Then
				ls_sourcetype = "date"
				ls_sourcedbtype = '"DATE"'
				ls_destype = "date"
				ls_desdbtype = '"DATE"'
			ElseIf Upper(ls_sourcetype_temp) = "DATETIME" Then
				ls_sourcetype = "datetime"
				ls_sourcedbtype = '"DATETIME"'
				ls_destype = "datetime"
				ls_desdbtype = '"DATETIME"'
			End If
			ls_inital = "~"today~""
		Case "INT"
			ls_sourcetype = "int"
			ls_sourcedbtype = '"SMALLINT"'
			ls_destype = "int"
			ls_desdbtype = '"SMALLINT"'
			ls_inital = "~"0~""
		Case "LON"
			ls_sourcetype = "long"
			ls_sourcedbtype = '"INTEGER"'
			ls_destype = "long"
			ls_desdbtype = '"INTEGER"'
			ls_inital = "~"0~""
		Case "DOU"
			ls_sourcetype = "double"
			ls_sourcedbtype = '"FLOAT"'
			ls_destype = "double"
			ls_desdbtype = '"FLOAT"'
			ls_inital = "~"0~""
		Case "TIM"
			ls_sourcetype = "time"
			ls_sourcedbtype = '"TIME"'
			ls_destype = "time"
			ls_desdbtype = '"TIME"'
			ls_inital = "~"0~""
	End Choose
	
	ls_pipeline3+= "COLUMN(NAME=~~~""+as_tablename+"."+ls_colname+"~~~")"
	//判断主键标识
	If Upper(Trim(ls_source_key)) = "YES" Then
		ls_pipeline2+= "COLUMN(type="+ls_sourcetype+",name=~""+ls_colname+"~",dbtype="+ls_sourcedbtype+",key=yes,nulls_allowed=yes)"+"~r~n"
		ls_pipeline4+= "COLUMN(type="+ls_destype+",name=~""+ls_colname+"~",dbtype="+ls_desdbtype+",key=yes,nulls_allowed=yes,initial_value="+ls_inital+")"+"~r~n"
	Else
		ls_pipeline2+= "COLUMN(type="+ls_sourcetype+",name=~""+ls_colname+"~",dbtype="+ls_sourcedbtype+",nulls_allowed=yes)"+"~r~n"
		ls_pipeline4+= "COLUMN(type="+ls_destype+",name=~""+ls_colname+"~",dbtype="+ls_desdbtype+",nulls_allowed=yes,initial_value="+ls_inital+")"+"~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


MessageBox("",ls_pipesyn)


pipe_sourcetodes.Syntax = ls_pipesyn //将数据管道语法字符串赋值给Syntax属性
li_return = pipe_sourcetodes.Start(atrans_source,atrans_destination,lds_source) //启动数据管道

Destroy lds_source
Destroy pipe_sourcetodes

Choose Case li_return //返回数据管道执行成功标志或错误代码
	Case 1
		Return "传输成功"
	Case -1
		Return("管道不能打开")
	Case -3
		Return("目标表在目标数据库中已存在。")
	Case -4
		Return("源数据库中不存在选定表。")
	Case -2
		Return("列太多。")
	Case -5
		Return("联结错误。")
	Case -6
		Return("检索变量错误。")
	Case -7
		Return("列不匹配。")
	Case -8
		Return("源中有致命的SQL错误。")
	Case -9
		Return("目标中有致命的SQL错误。")
	Case -10
		Return("超出了最大的错误数。")
	Case -12
		Return("表语法错误。")
	Case -13
		Return("没有提供必须的主键。")
	Case -15
		Return("管道操作已经进行。")
	Case -16
		Return("源数据库中有错误。")
	Case -17
		Return("目标数据库中有错误。")
	Case -18
		Return("目标数据库是只读的。")
	Case Else
		Return("未知错误")
End Choose




end function

核心思路就是通过datastore动态获取表结构，然后动态生成数据管道的syntax，其中有一个问题由于才疏学浅暂未找到办法，就是如何获取column的null属性，暂时处理方案是在数据管道拼接syntax是把nulls_allowed属性全设为yes了，以免空字段无法插入问题

后续再补充完善，记录一下，以备后用！