comparison mayachemtools/docs/modules/html/code/DBUtil.html @ 0:73ae111cf86f draft

Uploaded
author deepakjadmin
date Wed, 20 Jan 2016 11:55:01 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:73ae111cf86f
1 <html>
2 <head>
3 <title>MayaChemTools:Code:DBUtil.pm</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 <a name="package-DBUtil-"></a> 1 <span class="k">package </span><span class="i">DBUtil</span><span class="sc">;</span>
15 2 <span class="c">#</span>
16 3 <span class="c"># $RCSfile: DBUtil.pm,v $</span>
17 4 <span class="c"># $Date: 2015/02/28 20:47:02 $</span>
18 5 <span class="c"># $Revision: 1.34 $</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">Exporter</span><span class="sc">;</span>
44 31 <span class="k">use</span> <span class="w">Carp</span><span class="sc">;</span>
45 32 <span class="k">use</span> <span class="w">DBI</span><span class="sc">;</span>
46 33 <span class="k">use</span> <span class="w">TextUtil</span><span class="sc">;</span>
47 34
48 35 <span class="k">use</span> <span class="w">vars</span> <span class="q">qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS)</span><span class="sc">;</span>
49 36
50 37 <span class="i">@ISA</span> = <span class="q">qw(Exporter)</span><span class="sc">;</span>
51 38 <span class="i">@EXPORT</span> = <span class="q">qw(DBConnect DBDisconnect DBFetchSchemaTableNames DBSetupDescribeSQL DBSetupSelectSQL DBSQLToTextFile)</span><span class="sc">;</span>
52 39 <span class="i">@EXPORT_OK</span> = <span class="q">qw()</span><span class="sc">;</span>
53 40 <span class="i">%EXPORT_TAGS</span> = <span class="s">(</span><span class="w">all</span> <span class="cm">=&gt;</span> <span class="s">[</span><span class="i">@EXPORT</span><span class="cm">,</span> <span class="i">@EXPORT_OK</span><span class="s">]</span><span class="s">)</span><span class="sc">;</span>
54 41
55 42 <span class="c"># Connect to a specified database...</span>
56 <a name="DBConnect-"></a> 43 <span class="k">sub </span><span class="m">DBConnect</span> <span class="s">{</span>
57 44 <span class="k">my</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="i">@_</span><span class="sc">;</span>
58 45 <span class="k">my</span><span class="s">(</span><span class="i">$DBHandle</span><span class="cm">,</span> <span class="i">$DataSource</span><span class="s">)</span><span class="sc">;</span>
59 46
60 47 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span> <span class="k">eq</span> <span class="q">&quot;Oracle&quot;</span><span class="s">)</span> <span class="s">{</span>
61 48 <span class="i">$DataSource</span> = <span class="q">qq(DBI:$DBDriver:$DBHost)</span><span class="sc">;</span>
62 49 <span class="s">}</span>
63 50 <span class="k">else</span> <span class="s">{</span>
64 51 <span class="i">$DataSource</span> = <span class="q">qq(DBI:$DBDriver:database=$DBName)</span><span class="sc">;</span>
65 52 <span class="k">if</span> <span class="s">(</span><span class="i">$DBHost</span><span class="s">)</span> <span class="s">{</span>
66 53 <span class="i">$DataSource</span> .= <span class="q">qq(;host=$DBHost)</span><span class="sc">;</span>
67 54 <span class="s">}</span>
68 55 <span class="s">}</span>
69 56
70 57 <span class="c"># Don&#39;t raise the error; otherwise, DBI functions termiates on encountering an error.</span>
71 58 <span class="c"># All terminations decisions are made outside of DBI functions...</span>
72 59 <span class="i">$DBHandle</span> = <span class="w">DBI</span><span class="w">-&gt;connect</span><span class="s">(</span><span class="i">$DataSource</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="s">{</span> <span class="w">RaiseError</span> <span class="cm">=&gt;</span> <span class="n">0</span><span class="cm">,</span> <span class="w">AutoCommit</span> <span class="cm">=&gt;</span> <span class="n">0</span> <span class="s">}</span><span class="s">)</span> <span class="k">or</span> <span class="w">croak</span> <span class="q">&quot;Couldn&#39;t connect to database...&quot;</span><span class="sc">;</span>
73 60
74 61 <span class="k">return</span> <span class="i">$DBHandle</span><span class="sc">;</span>
75 62 <span class="s">}</span>
76 63
77 64 <span class="c"># Disconnect from a database...</span>
78 <a name="DBDisconnect-"></a> 65 <span class="k">sub </span><span class="m">DBDisconnect</span> <span class="s">{</span>
79 66 <span class="k">my</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
80 67
81 68 <span class="i">$DBHandle</span><span class="i">-&gt;disconnect</span> <span class="k">or</span> <span class="w">carp</span> <span class="q">&quot;Couldn&#39;t disconnect from a database...&quot;</span><span class="sc">;</span>
82 69 <span class="s">}</span>
83 70
84 71 <span class="c"># Fetch all table name for a database schema...</span>
85 <a name="DBFetchSchemaTableNames-"></a> 72 <span class="k">sub </span><span class="m">DBFetchSchemaTableNames</span> <span class="s">{</span>
86 73 <span class="k">my</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$DBHandle</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
87 74 <span class="k">my</span><span class="s">(</span><span class="i">@SchemaTableNames</span><span class="cm">,</span> <span class="i">$SQL</span><span class="cm">,</span> <span class="i">$SQLHandle</span><span class="s">)</span><span class="sc">;</span>
88 75
89 76 <span class="i">@SchemaTableNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
90 77
91 78 <span class="i">$SchemaName</span> = <span class="s">(</span><span class="k">defined</span> <span class="i">$SchemaName</span> &amp;&amp; <span class="k">length</span> <span class="i">$SchemaName</span><span class="s">)</span> ? <span class="i">$SchemaName</span> <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
92 79
93 80 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span> <span class="k">eq</span> <span class="q">&quot;mysql&quot;</span><span class="s">)</span> <span class="s">{</span>
94 81 <span class="c"># Switch schemas...</span>
95 82 <span class="i">$SQL</span> = <span class="q">qq(USE $SchemaName)</span><span class="sc">;</span>
96 83 <span class="i">$SQLHandle</span> = <span class="i">$DBHandle</span><span class="i">-&gt;prepare</span><span class="s">(</span><span class="i">$SQL</span><span class="s">)</span> <span class="k">or</span> <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
97 84 <span class="i">$SQLHandle</span><span class="i">-&gt;execute</span> <span class="k">or</span> <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
98 85 <span class="i">$SQLHandle</span><span class="i">-&gt;finish</span> <span class="k">or</span> <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
99 86
100 87 <span class="c"># Setup to fetch table names...</span>
101 88 <span class="i">$SQL</span> = <span class="q">qq(SHOW TABLES)</span><span class="sc">;</span>
102 89 <span class="s">}</span>
103 90 <span class="k">elsif</span> <span class="s">(</span><span class="i">$DBDriver</span> <span class="k">eq</span> <span class="q">&quot;Oracle&quot;</span><span class="s">)</span> <span class="s">{</span>
104 91 <span class="i">$SQL</span> = <span class="q">qq(SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE OWNER = &#39;$SchemaName&#39; AND SEGMENT_TYPE = &#39;TABLE&#39; ORDER BY SEGMENT_NAME)</span><span class="sc">;</span>
105 92 <span class="s">}</span>
106 93 <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>
107 94 <span class="i">$SQL</span> = <span class="q">qq(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = &#39;$SchemaName&#39;)</span><span class="sc">;</span>
108 95 <span class="s">}</span>
109 96 <span class="i">$SQLHandle</span> = <span class="i">$DBHandle</span><span class="i">-&gt;prepare</span><span class="s">(</span><span class="i">$SQL</span><span class="s">)</span> <span class="k">or</span> <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
110 97 <span class="i">$SQLHandle</span><span class="i">-&gt;execute</span> <span class="k">or</span> <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
111 98
112 99 <span class="k">my</span><span class="s">(</span><span class="i">@RowValues</span><span class="cm">,</span> <span class="i">$TableName</span><span class="s">)</span><span class="sc">;</span>
113 100 <span class="k">while</span> <span class="s">(</span><span class="i">@RowValues</span> = <span class="i">$SQLHandle</span><span class="i">-&gt;fetchrow_array</span><span class="s">)</span> <span class="s">{</span>
114 101 <span class="i">$TableName</span> = <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^(mysql|Oracle)$/i</span><span class="s">)</span> ? <span class="k">uc</span><span class="s">(</span><span class="i">$RowValues</span>[<span class="n">0</span>]<span class="s">)</span> <span class="co">:</span> <span class="i">$RowValues</span>[<span class="n">0</span>]<span class="sc">;</span>
115 102 <span class="k">if</span> <span class="s">(</span><span class="k">defined</span> <span class="i">$TableName</span> &amp;&amp; <span class="k">length</span> <span class="i">$TableName</span><span class="s">)</span> <span class="s">{</span>
116 103 <span class="k">push</span> <span class="i">@SchemaTableNames</span><span class="cm">,</span> <span class="i">$TableName</span><span class="sc">;</span>
117 104 <span class="s">}</span>
118 105 <span class="s">}</span>
119 106 <span class="i">$SQLHandle</span><span class="i">-&gt;finish</span> <span class="k">or</span> <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
120 107
121 108 <span class="k">return</span> <span class="i">@SchemaTableNames</span><span class="sc">;</span>
122 109 <span class="s">}</span>
123 110
124 111 <span class="c"># Setup describe SQL statement...</span>
125 <a name="DBSetupDescribeSQL-"></a> 112 <span class="k">sub </span><span class="m">DBSetupDescribeSQL</span> <span class="s">{</span>
126 113 <span class="k">my</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span><span class="sc">;</span>
127 114 <span class="k">my</span><span class="s">(</span><span class="i">$DescribeSQL</span><span class="s">)</span><span class="sc">;</span>
128 115
129 116 <span class="i">$DBDriver</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span> <span class="i">$TableName</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span> <span class="i">$SchemaName</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span>
130 117 <span class="k">if</span> <span class="s">(</span><span class="i">@_</span> == <span class="n">3</span><span class="s">)</span> <span class="s">{</span>
131 118 <span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span> = <span class="i">@_</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="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
135 122 <span class="s">}</span>
136 123 <span class="i">$TableName</span> = <span class="s">(</span><span class="k">defined</span> <span class="i">$TableName</span> &amp;&amp; <span class="k">length</span> <span class="i">$TableName</span><span class="s">)</span> ? <span class="i">$TableName</span> <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
137 124 <span class="i">$SchemaName</span> = <span class="s">(</span><span class="k">defined</span> <span class="i">$SchemaName</span> &amp;&amp; <span class="k">length</span> <span class="i">$SchemaName</span><span class="s">)</span> ? <span class="i">$SchemaName</span> <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
138 125
139 126 <span class="i">$DescribeSQL</span> = <span class="s">(</span><span class="i">$SchemaName</span><span class="s">)</span> ? <span class="s">(</span><span class="q">&quot;DESCRIBE &quot;</span> . <span class="q">&quot;$SchemaName&quot;</span> . <span class="q">&quot;.$TableName&quot;</span><span class="s">)</span> <span class="co">:</span> <span class="q">&quot;DESCRIBE $TableName&quot;</span><span class="sc">;</span>
140 127
141 128 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span> <span class="k">eq</span> <span class="q">&quot;Oracle&quot;</span><span class="s">)</span> <span class="s">{</span>
142 129 <span class="i">$DescribeSQL</span> = <span class="q">qq(SELECT COLUMN_NAME &quot;Column_Name&quot;, DECODE(NULLABLE, &#39;N&#39;,&#39;Not Null&#39;,&#39;Y&#39;,&#39;Null&#39;) &quot;Null&quot;, DATA_TYPE &quot;Data_Type&quot;, DATA_LENGTH &quot;Data_Length&quot;, DATA_PRECISION &quot;Data_Precision&quot; FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = &#39;$TableName&#39;)</span><span class="sc">;</span>
143 130 <span class="k">if</span> <span class="s">(</span><span class="i">$SchemaName</span><span class="s">)</span> <span class="s">{</span>
144 131 <span class="i">$DescribeSQL</span> .= <span class="q">qq( AND OWNER = &#39;$SchemaName&#39;)</span><span class="sc">;</span>
145 132 <span class="s">}</span>
146 133 <span class="i">$DescribeSQL</span> .= <span class="q">qq( ORDER BY COLUMN_ID)</span><span class="sc">;</span>
147 134 <span class="s">}</span>
148 135 <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>
149 136 <span class="i">$DescribeSQL</span> = <span class="q">qq(SELECT COLUMN_NAME &quot;Column_Name&quot;, data_type &quot;Data_Type&quot; FROM information_schema.columns WHERE table_name =&#39;$TableName&#39;)</span><span class="sc">;</span>
150 137 <span class="k">if</span> <span class="s">(</span><span class="i">$SchemaName</span><span class="s">)</span> <span class="s">{</span>
151 138 <span class="i">$DescribeSQL</span> .= <span class="q">&quot; and table_schema = &#39;$SchemaName&#39;&quot;</span><span class="sc">;</span>
152 139 <span class="s">}</span>
153 140 <span class="s">}</span>
154 141
155 142 <span class="k">return</span> <span class="i">$DescribeSQL</span><span class="sc">;</span>
156 143 <span class="s">}</span>
157 144
158 145 <span class="c"># Setup describe SQL statement...</span>
159 <a name="DBSetupSelectSQL-"></a> 146 <span class="k">sub </span><span class="m">DBSetupSelectSQL</span> <span class="s">{</span>
160 147 <span class="k">my</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span><span class="sc">;</span>
161 148 <span class="k">my</span><span class="s">(</span><span class="i">$SelectSQL</span><span class="s">)</span><span class="sc">;</span>
162 149
163 150 <span class="i">$DBDriver</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span> <span class="i">$TableName</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span> <span class="i">$SchemaName</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span>
164 151 <span class="k">if</span> <span class="s">(</span><span class="i">@_</span> == <span class="n">3</span><span class="s">)</span> <span class="s">{</span>
165 152 <span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
166 153 <span class="s">}</span>
167 154 <span class="k">else</span> <span class="s">{</span>
168 155 <span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
169 156 <span class="s">}</span>
170 157 <span class="i">$TableName</span> = <span class="s">(</span><span class="k">defined</span> <span class="i">$TableName</span> &amp;&amp; <span class="k">length</span> <span class="i">$TableName</span><span class="s">)</span> ? <span class="i">$TableName</span> <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
171 158 <span class="i">$SchemaName</span> = <span class="s">(</span><span class="k">defined</span> <span class="i">$SchemaName</span> &amp;&amp; <span class="k">length</span> <span class="i">$SchemaName</span><span class="s">)</span> ? <span class="i">$SchemaName</span> <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
172 159
173 160 <span class="i">$SelectSQL</span> = <span class="s">(</span><span class="i">$SchemaName</span><span class="s">)</span> ? <span class="s">(</span><span class="q">&quot;SELECT * FROM &quot;</span> . <span class="q">&quot;$SchemaName&quot;</span> . <span class="q">&quot;.$TableName&quot;</span><span class="s">)</span> <span class="co">:</span> <span class="q">&quot;SELECT * FROM $TableName&quot;</span><span class="sc">;</span>
174 161
175 162 <span class="k">return</span> <span class="i">$SelectSQL</span><span class="sc">;</span>
176 163 <span class="s">}</span>
177 164
178 165 <span class="c"># Prepare and execute a SQL statement and write out results into</span>
179 166 <span class="c"># a text file.</span>
180 <a name="DBSQLToTextFile-"></a> 167 <span class="k">sub </span><span class="m">DBSQLToTextFile</span> <span class="s">{</span>
181 168 <span class="k">my</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="sc">;</span>
182 169 <span class="k">my</span><span class="s">(</span><span class="i">$SQLHandle</span><span class="cm">,</span> <span class="i">$Status</span><span class="s">)</span><span class="sc">;</span>
183 170
184 171 <span class="i">$Status</span> = <span class="n">1</span><span class="sc">;</span>
185 172 <span class="i">$ExportDataLabels</span> = <span class="n">1</span><span class="sc">;</span>
186 173 <span class="i">$ExportLOBs</span> = <span class="n">0</span><span class="sc">;</span>
187 174 <span class="i">$ReplaceNullStr</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span>
188 175 <span class="k">if</span> <span class="s">(</span><span class="i">@_</span> == <span class="n">8</span><span class="s">)</span> <span class="s">{</span>
189 176 <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="i">@_</span><span class="sc">;</span>
190 177 <span class="s">}</span>
191 178 <span class="k">elsif</span> <span class="s">(</span><span class="i">@_</span> == <span class="n">7</span><span class="s">)</span> <span class="s">{</span>
192 179 <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="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
193 180 <span class="s">}</span>
194 181 <span class="k">elsif</span> <span class="s">(</span><span class="i">@_</span> == <span class="n">6</span><span class="s">)</span> <span class="s">{</span>
195 182 <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="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
196 183 <span class="s">}</span>
197 184 <span class="k">else</span> <span class="s">{</span>
198 185 <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="s">)</span> = <span class="i">@_</span><span class="sc">;</span>
199 186 <span class="s">}</span>
200 187
201 188 <span class="c"># Execute SQL statement...</span>
202 189 <span class="i">$SQLHandle</span> = <span class="i">$DBHandle</span><span class="i">-&gt;prepare</span><span class="s">(</span><span class="i">$SQL</span><span class="s">)</span> <span class="k">or</span> <span class="k">return</span> <span class="i">$Status</span><span class="sc">;</span>
203 190 <span class="i">$SQLHandle</span><span class="i">-&gt;execute</span><span class="s">(</span><span class="s">)</span> <span class="k">or</span> <span class="k">return</span> <span class="i">$Status</span><span class="sc">;</span>
204 191
205 192 <span class="k">my</span><span class="s">(</span><span class="i">$FieldsNum</span><span class="cm">,</span> <span class="i">@FieldNames</span><span class="cm">,</span> <span class="i">@RowValues</span><span class="cm">,</span> <span class="i">@ColNumsToExport</span><span class="cm">,</span> <span class="i">@ColLabels</span><span class="cm">,</span> <span class="i">$ColNum</span><span class="cm">,</span> <span class="i">$ColLabelsLine</span><span class="cm">,</span> <span class="i">@Values</span><span class="cm">,</span> <span class="i">$Value</span><span class="cm">,</span> <span class="i">$ValuesLine</span><span class="s">)</span><span class="sc">;</span>
206 193
207 194 <span class="i">$Status</span> = <span class="n">0</span><span class="sc">;</span>
208 195 <span class="c"># Figure out which column numbers need to be exported...</span>
209 196 <span class="i">$FieldsNum</span> = <span class="i">$SQLHandle</span>-&gt;{<span class="w">NUM_OF_FIELDS</span>}<span class="sc">;</span>
210 197 <span class="i">@FieldNames</span> = <span class="i">@</span>{<span class="i">$SQLHandle</span>-&gt;{<span class="w">NAME</span>}}<span class="sc">;</span>
211 198 <span class="i">@ColNumsToExport</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
212 199 <span class="k">if</span> <span class="s">(</span><span class="i">$ExportLOBs</span><span class="s">)</span> <span class="s">{</span>
213 200 <span class="i">@ColNumsToExport</span> = <span class="s">(</span><span class="n">0</span> .. <span class="i">$#FieldNames</span><span class="s">)</span><span class="sc">;</span>
214 201 <span class="s">}</span>
215 202 <span class="k">else</span> <span class="s">{</span>
216 203 <span class="k">my</span><span class="s">(</span><span class="i">@FieldTypes</span><span class="cm">,</span> <span class="i">@FieldTypeNames</span><span class="cm">,</span> <span class="i">$Type</span><span class="cm">,</span> <span class="i">$TypeName</span><span class="s">)</span><span class="sc">;</span>
217 204 <span class="i">@FieldTypes</span> = <span class="i">@</span>{<span class="i">$SQLHandle</span>-&gt;{<span class="w">TYPE</span>}}<span class="sc">;</span>
218 205 <span class="i">@FieldTypeNames</span> = <span class="k">map</span> <span class="s">{</span> <span class="k">scalar</span> <span class="i">$DBHandle</span><span class="i">-&gt;type_info</span><span class="s">(</span><span class="i">$_</span><span class="s">)</span>-&gt;{<span class="w">TYPE_NAME</span>} <span class="s">}</span> <span class="i">@FieldTypes</span><span class="sc">;</span>
219 206 <span class="k">for</span> <span class="i">$ColNum</span> <span class="s">(</span><span class="n">0</span> .. <span class="i">$#FieldNames</span><span class="s">)</span> <span class="s">{</span>
220 207 <span class="k">if</span> <span class="s">(</span><span class="i">$FieldTypeNames</span>[<span class="i">$ColNum</span>] !~ <span class="q">/lob|bytea/i</span> <span class="s">)</span> <span class="s">{</span>
221 208 <span class="k">push</span> <span class="i">@ColNumsToExport</span><span class="cm">,</span> <span class="i">$ColNum</span><span class="sc">;</span>
222 209 <span class="s">}</span>
223 210 <span class="s">}</span>
224 211 <span class="s">}</span>
225 212
226 213 <span class="k">if</span> <span class="s">(</span><span class="i">$ExportDataLabels</span><span class="s">)</span> <span class="s">{</span>
227 214 <span class="c"># Print out column labels...</span>
228 215 <span class="i">@ColLabels</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
229 216 <span class="k">for</span> <span class="i">$ColNum</span> <span class="s">(</span><span class="i">@ColNumsToExport</span><span class="s">)</span> <span class="s">{</span>
230 217 <span class="k">push</span> <span class="i">@ColLabels</span><span class="cm">,</span> <span class="i">$FieldNames</span>[<span class="i">$ColNum</span>]<span class="sc">;</span>
231 218 <span class="s">}</span>
232 219 <span class="i">$ColLabelsLine</span> = <span class="i">JoinWords</span><span class="s">(</span>\<span class="i">@ColLabels</span><span class="cm">,</span> <span class="i">$OutDelim</span><span class="cm">,</span> <span class="i">$OutQuote</span><span class="s">)</span><span class="sc">;</span>
233 220 <span class="k">print</span> <span class="i">$TextFile</span> <span class="q">&quot;$ColLabelsLine\n&quot;</span><span class="sc">;</span>
234 221 <span class="s">}</span>
235 222 <span class="c"># Print out row values...</span>
236 223 <span class="k">while</span> <span class="s">(</span><span class="i">@RowValues</span> = <span class="i">$SQLHandle</span><span class="i">-&gt;fetchrow_array</span><span class="s">)</span> <span class="s">{</span>
237 224 <span class="i">@Values</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
238 225 <span class="k">for</span> <span class="i">$ColNum</span> <span class="s">(</span><span class="i">@ColNumsToExport</span><span class="s">)</span> <span class="s">{</span>
239 226 <span class="k">if</span> <span class="s">(</span><span class="k">defined</span><span class="s">(</span><span class="i">$RowValues</span>[<span class="i">$ColNum</span>]<span class="s">)</span> &amp;&amp; <span class="k">length</span><span class="s">(</span><span class="i">$RowValues</span>[<span class="i">$ColNum</span>]<span class="s">)</span><span class="s">)</span> <span class="s">{</span>
240 227 <span class="i">$Value</span> = <span class="i">$RowValues</span>[<span class="i">$ColNum</span>]<span class="sc">;</span>
241 228 <span class="s">}</span>
242 229 <span class="k">else</span> <span class="s">{</span>
243 230 <span class="i">$Value</span> = <span class="i">$ReplaceNullStr</span> ? <span class="i">$ReplaceNullStr</span> <span class="co">:</span> <span class="q">&quot;&quot;</span><span class="sc">;</span>
244 231 <span class="s">}</span>
245 232 <span class="k">push</span> <span class="i">@Values</span><span class="cm">,</span> <span class="i">$Value</span><span class="sc">;</span>
246 233 <span class="s">}</span>
247 234 <span class="i">$ValuesLine</span> = <span class="i">JoinWords</span><span class="s">(</span>\<span class="i">@Values</span><span class="cm">,</span> <span class="i">$OutDelim</span><span class="cm">,</span> <span class="i">$OutQuote</span><span class="s">)</span><span class="sc">;</span>
248 235 <span class="k">print</span> <span class="i">$TextFile</span> <span class="q">&quot;$ValuesLine\n&quot;</span><span class="sc">;</span>
249 236 <span class="s">}</span>
250 237 <span class="i">$SQLHandle</span><span class="i">-&gt;finish</span> <span class="k">or</span> <span class="k">return</span> <span class="i">$Status</span><span class="sc">;</span>
251 238 <span class="i">$Status</span> = <span class="n">0</span><span class="sc">;</span>
252 239
253 240 <span class="k">return</span> <span class="i">$Status</span><span class="sc">;</span>
254 241 <span class="s">}</span>
255 242
256 <a name="EOF-"></a></pre>
257 <p>&nbsp;</p>
258 <br />
259 <center>
260 <img src="../../../images/h2o2.png">
261 </center>
262 </body>
263 </html>