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!