Mercurial > repos > deepakjadmin > mayatool3_test3
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 <msud@san.rr.com></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 <http://www.gnu.org/licenses/> 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">=></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">"Oracle"</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'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">->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">=></span> <span class="n">0</span><span class="cm">,</span> <span class="w">AutoCommit</span> <span class="cm">=></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">"Couldn't connect to database..."</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">->disconnect</span> <span class="k">or</span> <span class="w">carp</span> <span class="q">"Couldn't disconnect from a database..."</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> && <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">""</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">"mysql"</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">->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">->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">->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">"Oracle"</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 = '$SchemaName' AND SEGMENT_TYPE = 'TABLE' 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 = '$SchemaName')</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">->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">->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">->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> && <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">->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">""</span><span class="sc">;</span> <span class="i">$TableName</span> = <span class="q">""</span><span class="sc">;</span> <span class="i">$SchemaName</span> = <span class="q">""</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> && <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">""</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> && <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">""</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">"DESCRIBE "</span> . <span class="q">"$SchemaName"</span> . <span class="q">".$TableName"</span><span class="s">)</span> <span class="co">:</span> <span class="q">"DESCRIBE $TableName"</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">"Oracle"</span><span class="s">)</span> <span class="s">{</span> | |
| 142 129 <span class="i">$DescribeSQL</span> = <span class="q">qq(SELECT COLUMN_NAME "Column_Name", DECODE(NULLABLE, 'N','Not Null','Y','Null') "Null", DATA_TYPE "Data_Type", DATA_LENGTH "Data_Length", DATA_PRECISION "Data_Precision" FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '$TableName')</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 = '$SchemaName')</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 "Column_Name", data_type "Data_Type" FROM information_schema.columns WHERE table_name ='$TableName')</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">" and table_schema = '$SchemaName'"</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">""</span><span class="sc">;</span> <span class="i">$TableName</span> = <span class="q">""</span><span class="sc">;</span> <span class="i">$SchemaName</span> = <span class="q">""</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> && <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">""</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> && <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">""</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">"SELECT * FROM "</span> . <span class="q">"$SchemaName"</span> . <span class="q">".$TableName"</span><span class="s">)</span> <span class="co">:</span> <span class="q">"SELECT * FROM $TableName"</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">""</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">->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">->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>->{<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>->{<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>->{<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">->type_info</span><span class="s">(</span><span class="i">$_</span><span class="s">)</span>->{<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">"$ColLabelsLine\n"</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">->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> && <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">""</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">"$ValuesLine\n"</span><span class="sc">;</span> | |
| 249 236 <span class="s">}</span> | |
| 250 237 <span class="i">$SQLHandle</span><span class="i">->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> </p> | |
| 258 <br /> | |
| 259 <center> | |
| 260 <img src="../../../images/h2o2.png"> | |
| 261 </center> | |
| 262 </body> | |
| 263 </html> |