Monday, March 31, 2008

libdbi..TEXT Datatype..MySQL VS PostgreSQL

I am currently working with porting a very much sophisticated & advanced NLP software/system written in C++ which is actually targeted to run using a MySQL Database.

My current or short term target is to port it to PostgreSQL.

After created necessary & equivalent tables in PostgreSQL database, I wrote a script to transfer data from MySQL database to PostgreSQL database.
After some fixing it worked fine.

Then I ran the software using PostgreSQL database. loading failed :(

I found that if I just changed the database driver from "pgsql" to "mysql" it worked fine.
The problem occurred if database driver is changed from "mysql" to "pgsql"

I thoroughly investigated the table definitions and data in various tables and found that the problem was not originated from the database end.

So, why I faced the problem?? Why???? :(

After loosing some valuable hairs:(, I found this:

In case of MySQL database, libdbi-driver for MySQL wants to fetch TEXT as binary and thats why in the original source code of the software, dbi_result_get_binary was used to fetch TEXT type data.

But, in case of PostgreSQL database, this is different. i.e. libdbi-driver for PostgreSQL wants to fetch TEXT as string not binary.
So, the fix is to replace dbi_result_get_binary with dbi_result_get_string

Ahh...The world is beautiful again :D

No comments: