DatabaseLayer-devel

[damir]

Hi!
I think that I resolved problems with DatabaseStringConverter class. Here are two functions which do the job and I think that they are compatibile with unicode build too.

Code: Select all

wxString ConvertToUTF8(wxString value)
{
	wxString str(value.wc_str(*wxConvCurrent), wxConvUTF8);
	return str;
}

wxString ConvertFromUTF8(wxString value)
{
	wxString str(value.wc_str(wxConvUTF8), *wxConvCurrent);
	return str;
}

I think that we don't need DatabaseStringConverter class at all. And there is no need for other class to derive from it. We can use them as standalone. Or maybe not. I don't know. Opinion?
So what's next?
On every place where database is especting char parameter (sql command or parameter in prepared statement) we have to:

• 1. convert it with ConvertToUTF8 function and
  2. procede it with wxString::c_str() to database api function.

And when we return some char data we have to:

• 1. convert it to op.sys. locale with ConvertFromUTF8 function and
  2. return the new string value

I noticed that now in prepared statement there is no any conversion. Why? We have to fix this too on the way above.

And I have one question/suggestion. There are two RunQuery() and RunQueryWithResult() functions. One in DatabaseLayer class and one in PreparedStatement. I think that this is a little bit confusing. Maybe we should unique it somehow. We should separate transactions, statements and database like in IBPP. I admit that I'm under an impact of IBPP library and that's because I'm using it for about a year, so sorry if I mention it a lot.

All the best,
Damir

[damir]

I just figured that the code above is not working with unicode build because wxString::c_str() returns wchar_t and database functions (at least firebird, sqlite has xxx16() variant of functions but they for some reason expect char* type) don't exept wchar_t type.
In unicode build we need some function which converts wchar_t to char.
Any idea?

[jb_coder]

That looks similar to the original conversion code which used to be centralized in the DatabaseLayer.cpp file. You can see that if you look at previous CVS versions at
http://cvs.sourceforge.net/viewcvs.py/w ... 6&view=log

The wxCharBuffer return type on the current function is used to avoid the wchar_t/char issues.

Don't worry about referencing the IBPP too much. The Firebird code originally used the IBPP library, but it was changed to use the firebird API directly. Also, the DatabaseLayer API was written after I was programminng Java JDBC code at work for a couple years. So it was influenced a lot by the JDBC interfaces.

I think that it would be a good idea to keep the API consistent across database backends. Each database backend supports different data types, and the API has a "least common denominator" set of classes where the API from all the database can be used to hide the internals behind a consistent interface. Transactions in particular don't necessitate their own class because the only functionality in many of the database backends is begin, commit, and rollback. As someone mentioned in a different thread, the next API class that would make the most sense would be to add a ResultSetMetaData class.

I'm open to suggestions on how the different RunQuery and RunQueryWithResults could be less confusing. The main reason that they are named the same is that they perform the same function, and the only difference is whether the statement was prepared first or not. So in my mind it made sense for them to have the same function name.

[damir]

Ok. I didn't know that cvs version contains those functions. I have not been tested cvs version but i think that it doesn't work. When we are not in unicode build there is no conversion at all. And we also need to convert even if we are not in unicode build. For example we are using windows and our charset is WIN1250 (central europe, i think). Windows controls (textctrl etc.) are sending string to application in WIN1250 encoding. We have to convert it to UTF8 with function in beggining of this post and then procede it to database.
Those functions which I wrote do that exactly, but the problem is that in UNICODE build because there is no char type, instead there is wchar_t type which is 2-byte long. We have to find solution for conversion between char and wchar_t. I think that there is alredy solution for this (ConvertFromUnicodeStream does somethink like that) but I have to check it.

All the best,
Damir

[jb_coder]

Just to clarify... I didn't mean the latest CVS version of DatabaseLayer.cpp that had that functionality. I meant that's what it looked like before the code was factored out into the DatabaseStringConverter class.

[damir]

Ok. I understand. But the problem is still here

[damir]

I'm open to suggestions on how the different RunQuery and RunQueryWithResults could be less confusing. The main reason that they are named the same is that they perform the same function, and the only difference is whether the statement was prepared first or not. So in my mind it made sense for them to have the same function name.

Maybe I wasn't clear. There is no problem about naming functions. I posted that those functions exists in both DatabaseLayer and PreparedStatement classes. I think that's a little bit confusing and we should decide from which object (class) will we execute queryes independent of will statement be prepared or not or returning values or not. Like in IBPP you have statement object and you call queryes JUST from that object and not from Database nor Transaction. I hope you undestand now what I'm trying to tell you.

[jb_coder]

Sorry for the delayed response.

I'm trying to create a unit test for this so that I can be sure whatever fix we put in place works on all the different database backends.

Would someone like to following work to demonstrate the current problem?

1) CREATE TABLE table1 (stringField VARCHAR(255) CHARACTER SET KSC_5601 COLLATE KSC_DICTIONARY);
2) Read a string from a ISO-8859-2 encoded file
3) Insert the string from the file into the database table
4) Read the string out of the database table
5) Compare the string read from the file to the string read from the database table.

[jb_coder]

Here's the following unit test that has been written to test this:

Code: Select all

      void testEncodingSupport( void )
      {
        wxPrintf(_("testEncodingSupport\n"));
        TS_ASSERT( m_pDatabaseLayer );
        if (m_pDatabaseLayer)
        {
          try
          {
          m_pDatabaseLayer->RunQuery(_("CREATE TABLE table5 (stringField VARCHAR(255) CHARACTER SET KSC_5601 COLLATE KSC_DICTIONARY);"));

          wxCSConv conv(wxT("ISO-8859-1"));
          wxTextFile fileIn(_("test.txt"));
          bool openedSuccessfully = fileIn.Open(conv);
          TS_ASSERT_EQUALS( true, openedSuccessfully );
          wxString testString(fileIn.GetFirstLine(), *wxConvCurrent);
          fileIn.Close();

          // Add a new row to the database table
          wxString strSQL = wxString::Format(_("INSERT INTO table5 (stringField) VALUES ('%s');"), testString.c_str());
          m_pDatabaseLayer->RunQuery(strSQL);

          strSQL = _("SELECT stringField FROM table5");
          DatabaseResultSet* pResultSet = m_pDatabaseLayer->RunQueryWithResults(strSQL);
          TS_ASSERT( pResultSet );
          if (pResultSet)
          {
            if (pResultSet->Next())
            {
              wxString returnString = pResultSet->GetResultString(_("stringField"));
              TS_ASSERT_EQUALS( testString, returnString );
            }
            else
            {
              wxLogError(_("No records found\n"));
            }
            m_pDatabaseLayer->CloseResultSet(pResultSet);
          }
          else
          {
            wxLogError(m_pDatabaseLayer->GetErrorMessage());
          }

          }
          catch (DatabaseLayerException& e)
          {
            TSM_ASSERT( (const char*)wxString::Format(_("Error (%d): %s\n"), e.GetErrorCode(), (const char*)e.GetErrorMessage().mb_str(wxConvUTF8)).mb_str(wxConvUTF8), false );
          }
          // Remove the test table
          m_pDatabaseLayer->RunQuery(_("DROP TABLE table5;"));
        }
      }

and the content of the file test.txt is the ISO-8859-1 string "

[damir]

Sorry for delay.

No, this is not a problem I was talking about. I don't know exactly why is this happening to you.

What characterset did you defined on connection?

[jb_coder]

I'm not sure if this answers your question, but in the call to isc_expand_dpb the value of isc_dpb_lc_ctype is set to "UTF-8"

[damir]

OK. And what is your op. system character set? What are you using? Windows or linux?
Before sending a string to database you have to convert it to UTF-8. Purpose of setting charset on connection is that firebird knows how to convert chars from its database.

[jb_coder]

I've tried it on a Linux box where the OS character set was UTF-8 (Fedora) and also on one where it was ISO-8859-1 (SuSE). Same results each time. The way that I was approaching it was to set the connection charset to UTF-8 and then use DatabaseStringConverter::ConvertToUnicodeStream to convert the strings to UTF-8.

I should also mention that if I change the contents of test.txt to something like "test" then the test passes so it's definitely the ISO-8859-1 string in the file that causing the error. I'm hopeful that by fixing one of these problems, both will be fixed.

[damir]

I think that you should create text file in kedit or kate and save it as some encoding. And then you should use your own read_from_file function (I don't trust wxCSConv class ). read_form_file should do raw read content byte by byte and store it in char* with '\0' ending. Then you should convert that char* to UTF-8 from encoding you choosed on saving file but with following function:

wxString ConvertToUTF8(wxString value)
{
wxString str(value.wc_str(*wxConvYourEncoding), wxConvUTF8);
return str;
}
In the place of wxConvYourEncoding you have to put encoding of txt file.
And then procede it to firebird. I think that this should work.

Damir

[jb_coder]

I've added that unit test (and finally got it passing on all 4 database backends). There were a lot of changes to the DatabaseStringConverter class. Please let me know if the latest code in CVS works as you would expect in Unicode and Ansi builds.

Thanks