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