1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml" id="sixapart-standard">
5 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
6 <meta name="generator" content="Movable Type 5.2.3" />
8 <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles-site.css" type="text/css" />
9 <link rel="alternate" type="application/atom+xml" title="Atom" href="http://defaria.com/blogs/Status/atom.xml" />
10 <link rel="alternate" type="application/rss+xml" title="RSS 2.0" href="http://defaria.com/blogs/Status/index.xml"$>" />
12 <title>Status for Andrew DeFaria: March 18, 2007 - March 24, 2007 Archives</title>
14 <link rel="start" href="http://defaria.com/blogs/Status/" title="Home" />
15 <link rel="prev" href="http://defaria.com/blogs/Status/archives/week_2007_03_11.html" title="March 11, 2007 - March 17, 2007" />
16 <link rel="next" href="http://defaria.com/blogs/Status/archives/week_2007_03_25.html" title="March 25, 2007 - March 31, 2007" />
18 <body class="layout-one-column">
20 <div id="container-inner" class="pkg">
23 <div id="banner-inner" class="pkg">
24 <h1 id="banner-header"><a href="http://defaria.com/blogs/Status/" accesskey="1">Status for Andrew DeFaria</a></h1>
25 <h2 id="banner-description">Searchable status reports and work log</h2>
30 <div id="pagebody-inner" class="pkg">
32 <div id="alpha-inner" class="pkg">
34 <p class="content-nav">
35 <a href="http://defaria.com/blogs/Status/archives/week_2007_03_11.html">« March 11, 2007 - March 17, 2007</a> |
36 <a href="http://defaria.com/blogs/Status/">Main</a>
37 | <a href="http://defaria.com/blogs/Status/archives/week_2007_03_25.html">March 25, 2007 - March 31, 2007 »</a>
42 <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
43 xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/"
44 xmlns:dc="http://purl.org/dc/elements/1.1/">
46 rdf:about="http://defaria.com/blogs/Status/archives/week_2007_03_18.html#entry-000625"
47 trackback:ping="http://defaria.com/mt/mt-tb.cgi/14"
48 dc:title="GPDB Web Update/Oracle testing"
49 dc:identifier="http://defaria.com/blogs/Status/archives/week_2007_03_18.html#entry-000625"
50 dc:subject="Texas Instruments"
51 dc:description=" Added menus to GPDB web Tested and documented access of Oracle databases, such as GPDB, using standard Perl and standard Oracle libraries from various architectures..."
53 dc:date="2007-03-21T16:52:37-06:00" />
58 <h2 class="date-header">March 21, 2007</h2>
60 <div class="entry" id="entry-625">
61 <h3 class="entry-header">GPDB Web Update/Oracle testing</h3>
62 <div class="entry-content">
63 <div class="entry-body">
65 <li>Added menus to GPDB web</li>
67 <li>Tested and documented access of Oracle databases, such as GPDB, using standard Perl and standard Oracle libraries from various architectures</li>
70 <p>Joe Ramey had worked on getting the Oracle Linux client API library to work with Perl. I am trying to validate that this works on all supported architectures where <i>all supported architectures</i> is considered the subset of:</p>
72 <li>Solaris (e.g. Stashu)</li>
73 <li>Linux 32-bit (e.g. Fad01)</li>
74 <li>Linux 64-bit AMD Opteron processor (e.g. DrTeeth)</li>
75 <li>Linux 64-bit Intel processor (e.g. Fad11)</li>
78 <h3>How to properly code Perl to access an Oracle DB</h3>
80 <p>Normal access to SQL databases in Perl usually consists of use'ing two different Perl modules. First a DBI.pm module which is the<i> <b>D</b>ata<b>B</b>ase <b>I</b>ndependent</i> interface and the DBD or <b><i>D</i></b><i>ata<b>B</b>ase <b>D</b>river</i> module for the particular SQL database involved. We are interested in Oracle here but others include mSQL, MySQL, etc.</p>
82 <p>Couple this with the fact that we wish to use the standard Perl (e.g. /apps/perl/5.8.3/bin/perl) and standard Perl modules (/apps/perl/modules-<<i>datecode</i>> where <i>datecode</i> is some combination of a year/month date code whose apparent LCD seems to be modules-0412).</p>
84 <p>So first our Perl script should start with:</p>
87 <font color="#3333ff">#!/apps/perl/5.8.3/bin/perl</font>
88 <font color="#ff0000">use strict</font>;
89 <font color="#ff0000">use warnings</font>;
91 <font color="#3333ff"># Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm</font>
92 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/perl/modules-0412/lib</font>";
95 <p>So as the comment indicates we are picking up the DBI.pm module from here. Next, normally, we'd simply include the lib path for the Oracle DBD.pm (or in the special case of Oracle, the Oracle.pm file) as well as the supporting shared library Oracle.so (which is architecturally dependent) by coding the following:</p>
98 <font color="#3333ff"># Perl/Oracle libraries</font>
99 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/oracle/perl/10.2.0.1.0/lib</font>";
102 <p>However, since Linux client support is not included in /apps/oracle/perl/10.2.0.1.0/lib (yet) we need to pick up Joe's stuff:</p>
104 <div class=code><pre>
105 <font color="#3333ff"># Linux API is in Ramey's home dir...</font>
106 <font color="#ff0000">use lib</font> "<font color="#009900">/home/ramey/oracle/perl/10.2.0.1.0/lib</font>";
109 <p>The next excerpt from my code, shown in it's entirety later, shows a lib path I used to use that allows Solaris clients to work correctly, however we wish to move away from this special cased library thus it's commented out here:</p>
111 <div class=code><pre>
112 <font color="#3333ff"># This is a non standard Oracle</font>
113 <font color="#3333ff">#use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";</font>
116 <p>As the full code listing that follows shows I then use my current development libraries and attempt to log into the development version of GPDB, and Oracle database, and retrieve and display some rudimentary data:</p>
118 <p><font color="#999999">Entire code listing of ~x0062320/testgpdb.pl</font></p>
120 <div class=code><pre>
121 <font color="#3333ff">#!/apps/perl/5.8.3/bin/perl</font>
122 <font color="#ff0000">use strict</font>;
123 <font color="#ff0000">use warnings</font>;
125 <font color="#3333ff"># Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm</font>
126 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/perl/modules-0412/lib</font>";
128 <font color="#3333ff"># Perl/Oracle libraries</font>
129 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/oracle/perl/10.2.0.1.0/lib</font>";
131 <font color="#3333ff"># Linux API is in Ramey's home dir...</font>
132 <font color="#ff0000">use lib</font> "<font color="#009900">/home/ramey/oracle/perl/10.2.0.1.0/lib</font>";
134 <font color="#3333ff"># This is a non standard Oracle</font>
135 <font color="#3333ff">#use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";
137 # Current development GPDB modules</font>
138 <font color="#ff0000">use lib</font> "<font color="#009900">/web/gpdb/cgi-bin</font>";
140 <font color="#ff0000">use GPDB::gpdb</font>;
142 <font color="#3333ff"># Log into GPDB - read only</font>
143 gpdb_login (<font color="#33cc00">undef</font>, <font color="#33cc00">undef</font>, "<font color="#009900">OracleDevelopment</font>");
145 <font color="#3333ff"># Get a project</font>
146 <font color="#ff0000">my</font> <font color="#3333ff"><b>@projects</b></font> = gpdb_getProject ("<font color="#009900">uma</font>");
148 <font color="#3333ff"># Play with it a little bit...</font>
149 <font color="#ff0000">my</font> <i><font color="#ff0000">%project</font></i> = %{<font color="#3333ff"><b>$projects</b></font> [0][0]};
151 <font color="#33cc00">print</font> "<font color="#009900">Project: ${project {PROJECT}{NAME}}\n</font>";
152 <font color="#33cc00">print</font> "<font color="#009900">Site: ${project {PROJECT}{SITE_NAME}}\n</font>";
154 <font color="#33cc00">print</font> "<font color="#009900">Vobs:\n</font>";
156 <font color="#ff0000">foreach</font> (@{<font color="#ff0000">$project</font> {CLEARCASE}}) {
157 <font color="#ff0000">my</font> <i><font color="#ff0000">%cc</font></i> = %{$_};
159 <font color="#33cc00">print</font> "<font color="#009900">\t${cc {VOB_TAG}}\n</font>";
160 } <font color="#3333ff"># foreach</font>
162 <font color="#33cc00">print</font> "<font color="#009900">done\n</font>";
167 <p>Here's the results running on the target architectures:</p>
169 <div class=code><pre>
170 $ ssh stashu testgpdb.pl
171 DBI connect('host=dflorad01.itg.ti.com;sid=flddsync;port=1521','cm_gpdb_readonly',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc. at /web/gpdb/cgi-bin/GPDB/DBHelper.pm line 136
172 Could not connect to database. Check $ORACLE_HOME. at /web/gpdb/cgi-bin/GPDB/primitive.pm line 81
173 $ ssh fad01 testgpdb.pl
182 $ ssh drteeth testgpdb.pl
191 $ ssh fad11 testgpdb.pl
193 '/home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so' for module DBI: /lib/libc.so.6: version `GLIBC_2.3' not found (required by
194 /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so) at /apps/perl/5.8.3/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229.
195 at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259
196 BEGIN failed--compilation aborted at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259.
197 Compilation failed in require at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
198 BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
199 Compilation failed in require at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
200 BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
201 Compilation failed in require at ./testgpdb.pl line 20.
202 BEGIN failed--compilation aborted at ./testgpdb.pl line 20.
205 <p>So as we can see this is <b>not</b> working for Solaris or Linux 64-bit Intel.</p>
207 <p class="entry-footer">
208 <span class="post-footers">Posted by at 4:52 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000625.html">Permalink</a>
210 | <a href="http://defaria.com/blogs/Status/archives/000625.html#trackback">TrackBacks (0)</a>