Saturday, December 19, 2009

Install PAE Kernel and Update Grub CentOS 5.x

On my new 32-bit CentOS 5.3 server with 4GIG of RAM only 3+ GIG are visible:

[root@server1 ~]# free -m
total used free shared buffers cached
Mem: 3280 275 3005 0 15 106
-/+ buffers/cache: 154 3126
Swap: 1788 0 1788
[root@server1 ~]# top
top - 19:19:01 up 4 min, 1 user, load average: 0.07, 0.27, 0.14
Tasks: 117 total, 2 running, 115 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.8%id, 0.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3359720k total, 282788k used, 3076932k free, 15580k buffers
Swap: 1831400k total, 0k used, 1831400k free, 108888k cached

For x86 systems that support PAE, you will need to install the kernel-PAE package to see your RAM.

From CentOS 5 FAQ:

kernel-PAE (x86-only): Contains a kernel with support for 64GB of memory on x86 systems through Physical Address Extensions (PAE). Only x86 CPUs that support PAE can run this kernel. This kernel is provided because x86 systems can't address more than 4GB of memory without PAE extensions.


1. Install kernel-PAE:

[root@server1 ~]# yum install kernel-PAE

2. Update grub.conf to boot from PAE kernel. After installing, vi /boot/grub/grub.conf and look for the line "default=..".

The value determines the default boot kernel and the sequence begins with 0. Review the kernel titles and update the default to the PAE kernel you installed (0 in my case below) and :wq

[root@server1 ~]# vi /boot/grub/grub.conf

grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You do not have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /, eg.
#          root (hd0,0)
#          kernel /boot/vmlinuz-version ro root=/dev/md0
#          initrd /boot/initrd-version.img
#boot=/dev/md0
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title CentOS (2.6.18-164.9.1.el5PAE)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.18-164.9.1.el5PAE ro root=/dev/md0
        initrd /boot/initrd-2.6.18-164.9.1.el5PAE.img
title CentOS (2.6.18-8.el5)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.18-8.el5 ro root=/dev/md0
        initrd /boot/initrd-2.6.18-8.el5.img
~
~
"/boot/grub/grub.conf" 21L, 742C


3. Reboot and check:

[root@server1 ~]# free -m
total used free shared buffers cached
Mem: 4042 955 3087 0 175 583
-/+ buffers/cache: 196 3846
Swap: 4000 0 4000
[root@server1 ~]# top
top - 01:04:22 up 2 days, 2:34, 1 user, load average: 0.05, 0.16, 0.15
Tasks: 121 total, 1 running, 120 sleeping, 0 stopped, 0 zombie
Cpu(s): 6.1%us, 9.2%sy, 0.0%ni, 68.1%id, 0.8%wa, 0.4%hi, 15.3%si, 0.0%st
Mem: 4139764k total, 978236k used, 3161528k free, 179280k buffers
Swap: 4096440k total, 0k used, 4096440k free, 597708k cached

Sunday, October 18, 2009

Jasper Reports Create a Union Query in iReport

This is a simple, step by step guide to creating a union query using Jasper's iReport.

For my sample, I am using a single table, "BookList", that contains data for a local library.

In my sample table, regular book titles have an ISBN number, but the library also has unpublished material that have an internal reference number called "LocalFileNumber".

For my Union Query, I want to select from this table the Author, Title, ISBN if available, and LocalFileNumber where ISBN is null.


Here is my sample data:

-- --------------------------------------------------------

--
-- Table structure for table `BookList`
--

CREATE TABLE IF NOT EXISTS `BookList` (
`id` int(11) NOT NULL auto_increment,
`Author` varchar(50) default NULL,
`Title` varchar(50) default NULL,
`ISBN` varchar(50) default NULL,
`LocalFileNumber` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Data for table `BookList`
--

INSERT INTO `BookList` (`id`, `Author`, `Title`, `ISBN`, `LocalFileNumber`) VALUES
(1, 'Henry James', 'The Portrait of a Lady', '0141439637', NULL),
(2, 'T.S. Eliot', 'Collected Poems', '0151189781', NULL),
(3, 'William Gaddis', 'The Recognitions', '0844667404', NULL),
(4, 'John Smith', 'History of Our Library', NULL, 'JS12345678'),
(5, 'James Merrill', 'The Changing Light at Sandover', '0307263215', NULL),
(6, 'Wendy Jones', 'Library Policy Manual', NULL, 'WJ12345678');



So, we have 4 titles with ISBN numbers, and 2 titles that use the libraries internal reference number (highlighted in bold below):


mysql> select * from BookList;
+----+----------------+--------------------------------+------------+-----------
------+
| id | Author         | Title                          | ISBN       | LocalFileNumber |
+----+----------------+--------------------------------+------------+-----------
------+
|  1 | Henry James    | The Portrait of a Lady         | 0141439637 | NULL
      |
|  2 | T.S. Eliot     | Collected Poems                | 0151189781 | NULL
      |
|  3 | William Gaddis | The Recognitions               | 0844667404 | NULL
      |
4 | John Smith     | History of Our Library         | NULL       | JS12345678
      |
|  5 | James Merrill  | The Changing Light at Sandover | 0307263215 | NULL
      |
6 | Wendy Jones    | Library Policy Manual          | NULL       | WJ12345678
      |
+----+----------------+--------------------------------+------------+-----------
------+
6 rows in set (0.00 sec)


1. Start up iReport and create your connection.

2. At the Query section, add the 'BookList' table into the viewer and select Author, Title, and IBSN:





3.  Right click on WHERE and select Add a Condition:


 

4. The WHERE clause in our first select will be ISBN IS NOT NULL



5. Now that we have our first select with our WHERE clause, right click on ROOTQUERY and select UNION

 

6. Note that the UNION node now appears in the query explorer pane (just above the ORDER BY node).

 

7. Highlight the SELECT node under Union.

 

8. Double click on the BookList table to add it into the pane. This time we will select Author, Title, and LocalFileNumber.





9. Right click on the WHERE node for the query and select Add Condition:





9. For our condition on this select, we will now enter WHERE ISBN IS NULL




10. Expand all of the relevant nodes so we can see our query:




11. Now, at the bottom of the Query Builder, select the SQL tab:




12. From here, we can edit our query so that both ISBN from the first select and LocalFileNumber from the second select are selected as "BookCode" as shown below:





SELECT
     booklist.`Author` AS booklist_Author,
     booklist.`Title` AS booklist_Title,
     booklist.`ISBN` AS BookCode
FROM
     `booklist` booklist
WHERE
     ISBN IS NOT NULL
UNION
SELECT
     booklist.`Author` AS booklist_Author,
     booklist.`Title` AS booklist_Title,
     booklist.`LocalFileNumber` AS  BookCode
FROM
     `booklist` booklist
WHERE
     ISBN IS NULL


So we now have a query that will return Author, Title, and BookCode (ISBN or LocalFileNumber when ISBN is null).

One item you may note above is that we did not need to qualify our alias BookCode with quotation marks.

13. Click OK and review your query: 








PHP MySQL Union Query

Union Query with PHP and MySQL

For deailed explanation of MySQL sample data and logic, please see my Jasper Reports Query post

-- --------------------------------------------------------

--
-- Table structure for table `BookList`
--

CREATE TABLE IF NOT EXISTS `BookList` (
`id` int(11) NOT NULL auto_increment,
`Author` varchar(50) default NULL,
`Title` varchar(50) default NULL,
`ISBN` varchar(50) default NULL,
`LocalFileNumber` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Data for table `BookList`
--

INSERT INTO `BookList` (`id`, `Author`, `Title`, `ISBN`, `LocalFileNumber`) VALUES
(1, 'Henry James', 'The Portrait of a Lady', '0141439637', NULL),
(2, 'T.S. Eliot', 'Collected Poems', '0151189781', NULL),
(3, 'William Gaddis', 'The Recognitions', '0844667404', NULL),
(4, 'John Smith', 'History of Our Library', NULL, 'JS12345678'),
(5, 'James Merrill', 'The Changing Light at Sandover', '0307263215', NULL),
(6, 'Wendy Jones', 'Library Policy Manual', NULL, 'WJ12345678');




<?php
$myconn = mysql_connect("localhost","username","password");
if (!$myconn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Books", $myconn);


$result = mysql_query
('select Author, Title, ISBN as "BookCode"
FROM BookList where ISBN is not null
UNION
select Author, Title, LocalFileNumber as "BookCode"
FROM BookList where ISBN is null')
or die(mysql_error());

while($row = mysql_fetch_array($result))
  {
  echo $row['Author'] . " " . $row['Title'] . " " .$row['BookCode'];
  echo "</br
>";
  }

mysql_close($myconn);
?>



(and with a bit formatting.........


<?php
$myconn = mysql_connect("localhost","username","password");
if (!$myconn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Books", $myconn);

$result = mysql_query('select Author, Title, ISBN as "BookCode"
FROM BookList where ISBN is not null
UNION
select Author, Title, LocalFileNumber as "BookCode"
FROM BookList where ISBN is null')
or die(mysql_error());

echo "<table border='1'>
<tr>
<th>Author</th>
<th>Title</th>
<th>BookCode</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Author'] . "</td>";
  echo "<td>" . $row['Title'] . "</td>";
  echo "<td>" . $row['BookCode'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($myconn);
?>

Saturday, October 10, 2009

Install Group Office on CentOS 5.3

Install Group Office on CentOS 5.3

Start Here: http://www.group-office.com/wiki/Installation

The server is going to be used primarily for Group Office, so I stayed with the script and unpacked the tar file to /var/www/html/groupoffice-pro-3.2.37

Overall, this was fairly straight forward, with the exception of having to upgrade PHP and a some brief scrounging for some rpms.

The installation package includes a test file in the root directory, gotest.php, that provides a useful summary of required items that your server is lacking. So if you don't have php configured, that should be the first order of business.

One of the reasons I use CentOS whenever possible is it's stability and it's conservative approach.

Unfortunately, this means that php >=5.2, one of the prerequisites for Group Office, was not available from the repo.

Not wanting to recompile PHP, it meant having to avail myself of Jason Litka's repository.

[root@server4 ~]# rpm --import http://www.jasonlitka.com/media/RPM-GPG-KEY-jlitka
[root@server4 ~]# vi /etc/yum.repos.d/utterramblings.repo
[utterramblings]

Enter the following and then :wq
[utterramblings]
name=Jason's Utter Ramblings Repo
baseurl=http://www.jasonlitka.com/media/EL$releasever/$basearch/
enabled=1
gpgcheck=1
gpgkey=http://www.jasonlitka.com/media/RPM-GPG-KEY-jlitka

[root@server4 ~]# yum update php
[root@server4 ~]# yum update mysqlat

After upgarding php, simply consult the gotest.php script to find missing prerequisites. In my case:

[root@server4 ~]# yum install php-imap php-xml php-mbstring php-devel
[root@server4 ~]# service httpd restart

Install Zend Ion Cube

[root@server4 ~]# wget http://downloads2.ioncube.com/loader_downloads/ioncube_loaders_lin_x86.tar.gz
[root@server4 ~]# tar -zxvf ioncube_loaders*
[root@server4 ~]# mv ioncube /var/www/html/ioncube
[root@server4 ~]# service httpd restart

After installing ion cube above, add the Zend extension in bold below to your php.ini file under Language Options before the Zend references:

;;;;;;;;;;;;;;;;;;;;
; Language Options ;
;;;;;;;;;;;;;;;;;;;;

; Enable the PHP scripting language engine under Apache.
engine = On

zend_extension = /var/www/html/ioncube/ioncube_loader_lin_5.2.so


Restart Apache:
[root@server4 ~]# service httpd restart

Check your php version is now updated and ion cube installed:

[root@server4 ~]# php -v
PHP 5.2.11 (cli) (built: Sep 21 2009 14:49:09)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies
with the ionCube PHP Loader v3.1.34, Copyright (c) 2002-2009, by ionCube Ltd.


Install libwbxml:

[root@server4 ~]# rpm -Uvh http://dl.atrpms.net/all/wbxml2-0.9.2-5.el4.at.i386.rpm

Install TNEF:

For whatever reason, I could not pull down tnef-1.3.3-1.2.el4.rf.i386.rpm from dag.wieers.com, even searching manually.

I grabbed it here: http://www.rpmfind.net/linux/rpm2html/search.php?query=tnef
saved it to temp and installed:

[root@server4 ~]# cd /var/tmp
[root@server4 ~]# rpm -Uvh tnef-1.3.3-1.2.el4.rf.i386.rpm

That was about it.

Once you cleared all of the gotest.php prerequisites, you can then start the installation via the home directory, which will walk you through the installation. Keep putty open as you will need to do some mkdir's as some chmod's.

Add your virtual host entry in your Apache httpd.conf file.


<VirtualHost *:80>
ServerAdmin david@mydomain.com
DocumentRoot /var/www/html/groupoffice-pro-3.2.37
ServerName mycal.mydomain.com
ErrorLog logs/mycal.mydomain.com-error_log
CustomLog logs/mycal.mydomain.com-access_log common
</VirtualHost>

Tune Apache according to requirements.

I originally tested using the Community Edition, groupoffice-com-2.18-stable-21, and the procedure is identical.

If you will be using the CE, the items you can skip are indicated in the gotest.php file.

Wednesday, September 30, 2009

Install JasperServer 3.5 on CentOS 5.3

Using the bin installer, this could not have been easier.

The JasperServer 3.5 bin installer allows you the option of installing with the bundled MySQL and Tomcat server or use your own.

I used my own and also left off the optional iReport installation as I have iReport on my XP.

The last option is to install the sample data.

Just chmod 755 the installer and install:


-bash-3.2# chmod 755 jasperserver-3.5.0-linux-installer.bin
-bash-3.2# ./jasperserver-3.5.0-linux-installer.bin
----------------------------------------------------------------------------
Welcome to the JasperServer 3.5.0 Setup Wizard.

----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.

Press [Enter] to continue :

NOTICE TO USER:

PLEASE READ THIS AGREEMENT CAREFULLY. BY DOWNLOADING,
COPYING, INSTALLING OR USING ALL OR ANY PORTION
OF THIS PROGRAM YOU ACCEPT ALL THE TERMS AND CONDITIONS
OF THIS AGREEMENT, INCLUDING WITHOUT LIMITATION THE GNU
GENERAL PUBLIC LICENSE VERSION 2. YOU AGREE THAT THIS AGREEMENT
IS LIKE ANY WRITTEN NEGOTIATED AGREEMENT SIGNED BY YOU.
THIS AGREEMENT IS ENFORCEABLE AGAINST YOU AND ANY LEGAL ENTITY
THAT OBTAINED THIS PROGRAM AND ON WHOSE BEHALF IT IS USED: FOR
EXAMPLE, IF APPLICABLE, YOUR EMPLOYER. IF YOU DO NOT AGREE TO
THE TERMS OF THIS AGREEMENT, DO NOT USE THIS PROGRAM. VISIT
http://www.jaspersoft.com AND http://www.gnu.org/licenses/ FOR
TERMS AND CONDITIONS.

IF YOU HAVE PURCHASED A COMMERCIAL LICENSE AGREEMENT FROM
JASPERSOFT, YOU MAY HAVE ANOTHER WRITTEN AGREEMENT DIRECTLY WITH
JASPERSOFT THAT SUPERSEDES ALL OR PORTIONS OF THIS AGREEMENT.

JASPERSOFT PERMITS YOU TO DOWNLOAD, COPY, INSTALL, USE, OR
OTHERWISE BENEFIT FROM THE FUNCTIONALITY OR INTELLECTUAL PROPERTY
OF THIS PROGRAM ONLY IN ACCORDANCE WITH THE TERMS OF THIS
Press [Enter] to continue :
AGREEMENT. USE OF SOME THIRD PARTY MATERIALS AND SERVICES
INCLUDED IN OR ACCESSED THROUGH THIS PROGRAM MAY BE SUBJECT TO
OTHER TERMS AND CONDITIONS TYPICALLY FOUND IN A SEPARATE LICENSE
AGREEMENT, TERMS OF USE OR "READ ME" FILE LOCATED WITHIN OR NEAR
SUCH MATERIALS AND SERVICES.


Copyright (C) 2005 - 2009 Jaspersoft Corporation. All rights
reserved.

Unless you have purchased a commercial license agreement from
Jaspersoft, the following license terms apply:

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
version 2 as published by the Free Software Foundation.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
General Public License version 2 for more details.

You can view a complete copy of the GNU General Public License at
Press [Enter] to continue :
http://www.gnu.org/licenses/. You also should receive a copy of
the GNU General Public License version 2 along with the program;
if not, write to the Free Software Foundation, Inc., 51 Franklin
Street, Fifth Floor, Boston, MA 02110-1301 USA.

Contact Jaspersoft Corporation
539 Bryant Street, Suite 100
San Francisco, CA 94107
Web: www.jaspersoft.com
General Inquiries: info@jaspersoft.com
Sales Inquiries: sales@jaspersoft.com



Press [Enter] to continue :

Do you accept this license? [y/n]: y

----------------------------------------------------------------------------
Installation Directory

Please specify the directory where JasperServer 3.5.0 will be installed.

Installation directory: [/opt/jasperserver-3.5.0]:

----------------------------------------------------------------------------
Please select the Tomcat configuration you wish to use

Tomcat installation:

[1] I wish to use the bundled Tomcat
[2] I wish to use an existing Tomcat
Please choose an option [1] : 2

----------------------------------------------------------------------------
Database Installation

Please select which database configuration you wish to use:

[1] I wish to use the bundled MySQL database
[2] I wish to use an existing MySQL database
Please choose an option [1] : 2

Setup will try to install new databases and will overwrite any existing duplicate. Do you wish you continue? [Y/n]: Y

----------------------------------------------------------------------------
Tomcat Directory

Please specify the directory where Tomcat is located.

Tomcat directory: [/opt/jasperserver-3.5.0/apache-tomcat]: /usr/share/apache-tomcat-6.0.18

----------------------------------------------------------------------------
Install Sample Data

Would you like to install sample databases and sample reports?

[Y/n]: Y


----------------------------------------------------------------------------
Install iReport

The iReport application is a graphical designer for JasperReports.
iReport connects to JasperServer to upload and download reports.

Would you like to install iReport?

[Y/n]: n


----------------------------------------------------------------------------
MySQL Information

Please enter your MySQL database information.

MySQL Binary Directory: [/opt/jasperserver-3.5.0/mysql/bin]: /usr/bin

Port: [3306]:

IP/Hostname Value Used: 127.0.0.1

----------------------------------------------------------------------------
MySQL Credentials

Please enter your MySQL database credentials.

Please enter your database root username:

Username: [root]: root

Please enter your database root user password:

Password: :
Re-enter password: :
The following JasperServer database user will be created: jasperdb/password



----------------------------------------------------------------------------
Setup is now ready to begin installing JasperServer 3.5.0 on your computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------
Please wait while Setup installs JasperServer 3.5.0 on your computer.

Installing
0% ______________ 50% ______________ 100%
########################################Info: Please shut down your application server and press OK to continue.
Press [Enter] to continue :
#

----------------------------------------------------------------------------
Setup has finished installing JasperServer 3.5.0 on your computer.

Opt-in for JasperServer Heartbeat.
Please help us build better products by Opting-in to the Heartbeat.

By allowing the Heartbeat to collect data, you confirm consent,
where relevant, from all individual users in your organization.

Please refer to: www.jaspersoft.com/heartbeat

[Y/n]:

-bash-3.2#




One initial issue I ran into was SMTP authentication errors when using the scheduler.

Thankfully, this post:

http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=60508

got me sorted out.

Sunday, September 20, 2009

Import XML Data into SQL Server

How to automate comparing, updating, and inserting data from an XML feed against an existing SQL Server table.

I have an existing table in SQl Server called AcmeProductTable.

It contains the following columns:

AcmeProductID
AcmeProductName
AcmeProductPrice
AcmeProductStockQuantity

The Acme Product Company has a url that provides me with an XML file of the latest data for their products. The feed is located at:

http://www.acme.com/acmeproductfeed.


The task is to:

1. Get the daily XML product feed from their url
2. Save the XML file with today's date
3. Compare today's XML file with my existing data in SQL Server
4. Update any changes to price and stock quantity for existing products
5. Insert any new products
6. Automate the entire process so I don't need to do anything

Well, for someone who knows SQL Server and XML, it's a simple task. But, like me, if you are Googling SQL Server and XML well....

The place to start is here: http://support.microsoft.com/kb/316005

This gives an example of creating an XML map, which you will need to import your XML data file.


My XML map looks something like this:


<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="product_id" dt:type="string" />
<ElementType name="product_name" dt:type="string" />
<ElementType name="product_price" dt:type="string" />
<ElementType name="product_stock_quanitity" dt:type="string" />


<ElementType name="acmeproductfeed" sql:is-constant="1">
<element type="acmefeed" />
</ElementType>
<ElementType name="acmefeed" sql:relation="acmefeed">
<element type="product_id" sql:field="product_id" />
<element type="product_name" sql:field="product_name" />
<element type="product_price" sql:field="product_price" />
<element type="product_stock_quanitity" sql:field="product_stock_quanitity" />
</ElementType>

</Schema>




In order to automate comparing the XML feed from the url above to my existing data in SQL Server,the AcmeProductTable, I will need the following four items:


1. An XML Map of the Acme xml feed file (see MSDN link and my example above).
2. A vbs script that will get the latest xml file from the url, date the file with today's date, save the file, and then locate the latest saved file (today's) and use the file to create a table in SQL Server.
3. A stored procedure in SQL Server that will compare the table we created in in step 2 above with my AcmeProductTable and perform any required updating and inserting.
4. A simple batch file to automate all of above.




Firstly, I am going to save my above XML map "AcmeMap.xml" into a folder on my desktop:

C:\Documents and Settings\Administrator\Desktop\Acme\AcmeMap.xml

I am going to use this same folder for all of the other files below as well.

For my VBS scipt, I will use the following, which I have saved as "acmescript.vbs":

C:\Documents and Settings\Administrator\Desktop\Acme\acmescript.vbs

Blogging, like SQL Server, is not my forte, so I have used italic where portions should be quoted out of the script:

' VBScript source code to get the file from the url then stamp the file with todays date and save it to my Acme file folder.
The important bit here is inserting the +sDate+ into the name of the saved file


sDay = Day(Now())
If Len(sDay) = 1 Then sDay = "0" & Day(Now())
sMonth = Month(Now())
If Len(sMonth) = 1 Then sMonth = "0" & Month(Now())
sYear = Year(Now())
sDate = sYear & sMonth & sDay
strFileURL = "http://www.acme.com/acmeproductfeed"
strHDLocation = "C:\Documents and Settings\Administrator\Desktop\Acme\acmefeed"+"-"+sDate+".xml"


Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")

objXMLHTTP.open "GET", strFileURL, false
objXMLHTTP.send()

If objXMLHTTP.Status = 200 Then
Set objADOStream = CreateObject("ADODB.Stream")
objADOStream.Open
objADOStream.Type = 1 'adTypeBinary

objADOStream.Write objXMLHTTP.ResponseBody
objADOStream.Position = 0 'Set the stream position to the start

Set objFSO = Createobject("Scripting.FileSystemObject")
If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
Set objFSO = Nothing

objADOStream.SaveToFile strHDLocation
objADOStream.Close
Set objADOStream = Nothing
End if

Set objXMLHTTP = Nothing



' Now create a staging stable - acmefeed - in my database I will use this acmefeed table to compare to my existing production table AcmeProductTable. Note - make the name of the staging table the same as the element below the ROOT element of the XML file. In my case, acmefeed. There is probably some way around this, but I could not find it.

Const DB_CONNECT_STRING = "provider=SQLOLEDB.1;data source=IP;database=YourDB;uid=user;pwd=password"

Set myConn = CreateObject("ADODB.Connection")

set myCommand = CreateObject("ADODB.Command" )

myConn.Open DB_CONNECT_STRING

Set myCommand.ActiveConnection = myConn

myCommand.CommandText = "CREATE TABLE acmefeed (acmefeed nvarchar(255),product_id nvarchar(255),product_name nvarchar(255),product_price nvarchar(255),product_stock_quantity nvarchar(255))"

myCommand.Execute

myConn.Close



' Now - find today's xml file that I saved above and use my xml map to insert it into the staging table, acmefeed, in my database. Note again that I using +sDate+ in the file name to get TODAY's file

sDay = Day(Now())
If Len(sDay) = 1 Then sDay = "0" & Day(Now())
sMonth = Month(Now())
If Len(sMonth) = 1 Then sMonth = "0" & Month(Now())
sYear = Year(Now())
sDate = sYear & sMonth & sDay
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=IP;database=YourDB;uid=user;pwd=password"
objBL.ErrorLogFile = "C:\Documents and Settings\Administrator\Desktop\Acme\error.log"
objBL.Execute "C:\Documents and Settings\Administrator\Desktop\Acme\AcmeMap.xml", "C:\Documents and Settings\Administrator\Desktop\Acme\acmefeed"+"-"+sDate+".xml"
Set objBL = Nothing


' Now with the latest XML feed in my database in the form of a table, I can execute a stored procedure to update Price and Stock Quantity and then insert new new data into the appropriate table and then drop my acmefeed table

Const DB_CONNECT_STRINGSP = "provider=SQLOLEDB.1;data source=IP;database=YourDB;uid=user;pwd=password"

Set mySPConn = CreateObject("ADODB.Connection")

set mySPCommand = CreateObject("ADODB.Command" )

mySPConn.Open DB_CONNECT_STRINGSP

Set mySPCommand.ActiveConnection = mySPConn

mySPCommand.CommandText = "EXEC dbo.update_acmefeed"

mySPCommand.Execute

mySPConn.Close






Below is the stored procedure, "update_acmefeed", that I am executing in the vb script above. This will compare the contents of my staging table, "acmefeed", against my actual production table AcmeProductTable. It will update any existing products where the price and/or stock quantity has changed, as well as insert any new products. I then drop the staging table (acmefeed).


CREATE PROCEDURE update_acmefeed
AS
BEGIN
-- First Update any changes to price and stock quantity for existing items
-- Then insert any new items into the AcmeProductTable
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @id varchar(255),@price numeric(9,2),@quantity varchar(255)
declare curFeed cursor for select product_id, product_price, product_quantity, from acmefeed
where exists( select AcmeProductID from AcmeProductTable where product_id=AcmeProductID)

open curFeed

Fetch next from curFeed into @id,@price,@quantity

while (@@fetch_Status = 0)
BEGIN

update AcmeProductTable set product_price=@price,product_stock_quantity=@quantity
where AcmeProductID=@id


Fetch next from curFeed into @id,@price,@quantity
end

CLOSE curFeed DEALLOCATE curFeed



insert into AcmeProductTable
select product_id, product_name,product_price,product_stock_quantity
from acmefeed A where not exists(select AcmeProductID from AcmeProductTable where AcmeProductID=a.product_id)

drop table acmefeed

END




Finally, I am going to schedule the above VBS to run daily by creating a simple batch file with the following contents:


wscript.exe C:\Documents and Settings\Administrator\Desktop\Acme\acmescript.vbs


There is obviously redundancy in the date function as well as the ADO connections, but this is the original scripting I used to get this to work.

I am sure there are more elegant ways of doing this, but I hope this helps someone...


Sources:

Download File:
http://blog.netnerds.net/2007/01/vbscript-download-and-save-a-binary-file/

Add File Date:
http://www.visualbasicscript.com/m29679.aspx

Friday, August 14, 2009

Tomcat Hangs After Restart

Tomcat 6.0.18
jdk1.6.0_12
apache-ant-1.7.1
Cocoon 2.1.11

Well, I have been working with Eclipse Galileo with BIRT the past few weeks and I have to say it's one of the best finds of recent memory.

It's great to be able to develop reports on my XP laptop with Galileo's friendly IDE and then easily deploy my reports to the BIRT Report Viewer on my Centos server.

The only issue (most likely my Tomcat restart script or some other fault on my end) is that after adding a new report and restarting Tomcat, I find that the Tomcat Manager, along with the BIRT report viewer hangs.

A check showed that after restart Tomcat appears to be up:

[root@server1 ~]# ps aux | grep -i tomcat
root 24216 0.2 4.2 1186320 172692 ? Sl 14:23 0:20 /usr/java/jdk1.6.0_12/bin/java -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.util.logging.config.file=/usr/share/apache-tomcat-6.0.18/conf/logging.properties -Djava.endorsed.dirs=/usr/share/apache-tomcat-6.0.18/endorsed -classpath :/usr/share/apache-tomcat-6.0.18/bin/bootstrap.jar -Dcatalina.base=/usr/share/apache-tomcat-6.0.18 -Dcatalina.home=/usr/share/apache-tomcat-6.0.18 -Djava.io.tmpdir=/usr/share/apache-tomcat-6.0.18/temp org.apache.catalina.startup.Bootstrap start
root 24745 0.0 0.0 3900 652 pts/0 R+ 16:41 0:00 grep -i tomcat
root 27432 5.3 6.8 1209172 277096 ? Sl Jul29 56:54 /usr/java/jdk1.6.0_12/bin/java -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.util.logging.config.file=/usr/share/apache-tomcat-6.0.18/conf/logging.properties -Djava.endorsed.dirs=/usr/share/apache-tomcat-6.0.18/endorsed -classpath :/usr/share/apache-tomcat-6.0.18/bin/bootstrap.jar -Dcatalina.base=/usr/share/apache-tomcat-6.0.18 -Dcatalina.home=/usr/share/apache-tomcat-6.0.18 -Djava.io.tmpdir=/usr/share/apache-tomcat-6.0.18/temp org.apache.catalina.startup.Bootstrap start

However, after sstopping Tomcat, when I issue pas -a, there are still java processes running.

[root@server1 ~]# service tomcat stop
Shutting down tomcat: Using CATALINA_BASE: /usr/share/apache-tomcat-6.0.18
Using CATALINA_HOME: /usr/share/apache-tomcat-6.0.18
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.18/temp
Using JRE_HOME: /usr/java/jdk1.6.0_12
done.
[root@server1 ~]# ps -a
PID TTY TIME CMD
24881 pts/0 00:00:20 java
24945 pts/0 00:00:00 ps

So, issue killall -9 java:

[root@server1 ~]# killall -9 java


Look at ps again:

[root@server1 ~]# ps -a
PID TTY TIME CMD
24957 pts/0 00:00:00 ps

Now start up Tomcat:

[root@server1 ~]# service tomcat start
Starting tomcat: Using CATALINA_BASE: /usr/share/apache-tomcat-6.0.18
Using CATALINA_HOME: /usr/share/apache-tomcat-6.0.18
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.18/temp
Using JRE_HOME: /usr/java/jdk1.6.0_12
done.
[root@server1 ~]#

And now my Manager, my Report viewer, and my newly added report are all accessible.

I have been using this same Tomcat script for some time now and have never had any issues restarting after changes. So I'm not sure if it is my script (most likely), or the BIRT viewer, or some other oversight on my part.

In any event, I won't be giving up my Galileo or Report Viewer any time soon.

Tuesday, February 17, 2009

SSL (HTTPS) for Oracle Apex on Oracle 10g Express (XE)

Adding HTTPS to Oracle Application Express on Oracle 10g Express

CentOS 5.x
Oracle 10g Express
APEX 3.x upgrade
Apache 2.x
mod_ssl
open_ssl

If you are running APEX on Oracle 10g Express, you do not have access to the Wallet Manager per the OTN agreement.

You can still add HTTPS functionality, however, via Apache using mod_ssl.

While pervasive, mod_ssl is a third party module not supplied by Apache. If not already installed, yum install mod_ssl.

Let's start with a non-HTTPS example.

Within our Apache httpd.conf file, we create a reverse proxy to our Oracle APEX application.

There are any number of reasons for doing this:

1. We might do this for domain mapping (i.e. so www.yourdomain.com resolves to a specified application ID and landing page www.yourdomain.com/apex/f?p=101:1)

2. To allow access when behind a firewall where :8080 would block access

3. To increase security by running our Apache installation on a public facing server and back-ending to our Oracle server behind a firewall through which only our Apache server has access.

To create the reverse proxy we can use the following Virtual Host configuration under /etc/httpd/conf/httpd.conf

For simplicity, in the example below, Apache is installed on the same server as Oracle. If your installation is otherwise, simply substitute your server hostname or IP for localhost below.

<VirtualHost *:80>
ServerAdmin admin@yourdomain.com
DocumentRoot /home/yourdomain.com/htdocs
ServerName yourdomain.com
ServerAlias www.yourdomain.com
ErrorLog logs/yourdomain.com-error_log
CustomLog logs/yourdomain.com-access_log common
RewriteEngine On

RewriteRule ^/$ apex/f?p=101:1 [R=301]

ProxyRequests Off
<Proxy *>
Order allow,deny
Allow from all
</Proxy>
ProxyPass / http://localhost:8080/
ProxyPassReverse / http://localhost:8080/
</VirtualHost>


Now, if we want to add HTTPS functionality, we begin by creating our SSL Key, CSR, and Certificate as we would for any standard SSL certificate.


If you have never done this before, RapidSSL has a very simple and useful step-by-step guides to doing so on Apache with OpenSSL and ModSSL:
 
1. Create Key and CSR: http://www.rapidssl.com/ssl-certificate-support/generate-csr/apache_mod_ssl.htm
 
2. Install Certificate: http://www.rapidssl.com/ssl-certificate-support/install-ssl-certificate/apache_mod_ssl.htm


If you want to take a practice run, or don't care about the certificate throwing a browser error, you can simply create a self-signed certificate.


I'm going to store my key and crt (certificate) files in the default locations.

With our certificate installed, lets return to the VHOST we created Now, under /etc/httpd/conf/httpd.conf change the Virtual Host we created above to below.


<VirtualHost *:80>
ServerAdmin admin@yourdomain.com
DocumentRoot /home/yourdomain.com/htdocs
ServerName yourdomain.com
ServerAlias www.yourdomain.com
ErrorLog logs/yourdomain.com-error_log
CustomLog logs/yourdomain.com-access_log common

RewriteEngine on

RewriteRule ^/(.*)$ https://%{HTTP_HOST}/$1 [L,R]

</VirtualHost>


The above rewrite RewriteRule ^/(.*)$ https://%{HTTP_HOST}/$1 [L,R] forces all requests made to :80 HTTP to :443 HTTPS (or whatever port you have configured to handle HTTPS requests).


Now that we have set up VHOST in httpd.conf and created our SSL key and certificate, we know need to create an additional VHOST under  /etc/httpd/conf.d/ssl.conf. The ssl.conf was created when we installed mod_ssl.

Under /etc/httpd/conf.d/ssl.conf configure the Virtual Host as below. I will be creating more than one SSL domain, so I am using IP 123.456.78.90 for this domain and will specify this in the Virtual Host below.


The SSL Virtual Host in our below in our /etc/httpd/conf.d/ssl.conf  file is truncated and I have highlighted the main areas where we have done our configuration in bold. I have also added a few comments in blue.

<VirtualHost 123.456.78.90:443>
# General setup for the virtual host, inherited from global configuration
DocumentRoot "/home/yourdomain.com/htdocs"
ServerName www.yourdomain.com:443


# Note that we have moved the reverse proxy from our httpd.conf file to our ssl.conf file
RewriteEngine on
RewriteRule ^/$ apex/f?p=101:1 [R=301]

ProxyRequests Off
<Proxy *>
Order deny,allow
Allow from all
</Proxy>
ProxyPass / http://localhost:8080/
ProxyPassReverse / http://localhost:8080/




# Use separate log files for the SSL virtual host; note that LogLevel
# is not inherited from httpd.conf.
ErrorLog logs/ssl_error_log
TransferLog logs/ssl_access_log
LogLevel warn

# SSL Engine Switch:
# Enable/Disable SSL for this virtual host.
SSLEngine on

# SSL Protocol support:
# List the enable protocol levels with which clients will be able to
# connect. Disable SSLv2 access by default:
SSLProtocol all -SSLv2

# SSL Cipher Suite:
# List the ciphers that the client is permitted to negotiate.
# See the mod_ssl documentation for a complete list.
SSLCipherSuite ALL:!ADH:!EXPORT:!SSLv2:RC4+RSA:+HIGH:+MEDIUM:+LOW

# Server Certificate:
# Point SSLCertificateFile at a PEM encoded certificate. If
# the certificate is encrypted, then you will be prompted for a
# pass phrase. Note that a kill -HUP will prompt again. A new
# certificate can be generated using the genkey(1) command.
# The path below gives the location where we stored the SSL certificate we created
SSLCertificateFile /etc/httpd/conf/ssl.cert/www.youdomain.com.crt

# Server Private Key:
# If the key is not combined with the certificate, use this
# directive to point at the key file. Keep in mind that if
# you've both a RSA and a DSA private key you can configure
# both in parallel (to also allow the use of DSA ciphers, etc.)
# The path below gives the location of the key we created.
SSLCertificateKeyFile /etc/httpd/conf/ssl.key/www.youdomain.com.key


SetEnvIf User-Agent ".*MSIE.*" \
nokeepalive ssl-unclean-shutdown \
downgrade-1.0 force-response-1.0

CustomLog logs/ssl_request_log \
"%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"

</VirtualHost>


For Oracle APEX on Windows - I have never used a local Apache installation on W2K3. The simplest method for doing the above would be to use Linux server as front end with the proxy back-ended to the APEX installation on the Windows server. In Windows Firewall, you can then open access to your Linux box.

Footnote - for those new to Apache, the above does not touch on tuning. ReverseProxy and rewrites require tuning in order to perform well. Be sure to set KeepAlive to On, use mod_deflate (or gzip for 1.3), as well as set reasonable prefork MPM and worker MPM values. Apache tuning is beyond the scope of this post, but start with checking the docs and Googling these items to get you going.

Oracle APEX Hosting

Tuesday, February 3, 2009

Service Temporarily Unavailable 503 Code for APEX Apache Proxy

On running a standard proxypass for an apex application on new server, kept getting:
Service Temporarily Unavailable 503


vhost.....


<VirtualHost:*80>
ServerName domain.com
ServerAlias www.domain.com
DocumentRoot /var/www/html/domain.com/htdocs
ServerAdmin admin@domain.com

RewriteEngine On

RewriteRule ^/$ apex/f?p=113:1 [R=301]

ProxyRequests Off
<proxy>
Order deny,allow
Allow from all
</proxy>
ProxyPass / http://server:8080/
ProxyPassReverse / http://server:8080/
</VirtualHost>

Checking the error logs: /var/log/httpd/error_log I find:

[Sun Feb 01 21:01:42 2009] [error] (13)Permission denied: proxy: HTTP: attempt to connect to localhost:8080 (1.2.3.4) failed

In this case it was SELinux, giving me the option of either disbaling:

# vi /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted - Only targeted network daemons are protected.
# strict - Full SELinux protection.
SELINUXTYPE=targeted

# SETLOCALDEFS= Check local definition changes
SETLOCALDEFS=0


Or, a more targeted:

/usr/sbin/setsebool httpd_can_network_connect 1


While in this case "(13)Permission denied:" was self-explanatory, the most common proxy failures I have come across were, in fact, due to my database not being configured on os properly.

Another area to check when your proxy stops proxying is the hostname configuration. Check /etc/hosts, /etc/hostname and make sure your hostname is set properly.

If it is, check listener.ora and tnsnames.ora under /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin and make sure they match what is in your host/hostname files.