-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathD2_00_UTIL2.TPW
308 lines (299 loc) · 12.4 KB
/
D2_00_UTIL2.TPW
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! File Name: D2UTIL2.tpw
#! Purpose: Utilites to deal with dictionaries
#! Author: Copyright © 1999-2013 by Roberto Artigas Jr
#! All rights reserved world wide.
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#UTILITY (UTIL_MSSQL_Connect, '2012.05.16: [DCT2SQL-UTIL] MSSQL database connect procedure'), WIZARD
#!--------------------------------------------------------------------
#! Changes:
#! 2012.05.16 Roberto Artigas
#! * Creates a procedure to connect into an MSSQL database
#!--------------------------------------------------------------------
#PREPARE
#!
#DECLARE(%DictName) #! RA.2010.09.27
#DECLARE(%DictPath) #! RA.2010.09.27
#DECLARE(%nLoc1) #! RA.2010.09.27
#DECLARE(%nLoc2) #! RA.2010.09.27
#DECLARE(%aFileUser) #! RA.2010.09.27
#DECLARE(%aFileOpt1) #! RA.2010.09.27
#DECLARE(%aFileOpt2) #! RA.2010.09.27
#DECLARE(%aFileOpt3) #! RA.2010.09.27
#DECLARE(%aFileOpt4) #! RA.2010.09.27
#!
#SET(%nLoc1,INSTRING('\',%DictionaryFile,1,1))
#LOOP,WHILE(%nLoc1)
#SET(%nLoc2,%nLoc1)
#SET(%nLoc1,INSTRING('\',%DictionaryFile,1,%nLoc1+1))
#ENDLOOP
#SET(%DictName,UPPER(SUB(%DictionaryFile,%nLoc2+1,LEN(%DictionaryFile)-%nLoc2-4)))
#SET(%DictPath,UPPER(SUB(%DictionaryFile,1,%nLoc2)))
#!
#DECLARE(%appTables),UNIQUE
#!
#FOR(%File)
#!---------------------------------------------------------------!
#! RA.2001.12.01 - Ignore file place marker definitions. !
#! These are files that are defined but that contain no fields. !
#!---------------------------------------------------------------!
#IF(NOT ITEMS(%Field)) #! RA.2001.12.01
#CYCLE #! RA.2001.12.01
#ENDIF #! RA.2001.12.01
#SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
#SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
#SET(%aFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
#!SET(%aFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
#SET(%aFileOpt1,EXTRACT(%aFileUser,'RASQL',1))
#SET(%aFileOpt2,EXTRACT(%aFileUser,'RASQL',2))
#SET(%aFileOpt3,EXTRACT(%aFileUser,'RASQL',3))
#SET(%aFileOpt4,EXTRACT(%aFileUser,'RASQL',4))
#!-------------------------------------------------------------!
#! The first option RASQL(NO) controls if a file is processed. !
#!-------------------------------------------------------------!
#IF(SUB(%aFileOpt1,1,1)='N')
#CYCLE
#ENDIF
#!
#CASE(%FileType)
#OF ('VIEW')
#OROF('ALIAS')
#CYCLE
#ENDCASE
#!
#CASE(%FileDriver)
#OF('ASCII')
#OROF('BASIC')
#OROF('DOS')
#OROF('IN-MEMORY')
#CYCLE
#ENDCASE
#!
#ADD(%appTables, %File)
#ENDFOR
#ENDPREPARE
#!-----------------------------------------------------------------------------------!
#!-----------------------------------------------------------------------------------!
#SHEET
#TAB ('MSSQL Connect Database Procedure Wizard')
#DISPLAY('The MSSQL Connect Database Procedure Utility.')
#DISPLAY('Copyright 2009-2013 © by Roberto Artigas Jr.')
#DISPLAY('All Rights Reserved World Wide.')
#DISPLAY('')
#DISPLAY('This wizard will create a procedure to connect to the PostgreSQL database.')
#DISPLAY('To specify how you want the procedure constructed, click on the Next button.')
#ENDTAB
#TAB ('MSSQL Database Connect Procedure Wizard - Connect ALL'), FINISH(1)
#DISPLAY('You can connect to all files in your dictionary, or you can select individual files to connect.')
#DISPLAY('')
#PROMPT ('Connect to all files in my dictionary.', CHECK), %DictionaryReportUseAllFiles, DEFAULT(1), AT(10)
#ENDTAB
#TAB ('MSSQL Database Connect Procedure Wizard - Connect SOME'), WHERE(~%DictionaryReportUseAllFiles), FINISH(1)
#PROMPT ('Table Selection', FROM (%appTables)), %DictionaryReportFileSelection, INLINE, SELECTION('Table Selection')
#ENDTAB
#TAB ('MSSQL Database Connect Procedure Wizard - Generate Procedure'), FINISH(1)
#DISPLAY('Generate the PostgreSQL database connect procedure.')
#DISPLAY('')
#PROMPT ('Generate database connect procedure.', CHECK), %InitializeGenerate, DEFAULT(1), AT(10)
#PROMPT ('Import database connect procedure.', CHECK), %InitializeImport, DEFAULT(0), AT(10)
#DISPLAY('')
#ENDTAB
#TAB ('MSSQL Database Connect Procedure Wizard - Finally...')
#DISPLAY ('This concludes the MSSQL database connect wizard.')
#DISPLAY ('Click on the Finish button to generate the procedure file.')
#ENDTAB
#ENDSHEET
#!--------------------------------------------------------------------
#DECLARE(%ReportedFiles),UNIQUE
#DECLARE(%filName,%ReportedFiles)
#!#DECLARE(%filDB,%ReportedFiles)
#!#DECLARE(%filSC,%ReportedFiles)
#!
#DECLARE(%sFileName) #! RA.2010.09.27
#DECLARE(%sFileNameInternal) #! RA.2010.09.27
#DECLARE(%sFileNameExternal) #! RA.2010.09.27
#DECLARE(%OutputFile)
#DECLARE(%CountFiles)
#DECLARE(%txaLine)
#EQUATE (%AtColPos, 19)
#!
#EQUATE (%AtColPos1, 35) #! RA.2010.09.27
#EQUATE (%AtColPos2, 91) #! RA.2010.09.27
#!--------------------------------------------------------------------
#IF (%DictionaryReportUseAllFiles)
#FOR (%appTables)
#FIX (%File, %appTables)
#ADD (%ReportedFiles, %appTables)
#SET (%filName, %appTables)
#ENDFOR
#ELSE
#FOR (%DictionaryReportFileSelection)
#FIX (%File, %DictionaryReportFileSelection)
#ADD (%ReportedFiles, %DictionaryReportFileSelection)
#SET (%filName, %DictionaryReportFileSelection)
#ENDFOR
#ENDIF
#!--------------------------------------------------------------------
#!--------------------------------------------------------------------
#! RA.2007.12.29 - Generate so I do not have to code it by hand.
#!--------------------------------------------------------------------
#IF(%InitializeGenerate)
#SET(%OutputFile, UPPER(SUB(%DictionaryFile, 1, LEN(%DictionaryFile) - 4)) & '_MSSQL_CONNECT.TXA')
#CREATE (%OutputFile)
#INSERT(%TxaMsSqlConnect)
#CLOSE (%OutputFile)
#ENDIF
#!--------------------------------------------------------------------
#IF(%InitializeImport)
#IMPORT(%OutputFile),REPLACE
#ENDIF
#!--------------------------------------------------------------------
#!--------------------------------------------------------------------
#!--------------------------------------------------------------------
#!--------------------------------------------------------------------
#GROUP(%TxaMsSqlConnect)
[PROCEDURE]
NAME MSSQL_Database_Connect
NOEXPORT
[COMMON]
DESCRIPTION '%@D010.@(TODAY()): Connect to MSSQL database.'
FROM ABC Source
CATEGORY 'MSSQL'
MODIFIED '%@D010@(TODAY())' '%@T04@(CLOCK())'
[FILES]
[OTHERS]
[PROMPTS]
%%ProcedureParameters MULTI LONG ()
%%ProcedureParameterName DEPEND %%ProcedureParameters DEFAULT TIMES 0
%%ProcedureParameterType DEPEND %%ProcedureParameters DEFAULT TIMES 0
%%ProcedureParameterDefault DEPEND %%ProcedureParameters DEFAULT TIMES 0
%%ProcedureParameterOmitted DEPEND %%ProcedureParameters LONG TIMES 0
%%ProcedureParameterByReference DEPEND %%ProcedureParameters LONG TIMES 0
%%Parameters DEFAULT ('')
%%GenerateOpenClose LONG (0)
%%GenerateSaveRestore LONG (0)
[EMBED]
EMBED %%DataSection
[DEFINITION]
[SOURCE]
PROPERTY:BEGIN
PRIORITY 3400
PROPERTY:END
!=====================================================!
! 2012.05.16 - Roberto Artigas !
! This procedure is used to set any global variables, !
! file varibles, and database connections you need to !
! make happen before any files open. !
!=====================================================!
[SOURCE]
PROPERTY:BEGIN
PRIORITY 3500
PROPERTY:END
#SET(%txaLine, 'WAIT_WINDOW' & ALL(' ', %AtColPos - LEN('WAIT_WINDOW')) & ' EQUATE(1)')
%txaLine #<! Show 'Please Wait...' message
[SOURCE]
PROPERTY:BEGIN
PRIORITY 3500
PROPERTY:END
SQLOpenWindow WINDOW('Datatabase Connect'),AT(,,247,26),FONT('MS Sans Serif',8,,FONT:regular),CENTER, |
GRAY,DOUBLE
STRING('Please wait while the program connects to the database.'),AT(35,3)
STRING('This process could take several seconds.'),AT(58,12)
END
[END]
EMBED %%ProcessedCode
[DEFINITION]
[SOURCE]
PROPERTY:BEGIN
PRIORITY 5100
PROPERTY:END
COMPILE('!***WAIT***', WAIT_WINDOW)
SETCURSOR(Cursor:Wait)
OPEN(SQLOpenWindow)
ACCEPT
IF EVENT() = Event:OpenWindow
!***WAIT***
!-----------------------------------------------!
! RA.2012.05.16 - Connect to the Database here. !
!-----------------------------------------------!
g:SQLConnectString = ''
#!
#!
#![SOURCE]
#!PROPERTY:BEGIN
#!PRIORITY 5200
#!PROPERTY:END
#SET(%CountFiles,0)
#FOR(%ReportedFiles)
#FIX(%File,%ReportedFiles) #! Just in case you need something
#!----------------------------------------!
#! RA.2010.09.27 - Get the EXTERNAL name. !
#!----------------------------------------!
#SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
#SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
#SET(%aFileUser,LOWER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
#!SET(%aFileUser,LOWER(EXTRACT(%FileUserOptions,'RASQL')))
#SET(%aFileOpt1,EXTRACT(%aFileUser,'RASQL',1))
#SET(%aFileOpt2,EXTRACT(%aFileUser,'RASQL',2))
#SET(%aFileOpt3,EXTRACT(%aFileUser,'RASQL',3))
#SET(%aFileOpt4,EXTRACT(%aFileUser,'RASQL',4))
#!
#SET(%sFileNameInternal, %File) #! Internal File Name
#SET(%sFileNameExternal, %aFileOpt2) #! External File Name
#SET(%sFileName, %sFileNameInternal)
#IF(%sFileNameExternal <> '')
#SET(%sFileName, %sFileNameExternal)
#ENDIF
#!!! %FilePrefix , %File [ %sFileNameInternal , %sFileNameExternal ] %sFileName
#!----------------------------------------!
#!----------------------------------------!
#IF((%CountFiles % 20) = 0)
[SOURCE]
PROPERTY:BEGIN
PRIORITY 5200
PROPERTY:END
#ENDIF
#IF(SUB(%FileName,1,1) <> '''')
#SET(%txaLine, ' ' & %FileName & ALL(' ', %AtColPos1 - (LEN(%FileName) + LEN(' '))) & '=')
#SET(%txaLine, %txaLine & ' ''dbo.' & %sFileName & '''')
#SET(%txaLine, %txaLine & ALL(' ', %AtColPos2 - (LEN(%txaLine) + LEN(' '))) & ' ! ' & %FilePrefix & ':' & %sFileNameInternal & ' [' & %sFileNameExternal & '] - ' & %FileDriver)
%txaLine
#ELSE
! %ReportedFiles #<! (%FilePrefix , %sFileNameInternal, --%FileName--) [%sFileNameExternal] - %FileDriver - has no name assignment
#ENDIF
#SET(%CountFiles,%CountFiles + 1)
#ENDFOR
#!
#!
[SOURCE]
PROPERTY:BEGIN
PRIORITY 5300
PROPERTY:END
#SET(%CountFiles,ITEMS(%ReportedFiles))
! There are %CountFiles tables that had their name set up.
COMPILE('!***WAIT***', WAIT_WINDOW)
POST(EVENT:CloseWindow)
END
END
CLOSE(SQLOpenWindow)
SETCURSOR()
!***WAIT***
MESSAGE('Globals have been intitalized and database connection has occured!', 'MSSQL_Database_Connect')
RETURN
[END]
[END]
#GROUP(%TxaMSSqlConnectEnd)
#!--------------------------------------------------------------------
#!--------------------------------------------------------------------
#GROUP(%UTILMSSQLConnectEnd)
#!--------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------