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