Unicode encoding

[HoGo]

Hi

I have one question. I try to use your Core Lab PostgreSQLDirect .NET, but i have one problem. I need to connect to database with Unicode encoding. When I receive result data from PgSqlDataReader there is always wrong characters. I try to set Unicode property in PgSqlConnection to true, but nothing happening. I set the charset property, but nothing happening. Can you help my how i can receive correct strings from my database?

Thank You for your help!

HoGo

[Yuri]

The only necessary action to take when working with unicode text is setting PgSqlConnection.Unicode property to true.
We couldn't reproduce the problem. Send us please small demo project to demonstrate the problem and include script to create server objects.

[HoGo]

The source code:

Code: Select all

    Public Function ConnectToDB() As CoreLab.PostgreSql.PgSqlConnection
        Dim SqlConn As New CoreLab.PostgreSql.PgSqlConnection

        With SqlConn
            .Host = GetXmlAttr(ConfPath, "Database", "Host")
            .Port = GetXmlAttr(ConfPath, "Database", "Port")
            .Database = GetXmlAttr(ConfPath, "Database", "DBName")
            .UserId = CaesarEncode(GetXmlAttr(ConfPath, "Database", "User"))
            .Password = CaesarEncode(GetXmlAttr(ConfPath, "Database", "Password"))
            '.Unicode = False
            '.Charset = "WIN1252"
        End With

        Try
            SqlConn.Open()
        Catch ex As Exception
            MessageBox.Show(GetOutText("ComMsg", "CannotConnectDb") & ex.Message, GetOutText("ComMsg", "DBErrorTitle"), MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return Nothing
        End Try

        Return SqlConn
    End Function
    Public Function GetDataFromDB(ByVal strSql As String) As CoreLab.PostgreSql.PgSqlDataReader
        Dim SqlCom As New CoreLab.PostgreSql.PgSqlCommand
        Dim sqlRead As CoreLab.PostgreSql.PgSqlDataReader

        SqlCom.CommandText = strSql
        SqlCom.Connection = ConnectToDB()

        Try
            sqlRead = SqlCom.ExecuteReader()
        Catch ex As Exception
            MessageBox.Show(GetOutText("ComMsg", "CannotConnectDb") & ex.Message, GetOutText("ComMsg", "DBErrorTitle"), MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return Nothing
        End Try
        If Not sqlRead.HasRows Then Return Nothing

        Return sqlRead
    End Function

    Sub PopulateCombo(ByVal cmbSel As ComboBox, ByVal SqlConn As CoreLab.PostgreSql.PgSqlConnection, ByVal strSql As String)
        Dim MyAdapter As New CoreLab.PostgreSql.PgSqlDataAdapter
        MyAdapter.SelectCommand = New CoreLab.PostgreSql.PgSqlCommand(strSql, SqlConn)

        Dim ddsData As DataSet = New DataSet
        MyAdapter.Fill(ddsData, strSql)

        With cmbSel
            .DataSource = ddsData.Tables(0).DefaultView
            .DisplayMember = ddsData.Tables(0).Columns(1).Caption
            .ValueMember = ddsData.Tables(0).Columns(0).Caption
            .Text = ""
        End With
    End Sub

I cannot send You a script, because the database is really big, but it is in Unicode. I try to get data with datareader too, but the result is the same.

Thank You

[HoGo]

Hi!

I have another question: when i create a new database, it's enough, if I declare a database as a Unicode database, or I must set any property for each table in database if I need to use them with Unicode charatcter set.

Thank You

P.S. Sorry for my english...

[Yuri]

If you want to store string values in Unicode character set it is enough to create database with Unicode encoding.
See example of working with unicode strings:

Code: Select all

Private Sub UnicodeTest()
    Dim connection As CoreLab.PostgreSql.PgSqlConnection = New CoreLab.PostgreSql.PgSqlConnection()
    connection.ConnectionString = "user = ***;server=***;database=***" 'database with unicode encoding
    connection.Unicode = True
    connection.Open()

    'create test table
    Dim command As CoreLab.PostgreSql.PgSqlCommand = connection.CreateCommand()
    command.CommandText = "CREATE TABLE unicode_test(text text) with oids;"
    command.ExecuteNonQuery()

    'insert unicode string
    Dim unicodeString As String = System.Text.Encoding.Unicode.GetString(New Byte() {138, 255, 119, 255, 138, 255, 138, 255})
    command.CommandText = "insert into unicode_test values(:unicodeParameter);"
    command.Parameters.Add("unicodeParameter", unicodeString)
    command.ExecuteNonQuery()
    command.Parameters.Clear()


    command.CommandText = "select * from unicode_test"
    Dim reader As CoreLab.PostgreSql.PgSqlDataReader = command.ExecuteReader()
    If (reader.Read) Then
      If reader.GetString(0)  unicodeString Then
        MessageBox.Show("bug in PostgreSQLDirect")
      End If
    End If
    reader.Close()

    command.CommandText = "drop table unicode_test;"
    command.ExecuteNonQuery()

    connection.Close()
  End Sub

[HoGo]

Thank You for your help.

I copy your code to my project under one button click event.

When I click on this button I receive "bug in PostgreSQLDirect" message.
Naturaly before this I set the host name, password, port, etc.
When I look, what contains the reader.GetString(0), there is '????'.

The only what I can conceive is, that there is a error in our database or bug in PostgreSQLDirect.

If you want, I can create user for you in this database, which is empty now and you can test then PostgreSQLDirect on it.

Please if you can help me, because I start a work on one big project and if I still have problem with this encoding I must search for another solution.

Thank You again.

[Yuri]

What server version do you use? Can you receive unicode strings another way, by PgAdmin or by npgsql?

[HoGo]

We using server v. 7.2.4-5.73, and yes, I can read and edit Unicode characters with PostgreSQL access software.

Can be a problem, that now I use only the Trial version of your software? I do this because I lost the installation package for full version when I reinstall my computer. I already write about this to [email protected].

[Yuri]

We reproduced problem and fixed it. This fix will be included in the next PostgreSQLDirect .NET build.
It will be available in a week.