利用動态資料管道技術遷移不同版本或者不同類型資料庫資料

本場景案例是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了，以免空字段無法插入問題

後續再補充完善，記錄一下，以備後用！

利用動态資料管道技術遷移不同版本或者不同類型資料庫資料

繼續閱讀

個人筆記：抽象

SpringMVC筆記整理

Ubuntu18.0顯示卡驅動安裝

《敏控創變》--收益是項目的最終目标

指令行模式進入檔案夾

随手一記(java計算之百分數保留兩位小數)

我的程式設計易錯點總結

Ubuntu建立快捷方式（eclipse）

8.Java學習個人筆記----異常

輸入參數首字母大寫

C# 打開浏覽器

NDIS筆記---（1）

element-ui合并表格、表格内編輯、vue父元件向子元件傳引用類型值問題解決

Python學習筆記提前說說

對于svn的總結，包括Android studio配置svn, svn第一次上傳項目，及忽略項目檔案

Uncaught DOMException: Failed to set the 'value' property on 'HTMLInputElement': This input element