Transfer Sybase SQL Anywhere IMAGE Binary data to PostgreSQL BYTEA, Part1 (using CSV)

As an update to my previous post, easy way to import images to PG, where I wanted to migrate a sybase image column to a postgresql bytea column. The method I explained there requires the image file be present in order to import it to the postgres database. But in cases where the images or documents are only available in an IMAGE column of a sybase database, I have used two methods to transfer them to a PG database.

1. From a CSV created from the sybase table
2. Using Pentaho Kettle Data Integration.

I am explaining the first method in this post, Importing the image data using CSV.

Step 1 (Export the sybase table to CSV)


First of all, we need to export the required data of the sybase table to a csv. Note that my table in sybase looks like below,

images
======
image_ID integer,
image_name varchar(50),
image_data image

Before exporting the data in the table, the images table, the image_data column should be converted to ASCII string, encoded with base64. This can be done with the inbuilt function BASE64_ENCODE() which will return a base64 string. Check the following query to get the required data set that can later be exported to csv,

SELECT image_ID, image_name, BASE64_ENCODE(image_data) FROM images.

Note that in the query, the image_data field is encoded with 64 with the above function.

Step 2 (Import csv to PostgreSQL)

Once the csv is ready, import it to the postgresql database. Note that the image_data field should be imported to a string type field of the postgresql database table. Check the table I have in PG below,

images
======
image_ID int,
image_name character varying(50),
image_text text
image_data bytea

The copy command that imports the csv into the PG table may look like below,

COPY images FROM 'C:\\PG\\images.csv' WITH QUOTE '''' CSV

Note that if the text data in your csv is quoted, then the relevant quote should be provided in the COPY command to prevent the same quote be copied into the table alone with the data. For example, if you didn't provide the QUOTE in the COPY command, you will have data like, 'company_logo', in the table. This should be avoided, specially for the base64 string.

Step 3 (Update bytea column with base64 string in the text column)

Once we have data in the images table, images saved in the text column encoded with base64, we need to update the bytea column of the same table by decoding data in the text column into the bytea format. Check the following script I have used in this example,

UPDATE images SET image_data = DECODE(image_text, 'base64');

This script decodes data in the image_text column and updates the image_data, which is a bytea column, with the correct binary data of the particular image. You can clear the image_text column later to save addition space.

It's easy as that and check out the next post, which is the PART 2 of this post, to see how we can directly transfer image data from sybase to postgresql using Pentaho Data Integration ETL package.

Easy way to import image to bytea field in PostgreSQL/ Postgres

During the migration of our Sybase databases to PostgreSql, I came across a small table with a few records that was used to store images used by the reports. Specially the company logos ..etc. " image " was the data type used in the sybase database and I had to store the same data in postgres data base in a " bytea " field. I tried exporting the sybase table into a csv and import the same csv to postgres which didn't give me successful results. So I had to import the actual image files(which I already had with me) into postgres and I wanted to do it without  doing any java coding. So here's the way I managed to do it,

Step 1 (convert the image to base64 string)

Using an online converter such as,  http://bit.ly/Ee6Ju, convert the image to base64 string.

Step 2 (Insert the baste64 string after decoding it into bytea)

Copy the string from the site and paste it in the PgAdmin query tool within the insert query like below,

INSERT INTO images (image_name, image_data)
VALUES( 'image_one', decode('', 'base64') );

And thats it, your base64 is converted into bytea and inserted in the bytea field of the image table.

Export JasperRerpots to Excel, Number Formats and Patterns Mapping for JasperServer and JaperReports

After a complaint I got from one of the client on some numerical formating are not applied when the reports are exported to excel, I had to look for a solution for this.
For Example,

When you apply a pattern such as "#,##0.0 %" in iReports and deploy the report on JasperServer and then export the report to excel from JS you would expect the values to be something like, "12.0%". But instead, what you find on the excel is "0.12". The reason for this is because, Java patterns in iReports should be explicitly mapped to Excel patterns or Masks in order to have them exactly the way they are on the JRXML.

How to do this in two simple Steps,

Step1,
You need to add the mappings in the applicatonContext.xml file which can be found in the following folder,

"tomcat_folder"\webapps\jasperserver\WEB-INF\

Search for the following,

<util:map id="formatPatternsMap">
<!-- entry key="¤ #,##0.00" value="$ #,##0.00"/-->
</util:map>

Add a mapping as below,

<util:map id="formatPatternsMap">
<!-- entry key="¤ #,##0.00" value="$ #,##0.00"/-->
<entry key="#,##0.0 %" value="0.00%"/>
</util:map>

Key should be the Java pattern, and the value should be the pertaining excel pattern/mask

Step2

Do Not forget to disable "detectCellType" property. To do this search for the following bean,

"xlsExportParameters"

and change the following property to false,

<property name="detectCellType" value="false"/>

Restart Tomcat for changes to take effect. After that you should be able to see your desired formatting on excel too.

Cheers!

Sybase SQL Anywhere Database Tracing and Profiling

While looking for a way to trace the incoming statements to one of our Sybase SQL Anywhere databases, I came across this really good post which helped me a lot. Sharing it for your reference.

To the Post

Integrating SharePoint and Silverlight 3

I'm working on developing Silverlight 3 web parts for SharePoint and found this article really interesting,

Link to Article

Cheers!

Install or Copy Assembly / dll into GAC in Vista

While doing some SharePoint stuff, I needed to make SharePoint find one of my DLLs I have used in my SharePoint web part. I was going to install the dll in my Global Assembly Cache of my Vista pc. I went to the %WINDOWS%\assembly folder and simply dragged the dll there, but it gave me an error saying "Access Denied". Then I did what I always do when I get this sort of authentication errors, I closed the explorer, then re-opened it as Administrator. Still no use. I tried to find the gacutil.exe but I couldn't find that either in my .NET Framework folders.

I had to do a bit of googling and found this way in one of the forums. You need to use the "Microsoft .NET Framework 2.0 Configuration" utility which lets you Install / Uninstall assemblies from the GAC easily. You can find this utility in the following location
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\mscorcfg.msc"

When you open the utility, there is a node called My Computer in the left hand pane. Expand it and click on the sub node "Assembly Cache". This will give you two options in the right hand pane, "View List of Assemblies...." and "Add an Assembly to ...". You can use the second option to add the assembly to GAC.

Hope this helps,
Cheers!

Import, Export, Transfer Multiple Tables with Pentaho Kettle

Working with Pentaho for the latest requirement we had on migrating sybase database to PostgreSQL, I had to find a way to Import/ Export all the tables from Sybase to Postgres and this had to be done quickly and with minimum effort. If it were SQL Server, I could go with the simple Import/ Export wizard it would have been a piece of cake. But Pentaho also has a similar approach and here's how to do it,

Step1: Create a Job in kettle.

You can do this simply going to file --? new --> Job

Step2: Create Data Connections

You need to create the source and destination database connections. You can do this in the view tab, right clicking the Data Connection and selecting New Data connection.

Step3: Open the wizard to Create the Job

Once the source and destination connections are in place, it's time to create the job that will actually copy the data. Go to Tools --> Wizards --> Copy Tables. In the first view select the source and the destination connections and press next. Then select the tables from the source connection that should be copied to the destination. In the third step of the wizard, provide details where the job and the relevant transformations should be saved. Press finish and it will create a new job with all the Create Scripts and Transformations.

Things to consider,

1. The wizard would not create Create Scripts for already existing tables in the Destination Database.
2. Based on what destination database product used, SQL Server, PostgreSQL, there might be things missing from the source database. i.e, Default Values, Identity Columns created as ordinary Numeric columns ..etc. In that case, it had better if the destination tables can be manually created.

There might be more considerations that should be taken care of, but these are what I came across.

Hope this helps!

Configure email in JasperServer with SMTP Authentication

This is an issue I came across while configuring JasperServer for emailing reports. Our mail server users SMTP Authentication. But, in the general email configuration of JasperServer (js.quartz.properties file) doesn't provide a way to put this property. But this property can be set in a different location.

In this case you need to modify a file called "applicationContext-report-scheduling.xml". This file can be found in TOMCAT Installation Folder\webapps\jasperserver\WEB-INF folder. You need to find the following tag <bean id="reportSchedulerMailSender"....

and put the following property inside it,

<property name="javaMailProperties">
<props>
<prop key="mail.smtp.auth">true</prop>
</props>
</property>

Restart tomcat and things should work fine. Refer to the following forum thread where I got the answer for my post.

Forum Thread

Transfer / Load data to PostgreSQL using SQL Server , SSIS

SSIS can be used to load, insert data to Postgres databases, but, you need the OLE DB driver for that. There is a OLE DB driver provided by Postgres, but that didn't work for me. May be I need to find more why it didn't. But in the meantime, there is this Native OLE DB Driver for Postgres which works really fine. But it is not free. You can download a demo version of the driver from here,

PostgreSQL Native OLEDB Provider (PGNP)

Once you install the driver, you can use it in SSIS, SQL Server ....etc as an ordinary OLEDB provider to directly load data to Postgres tables.

Cheers

Extracting data from Sybase SQL Anywhere using SSIS through ODBC

Recently I had to evaluate a few products for a data migration from Sybase SQL Anywhere 10 database to PostgreSQL database. SSIS was one of the tools. This post explains what I did to extract data from the Sybase database.

Step1: Create DSN

The first thing I did was to install SQL Anywhere on my computer so that it installs all the ODBC and OLE DB drivers. In my case I used the ODBC driver for SQL Anywhere 10. I created a system DSN using the odbc driver. I used the following values for the connection properties,

In ODBC Configuration for SQL Anywhere 10,
Under ODBC Tab,
I have given a name for my Connection in "Data Source Name" field.

Under Login Tab,
I have selected the "Supply User ID and Password" option and entered the credentials there.

Under the Database Tab,
Server name: The Name of the Sybase Server you are connecting to
Database name: The Name of the database.

And finally under Network Tab,
I have selected TCP/IP checkbox and entered the following details in the textbox next to it,
HOST=IP Address of the server;PORT=port number sybase server listens to(default 2638)

Go back to the ODBC tab and click on the "Test Connection" button which should give a test successful message box if everything went well.

Step2: Create Connection in SSIS

In SSIS, right click on the connection managers secsion and select ADO.NET connection. On the Connection Manager window, you need to select "odbc data provider" for the Provider. Then select the System DSN you created in the Use User or System data source name combo box. Test the connection to ensure that the connection is working.

Step3: Configure Data Reader Source

Inside a Data Flow Component, drag and drop a Data Reader Source and open its properties window.

Under the Connection Managers Tab,
Select the ADO.NET connection manager we just created from the Connection Manager combo box.

Under the Component Properties Tab,
Under Custom Properties, type in the Select Query in the text box next to "SQL Command" field.

In the Columns Mapping Tab,
Check whether the columns selected in the Select Query are correctly mapped with the out put columns. Generally these two should be identical. Press ok to close the window.

Ok now you are ready to extract data from a SQL Anywhere table. You can connect the dataflow with other transformations/Destinations.

Cheers!

Rebuild and Reorganize Indexes in SQL Server

With the time, when more and more data are inserted into a table, the indexes of it tend to fragment. This might critically effect query performance if not monitored and taken care of. Rebuilding the indexes time to time will prevent things like this happening. Here's how to rebuild indexes in SQL Server 2005 using TSQL,

Rebuilding all the indexes in a table

ALTER INDEX ALL ON dbo.Customers REBUILD

Rebuilding a specific index

Alternatively you can re-organize indexes instead of rebuilding them when the index is fragmented, but not very much. Re-organizing an index would not be as costly as Rebuilding. Also it would not need the table to be locked during the process as rebuilding would. Here's the TSQL for reorganizing an INDEX.

ALTER INDEX ALL ON dbo.Customers REORGANIZE

SharePoint and JasperServer Integration

It is true that JasperServer can be a real pain when it comes to maintaining all the reporting schedules. Specially when a particular schedule fails and when you want to re-run it. Yes, there isn't a way to re-run a schedule. You either have to edit the existing schedule so that I will run again or create a Run Now schedule with all the details copied from the old schedule.
But luckily JasperServer comes with its web services. Specially the "Repository" web service to administrate the resources in the JapserServer repository and "ReportScheduler" web service to work with all the scheduled jobs in JasperServer. the wsdls for these web services can be found in the following locations respectively,

http:///jasperserver/services/repository?wsdl
http://87.253.130.134:8083/jasperserver/services/ReportScheduler?wsdl

I created a custom web part for sharepoint that has mainly two interfaces. The first to list all the schedules that is currently on JasperServer and the second, once you click on a particular schedule it will display details of the schedule also a button to re-run that job, I recall, re-run it with one click.

This is simple. ReportScheduler web service has several methods, together which will achieve this.
The main method that is used in the webserivce to create a scheduled job is "scheduleJob()" This requires an object of type "Job" as its input parameter. Job object contains all the detail of a JasperServer scheduled job such as, reportUnitURI, baseOutputName, Email....etc.

So how to re-run an existing schedule?

First you need to get the job id of the scheduled job you need to re-run. and use the GetJob() method, which requires jobid as its input param. This method will return an Job type object which represent the scheduled job of the given jobid. you can get a copy of this Job type object but with a bit of change. There are two types of calendar triggers you can create. Calendar Trigger and Simple Trigger. you can create an new object of type Simple Trigger and make the Star Date/Time and End Date/Time null. Then you need to set this simple trigger as the copied Job type object's simple trigger. Now you have a Job object with a simple trigger that has null start and end dates.
Now you can call the method ScheduleJob() and pass your job object to it which will create a new Run Now job but with all the details of the old job. you can create a button and do this creating new job thing in it's event handler class.
This is just an overview of what I did to implement Re-Run functionality on SharePoint which is not provided out of the box by JasperServer. Hope this helps and below are some screenshots of my simple webpart,

From INTELLIGENT BIZ

From INTELLIGENT BIZ

Cheers!

Repository Migration from JasperServer Professional (Pro) to Community Edition (CE)

Recently I had to go through a painful server migration for JasperServer. Why was it painful? Because I had to shift from a Pro version to free Community version. The Free community version lacks from several features so restoring an repository export taken from a Pro version is simply a nightmare.
At some point I have almost given up after getting hundreds of errors and was going to manually upload all the reports, data sources, and create schedules. But bingo, something came to my mind. Instead of getting a full export from the Pro version, why not taking several exports of only the reporting folders? Yes that worked out. Yes, it is not as easy as the complete export, but much much easier than doing everything manually. I took several exports, specially of reporting schedules, avoiding resources that are not supported in the community edition, such as analysis views..etc. After that I individually imported these to my community edition and here it works smoothly.
So the trick is, if you ever want to transfer resources from a Pro version to Community version of JasperServer, take several exports/backups from the pro avoiding resources,features that are only supported in Pro. This will save your day!

Cheers!

Gauges in SSRS 2008, The feature I have been waiting for!

Gauges that show the exact percentage amount, as I have experienced with most of the clients, has been one of the features that people mostly ask for in BI reporting/Dashboards. People ask for cost effective BI solutions with one platform. Microsoft SQL Server is a very good example for that since it provides a Database Engine for the Relational Data Warehouses, Powerful ETL tool (Integration Services), Multidimensional Database (Analysis Services) and lastly, A reporting Server (Reporting Services). When it comes to BI reporting, we could convince our customers with SQL Server Reporting Services as a complete BI platform because it has most of the stuff BI Features like drill down reports, Charts of various kinds, Dashboards….etc. But most of them asked for Gauges, showing us Business Objects Dashboards and, to have gauges on SSRS we had to use 3^rd party tools such as Dundas Gauges.

But with SQL Server Reporting Services 2008, we are lucky to have built in gauges which we can use on SSRS reports and dashboards. I have put a screenshot of an example below.

Note that you can use gauges the same way you used charts. Its available in the toolbox of SQL Server Business Intelligence Development Studio. Drag and drop a gauge to the report area and try out different properties. You have a vast range of gauge types available, and also you have the flexibility to customize the look and feel of the gauges, such as colours of the different parts of the gauge …etc.

Enjoy the new feature!!

I thank Asela for helping me to initiate this blog and helping me with editing it with his expertise in web designing. Thanx again.
Chears!
Supun

Problem Running Bugzilla - Internal Server Error!!

Did everything in the manual, all the configurations to apache httpd.conf file but still getting the Internal Server Error! ?
wel the same thing happened to me too, after strugling for several days, this is the cause of it I found,
as you know you must put the following sataments in the httpd.conf to make .cgi file to run by apache using perl.

AddHandler cgi-script .cgi

Options Indexes FollowSymLinks ExecCGI

ScriptInterpreterSource Registry-Strict


But how does apache know where perl is? the problem is there, you must create registry values for .cgi extension so that apache can use perl to run them.

You can add the followng entries in the registry,

create the following key;

HKEY_CLASSES_ROOT\.cgi\Shell\ExecCGI\Command

Put 'default' value with the path to perl.exe followed by "-T"

example: C:\Perl\bin\perl.exe -T

Run bugzilla again, things should be ok if you have done other configurations correclty.

Chears!
Supun

Problem with Calculated Members in SSAS

Have you ever experienced disappearing all your data in the cube after putting a calculated member to it?
This strange thing heppened to me once,
I created this simple cube with SSAS, Deployed it and Processed it. After that I browed it with BI studio and the data were diplayed correctly.
Then I added a calculated member and processed the whole project again. Here I have an empty cube with no data. I processed and processed, which didn't make my data come back!!
Then I found the reason for this in MSDN forum
It was a stupid thing I did;
In you cube design screen, in Calculations tab, you have a pane in right hand side called 'Script Organizer' which lists all your scripts. There is one item called 'CALCULATE' by default. I have accidently deleted this item. When you do that it doesn't give any error and the project is processed successfully and the newly added calculated member is diplayed, but it makes the cube to be empty or disappear the data inside the cube.
You can put this 'CALCULATE;' statment either in the script organizer pane or somewhere in the script of your calculated member. Then the problem is solved.

Cheers!
Supun.