Handling utf8 data from SQLite

[Zettai Muri <zettaimuri gmail com>]

Hi All,

I am trying to retrieve a Japanese string from an SQLite database.

The table I'm attempting to retrieve data from has the following structure:

CREATE TABLE profile (
  profile_id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT,
  UNIQUE(username)
);

The profile table has the following rows in it:

1,黒崎,一護
2,zettai,絶対
3,大きい,small
4,fred,nurk

I am using a little dialog to search by the username (field2) and it
sets a label below it to 'password'. (field3)

The steps I follow are:
1. Type one of the usernames into the entry box
2. Click Find
- label displays the matching password found for that username

The only username:password combinations that work are (3 & 4). When
attempting to receive Japanese/UTF8/unsigned char* from SQLite it
displays the error:

(Japanese01.exe:2428): glibmm-CRITICAL **:
unhandled exception (type Glib::Error) in signal handler:
domain: g_convert_error
code  : 1
what  : Invalid byte sequence in conversion input

I realise I need to convert from the const unsigned char* that SQLite
returns to a Glib::ustring but have no idea how this can be done.

There seem to be lots of great string utils in both Gtk and Gtkmm but
I am new at applying the api to my own code so am struggling to find
the correct combination.  I played around with g_locale_from_utf8
which came close but displayed wingdings like characters in the label.
I have also found UTF8-CPP but again not sure how to apply it.

Below is the implementation of TestWindow.cpp:

#include <iostream>
#include <gtkmm/dialog.h>
#include "sqlite3.h"
#include "utf8.h"
#include "TestWindow.h"


TestWindow::TestWindow(): m_HBox(false, 8), // homogenous, spacing
                          m_btnHBox(false, 2),
                          m_Table(3, 3, false), // rows, cols, isHomogenous
                          m_Label1("Username: ", true), // true = mnemonic
                          m_Label2("Password: ", true),
                          m_lblPassword("PASS FROM DB", false),
                          m_btnFind("_Find", true),
                          m_btnClose("_Close", true)

{
    add(m_HBox); // put the HBox in the window
    m_HBox.set_border_width(8);
    m_Table.set_row_spacings(4);
    m_Table.set_col_spacings(4);
    m_HBox.pack_start(m_Table);
    m_btnHBox.pack_start(m_btnFind);
    m_btnHBox.pack_start(m_btnClose);
    m_Label1.set_mnemonic_widget(m_eUsername);
    //(Widget& child, guint left_attach, guint right_attach, guint
top_attach, guint bottom_attach,
    m_Table.attach(m_Label1, // The widget to add.
                   0, // The column number to attach the left side of
a child widget to.
                   1, // The column number to attach the right side of
a child widget to.
                   0, // The row number to attach the top of a child widget to.
                   1); // The row number to attach the bottom of a
child widget to.

    m_Table.attach(m_eUsername, 1, 2, 0, 1);
    m_Table.attach(m_Label2, 0, 1, 1, 2);
    m_Table.attach(m_lblPassword, 1, 2, 1, 2);
    m_Table.attach(m_btnHBox, 1, 2, 2, 3);

    // Connect signals
    m_btnFind.signal_clicked().connect( sigc::mem_fun(*this,
                &TestWindow::on_find_button_clicked) );

    m_btnClose.signal_clicked().connect( sigc::mem_fun(*this,
                &TestWindow::on_close_button_clicked) );

    show_all_children();
}

TestWindow::~TestWindow()
{
}

// Get username from text entry
Glib::ustring TestWindow::get_username() const
{
    return m_eUsername.get_text();
}

// Get password from label
Glib::ustring TestWindow::get_password() const
{
    return m_lblPassword.get_text();
}

// Display password on label
void TestWindow::set_password(Glib::ustring& pw)
{
    m_lblPassword.set_text(pw);
}

/* Test the following:
 * 1. Username = Japanese, Return Japanese password  //
kurosaki:ichigo (both in Japanese)
 * 2. Username = English, Return Japanese password // zettai (English)
zettai(Japanese)
 * OK3. Username = Japanese, Return English password   // big
(Japanese) small (English)
 * OK4. Username = English, Return English password  // fred:nurk
(both in English)
 */
void TestWindow::on_find_button_clicked()
{
    Glib::ustring user_entry = get_username();
    Glib::ustring pass = findPassword(user_entry);
    set_password(pass);

}

void TestWindow::on_close_button_clicked()
{
    this->hide();
}

Glib::ustring TestWindow::findPassword(Glib::ustring username)
{

    int rc, i, ncols;
    sqlite3_stmt *stmt;
    sqlite3* db;
    char *sql;
    const char *tail;

    rc = sqlite3_open("test.db", &db);

    if (rc)
    {
        std::cout << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_close(db);
        exit(1);
    }

    // Create statement
    sql = "SELECT password FROM profile WHERE username = ?";

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, &tail);
    if (rc != SQLITE_OK)
    {
        std::cout << "Error preparing statement ("
                  << rc
                  << "): "
                  << sqlite3_errmsg(db)
                  << std::endl;

        return false;
    }

    rc = sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK)
    {
        std::cout << "Error binding text ("
                  << rc
                  << "): "
                  << sqlite3_errmsg(db)
                  << std::endl;

        return false;
    }

    // Execute statement
    rc = sqlite3_step(stmt);
    ncols = sqlite3_column_count(stmt);

    std::cout << "Number of Cols: " << ncols << std::endl;
    std::cout << "RC (sqlite3_step): " << rc << std::endl;
    std::cout << "SQLITE_ROW: " << SQLITE_ROW << std::endl;
    std::cout << "SQLITE_DONE: " << SQLITE_DONE << std::endl;

    //char * test = g_locale_to_utf8(animaltype, -1, NULL, NULL, NULL);
    //password = g_locale_from_utf8(temp, -1, NULL, NULL, NULL);

    Glib::ustring password;
    while (rc == SQLITE_ROW)
    {

        password = reinterpret_cast< const char*
>(sqlite3_column_text(stmt, 0));

        // Move to next row (shouldn't be one)
        rc = sqlite3_step(stmt);
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    std::cout << "DB - Password: " << password << std::endl;
    return password;
}

Glib::ustring TestWindow::queryReturningJapanese()
{
    Glib::ustring name_from_db;

    return name_from_db;
}

I am guessing that the main point is here:
    Glib::ustring password;
    while (rc == SQLITE_ROW)
    {

        password = reinterpret_cast< const char*
>(sqlite3_column_text(stmt, 0));

        // Move to next row (shouldn't be one)
        rc = sqlite3_step(stmt);
    }

Is anyone able to show  me some code that I can use to encode/decode
to and from a const unsigned char* from SQLite to/from a
Glib::ustring?

I am using:
Dev-C++ v4.9.9.2
Windows XP
SQLite 3
GtkMM 2.4

but am hoping that one day I can also compile it on Linux so hoping
for a cross-platform solution.

ZM.