Thread: Re: Database->ServerEncoding, ClientEncoding

Re: Database->ServerEncoding, ClientEncoding

From
Dave Page
Date:

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 25 February 2002 21:07
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Database->ServerEncoding,
> ClientEncoding
>
>
> Hi Dave,
>
> 1) We need two client encodings :
> - one for querying system object,
> - another for displaying data.
>
> > DataEncodingID (Long)
> > DataEncodingName (String)
> The encoding used to display data.
> In my example, UTF-8 to export data, Latin1 otherwize.
>
> > SystemEncodingID (Long)
> > SystemEncodingName (String)
> The encoding used to program schema objects, in my case
> Latin1. "System" because this term is used in pgServerExecSQL.
>
> Example : my database is UTF-8:
> - Latin1 objects,
> - Japanese data.
>
> 2) I hacked pgServerExecSQL like this :
> >As I said in my previous message, we can only set it once for each
> database, otherwise it will become unpredictable as you jump
> from window to window.
>
>   'Get the Query Type and set client encoding
>   szClientEncoding = "SQL_ASCII"
>   SQL_Encoding = ""
>
>   If (QryType And qrySystem) = qrySystem Then
>     szQueryType = "System"
>     szClientEncoding =
> objServer.Databases(Database).SystemEncodingName
>   ElseIf (QryType And qryData) = qryData Then
>     szQueryType = "Data"
>     szClientEncoding = objServer.Databases(Database).DataEncodingName
>   ElseIf (QryType And qryUser) = qryUser Then
>     szQueryType = "User"
>     szClientEncoding = "SQL_ASCII"
>   End If
>
>   If szClientEncoding <> "" Then
>     SQL_Encoding = "SET CLIENT_ENCODING = '" &
> szClientEncoding & "'; "
>   End If
>
>   RaiseEvent SQLExecute(SQL, Connection)
>   objServer.iLogEvent "SQL (" &
> GetDatabase(Connection.ConnectionString) &
> "): " & SQL, etSQL
>   Set ExecSQL = Connection.Execute(SQL_Encoding & SQL)
>
> What do you think?

I forgot I added the query types (that was a recent change to support the
session recorder). Something like that will probably do it, yes. From a
sensible user interface point of view, you could select the system encoding
when logging on (that would be server-wide - would that be a problem), and
the data/user encoding from the SQL window, but default to the system
encoding if a different selection has never been made.

Thoughts?

/D

Re: Database->ServerEncoding, ClientEncoding

From
Jean-Michel POURE
Date:
Le Mardi 26 Février 2002 09:46, Dave Page a écrit :
> I forgot I added the query types (that was a recent change to support the
> session recorder). Something like that will probably do it, yes. From a
> sensible user interface point of view, you could select the system encoding
> when logging on (that would be server-wide - would that be a problem), and
> the data/user encoding from the SQL window, but default to the system
> encoding if a different selection has never been made.

Hmm, you are right, now I understand that client encoding is set for all
databases... because it is a PostgreSQL variable.

This solution works for me. Shall I add pgServer->ClientEncoding?

Cheers,
Jean-Michel


Re: Database->ServerEncoding, ClientEncoding

From
Dave Page
Date:

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 26 February 2002 09:44
> To: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Database->ServerEncoding,
> ClientEncoding
>
>
> > ServerEncodingID (Long)
> > ServerEncodingName (String)
> I still wonder why ServerEncodingID is needed. It adds
> complexity to pgSchema
> without beeing used. Can't ServerEncodingID be masked?
>
> > ClientEncodingID (Long)
> > The ClientEncodingID would be the Windows LCID (I assume that's
> > relevant in this case)
> To you plan to use ClientEncodingID to select fonts? What is
> LCID for? My
> knowledge is very little in this field.

so's mine - I thought I was learning from you!!

LCID is the Windows Locale ID

If it's not important, ignore it.

Regards, Dave.

Re: Database->ServerEncoding, ClientEncoding

From
Jean-Michel POURE
Date:
Le Mardi 26 Février 2002 11:21, Dave Page a écrit :
> Do we set system encoding per database, or per server.
> Where do we set it?
> How do we set it?

Yesterday, tried a hack in SQLExecute, applying CLIENT_ENCODING on the fly at
the beginning of each SQL query. There does not seem to be a real overhead.

> Do we set user/data encoding per database or per server
> Where do we set it (bear in mind that there is no SQLInput form if the user
> does a 'View Data')?
> How do we set it?

It would be better if we could set it per database (?) in
pgDatabase->DataEncoding and pgDatabase->SystemEncoding.

In SQLInput, if a user does not choose a special encoding, we apply
pgDatabase->DataEncoding as default.

Locale, Encoding

From
Jean-Michel POURE
Date:
> LCID is the Windows Locale ID
> If it's not important, ignore it.

Locales and encodings are different:
Locale : "fr_fr",
Encoding : "Latin1".

I guess LCID is not a locale but a font ID. What should $M express locales
using IDs?

Still I don't know if we should keep ServerEncodingID (Long) and
ClientEncodingID (Long). I don't see how these numbers can be used. Do you?

Cheers,
Jean-Michel

Re: Database->ServerEncoding, ClientEncoding

From
Dave Page
Date:

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 26 February 2002 10:54
> To: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Database->ServerEncoding,
> ClientEncoding
>
>
> Le Mardi 26 Février 2002 11:21, Dave Page a écrit :
> > Do we set system encoding per database, or per server.
> > Where do we set it?
> > How do we set it?
>
> Yesterday, tried a hack in SQLExecute, applying
> CLIENT_ENCODING on the fly at
> the beginning of each SQL query. There does not seem to be a
> real overhead.

Sounds a little messy to me, and will certainly clutter up logs and things.

> > Do we set user/data encoding per database or per server
> > Where do we set it (bear in mind that there is no SQLInput
> form if the
> > user does a 'View Data')? How do we set it?
>
> It would be better if we could set it per database (?) in
> pgDatabase->DataEncoding and pgDatabase->SystemEncoding.

Yes, I think I agree.

> In SQLInput, if a user does not choose a special encoding, we apply
> pgDatabase->DataEncoding as default.

I think the neatest place to set these will be in frmDatabase. Doing it
elsewhere will just be confusing. For most users, they can just leave them
alone, someone like yourself can just set & forget.

This would have to be stored in the registry - HKCU\Software\pgAdmin
II\Encodings\>Connection ID>\<Database Name>\[DataEncoding | systemEncoding]
- where <Connection ID> is of the form user|server|port as we using in the
Connections MRU list.

So, when a database is first connected, we read the relevant value and issue
a SET CLIENT_ENCODING. If pgDatabase->SystemEncoding gets updated, we write
to the registry, and issue another SET CLIENT_ENC...

That will work for System stuff. On the User/Data side, those queries *only*
come from frmSQLInput or a 'View Data' (iirc). For these types of query,
*if* we had a second connection to the database, then it could maintain it's
client encoding neatly as well. This would also have the advantage that
large queries would not be using the system connections and could
effectively give us a 'threaded' effect.

The downside would be that we would have extra connections (potentially
double). This could be minimised by only connecting on first use.

Thoughts?

Regards, Dave.



Re: Database->ServerEncoding, ClientEncoding

From
Jean-Michel POURE
Date:
Le Mardi 26 Février 2002 12:20, Dave Page a écrit :
> That will work for System stuff. On the User/Data side, those queries
> *only* come from frmSQLInput or a 'View Data' (iirc). For these types of
> query, *if* we had a second connection to the database, then it could
> maintain it's client encoding neatly as well.

Is encoding set by connection? Cheers/JMP

Re: Database->ServerEncoding, ClientEncoding

From
Dave Page
Date:

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 26 February 2002 12:09
> To: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Database->ServerEncoding,
> ClientEncoding
>
>
> Le Mardi 26 Février 2002 12:20, Dave Page a écrit :
> > That will work for System stuff. On the User/Data side,
> those queries
> > *only* come from frmSQLInput or a 'View Data' (iirc). For
> these types
> > of query, *if* we had a second connection to the database, then it
> > could maintain it's client encoding neatly as well.
>
> Is encoding set by connection? Cheers/JMP

Yes, we would have one connection for the System stuff, and one for
User/Data. That way, there's no need to keep changing the encoding, only
when the user manually changes it or upon initial connection.

/D