Re: psqlodbclibpq 8_1_03 unicode problem

What I had to do was convert the database to Unicode.
The 7.4 driver can read Unicode in SQLASCII, but the 8.0 series does not
read uncode dat from a SQLASCII database.

Specifically I wrote a application in .net that uses the npgsql .net objects
(downloaded from the downloads menu on postgres.org site).
With it you can specify reading from ascii and writing to Unicode.
The conection to the new Unicode database should specify encoding = Unicode
(actual syntax is in their help).
In the net application after reading the string from the aschii database I
move it to a unicode encodeing object (I did not have to actually do the
conversion as the data was already Unicode I believe, it came from MSSQL
server) and write to the connection to the new database which specifies
encoding =Unicode.
I will include the .net source snippet for the conversion as an example:

This is the conversion, note I had to comment most of the example from help
out to work for me.
Private Function sqltounicode(ByVal asciiString As String) As String
'Dim unicodeString As String = "This string contains the unicode
character Pi(" & ChrW(&H3A0) & ")"
' Create two different encodings.
Dim ascii As Encoding = Encoding.ASCII
Dim [unicode] As Encoding = Encoding.Unicode
'Dim [unicode] As Encoding = Encoding.UTF8
'Dim myconvertstr
'myconvertstr = "à"
' Convert the string into a byte[].
Dim unicodeBytes As Byte() = [unicode].GetBytes(asciiString)
'Dim asciiBytes As Byte() = ascii.GetBytes(asciiString)
'Dim asciiBytes As Byte() = [unicode].GetBytes(myconvertstr)
'Dim test1, test2
'test1 = [unicode].GetString(asciiBytes)
'test2 = ascii.GetString(asciiBytes)
' Perform the conversion from one encoding to the other.
'Dim unicodeBytes As Byte() = Encoding.Convert(ascii, [unicode],
asciiBytes)
'test1 = [unicode].GetString(unicodeBytes)
'test2 = ascii.GetString(unicodeBytes)

'sqltounicode = [unicode].GetString(unicodeBytes)
sqltounicode = [unicode].GetString(unicodeBytes)
' Convert the new byte[] into a char[] and then into a string.
' This is a slightly different approach to converting to illustrate
' the use of GetCharCount/GetChars.
'Dim asciiChars(ascii.GetCharCount(asciiBytes, 0,
asciiBytes.Length)) As Char
' Dim unicodeChars([unicode].GetCharCount(unicodeBytes, 0,
unicodeBytes.Length)) As Char
'ascii.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0)
' [unicode].GetChars(unicodeBytes, 0, unicodeBytes.Length,
unicodeChars, 0)
' Dim unicodeString As New String(unicodeChars)
'sqltounicode = unicodeString
' Display the strings created before and after the conversion.
'Console.WriteLine("Original string: {0}", unicodeString)
'Console.WriteLine("Ascii converted string: {0}", asciiString)
End Function

This loops through a set of table names (I used a seq to deal with
dependancies
Private Sub Buttontounicode_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Buttontounicode.Click
Dim strConnectSource
Dim insertsqltext As String
Dim isfirstrow As Boolean
Dim NeedsQuotes As Boolean
Dim insertvaluestr As String
Dim addingfield As Boolean
Dim SQLconnString2 As String
'Dim dbconntbl As SqlConnection
Dim dbcomm2 As NpgsqlCommand 'used to get table names
Dim dbcomm As New NpgsqlCommand 'used to get data
'dbcomm = New NpgsqlCommand(strSQL, cnn)
Dim cmd As New NpgsqlCommand
Dim tablename As String
Dim cnn As New NpgsqlConnection("Server=" +
TextBoxpostgresserver.Text + ";Port=5432;User
Id=postgres;Password=;Database=" + TextBoxposgresdbname.Text + ";")
cnn.Open()
Dim myreader As NpgsqlDataReader
Dim cnnunicode As New NpgsqlConnection("Server=" +
TextBoxunicodeserver.Text + ";Port=5432;User
Id=postgres;Password=;Database=" + TextBoxunicodedbname.Text +
";Encoding=UNICODE;")
cnnunicode.Open()

Dim i As Integer
Dim mytype, myfieldname As String
Dim strSQL, rptsql, datagrid1, datagrid2, datagrid2a, datagrid3,
dataCust, dbread
Dim teststr
' Start a local transaction
Try
' myCommand.CommandText = "SET client_encoding= 'UNICODE';"
' myCommand.ExecuteNonQuery()

' dbconn.Open()

strSQL = "Select name "
strSQL = strSQL + " from tbl_tablnames " 'where name ~*
'tblresponse_line'"
strSQL = strSQL + " order by seq "

'strSQL = "SELECT tablename FROM pg_tables WHERE tableowner =
current_user and schemaname = 'public'"
dbcomm2 = New NpgsqlCommand(strSQL, cnn)
Dim myreader2 As NpgsqlDataReader = dbcomm2.ExecuteReader
While myreader2.Read()
tablename = myreader2(0).ToString()
'tablename = "tblcase"
strSQL = "SELECT * FROM " + tablename '+ " where clientnum =
'law' and customerid = 41288 "
'strSQL = "SELECT * FROM tblcase where clientnum = 'HBC' and
casenum ='01613-00082-05'"
dbcomm.Connection = cnn
dbcomm.CommandText = strSQL
'Dim myreader As NpgsqlDataReader = dbcomm.ExecuteReader
myreader = dbcomm.ExecuteReader
'Dim myreader As NpgsqlDataReader = dbcomm.ExecuteReader
While myreader.Read()
isfirstrow = True
insertsqltext = "insert into " + tablename + "("
For i = 0 To myreader.FieldCount() - 1
addingfield = True
If IsDBNull(myreader(i)) Then
addingfield = False
End If
If addingfield Then
teststr = myreader.GetDataTypeName(i)
If myreader.GetDataTypeName(i) = "timestamp" Or
myreader.GetDataTypeName(i) = "datetime" Or myreader.GetDataTypeName(i) =
"smalldatetime" Then
insertvaluestr = CStr(myreader(i))
If Len(insertvaluestr) = 9 Or
Len(insertvaluestr) = 8 Or Len(insertvaluestr) = 11 Or Len(insertvaluestr) =
10 Then
If InStr(insertvaluestr, ":") > 0 Then
addingfield = False
End If
End If
End If
End If
If addingfield Then
insertvaluestr = myreader.GetName(i).ToString
If isfirstrow Then
isfirstrow = False
insertsqltext = insertsqltext +
insertvaluestr
Else
insertsqltext = insertsqltext + "," +
insertvaluestr
End If
End If
Next
'**********Get values
insertsqltext = insertsqltext + ") values("
isfirstrow = True
For i = 0 To myreader.FieldCount() - 1
addingfield = True
If IsDBNull(myreader(i)) Then
addingfield = False
End If
If addingfield Then
If myreader.GetDataTypeName(i) = "timestamp" Or
myreader.GetDataTypeName(i) = "datetime" Or myreader.GetDataTypeName(i) =
"smalldatetime" Then
insertvaluestr = CStr(myreader(i))
If Len(insertvaluestr) = 9 Or
Len(insertvaluestr) = 8 Or Len(insertvaluestr) = 11 Or Len(insertvaluestr) =
10 Then
If InStr(insertvaluestr, ":") > 0 Then
addingfield = False
Else
insertvaluestr = insertvaluestr + "
10:00:00"
End If
End If
End If
End If
If myreader.GetDataTypeName(i) = "bit" Then
NeedsQuotes = False
ElseIf myreader.GetDataTypeName(i) = "char" Or
myreader.GetDataTypeName(i) = "ntext" Or myreader.GetDataTypeName(i) =
"text" Or myreader.GetDataTypeName(i) = "varchar" Or
myreader.GetDataTypeName(i) = "nvarchar" Or myreader.GetDataTypeName(i) =
"datetime" Or myreader.GetDataTypeName(i) = "smalldatetime" Then
NeedsQuotes = True
ElseIf myreader.GetDataTypeName(i) = "decimal" Or
myreader.GetDataTypeName(i) = "float" Or myreader.GetDataTypeName(i) = "int"
Or myreader.GetDataTypeName(i) = "money" Or myreader.GetDataTypeName(i) =
"smallint" Or myreader.GetDataTypeName(i) = "smallmoney" Or
myreader.GetDataTypeName(i) = "tinyint" Then
NeedsQuotes = False
End If
If addingfield Then
insertvaluestr =
myreader.GetName(i).ToString.Trim()
insertvaluestr = CStr(myreader(i))
insertvaluestr = sqltounicode(insertvaluestr)
insertvaluestr = insertvaluestr.Replace("'",
"''")
insertvaluestr = insertvaluestr.Replace("\",
"\\")
insertvaluestr = insertvaluestr.Trim

If UCase(myreader.GetName(i).ToString.Trim) =
"ISDELETED" Then
If UCase(insertvaluestr) = "1" Then
insertvaluestr = "True"
Else
If UCase(insertvaluestr) = "0" Then
insertvaluestr = "False"
End If
End If
End If

If UCase(myreader.GetName(i).ToString.Trim) =
"ACTIVE" Then
If UCase(insertvaluestr) = "Y" Then
insertvaluestr = "True"
Else
If UCase(insertvaluestr) = "N" Then
insertvaluestr = "False"
End If
End If
End If
'End If

If NeedsQuotes Then
If isfirstrow Then
isfirstrow = False
insertsqltext = insertsqltext + "'" +
insertvaluestr + "'"
Else
insertsqltext = insertsqltext + ",'" +
insertvaluestr + "'"
End If
Else
If isfirstrow Then
isfirstrow = False
insertsqltext = insertsqltext + "" +
insertvaluestr
Else
insertsqltext = insertsqltext + "," +
insertvaluestr
End If
End If
End If
Next
'insert into
tblUser(ClientNum,UserID,LocationID,CompleteName,UserName,PassWord,CreateDat
e,LastModified,IsLocked,Email,OfflineModules,FailedLogins,PermCaseManagement
,PermGeneralInv,PermEmployeInv,PermExceptionReportsTemplates,PermExceptionRe
portsQueue,PermAssociateDatabase,PermEmergencyContact,PermAwardsManagement,P
ermIncidentsManagement,PermSystemAdministration,IsSuperUser,LevelID,PermPara
meterSetting,JobTitleID,CreatedByID,IsDeleted,PermCollectionManagement,Last_
Login,PermSafe,PermRisk,PermClaims,PermAudit,PermPrior,PermORT,PermAuditCrea
tion,PermTraining,PermStats,isCollected,PermShrink,PermExport,DefaultAuditVi
ew,DefaultDisplay,Last_PassWord_Update,UserMovementLogging,PermEthics,Presen
tationID,PermBlackberryAudit,PermEvidenceFolder,PermPresentationLayer,PermTr
ansportSealIssues,PermTimeandBilling,PermFacilitiesManagement,PermSkillInven
tory,PermSubcontractor,PermVendor,PermInternalInventoryTracking,PermParenAud
it) values('LIM',1,1,'Super user','admin','1brain','1/13/2000 3:53:03
PM','4/10/2002 11:21:05
AM',False,'','',0,'1;1;1;1;','1;1;1;1;','1;1;1;1;','0;0;0;0;','0;0;0;0;','1;
1;1;1;','1;1;1;1;','1;1;1;1;1;','1;1;1;1;','1;1;1;1;',False,1,'1;1;1;1;',,,F
alse,'1;1;1;1;','10/30/2004 3:44:15
PM','1;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;','0;0;0;0;','1;1;1
;1;','0;0;0;0;','1;1;1;1;',True,'1;1;1;1;','1;1;0;0;','S','A
','1/1/2002','N','1;1;1;1;',1,'1;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;','1
;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;','1;1;1;1;')
insertsqltext = insertsqltext + ")"
Try
'Dim cmd As New NpgsqlCommand(insertsqltext,
cnnunicode)
cmd.Connection = cnnunicode
'insertsqltext = sqltounicode(insertsqltext)
cmd.CommandText = insertsqltext
cmd.ExecuteNonQuery()
Catch ex As Exception
HandleError(ex, insertsqltext)
Labelinfo.Text &= "An exception of type " &
ex.GetType().ToString() & "was encountered while inserting the data.:" +
insertsqltext
Finally
End Try
End While 'getting data
'Client = myreader("client")
myreader.Close()
'myConnection.Close()
End While 'getting tables
myreader = Nothing
myreader2.Close()
myreader2 = Nothing
Catch ex As System.OutOfMemoryException
HandleError(ex, strSQL)
Catch ex As System.Data.OleDb.OleDbException
HandleError(ex, strSQL)
Catch ex As System.Data.SqlClient.SqlException
HandleError(ex, strSQL)
Catch ex As System.Data.Odbc.OdbcException
HandleError(ex, strSQL)
Finally
' always call Close when done reading.
'myConnection.Close()
dbcomm.Dispose()
dbcomm2.Dispose()
cmd.Dispose()
cnnunicode.Close()
cnnunicode.Dispose()
cnn.Close()
cnn.Dispose()
End Try

End Sub

Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305

jfradkin(at)wazagua(dot)com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

-----Original Message-----
From: Simeó Reig [mailto:simeo(at)incofisa(dot)com]
Sent: Wednesday, August 10, 2005 10:55 AM
To: Joel Fradkin
Subject: Re: [ODBC] psqlodbclibpq 8_1_03 unicode problem

Hi Joel,

It's seems like this is my problem, I've a SQL_ASCII database ... Is it
possible to change to unicode for read catalan letters from ODBC ? I 've
been reading alter database manual and seems like there is not this
possibility ... How do you made it ?

Thanks

Simeó Reig
Barcelona

----- Original Message -----
From: "Joel Fradkin" <jfradkin(at)wazagua(dot)com>
To: "'Josef Springer'" <Josef(dot)Springer(at)JOOPS(dot)COM>;
<pgsql-odbc(at)postgresql(dot)org>
Sent: Wednesday, August 10, 2005 3:01 PM
Subject: Re: [ODBC] psqlodbclibpq 8_1_03 unicode problem

Not sure why your getting that.
Is the database Unicode?
When I was in SQLASCII mine read as ?
The older driver 7.4 reas SQLASCII ok and presented French characters ok,
but newer version did not.
When I converted to a Unicode database I could read the French characters ok
with the new driver.

By the way been up Since Monday no odbc errors yet :)

Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305

jfradkin(at)wazagua(dot)com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Josef Springer
Sent: Wednesday, August 10, 2005 7:58 AM
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] psqlodbclibpq 8_1_03 unicode problem

I have the same problem and reported a bug.
It seems, that Unicode is readed unencoded (one byte per character)

Josef Springer

Simeó Reig wrote:

>> I am using the versionde psqlodbclibpq 8_01_003, to be able to use ssl
>> connections, and until the moment works well, but the problem has it
>> with
>> caracters like:
>>
>> character Ñ, show like character ?
>> character ó, show like character ??
>>
>> that I can make?
>>
>> Thank you
>
>
> I've the same problem too with spanish and catalan letters, Seems like
> it don't support unicode characters.
>
> Thanks a lot
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend