Storing Unicode Data in Databases

This post is in continuation to my earlier post VB Forms in Hindi Unicode Fonts following a few comments regarding improper display of stored data.

Let’s first use an MS Access Database (referred in code as D:\uni.mdb), containing a single table “unitable” which itself contains only one text field “unistring”.

The code for storing data to tables and retrieving them are referred from Database - How can I add a record to a database? and Database - How can I fill a combobox/Listbox with values in a database?, which may be referred for more info on relevant task.

As visible above, the form contains a text box (’txtNewString’) for user Input, a button (’cmdAdd’) labelled ‘Add’, on clicking which the text from the textbox is inserted into the table.

Another button (’cmdRetrieve’) labelled ‘Retrieve’, on press, retrieves all the data from the table to populate the listbox (’lstRetrievedData’).

As also discussed in VB Forms in Hindi Unicode Fonts, these controls should be capable of accepting and displaying Unicode Data, which the default VB controls are not capable of. However, corresponding controls available with “Microsoft Forms 2.0 Object Library” allow unicode data for which just right-click the toolbox, click on ‘components…’ and check “Microsoft Forms 2.0 Object Library”. Now use these controls instead.

The code below demonstrate adding and retrieving data from table on press of relevant buttons. Go through the code… nothing new!! all that you would have written for English based forms…

Private Sub cmdAdd_Click()

    Dim oADOCon As ADODB.Connection

    Dim strQuery As String

    Set oADOCon = New ADODB.Connection
    oADOCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\uni.mdb;User ID=;Password=;"

    strQuery = "insert into unitable values('" & Form1.txtNewString & "')"
    oADOCon.Execute strQuery

    txtNewString = ""

    Set oADOCon = Nothing

End Sub

Private Sub cmdRetrieve_Click()

    Dim oADOCon As ADODB.Connection
    Dim oADORS As ADODB.Recordset

    Dim strQuery As String

    Set oADOCon = New ADODB.Connection
    Set oADORS = New ADODB.Recordset

    oADOCon.CursorLocation = adUseClient

    oADOCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\uni.mdb;User ID=;Password=;"

    strQuery = "SELECT unistring FROM unitable"
    oADORS.Open strQuery, oADOCon, adOpenForwardOnly, adLockReadOnly, adCmdText

    lstRetrievedData.Clear

    Do While Not oADORS.EOF

        lstRetrievedData.AddItem oADORS.Fields("unistring")
        oADORS.MoveNext

    Loop

    Set oADOCon = Nothing
    Set oADORS = Nothing

End Sub

Work’s fine! Now let’s try the above code with an MS-SQL database… Create a similar table in an existing database or to a new database (I used existing Northwind database) with a single text field.

The code should normally work by making changes to line(s) below.

    oADOCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\uni.mdb;User ID=;Password=;"
    oADOCon.Open "DSN=YourDSN;database=Northwind;uid=user;pwd=pwd"

But it doesn’t and the text is saved as ?????? and retrieved as such, since the data types char, varchar, text are designed to accept only 8-bit characters and not the unicode ones. Data types nchar, nvarchar and ntext corresponding to the previously mentioned data types do however allow unicode characters, so we will have to ensure that the data type of the single field in our table is changed accordingly. ‘n’ in these data types is elaborated by MySQL as ‘National’.

That’s not all!, Now that we have made arrangents for correct storage at the database level, we also need to ensure that the data sent by us is recognized by the database as Unicode (or National, as described by MySQL). For this we need to change the insert script by adding a single character ‘N’ just before the unicode string as :

    strQuery = "insert into unitable values('" & Form1.txtNewString & "')"
    strQuery = "insert into unitable values(N'" & Form1.txtNewString & "')"

That’s all. By now you can create database Apps for storing hindi text using MS Access, MS-SQL (both tested) and MySQL (not tested, but documentation seems to support my belief). You can also try other SQL servers also. I would later sometime test this with Oracle and if any issue found, would post it.