MySql BLOB, Marathi Text and Qt

For many days I have been working on this problem, but every time I started work on this, something would come and interrupt me. From the brief research I did, I knew that there's not much data on the net about this problem and if anything is there then I'm not searching with the right keywords. 


Anyway let me tell you what the problem was: I wanted to enter Marathi sentences, special characters etc in MySQL and have them encrypted. Now encryption was the catch - otherwise it worked alright. I am using AES ENCRYPTION here. And I found out that normal alphabets fared okay but special characters used to get screwed. e.g. the degree sign would be replaced by something like Ǻ. 


Now, I was in a bad fix here since I wasn't much familiar with BLOBs. But I vowed that I would overcome every limitation and I would find the solution to this problem. Head on to see what mountains I crossed!!!

Image© http://bloomillustration.blogspot.com

Code: I used Qt Creator for this mini project. The Project Code is here. It contains the SQL file for the database. The database name is blobtest. 


The process is actually very simple but finding the few key ingredients and recipe was the difficult job. 


1. Database: I created a new table in the database with srno field as integer as well as primary key and name as medium blob. I had experienced the blob size problem in the other program that I did where in I was storing an Image in a mysql database using blob. I had chosen blob as column datatype and the image was ~500KB. Now in blob field you can store only 64KB data, and so I was getting a corrupt image on retrieval. Took me about 2 hrs while I went through whole program at least 10 times trying to locate the problem. 
So to be on the safe side I'm choosing mediumblob as column datatype here. 
The limits are : 
TINYBLOB: 255 BYTES
BLOB: 65,535 BYTES ~=64Kb
MEDIUM BLOB:16,777,215 BYTES ~= 16Mb
LONGBLOG: 4,294,967,295 BYTES ~= 4Gb


Ref:
http://www.tech-evangelist.com/2007/12/09/mysql-data-types/
http://www.issociate.de/board/post/391217/blob_size.html
http://enricogi.blogspot.com/2008/12/blob-type-in-mysql.html
http://help.scibit.com/mascon/masconMySQL_Field_Types.html


Anyway so I decided to use medium blob because oh this. 


2. Insert operation: 
The insert operation was actually simple. I converted the QString into a utf8 QByteArray. And passed this array to the insert query where I used bindValue function to bind the bytearray to query. 


QByteArray ar = name.toUtf8();

    //Insert sr no record to db

    QSqlQuery query;

    query.prepare("insert into marText values("+srno+",AES_ENCRYPT(:name,'"+encKey+"'));");

    query.bindValue(":name",ar);

    if(query.exec())

{

3. Select Operation:

I got the insert operation right at the first time, but I wasn't able to think around to how to retrieve the data. I used QString(QByteArray &) constructor to read the ByteArray into string and all I got was garbage. And there wasn't any function that will interpret the array made from a String of Unicode characters. I was badly stuck. I searched online. I went through QT's documentation, help, online forums but to no avail!

I had given up hope when I typed a crooked search query to Google - Unicode and Qt. The major portion was things I had already plowed through, but one link caught my attention. 

http://techbase.kde.org/Development/Tutorials/Localization/Unicode

I went through the page and I stumbled on this: 


QTextCodec Utf8Codec  = QTextCodec::codecForName("utf-8");
QTextCodec Utf16Codec = QTextCodec::codecForName("utf-16");
 
 

And I was like 'OMG! What's this? And did they forgot about any such thing?'! So I copied the lines and pasted them into the editor. Went to help and searched for QTextEdit, and went through all the documentation. After about 15 Mins, I knew this was the diamond I was searching for! 
So after some tweaking, I ended with this block of code which worked. 

query.prepare("select AES_DECRYPT(name,'"+encKey+"') from marText where srno = "+srno);

        query.exec();

        if(query.lastError().isValid())

        {

            qWarning()<<query.lastError().text();

            return;

        }

        if(query.next())

        {

            QByteArray byRead = query.value(0).toByteArray();

            QTextCodec* qtc = QTextCodec::codecForName("UTF-8");

            QString qs = qtc->toUnicode(byRead);

            ui->leName->setText(qs);

            return;

        }

4.Screens:

Click to see at full resolution.

1. Application:

 2.Success Saving the Text to Database:

 3. Showing selected Text:

 4. Both Applications:

Right side application shows image stored in database.