Mercurial > repos > deepakjadmin > mayatool3_test2

comparison docs/scripts/html/code/DBSQLToTextFiles.html @ 0:4816e4a8ae95 draft default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Uploaded
author deepakjadmin
date Wed, 20 Jan 2016 09:23:18 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:4816e4a8ae95
1 <html>
2 <head>
3 <title>MayaChemTools:Code:DBSQLToTextFiles.pl</title>
4 <meta http-equiv="content-type" content="text/html;charset=utf-8">
5 <link rel="stylesheet" type="text/css" href="../../../css/MayaChemToolsCode.css">
6 </head>
7 <body leftmargin="20" rightmargin="20" topmargin="10" bottommargin="10">
8 <br/>
9 <center>
10 <a href="http://www.mayachemtools.org" title="MayaChemTools Home"><img src="../../../images/MayaChemToolsLogo.gif" border="0" alt="MayaChemTools"></a>
11 </center>
12 <br/>
13 <pre>
14 1 #!/usr/bin/perl -w
15 2 <span class="c">#</span>
16 3 <span class="c"># $RCSfile: DBSQLToTextFiles.pl,v $</span>
17 4 <span class="c"># $Date: 2015/02/28 20:46:19 $</span>
18 5 <span class="c"># $Revision: 1.32 $</span>
19 6 <span class="c">#</span>
20 7 <span class="c"># Author: Manish Sud &lt;msud@san.rr.com&gt;</span>
21 8 <span class="c">#</span>
22 9 <span class="c"># Copyright (C) 2015 Manish Sud. All rights reserved.</span>
23 10 <span class="c">#</span>
24 11 <span class="c"># This file is part of MayaChemTools.</span>
25 12 <span class="c">#</span>
26 13 <span class="c"># MayaChemTools is free software; you can redistribute it and/or modify it under</span>
27 14 <span class="c"># the terms of the GNU Lesser General Public License as published by the Free</span>
28 15 <span class="c"># Software Foundation; either version 3 of the License, or (at your option) any</span>
29 16 <span class="c"># later version.</span>
30 17 <span class="c">#</span>
31 18 <span class="c"># MayaChemTools is distributed in the hope that it will be useful, but without</span>
32 19 <span class="c"># any warranty; without even the implied warranty of merchantability of fitness</span>
33 20 <span class="c"># for a particular purpose. See the GNU Lesser General Public License for more</span>
34 21 <span class="c"># details.</span>
35 22 <span class="c">#</span>
36 23 <span class="c"># You should have received a copy of the GNU Lesser General Public License</span>
37 24 <span class="c"># along with MayaChemTools; if not, see &lt;http://www.gnu.org/licenses/&gt; or</span>
38 25 <span class="c"># write to the Free Software Foundation Inc., 59 Temple Place, Suite 330,</span>
39 26 <span class="c"># Boston, MA, 02111-1307, USA.</span>
40 27 <span class="c">#</span>
41 28
42 29 <span class="k">use</span> <span class="w">strict</span><span class="sc">;</span>
43 30 <span class="k">use</span> <span class="w">FindBin</span><span class="sc">;</span> <span class="k">use</span> <span class="w">lib</span> <span class="q">&quot;$FindBin::Bin/../lib&quot;</span><span class="sc">;</span>
44 31 <span class="k">use</span> <span class="w">Getopt::Long</span><span class="sc">;</span>
45 32 <span class="k">use</span> <span class="w">File::Basename</span><span class="sc">;</span>
46 33 <span class="k">use</span> <span class="w">Text::ParseWords</span><span class="sc">;</span>
47 34 <span class="k">use</span> <span class="w">Benchmark</span><span class="sc">;</span>
48 35 <span class="k">use</span> <span class="w">FileUtil</span><span class="sc">;</span>
49 36 <span class="k">use</span> <span class="w">TextUtil</span><span class="sc">;</span>
50 37 <span class="k">use</span> <span class="w">DBUtil</span><span class="sc">;</span>
51 38
52 39 <span class="k">my</span><span class="s">(</span><span class="i">$ScriptName</span><span class="cm">,</span> <span class="i">%Options</span><span class="cm">,</span> <span class="i">$StartTime</span><span class="cm">,</span> <span class="i">$EndTime</span><span class="cm">,</span> <span class="i">$TotalTime</span><span class="s">)</span><span class="sc">;</span>
53 40
54 41 <span class="c"># Autoflush STDOUT</span>
55 42 <span class="i">$|</span> = <span class="n">1</span><span class="sc">;</span>
56 43
57 44 <span class="c"># Starting message...</span>
58 45 <span class="i">$ScriptName</span> = <span class="i">basename</span><span class="s">(</span><span class="i">$0</span><span class="s">)</span><span class="sc">;</span>
59 46 <span class="k">print</span> <span class="q">&quot;\n$ScriptName: Starting...\n\n&quot;</span><span class="sc">;</span>
60 47 <span class="i">$StartTime</span> = <span class="w">new</span> <span class="w">Benchmark</span><span class="sc">;</span>
61 48
62 49 <span class="c"># Get the options and setup script...</span>
63 50 <span class="i">SetupScriptUsage</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
64 51 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">help</span>} || <span class="i">@ARGV</span> &lt; <span class="n">1</span><span class="s">)</span> <span class="s">{</span>
65 52 <span class="k">die</span> <span class="i">GetUsageFromPod</span><span class="s">(</span><span class="q">&quot;$FindBin::Bin/$ScriptName&quot;</span><span class="s">)</span><span class="sc">;</span>
66 53 <span class="s">}</span>
67 54
68 55 <span class="k">my</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$DBHost</span><span class="cm">,</span> <span class="i">$DBName</span><span class="cm">,</span> <span class="i">$DBUser</span><span class="cm">,</span> <span class="i">$DBPassword</span><span class="cm">,</span> <span class="i">$DBMode</span><span class="cm">,</span> <span class="i">$ExportDataLabels</span><span class="cm">,</span> <span class="i">$ExportLOBs</span><span class="cm">,</span> <span class="i">$OutDelim</span><span class="cm">,</span> <span class="i">$OutQuote</span><span class="cm">,</span> <span class="i">$ReplaceNullStr</span><span class="s">)</span><span class="sc">;</span>
69 56 <span class="i">ProcessOptions</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
70 57
71 58 <span class="c"># Collect input parameters information...</span>
72 59 <span class="k">print</span> <span class="q">&quot;Checking input parameter(s)...\n&quot;</span><span class="sc">;</span>
73 60 <span class="k">my</span><span class="s">(</span><span class="i">@DBSQLStatements</span><span class="cm">,</span> <span class="i">@DBTextFiles</span><span class="s">)</span><span class="sc">;</span>
74 61 <span class="i">RetrieveDBInfo</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
75 62
76 63 <span class="c"># Connect to database...</span>
77 64 <span class="k">my</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span><span class="sc">;</span>
78 65 <span class="k">print</span> <span class="q">&quot;Connecting to $DBDriver:database=$DBName as $DBUser...\n&quot;</span><span class="sc">;</span>
79 66 <span class="i">$DBHandle</span> = <span class="i">DBConnect</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$DBName</span><span class="cm">,</span> <span class="i">$DBHost</span><span class="cm">,</span> <span class="i">$DBUser</span><span class="cm">,</span> <span class="i">$DBPassword</span><span class="s">)</span><span class="sc">;</span>
80 67
81 68 <span class="c"># Generate text files...</span>
82 69 <span class="k">if</span> <span class="s">(</span><span class="i">@DBTextFiles</span> &gt; <span class="n">1</span><span class="s">)</span> <span class="s">{</span>
83 70 <span class="k">print</span> <span class="q">&quot;Generating text files...\n&quot;</span><span class="sc">;</span>
84 71 <span class="s">}</span>
85 72 <span class="k">my</span><span class="s">(</span><span class="i">$Index</span><span class="cm">,</span> <span class="i">$TextFile</span><span class="cm">,</span> <span class="i">$SQL</span><span class="s">)</span><span class="sc">;</span>
86 73 <span class="j">TEXTFILE:</span> <span class="k">for</span> <span class="i">$Index</span> <span class="s">(</span><span class="n">0</span> .. <span class="i">$#DBTextFiles</span><span class="s">)</span> <span class="s">{</span>
87 74 <span class="i">$TextFile</span> = <span class="i">$DBTextFiles</span>[<span class="i">$Index</span>]<span class="sc">;</span>
88 75 <span class="i">$SQL</span> = <span class="i">$DBSQLStatements</span>[<span class="i">$Index</span>]<span class="sc">;</span>
89 76
90 77 <span class="k">if</span> <span class="s">(</span><span class="i">@DBTextFiles</span> &gt; <span class="n">1</span><span class="s">)</span> <span class="s">{</span>
91 78 <span class="k">print</span> <span class="q">&quot;\nGenerating text file $TextFile...\n&quot;</span><span class="sc">;</span>
92 79 <span class="s">}</span>
93 80 <span class="k">else</span> <span class="s">{</span>
94 81 <span class="k">print</span> <span class="q">&quot;Generating text file $TextFile...\n&quot;</span><span class="sc">;</span>
95 82 <span class="s">}</span>
96 83 <span class="k">print</span> <span class="q">&quot;Processing SQL statement \&quot;$SQL\&quot;...\n&quot;</span><span class="sc">;</span>
97 84
98 85 <span class="k">if</span> <span class="s">(</span>!<span class="k">open</span> <span class="w">TEXTFILE</span><span class="cm">,</span> <span class="q">&quot;&gt;$TextFile&quot;</span><span class="s">)</span> <span class="s">{</span>
99 86 <span class="k">warn</span> <span class="q">&quot;Warning: Abandoning $TextFile generation: Couldn&#39;t open it: $! \n&quot;</span><span class="sc">;</span>
100 87 <span class="k">next</span> <span class="j">TEXTFILE</span><span class="sc">;</span>
101 88 <span class="s">}</span>
102 89
103 90 <span class="k">if</span> <span class="s">(</span><span class="i">DBSQLToTextFile</span><span class="s">(</span><span class="i">$DBHandle</span><span class="cm">,</span> <span class="i">$SQL</span><span class="cm">,</span> \<span class="i">*TEXTFILE</span><span class="cm">,</span> <span class="i">$OutDelim</span><span class="cm">,</span> <span class="i">$OutQuote</span><span class="cm">,</span> <span class="i">$ExportDataLabels</span><span class="cm">,</span> <span class="i">$ExportLOBs</span><span class="cm">,</span> <span class="i">$ReplaceNullStr</span><span class="s">)</span><span class="s">)</span> <span class="s">{</span>
104 91 <span class="k">warn</span> <span class="q">&quot;Warning: Abandoning $TextFile generation...\n&quot;</span><span class="sc">;</span>
105 92 <span class="k">next</span> <span class="j">TEXTFILE</span><span class="sc">;</span>
106 93 <span class="s">}</span>
107 94 <span class="k">close</span> <span class="w">TEXTFILE</span><span class="sc">;</span>
108 95 <span class="s">}</span>
109 96 <span class="k">print</span> <span class="q">&quot;\nDisconnecting from $DBDriver:database=$DBName...\n&quot;</span><span class="sc">;</span>
110 97 <span class="i">DBDisconnect</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span><span class="sc">;</span>
111 98
112 99 <span class="k">print</span> <span class="q">&quot;$ScriptName:Done...\n\n&quot;</span><span class="sc">;</span>
113 100
114 101 <span class="i">$EndTime</span> = <span class="w">new</span> <span class="w">Benchmark</span><span class="sc">;</span>
115 102 <span class="i">$TotalTime</span> = <span class="w">timediff</span> <span class="s">(</span><span class="i">$EndTime</span><span class="cm">,</span> <span class="i">$StartTime</span><span class="s">)</span><span class="sc">;</span>
116 103 <span class="k">print</span> <span class="q">&quot;Total time: &quot;</span><span class="cm">,</span> <span class="i">timestr</span><span class="s">(</span><span class="i">$TotalTime</span><span class="s">)</span><span class="cm">,</span> <span class="q">&quot;\n&quot;</span><span class="sc">;</span>
117 104
118 105 <span class="c">###############################################################################</span>
119 106
120 107 <span class="c"># Collect input parameters information...</span>
121 <a name="RetrieveDBInfo-"></a> 108 <span class="k">sub </span><span class="m">RetrieveDBInfo</span> <span class="s">{</span>
122 109 <span class="k">my</span><span class="s">(</span><span class="i">$FileExt</span><span class="cm">,</span> <span class="i">$UserFileName</span><span class="s">)</span><span class="sc">;</span>
123 110
124 111 <span class="c"># Setup out file ext...</span>
125 112 <span class="i">$FileExt</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">outdelim</span>} =~ <span class="q">/^tab$/i</span><span class="s">)</span> ? <span class="q">&quot;tsv&quot;</span> <span class="co">:</span> <span class="q">&quot;csv&quot;</span><span class="sc">;</span>
126 113
127 114 <span class="c"># Get user specified information...</span>
128 115 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">root</span>} &amp;&amp; <span class="s">(</span><span class="i">@ARGV</span> == <span class="n">1</span><span class="s">)</span><span class="s">)</span> <span class="s">{</span>
129 116 <span class="k">my</span><span class="s">(</span><span class="i">$RootFileDir</span><span class="cm">,</span> <span class="i">$RootFileName</span><span class="cm">,</span> <span class="i">$RootFileExt</span><span class="s">)</span> = <span class="i">ParseFileName</span><span class="s">(</span><span class="i">$Options</span>{<span class="w">root</span>}<span class="s">)</span><span class="sc">;</span>
130 117 <span class="k">if</span> <span class="s">(</span><span class="i">$RootFileName</span> &amp;&amp; <span class="i">$RootFileExt</span><span class="s">)</span> <span class="s">{</span>
131 118 <span class="i">$UserFileName</span> = <span class="i">$RootFileName</span><span class="sc">;</span>
132 119 <span class="s">}</span>
133 120 <span class="k">else</span> <span class="s">{</span>
134 121 <span class="i">$UserFileName</span> = <span class="i">$Options</span>{<span class="w">root</span>}<span class="sc">;</span>
135 122 <span class="s">}</span>
136 123 <span class="s">}</span>
137 124
138 125 <span class="k">my</span><span class="s">(</span><span class="i">$Param</span><span class="cm">,</span> <span class="i">$SQL</span><span class="cm">,</span> <span class="i">$SQLNo</span><span class="cm">,</span> <span class="i">$FileName</span><span class="s">)</span><span class="sc">;</span>
139 126 <span class="c"># Go over all the input parameters...</span>
140 127 <span class="i">@DBSQLStatements</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
141 128 <span class="i">@DBTextFiles</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
142 129 <span class="i">$SQLNo</span> = <span class="n">0</span><span class="sc">;</span>
143 130 <span class="j">PARAM:</span> <span class="k">for</span> <span class="i">$Param</span> <span class="s">(</span><span class="i">@ARGV</span><span class="s">)</span> <span class="s">{</span>
144 131 <span class="k">if</span> <span class="s">(</span><span class="i">$DBMode</span> =~ <span class="q">/^SQLStatement$/i</span><span class="s">)</span> <span class="s">{</span>
145 132 <span class="i">$SQLNo</span>++<span class="sc">;</span>
146 133 <span class="i">$SQL</span> = <span class="i">$Param</span><span class="sc">;</span>
147 134 <span class="i">$FileName</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">root</span>} &amp;&amp; <span class="s">(</span><span class="i">@ARGV</span> == <span class="n">1</span><span class="s">)</span><span class="s">)</span> ? <span class="i">$UserFileName</span> <span class="co">:</span> <span class="s">(</span><span class="q">&quot;SQLStatement&quot;</span> . <span class="q">&quot;$SQLNo&quot;</span><span class="s">)</span><span class="sc">;</span>
148 135 <span class="i">$FileName</span> .= <span class="q">&quot;.$FileExt&quot;</span><span class="sc">;</span>
149 136 <span class="k">if</span> <span class="s">(</span>!<span class="i">$Options</span>{<span class="w">overwrite</span>}<span class="s">)</span> <span class="s">{</span>
150 137 <span class="k">if</span> <span class="s">(</span><span class="k">-e</span> <span class="i">$FileName</span><span class="s">)</span> <span class="s">{</span>
151 138 <span class="k">die</span> <span class="q">&quot;Error: The file $FileName already exists.\n&quot;</span><span class="sc">;</span>
152 139 <span class="s">}</span>
153 140 <span class="s">}</span>
154 141 <span class="k">push</span> <span class="i">@DBSQLStatements</span><span class="cm">,</span> <span class="i">$SQL</span><span class="sc">;</span>
155 142 <span class="k">push</span> <span class="i">@DBTextFiles</span><span class="cm">,</span> <span class="i">$FileName</span><span class="sc">;</span>
156 143 <span class="s">}</span>
157 144 <span class="k">elsif</span> <span class="s">(</span><span class="i">$DBMode</span> =~ <span class="q">/^SQLFile$/i</span><span class="s">)</span> <span class="s">{</span>
158 145 <span class="c"># Read SQL file...</span>
159 146 <span class="k">my</span><span class="s">(</span><span class="i">$SQLFile</span><span class="s">)</span> = <span class="i">$Param</span><span class="sc">;</span>
160 147 <span class="k">if</span> <span class="s">(</span>! <span class="k">-e</span> <span class="i">$Param</span><span class="s">)</span> <span class="s">{</span>
161 148 <span class="k">warn</span> <span class="q">&quot;Warning: Ignoring file $SQLFile: It doesn&#39;t exist\n&quot;</span><span class="sc">;</span>
162 149 <span class="k">next</span> <span class="j">PARAM</span><span class="sc">;</span>
163 150 <span class="s">}</span>
164 151 <span class="k">if</span> <span class="s">(</span>!<span class="k">open</span> <span class="w">SQLFILE</span><span class="cm">,</span> <span class="q">&quot;$SQLFile&quot;</span> <span class="s">)</span> <span class="s">{</span>
165 152 <span class="k">warn</span> <span class="q">&quot;Warning: Ignoring file $SQLFile: Couldn&#39;t open it: $! \n&quot;</span><span class="sc">;</span>
166 153 <span class="k">next</span> <span class="j">PARAM</span><span class="sc">;</span>
167 154 <span class="s">}</span>
168 155 <span class="k">my</span><span class="s">(</span><span class="i">$Line</span><span class="cm">,</span> <span class="i">$SQLString</span><span class="s">)</span><span class="sc">;</span>
169 156 <span class="i">$SQLString</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span>
170 157 <span class="j">LINE:</span> <span class="k">while</span> <span class="s">(</span><span class="i">$Line</span> = <span class="i">GetTextLine</span><span class="s">(</span>\<span class="i">*SQLFILE</span><span class="s">)</span><span class="s">)</span> <span class="s">{</span>
171 158 <span class="c"># Ignore comments line...</span>
172 159 <span class="k">if</span> <span class="s">(</span><span class="i">$Line</span> =~ <span class="q">/^#/</span> || <span class="i">$Line</span> =~ <span class="q">/^-/</span><span class="s">)</span> <span class="s">{</span>
173 160 <span class="k">next</span> <span class="j">LINE</span><span class="sc">;</span>
174 161 <span class="s">}</span>
175 162 <span class="i">$SQLString</span> .= <span class="i">$Line</span><span class="sc">;</span>
176 163 <span class="s">}</span>
177 164 <span class="k">close</span> <span class="w">SQLFILE</span><span class="sc">;</span>
178 165 <span class="c"># Extract select SQL statements...</span>
179 166 <span class="k">my</span><span class="s">(</span><span class="i">$SQLFileDir</span><span class="cm">,</span> <span class="i">$SQLFileName</span><span class="cm">,</span> <span class="i">$SQLFileExt</span><span class="s">)</span> = <span class="i">ParseFileName</span><span class="s">(</span><span class="i">$SQLFile</span><span class="s">)</span><span class="sc">;</span>
180 167 <span class="k">my</span><span class="s">(</span><span class="i">@SQLSplits</span><span class="s">)</span> = <span class="k">split</span> <span class="q">&quot;\;&quot;</span><span class="cm">,</span> <span class="i">$SQLString</span><span class="sc">;</span>
181 168 <span class="i">$SQLNo</span> = <span class="n">0</span><span class="sc">;</span>
182 169 <span class="j">SQLSPLIT:</span> <span class="k">for</span> <span class="i">$SQL</span> <span class="s">(</span><span class="i">@SQLSplits</span><span class="s">)</span> <span class="s">{</span>
183 170 <span class="i">$SQLNo</span>++<span class="sc">;</span>
184 171 <span class="i">$FileName</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">root</span>} &amp;&amp; <span class="s">(</span><span class="i">@ARGV</span> == <span class="n">1</span><span class="s">)</span><span class="s">)</span> ? <span class="s">(</span><span class="q">&quot;$UserFileName&quot;</span> . <span class="q">&quot;$SQLNo&quot;</span><span class="s">)</span> <span class="co">:</span> <span class="s">(</span><span class="q">&quot;$SQLFileName&quot;</span> . <span class="q">&quot;SQLStatement&quot;</span> . <span class="q">&quot;$SQLNo&quot;</span><span class="s">)</span><span class="sc">;</span>
185 172 <span class="i">$FileName</span> .= <span class="q">&quot;.$FileExt&quot;</span><span class="sc">;</span>
186 173 <span class="k">if</span> <span class="s">(</span>!<span class="i">$Options</span>{<span class="w">overwrite</span>}<span class="s">)</span> <span class="s">{</span>
187 174 <span class="k">if</span> <span class="s">(</span><span class="k">-e</span> <span class="i">$FileName</span><span class="s">)</span> <span class="s">{</span>
188 175 <span class="k">die</span> <span class="q">&quot;Error: The file $FileName already exists.\n&quot;</span><span class="sc">;</span>
189 176 <span class="s">}</span>
190 177 <span class="s">}</span>
191 178 <span class="k">push</span> <span class="i">@DBSQLStatements</span><span class="cm">,</span> <span class="i">$SQL</span><span class="sc">;</span>
192 179 <span class="k">push</span> <span class="i">@DBTextFiles</span><span class="cm">,</span> <span class="i">$FileName</span><span class="sc">;</span>
193 180 <span class="s">}</span>
194 181 <span class="s">}</span>
195 182 <span class="s">}</span>
196 183 <span class="s">}</span>
197 184
198 185 <span class="c"># Process option values...</span>
199 <a name="ProcessOptions-"></a> 186 <span class="k">sub </span><span class="m">ProcessOptions</span> <span class="s">{</span>
200 187
201 188 <span class="i">$DBDriver</span> = <span class="i">$Options</span>{<span class="w">dbdriver</span>} ? <span class="i">$Options</span>{<span class="w">dbdriver</span>} <span class="co">:</span> <span class="s">(</span><span class="k">exists</span> <span class="i">$ENV</span>{<span class="w">DBI_DRIVER</span>} ? <span class="i">$ENV</span>{<span class="w">DBI_DRIVER</span>} <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="s">)</span> <span class="sc">;</span>
202 189 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span><span class="s">)</span> <span class="s">{</span>
203 190 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^Oracle$/i</span><span class="s">)</span> <span class="s">{</span>
204 191 <span class="i">$DBDriver</span> = <span class="q">&quot;Oracle&quot;</span><span class="sc">;</span>
205 192 <span class="s">}</span>
206 193 <span class="k">elsif</span> <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^mysql$/i</span><span class="s">)</span> <span class="s">{</span>
207 194 <span class="i">$DBDriver</span> = <span class="q">&quot;mysql&quot;</span><span class="sc">;</span>
208 195 <span class="s">}</span>
209 196 <span class="k">elsif</span> <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^(Pg|Postgres)$/i</span><span class="s">)</span> <span class="s">{</span>
210 197 <span class="i">$DBDriver</span> = <span class="q">&quot;Pg&quot;</span><span class="sc">;</span>
211 198 <span class="s">}</span>
212 199 <span class="k">else</span> <span class="s">{</span>
213 200 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">dbdriver</span>}<span class="s">)</span> <span class="s">{</span>
214 201 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $DBDriver, for option \&quot;-d --dbdriver\&quot; is not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n&quot;</span><span class="sc">;</span>
215 202 <span class="s">}</span>
216 203 <span class="k">else</span> <span class="s">{</span>
217 204 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n&quot;</span><span class="sc">;</span>
218 205 <span class="s">}</span>
219 206 <span class="s">}</span>
220 207 <span class="s">}</span>
221 208 <span class="k">else</span> <span class="s">{</span>
222 209 <span class="i">$DBDriver</span> = <span class="q">&quot;mysql&quot;</span><span class="sc">;</span>
223 210 <span class="s">}</span>
224 211 <span class="i">$DBHost</span> = <span class="i">$Options</span>{<span class="w">dbhost</span>} ? <span class="i">$Options</span>{<span class="w">dbhost</span>} <span class="co">:</span> <span class="s">(</span><span class="k">exists</span> <span class="i">$ENV</span>{<span class="w">DBI_HOST</span>} ? <span class="i">$ENV</span>{<span class="w">DBI_HOST</span>} <span class="co">:</span> <span class="q">&quot;127.0.0.1&quot;</span><span class="s">)</span><span class="sc">;</span>
225 212 <span class="i">$DBName</span> = <span class="i">$Options</span>{<span class="w">dbname</span>} ? <span class="i">$Options</span>{<span class="w">dbname</span>} <span class="co">:</span> <span class="s">(</span><span class="k">exists</span> <span class="i">$ENV</span>{<span class="w">DBI_NAME</span>} ? <span class="i">$ENV</span>{<span class="w">DBI_NAME</span>} <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="s">)</span><span class="sc">;</span>
226 213 <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBName</span><span class="s">)</span> <span class="s">{</span>
227 214 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^mysql$/i</span><span class="s">)</span> <span class="s">{</span>
228 215 <span class="i">$DBName</span> = <span class="q">&quot;mysql&quot;</span><span class="sc">;</span>
229 216 <span class="s">}</span>
230 217 <span class="k">elsif</span> <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^pg|Postgres$/i</span><span class="s">)</span> <span class="s">{</span>
231 218 <span class="i">$DBName</span> = <span class="q">&quot;postgres&quot;</span><span class="sc">;</span>
232 219 <span class="s">}</span>
233 220 <span class="s">}</span>
234 221 <span class="i">$DBUser</span> = <span class="i">$Options</span>{<span class="w">dbusername</span>} ? <span class="i">$Options</span>{<span class="w">dbusername</span>} <span class="co">:</span> <span class="s">(</span><span class="k">exists</span> <span class="i">$ENV</span>{<span class="w">DBI_USER</span>} ? <span class="i">$ENV</span>{<span class="w">DBI_USER</span>} <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="s">)</span> <span class="sc">;</span>
235 222 <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBUser</span><span class="s">)</span> <span class="s">{</span>
236 223 <span class="k">die</span> <span class="q">&quot;Error: No database username specified. Use \&quot;--dbusername\&quot; option or environment variable DBI_USER to enter a valid value.\n&quot;</span><span class="sc">;</span>
237 224 <span class="s">}</span>
238 225 <span class="i">$DBPassword</span> = <span class="i">$Options</span>{<span class="w">dbpassword</span>} ? <span class="i">$Options</span>{<span class="w">dbpassword</span>} <span class="co">:</span> <span class="s">(</span><span class="k">exists</span> <span class="i">$ENV</span>{<span class="w">DBI_PASS</span>} ? <span class="i">$ENV</span>{<span class="w">DBI_PASS</span>} <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="s">)</span> <span class="sc">;</span>
239 226 <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBPassword</span><span class="s">)</span> <span class="s">{</span>
240 227 <span class="k">die</span> <span class="q">&quot;Error: No database password specified. Use \&quot;--dbpassword\&quot; option or environment variable DBI_PASS to enter a valid value.\n&quot;</span><span class="sc">;</span>
241 228 <span class="s">}</span>
242 229 <span class="i">$DBMode</span> = <span class="i">$Options</span>{<span class="w">mode</span>}<span class="sc">;</span>
243 230 <span class="i">$ExportLOBs</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">exportlobs</span>} =~ <span class="q">/^yes$/</span><span class="s">)</span> ? <span class="n">1</span> <span class="co">:</span> <span class="n">0</span><span class="sc">;</span>
244 231 <span class="i">$ExportDataLabels</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">exportdatalabels</span>} =~ <span class="q">/^yes$/i</span><span class="s">)</span> ? <span class="n">1</span> <span class="co">:</span> <span class="n">0</span><span class="sc">;</span>
245 232
246 233 <span class="i">$OutDelim</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">outdelim</span>} =~ <span class="q">/^tab$/i</span> <span class="s">)</span> ? <span class="q">&quot;\t&quot;</span> <span class="co">:</span> <span class="s">(</span><span class="s">(</span><span class="i">$Options</span>{<span class="w">outdelim</span>} =~ <span class="q">/^semicolon$/i</span><span class="s">)</span> ? <span class="q">&quot;\;&quot;</span> <span class="co">:</span> <span class="q">&quot;\,&quot;</span><span class="s">)</span><span class="sc">;</span>
247 234 <span class="i">$OutQuote</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">quote</span>} =~ <span class="q">/^yes$/i</span><span class="s">)</span> ? <span class="n">1</span> <span class="co">:</span> <span class="n">0</span><span class="sc">;</span>
248 235
249 236 <span class="i">$ReplaceNullStr</span> = <span class="s">(</span><span class="k">defined</span><span class="s">(</span><span class="i">$Options</span>{<span class="w">replacenullstr</span>}<span class="s">)</span> &amp;&amp; <span class="k">length</span><span class="s">(</span><span class="i">$Options</span>{<span class="w">replacenullstr</span>}<span class="s">)</span><span class="s">)</span> ? <span class="i">$Options</span>{<span class="w">replacenullstr</span>} <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
250 237 <span class="s">}</span>
251 238
252 239 <span class="c"># Setup script usage and retrieve command line arguments specified using various options...</span>
253 <a name="SetupScriptUsage-"></a> 240 <span class="k">sub </span><span class="m">SetupScriptUsage</span> <span class="s">{</span>
254 241
255 242 <span class="c"># Retrieve all the options...</span>
256 243 <span class="i">%Options</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
257 244 <span class="i">$Options</span>{<span class="w">mode</span>} = <span class="q">&quot;SQLStatement&quot;</span><span class="sc">;</span>
258 245 <span class="i">$Options</span>{<span class="w">exportlobs</span>} = <span class="q">&quot;no&quot;</span><span class="sc">;</span>
259 246 <span class="i">$Options</span>{<span class="w">exportdatalabels</span>} = <span class="q">&quot;yes&quot;</span><span class="sc">;</span>
260 247 <span class="i">$Options</span>{<span class="w">outdelim</span>} = <span class="q">&quot;comma&quot;</span><span class="sc">;</span>
261 248 <span class="i">$Options</span>{<span class="w">quote</span>} = <span class="q">&quot;yes&quot;</span><span class="sc">;</span>
262 249
263 250 <span class="k">if</span> <span class="s">(</span>!<span class="i">GetOptions</span><span class="s">(</span>\<span class="i">%Options</span><span class="cm">,</span> <span class="q">&quot;dbdriver|d=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbhost=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbname=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbpassword=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbusername=s&quot;</span><span class="cm">,</span> <span class="q">&quot;exportdatalabels=s&quot;</span><span class="cm">,</span> <span class="q">&quot;exportlobs=s&quot;</span><span class="cm">,</span> <span class="q">&quot;help|h&quot;</span><span class="cm">,</span> <span class="q">&quot;mode|m=s&quot;</span><span class="cm">,</span> <span class="q">&quot;outdelim=s&quot;</span><span class="cm">,</span> <span class="q">&quot;overwrite|o&quot;</span><span class="cm">,</span> <span class="q">&quot;quote|q=s&quot;</span><span class="cm">,</span> <span class="q">&quot;root|r=s&quot;</span><span class="cm">,</span> <span class="q">&quot;replacenullstr=s&quot;</span><span class="cm">,</span> <span class="q">&quot;workingdir|w=s&quot;</span><span class="s">)</span><span class="s">)</span> <span class="s">{</span>
264 251 <span class="k">die</span> <span class="q">&quot;\nTo get a list of valid options and their values, use \&quot;$ScriptName -h\&quot; or\n\&quot;perl -S $ScriptName -h\&quot; command and try again...\n&quot;</span><span class="sc">;</span>
265 252 <span class="s">}</span>
266 253 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">workingdir</span>}<span class="s">)</span> <span class="s">{</span>
267 254 <span class="k">if</span> <span class="s">(</span>! <span class="k">-d</span> <span class="i">$Options</span>{<span class="w">workingdir</span>}<span class="s">)</span> <span class="s">{</span>
268 255 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{workingdir}, for option \&quot;-w --workingdir\&quot; is not a directory name.\n&quot;</span><span class="sc">;</span>
269 256 <span class="s">}</span>
270 257 <span class="k">chdir</span> <span class="i">$Options</span>{<span class="w">workingdir</span>} <span class="k">or</span> <span class="k">die</span> <span class="q">&quot;Error: Couldn&#39;t chdir $Options{workingdir}: $! \n&quot;</span><span class="sc">;</span>
271 258 <span class="s">}</span>
272 259 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">exportdatalabels</span>} !~ <span class="q">/^(yes|no)$/i</span><span class="s">)</span> <span class="s">{</span>
273 260 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{exportlobs}, for option \&quot;--exportdatalabels\&quot; is not valid. Allowed values: yes or no\n&quot;</span><span class="sc">;</span>
274 261 <span class="s">}</span>
275 262 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">exportlobs</span>} !~ <span class="q">/^(yes|no)$/i</span><span class="s">)</span> <span class="s">{</span>
276 263 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{exportlobs}, for option \&quot;--exportlobs\&quot; is not valid. Allowed values: yes or no\n&quot;</span><span class="sc">;</span>
277 264 <span class="s">}</span>
278 265 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">mode</span>} !~ <span class="q">/^(SQLStatement|SQLFile)$/i</span><span class="s">)</span> <span class="s">{</span>
279 266 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{mode}, for option \&quot;-m --mode\&quot; is not valid. Allowed values: SQLStatement or SQLFile\n&quot;</span><span class="sc">;</span>
280 267 <span class="s">}</span>
281 268 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">outdelim</span>} !~ <span class="q">/^(comma|semicolon|tab)$/i</span><span class="s">)</span> <span class="s">{</span>
282 269 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{outdelim}, for option \&quot;--outdelim\&quot; is not valid. Allowed values: comma, tab, or semicolon\n&quot;</span><span class="sc">;</span>
283 270 <span class="s">}</span>
284 271 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">quote</span>} !~ <span class="q">/^(yes|no)$/i</span><span class="s">)</span> <span class="s">{</span>
285 272 <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{quote}, for option \&quot;-q --quote\&quot; is not valid. Allowed values: yes or no\n&quot;</span><span class="sc">;</span>
286 273 <span class="s">}</span>
287 274 <span class="s">}</span>
288 275
289 <a name="EOF-"></a></pre>
290 <p>&nbsp;</p>
291 <br />
292 <center>
293 <img src="../../../images/h2o2.png">
294 </center>
295 </body>
296 </html>