下面看看,如何根据中间层的数据,构建管道语法:
1.首先建立一个数据窗口对象:d_vdtcolumns
SQL语法是:
SELECT vdt_columns.utid,
vdt_columns.uid,
vdt_columns.upkey,
vdt_columns.udmid,
vdt_columns.udmname,
vdt_columns.unulls,
vdt_columns.uwidth,
vdt_columns.uscale,
vdt_columns.uname,
vdt_columns.udefault,
vdt_columns.ucheck,
vdt_columns.utname,
vdt_columns.uidentity
FROM vdt_columns
WHERE utname = :as_tname
2.准备工作就绪,下面就是主战场了,开始构建数据管道.
考虑到一个管道对象可以传输多个任务,建立一个对象nvo_pipetransattrib保存传输需要的语法:
它包含了一下的instance变量:
string is_objectname //表名
string is_syntax //管道语法
string is_sconnect='zw',is_dconnect='daixf' //源数据库连接和目的数据库连接
string is_ptype,is_pcommit,is_errors //管道的几个属性
string is_sname,is_dname //源表名,目的表名
string is_sqlsyntax //管道的SQL语法
建立一个对象,从数据管道对象继承.
开始构造语法:写一个函数.
nvo_pipetransattrib inv_attrib[]
string ls_syntax,ls_sourcesyntax,ls_destsyntax
int li,lj,li_ind,li_find,li_rows,li_identity
string ls_tablename,ls_default,ls_defaultvalue,ls_pbdttype
boolean lb_find
dec ld_uwidth,ld_prec,ld_uscale
string ls_types,ls_dbtype,ls_prikey,ls_name,ls_nulls,ls_msg,ls_title='of_constrpipesyntax()'
nvo_string lnv_string
nvo_datastore lds_vdtcolumns
boolean lb_key
lds_vdtcolumns=create nvo_datastore
lds_vdtcolumns.dataobject='d_vdtcolumns'
lds_vdtcolumns.settransobject(SrcSqlca)
li=1
of_input(inv_attrib[li])
li_find=pos(inv_attrib[li].is_sqlsyntax,'*',1)
if li_find>0 then
lds_vdtcolumns.retrieve(as_tablename)
of_filterimg(lds_vdtcolumns)
li_rows=lds_vdtcolumns.rowcount()
for lj=1 to li_rows
ls_name=lds_vdtcolumns.getitemstring(lj,'uname')
ls_types=lds_vdtcolumns.getitemstring(lj,'udmname')
li_identity = lds_vdtcolumns.getitemnumber(lj,'uidentity')
ls_types=of_getpipedbtype(is_s_dbtype,ls_types)
ls_pbdttype=of_getpbdttype(is_s_dbtype,ls_types)
choose case ls_types
case 'char','varchar','nchar','nvarchar','long varchar'
if ls_types='long varchar' then ls_types='varchar'
ld_uwidth=lds_vdtcolumns.getitemnumber(lj,'uwidth')
ls_dbtype=ls_types+'('+string(int(ld_uwidth))+')'
case 'decimal','numeric'
ld_uwidth=lds_vdtcolumns.getitemnumber(lj,'uwidth')
ld_uscale=lds_vdtcolumns.getitemnumber(lj,'uscale')
if li_identity=1 then
ls_dbtype='identity'+'('+string(int(ld_uwidth))+','+string(int(ld_uscale))+')'
else
ls_dbtype=ls_types+'('+string(int(ld_uwidth))+','+string(int(ld_uscale))+')'
end if
case else
ls_dbtype=ls_types
end choose
ls_prikey=lds_vdtcolumns.getitemstring(lj,'upkey')
if ls_prikey='Y' then
lb_key=true
ls_prikey='key=yes,'
else
ls_prikey=''
end if
ls_nulls=lds_vdtcolumns.getitemstring(lj,'unulls')
if ls_nulls='Y' then
ls_nulls='yes'
else
ls_nulls='no'
end if
ls_default=isnull(lds_vdtcolumns.getitemstring(lj,'udefault'),'')
ls_sourcesyntax+="COLUMN(type="+ls_pbdttype+",name=~""+ls_name+"~",dbtype=~""+ls_dbtype+"~","+ls_prikey+"nulls_allowed="+ls_nulls+")~r~n"
if ls_default='' then
if li_identity = 1 then
ls_destsyntax+="COLUMN(type="+ls_pbdttype+",name=~""+ls_name+"~",dbtype=~""+ls_dbtype+"~","+ls_prikey+"nulls_allowed="+ls_nulls+",initial_value=~"exclude~")~r~n"
else
ls_destsyntax+="COLUMN(type="+ls_pbdttype+",name=~""+ls_name+"~",dbtype=~""+ls_dbtype+"~","+ls_prikey+"nulls_allowed="+ls_nulls+")~r~n"
end if
else
if li_identity = 1 then
ls_destsyntax+="COLUMN(type="+ls_pbdttype+",name=~""+ls_name+"~",dbtype=~""+ls_dbtype+"~","+ls_prikey+"nulls_allowed="+ls_nulls+",default_value=~""+ls_default+"~",initial_value=~"exclude~")~r~n"
else
ls_destsyntax+="COLUMN(type="+ls_pbdttype+",name=~""+ls_name+"~",dbtype=~""+ls_dbtype+"~","+ls_prikey+"nulls_allowed="+ls_nulls+",default_value=~""+ls_default+"~")~r~n"
end if
end if
next
else
return ''
end if
ls_sourcesyntax+=')'
ls_destsyntax+=')'
//generate PIPELINE
//example:
//PIPELINE(source_connect=csfdata,destination_connect=csfdata,type=replace,commit=100,errors=100,keyname="Bar_x")
if lb_key then
ls_syntax+='PIPELINE(source_connect='+inv_attrib[li].is_sconnect+',destination_connect='+inv_attrib[li].is_dconnect+',type='+inv_attrib[li].is_ptype+',commit='+inv_attrib[li].is_pcommit+',errors='+inv_attrib[li].is_errors+',keyname="'+as_tablename+'_x")~r~n'
else
ls_syntax+='PIPELINE(source_connect='+inv_attrib[li].is_sconnect+',destination_connect='+inv_attrib[li].is_dconnect+',type='+inv_attrib[li].is_ptype+',commit='+inv_attrib[li].is_pcommit+',errors='+inv_attrib[li].is_errors+')~r~n'
end if
//generate SOURCE
//example:
//SOURCE(name="Bar",COLUMN(type=char,name="CustomCode",dbtype="char(8)",key=yes,nulls_allowed=no)
ls_syntax+='SOURCE(name="'+inv_attrib[li].is_sname+'",'
ls_syntax+=ls_sourcesyntax
//generate RETRIEVE
//example:
//RETRIEVE(statement="SELECT Bar.CustomCode,Bar.BarCode,Bar.ItemCode,Bar.Metering,Bar.PackSize,Bar.Length,Bar.Width,Bar.High,Bar.Vol,Bar.Weight,Bar.NewPackFlagFROM Bar")
ls_syntax+='RETRIEVE(statement="'+inv_attrib[li].is_sqlsyntax+'")'
//generate DESTINATION
//example:
//DESTINATION(name="Bar_copy",
//COLUMN(type=char,name="CustomCode",dbtype="char(8)",key=yes,nulls_allowed=no,initial_value="spaces")
ls_syntax+='DESTINATION(name="'+inv_attrib[li].is_dname+'",'
ls_syntax+=ls_destsyntax
return ls_syntax
这个函数的返回值就是构建完成的管道语法了.
其中:初始化的函数:of_input(inv_attrib[li])
是初始化,inv_attrib的函数,初始化的数据主要是用户需要输入的条件,比如管道的type,commit,errors,select语句.
需要说明一下,其中处理了几个特殊的情况的函数.
of_filterimg(lds_vdtcolumns):
过滤掉表中的image列,因为管道不支持image数据传输.
of_getpipedbtype(is_s_dbtype,ls_types):
根据表中列的类型得到管道中数据列的类型,因为他们不是总是一一对应的.
这个可以通过一个extend datawindowobject,并包含有初始数据来实现.
of_getpbdttype(is_s_dbtype,ls_types):
根据表中列的类型得到管道中列的类型,因为他们也不是总是一一对应的.
这个可以通过一个extend datawindowobject,并包含有初始数据来实现.
管道语法构建完成了,就可以执行管道传输了:
this.syntax=得到的语法
li_RC = this.Start(SrcSqlca,DestSqlca,idw_Errors)
If li_RC <> 1 Then
if not ib_silence then msg(ls_title,"对象传输失败: " + string(li_rc))
of_addtransmsg(' 对象<'+is_currentobj+'>传输失败：' + string(li_rc) )
return li_RC
rollback ;
else
Commit;
End if
