Property	Value
SubType	Transform Function
Category	KimD
Author	Kim Duke
Arg Count	2
Version	2.0.0
Routine Type	0
Short Description	Last Changed: 07-04-2004
Long Description	Routine: KgdCreateDdlMetadata ------------------------------------------------------------------------------------- Non-technical description: Create DDL statement from records in DS_METADATA. ------------------------------------------------------------------------------------- Arguments: 1. DsMetadataId 2. OracleStyle ------------------------------------------------------------------------------------- Modification History: ------------------------------------------------------------------------------------- UserId Date Version Modification Description ------------------------------------------------------------------------------------- kduke 07-04-2004 1 Created job. -------------------------------------------------------------------------------------

Argument	Description
DsMetadataId
OracleStyle

LineNo	Source Code
1	FUNCTION KgdCreateDdlMetadata(DsMetadataId,OracleStyle)
2	* -----------------------------------------------------------------
3	* KgdCreateDdlMetadata(DsMetadataId, OracleStyle)
4	* Decription: Create DDL statement from records in DS_METADATA.
5	* Written by: Kim Duke
6	* Notes:
7	* -----------------------------------------------------------------
8	* $INCLUDE DSINCLUDE JOBCONTROL.H
9	Ans = ''
10	RoutineName = "KgdCreateDdlMetadata"
11	Usage = RoutineName:'(DsMetadataId, OracleStyle)'
12	* -----------------------------------------------------------------
13	* initialize variables
14	* -----------------------------------------------------------------
15	True = 1
16	False = 0
17	Other = True
18	Forever = True
19	Today = oconv(@DATE, "D4-")
20	ErrMsg = ''
21	NullRec = ''
22	CrLf = char(13):char(10)
23	DsMetadataSep = '\'
24	DebugMode = False
25	* -----------------------------------------------------------------
26	FileName = 'DS_METADATA'
27	open FileName to FilePtr else
28	ErrMsg = 'Can not open ':FileName:' file.'
29	goto ErrRtn
30	end
31	DsMetadataPtr = FilePtr
32	* -----------------------------------------------------------------
33	DDL = ''
34	PKeys = ''
35	* -----------------------------------------------------------------
36	read DsMetadataRec from DsMetadataPtr, DsMetadataId then
37	Occurrence = 1
38	* -----------------------------------------------------------------
39	FindColumns:
40	FindStr "CMetaColumn" In DsMetadataRec, Occurrence Setting AttrNo, MvNo, SvNo Then
41	* -----------------------------------------------------------------
42	* number of column definitions in collection
43	* -----------------------------------------------------------------
44	NoOfFields = Field(DsMetadataRec<AttrNo>, "/", 2, 1)
45	If NoOfFields = 0 Then
46	* Stored procedures use the second columns collection in the record.
47	* Go get the next one.
48	Occurrence += 1
49	GoTo FindColumns
50	End
51	TableName = field(DsMetadataId, DsMetadataSep, dcount(DsMetadataId, DsMetadataSep))
52	DDL<-1> = 'Create Table ':TableName
53	DDL<-1> = '('
54	Indent = spaces(3*1)
55	* NoOfFields = dcount(DsMetadataRec<AttrNo+1>,
56	for MvNo=1 to NoOfFields
57	ColumnName = DsMetadataRec<AttrNo+1, MvNo>
58	if DebugMode then print 'ColumnName=':ColumnName
59	ColumnDescription = DsMetadataRec<AttrNo+2, MvNo>
60	DataElement = DsMetadataRec<AttrNo+3, MvNo>
61	if DebugMode then print 'DataElement=':DataElement
62	SQLDataTypeCode = DsMetadataRec<AttrNo+4, MvNo>
63	if DebugMode then print 'SQLDataTypeCode=':SQLDataTypeCode
64	Precision = DsMetadataRec<AttrNo+5, MvNo>
65	Scale = DsMetadataRec<AttrNo+6, MvNo>
66	IsNullable = DsMetadataRec<AttrNo+7, MvNo>
67	if DebugMode then print 'IsNullable=':IsNullable
68	KeyPosition = DsMetadataRec<AttrNo+8, MvNo>
69	DisplayWidth = DsMetadataRec<AttrNo+9, MvNo>
70	Begin case
71	case SQLDataTypeCode = -7
72	SQLType = "BIT"
73	OracleType = "VARCHAR2"
74	case SQLDataTypeCode = -6
75	SQLType = "TINYINT"
76	OracleType = "NUMBER"
77	case SQLDataTypeCode = -5
78	SQLType = "BIGINT"
79	OracleType = "NUMBER"
80	case SQLDataTypeCode = -4
81	SQLType = "LONGVARBINARY"
82	OracleType = "VARCHAR2"
83	case SQLDataTypeCode = -3
84	SQLType = "VARBINARY"
85	OracleType = "VARCHAR2"
86	case SQLDataTypeCode = -2
87	SQLType = "BINARY"
88	OracleType = "VARCHAR2"
89	case SQLDataTypeCode = -1
90	SQLType = "LONGVARCHAR"
91	OracleType = "VARCHAR2"
92	case SQLDataTypeCode = 0
93	SQLType = "VARCHAR"
94	OracleType = "VARCHAR2"
95	case SQLDataTypeCode = 1
96	SQLType = "CHAR"
97	OracleType = "CHAR"
98	case SQLDataTypeCode = 2
99	SQLType = "NUMERIC"
100	OracleType = "NUMBER"
101	case SQLDataTypeCode = 3
102	SQLType = "NUMERIC"
103	OracleType = "NUMBER"
104	case SQLDataTypeCode = 4
105	SQLType = "INTEGER"
106	OracleType = "NUMBER"
107	case SQLDataTypeCode = 5
108	SQLType = "SMALLINT"
109	OracleType = "NUMBER"
110	case SQLDataTypeCode = 6
111	SQLType = "FLOAT"
112	OracleType = "NUMBER"
113	case SQLDataTypeCode = 7
114	SQLType = "REAL"
115	OracleType = "NUMBER"
116	case SQLDataTypeCode = 8
117	SQLType = "DOUBLE"
118	OracleType = "NUMBER"
119	case SQLDataTypeCode = 9
120	SQLType = "DATE"
121	OracleType = "DATE"
122	case SQLDataTypeCode = 10
123	SQLType = "TIME"
124	OracleType = "DATE"
125	case SQLDataTypeCode = 11
126	SQLType = "TIMESTAMP"
127	OracleType = "DATE"
128	case SQLDataTypeCode = 12
129	SQLType = "VARCHAR"
130	OracleType = "VARCHAR2"
131	case True
132	SQLType = "VARCHAR"
133	OracleType = "VARCHAR2"
134	end case
135	if OracleStyle then
136	If OracleType = "NUMBER" Then
137	If Precision = @NULL Then
138	DataType = OracleType
139	End Else
140	If Scale = @NULL Then
141	DataType = OracleType : "(" : Precision : ")"
142	End Else
143	DataType = OracleType : "(" : Precision : "," : Scale : ")"
144	End
145	End
146	End Else
147	if OracleType = "DATE" then
148	DataType = OracleType
149	end else
150	DataType = OracleType : "(" : Precision : ")"
151	end
152	End
153	end else
154	If SQLType = "NUMERIC" Then
155	If Precision = @NULL Then
156	DataType = SQLType
157	End Else
158	If Scale = @NULL Then
159	DataType = SQLType : "(" : Precision : ")"
160	End Else
161	DataType = SQLType : "(" : Precision : "," : Scale : ")"
162	End
163	End
164	End Else
165	if SQLType = "DATE" or SQLType = "TIME" or SQLType = "TIMESTAMP" then
166	DataType = SQLType
167	end else
168	DataType = SQLType : "(" : Precision : ")"
169	end
170	End
171	end
172	if DebugMode then print 'DataType=':DataType
173	if IsNullable then
174	NullAble = ''
175	end else
176	NullAble = ' NOT NULL'
177	end
178	if DebugMode then print 'NullAble=':NullAble
179	if MvNo = 1 then
180	DDL<-1> = Indent:' ':ColumnName "L#40":' ':DataType:NullAble
181	end else
182	DDL<-1> = Indent:', ':ColumnName "L#40":' ':DataType:NullAble
183	end
184	if DebugMode then print 'KeyPosition=':KeyPosition
185	if KeyPosition then
186	PKeys<-1> = ColumnName
187	end
188	next MvNo
189	if not(OracleStyle) then
190	Indent = spaces(3*1)
191	DDL<-1> = Indent:', Primary Key ('
192	NoOfLines = dcount(PKeys, @FM)
193	for i=1 to NoOfLines
194	Indent = spaces(3*2)
195	if DebugMode then print PKeys<i>
196	if i =1 then
197	DDL<-1> = Indent:' ':PKeys<i>
198	end else
199	DDL<-1> = Indent:', ':PKeys<i>
200	end
201	next i
202	Indent = spaces(3*1)
203	DDL<-1> = Indent:')'
204	end
205	DDL<-1> = ')'
206	DDL<-1> = ';'
207	if OracleStyle then
208	DDL<-1> = ' '
209	Indent = spaces(3*1)
210	DDL<-1> = 'Alter Table ':TableName:' Add ('
211	DDL<-1> = Indent:'Primary Key ('
212	NoOfLines = dcount(PKeys, @FM)
213	for i=1 to NoOfLines
214	Indent = spaces(3*2)
215	if DebugMode then print PKeys<i>
216	if i =1 then
217	DDL<-1> = Indent:' ':PKeys<i>
218	end else
219	DDL<-1> = Indent:', ':PKeys<i>
220	end
221	next i
222	Indent = spaces(3*1)
223	DDL<-1> = Indent:')'
224	DDL<-1> = ')'
225	DDL<-1> = ';'
226	end
227
228	if DebugMode then print
229	NoOfLines = dcount(DDL, @FM)
230	for i=1 to NoOfLines
231	if DebugMode then print DDL<i>
232	next i
233
234	Ans = DDL
235	end else
236	ErrMsg = 'Could not find CMetaColumn in ':DsMetadataId
237	goto ErrRtn
238	end
239	end else
240	ErrMsg = 'Could not find ':DsMetadataId:' in DS_METADATA.'
241	goto ErrRtn
242	end
243
244	goto TheEnd
245	* -----------------------------------------------------------------
246	ErrRtn:
247	if DebugMode then print
248	if DebugMode then print ErrMsg
249	if DebugMode then print
250	Ans = ErrMsg
251	* -----------------------------------------------------------------
252	TheEnd:
253	if DebugMode then print
254	if DebugMode then print
255	if DebugMode then print Ans
256	if DebugMode then print
257
258	RETURN(Ans)