Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Postgres library improvements
Postgres library improvements [message #22561] Mon, 27 July 2009 10:44 Go to next message
Zbych is currently offline  Zbych
Messages: 304
Registered: July 2009
Senior Member
1. Client code page

Functions PostgreSQLConnection::ErrorMessage and PostgreSQLSession::ErrorMessage use FromSystemCharset to convert code page. Unfortunately PQerrorMessage AFAIK does not use client's system code page but the code page that is declared in lc_messages (postgresql.conf), or the code page explicit set by the client (function PQsetClientEncoding).

So I think those functions should be modified:

String PostgreSQLConnection::ErrorMessage()
{
	// no code page conversion, leave it to postgres
	return AsString(PQerrorMessage(conn));
}

String PostgreSQLSession::ErrorMessage()
{
	// no code page conversion, leave it to postgres
	return AsString(PQerrorMessage(conn));
}


And of course postgres should be informed about the code page:

bool PostgreSQLSession::Open(const char *connect)
{
	Close();
	conn = PQconnectdb(connect);
	if(PQstatus(conn) != CONNECTION_OK)
	{
		SetError(ErrorMessage(), "Opening database");
		Close();
		return false;
	}
	level = 0;
	
	// set client's code page
	int stat = PQsetClientEncoding(conn, CharsetName(GetDefaultCharset()));
	ASSERT(stat == 0);
	LOG( String("Postgresql client encoding: ") + pg_encoding_to_char( PQclientEncoding(conn) ) );
	
	return true;
}


I made some tests with UTF-8 and WIN1250, and it appears that postgres accepts code page names from ultimate Smile


2. Re-establishing connection

It would be nice to have function that reconnects to database, when connection is lost:

bool PostgreSQLSession::ReOpen()
{
	PQreset(conn);
	if(PQstatus(conn) != CONNECTION_OK)
	{
		SetError(ErrorMessage(), "Opening database");
		return false;
	}
	level = 0;
	
	return true;	
}


Re: Postgres library improvements [message #22595 is a reply to message #22561] Wed, 29 July 2009 08:40 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11706
Registered: November 2005
Ultimate Member
Thanks, applied. I hope this will not cause any trouble (but it seems quite reasonable).

Mirek
Re: Postgres library improvements [message #22600 is a reply to message #22595] Wed, 29 July 2009 14:52 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 304
Registered: July 2009
Senior Member
luzr wrote on Wed, 29 July 2009 08:40

Thanks, applied. I hope this will not cause any trouble (but it seems quite reasonable)


I see two possible sources of troubles:

1. not all code page names from CharsetName() are accepted by postgres (that is why I've added ASSERT). Code page names: iso8859-xx, windows-125x, koi8-r, UTF-8 are accepted and cp852, mjk, cp850 are rejected.

2. I don't know what code page is used before connection is established (in my case all messages at this time are in english, probably because of this bug ).

[Updated on: Wed, 29 July 2009 15:21]

Report message to a moderator

Re: Postgres library improvements [message #22606 is a reply to message #22561] Thu, 30 July 2009 22:12 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 304
Registered: July 2009
Senior Member
I've made some additional tests and it appears that code page and client language are one big mess in windows version of postgresql:

1. Before client is connected, error messages (for example when server is down) use win-1250 code page (environment variable LC_LOCALE is ignored, bug)

2. When client tries to connect, but connection is refused (for example when client has no right to connect to database), error messages use code page and language set in configuration of server (in my case utf-8)

3. When client is connected and code page is set by PQsetClientEncoding, error messages use correct code page.

I tried to fix the problem:
String PostgreSQLSession::ErrorMessage()
{
    if (PQclientEncoding(conn) >= 0) return PQerrorMessage(conn);
    return FromSystemCharset(PQerrorMessage(conn));
}

but it only solves case 1 i 3.

Any ideas how to handle this correctly?

[Updated on: Thu, 30 July 2009 22:12]

Report message to a moderator

Re: Postgres library improvements [message #22607 is a reply to message #22606] Fri, 31 July 2009 10:22 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11706
Registered: November 2005
Ultimate Member
Zbych wrote on Thu, 30 July 2009 16:12

I've made some additional tests and it appears that code page and client language are one big mess in windows version of postgresql:

1. Before client is connected, error messages (for example when server is down) use win-1250 code page (environment variable LC_LOCALE is ignored, bug)

2. When client tries to connect, but connection is refused (for example when client has no right to connect to database), error messages use code page and language set in configuration of server (in my case utf-8)

3. When client is connected and code page is set by PQsetClientEncoding, error messages use correct code page.

I tried to fix the problem:
String PostgreSQLSession::ErrorMessage()
{
    if (PQclientEncoding(conn) >= 0) return PQerrorMessage(conn);
    return FromSystemCharset(PQerrorMessage(conn));
}

but it only solves case 1 i 3.

Any ideas how to handle this correctly?



For now, patch applied.

I guess that perhaps the issue is not that serious. error messages tend to be in basic ascii, especially connection messages.

Moreover, you do not expect regular users to understand them anyway...

Mirek
Re: Postgres library improvements [message #22981 is a reply to message #22607] Fri, 04 September 2009 14:24 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11706
Registered: November 2005
Ultimate Member
So it happened, we achieved to broke important existing application...

The reason is handlong of SQL_ASCII.

I have reworked the whole issue:

- now, the db encoding is detected first.
- if it is SQL_ASCII, no further conversions are performed.
- if it is anything else, we set encoding to UTF-8 and convert everything from us to UTF-8 and back
- to workaround the rest, there is now SetCharset method to force it

Mirek
Re: Postgres library improvements [message #23024 is a reply to message #22981] Tue, 08 September 2009 16:02 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 304
Registered: July 2009
Senior Member
luzr wrote on Fri, 04 September 2009 14:24

So it happened, we achieved to broke important existing application...


Sorry to hear that.
But your solution does the conversion twice (first one on database server side, and the second one in the application). To be honest, I don't see much sense in this solution. It would be much easier to give user a choice by adding new parameter to PostgreSQLSession::Open(const char *connect, bool autoconvert = false);
By default conversion can be turned off (for compatibility with older applications).

Another problem is in ErrorMessage function:
Now:
{
	if(PQclientEncoding(conn) >= 0)
		/* Client is connected, you should use FromCharset here */
		return PQerrorMessage(conn);
	/* Client is disconnected, use system code page */
	return FromCharset(PQerrorMessage(conn));
}

Proposition:
{
	if(PQclientEncoding(conn) >= 0)
		return FromCharset(PQerrorMessage(conn))
	return FromSystemCharset(PQerrorMessage(conn));
}


Re: Postgres library improvements [message #23041 is a reply to message #23024] Thu, 10 September 2009 10:12 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11706
Registered: November 2005
Ultimate Member
Zbych wrote on Tue, 08 September 2009 10:02

luzr wrote on Fri, 04 September 2009 14:24

So it happened, we achieved to broke important existing application...


Sorry to hear that.
But your solution does the conversion twice (first one on database server side, and the second one in the application).



Well, I bet on the fact that most apps will use either UTF-8 or ASCII in DB and UTF-8 in app.
Re: Postgres library improvements [message #23042 is a reply to message #23024] Thu, 10 September 2009 10:20 Go to previous message
mirek is currently offline  mirek
Messages: 11706
Registered: November 2005
Ultimate Member
Zbych wrote on Tue, 08 September 2009 10:02


Another problem is in ErrorMessage function:
Now:
{
	if(PQclientEncoding(conn) >= 0)
		/* Client is connected, you should use FromCharset here */
		return PQerrorMessage(conn);
	/* Client is disconnected, use system code page */
	return FromCharset(PQerrorMessage(conn));
}

Proposition:
{
	if(PQclientEncoding(conn) >= 0)
		return FromCharset(PQerrorMessage(conn))
	return FromSystemCharset(PQerrorMessage(conn));
}






Yes, thanks. Actually, I believe this fix will do:

String PostgreSQLConnection::ErrorMessage()
{
	return FromCharset(PQerrorMessage(conn));
}


and not using ErrorMessage for connection failure.

Mirek
Previous Topic: Postgresql and bool
Next Topic: Acquiring large record
Goto Forum:
  


Current Time: Tue Feb 19 19:43:55 CET 2019

Total time taken to generate the page: 0.00977 seconds