Switching Client Encoding for PostgreSQL

[cnliou]

This is the patch to fix the flaw as mentioned by myself at

http://zeos.firmos.at/viewtopic.php?p=13794#13794

I have just fixed the flaw and the result looks fine. Summary of the rework:

- Ignore entry "codepage=..." in TZConnection.Properties. That is said, setting this entry does not change anything any more.

- The correct way to switch code page is calling TZConnection.SetCodePage(String). You can make the call any time after TZConnection is connected.

- The cycle of "disconnect - reset code page - connect" is no longer necessary.

Four files are affected as attached.

I guess the code base I worked on is version 6.6 but I am not absolutely sure. I do not know where to download the most up-to-date branches through svn and then work on that version(s)/branche(s). Besides, because I only have BCB 6, I hope some one with patching privileges can manually patch these files for me.

Regards,
CN

[mdaems]

cnliou,
Seems to me we have to make sure this new interface function can be implemented for all databases.
And if that's done we should make it react to an entry "codepage=..." in TZConnection.Properties the way it does now.
Have you done this check already? Not sure if all databases accept this change after the connection is effectively made.
If some databases do not accept this, I'd even say this change is against the basic principle that dbc interface functions should work for all databases in a similar way.
In that case you still have 2 options available
- Add it to the postgres connection dbc interface, so postgres users can use it if they want.
- Leave it as it is now.

Your opinion?

Mark

[cnliou]

I have just quickly reviewed mysql, sqlite, and interbase6. It appears to me that none of them reacts to the change of "codepage=..." after connection, and sqlite even completely ignores this parameter.

It will be fine for myself to make SetCodePage() available only to PostgreSQL. However, I don't know how to directly call zdbc procedures from my application. If you can show me how, I can try exporting SetCodePage() to zdbc instead of TZConnection.

Regards,
CN

[cnliou]

It will be fine for myself to make SetCodePage() available only to PostgreSQL. However, I don't know how to directly call zdbc procedures from my application. If you can show me how, I can try exporting SetCodePage() to zdbc instead of TZConnection.

I have read
http://zeos.firmos.at/kb.php?mode=artic ... 22ea6132bd

and

http://zeos.firmos.at/kb.php?mode=artic ... 22ea6132bd

If I understand correctly, this is not a good way for me to go

Code: Select all

... 
 var 
 Connection: IZConnection; 
 Params: TStrings; 
 ... 
 Connection := DriverManager.GetConnectionWithParams(...)

because, if my understanding is right, my application will create two connections: TZConnection and IZPostgreSQLConnection. Components such as TZQuery connect to server via TZConnection. IZPostgreSQLConneciton is for fast direct access to server. The problem is that switching code pages in IZConnection does not affect TZConnection and accordingly TZQuery.

As to the tutorial

Code: Select all

IZMySQLConnection = interface (IZConnection) 
 function Ping(...); 
 function Kill(..); 
 end;

, I might be able to move SetCodePage() as the example demonstrates:

Code: Select all

IZPostgreSQLConnection = interface (IZConnection) 
  SetCodePage();
 end;

My problem is I don't know how to implement a function that returns IZPostgreSQLConnection using TZConnection and its properties/functions similar to the tutorial

Code: Select all

MyConnection: IZMySQLConnection; 

 MyConnection := DriverManager.CreateConnection(Url) as IZMySQLConnection; 
 MyConnection.Ping;

I have also noticed that interbase6 has huge number of parameters to be passed to server. As a result, implementing procedures one by one for all these parameters is impractical. Some mechanism that reacts to the changes of TZConnection.Properties seems to be more practical in terms of interbase 6. On the other hand, such mechanism requires iterating the entries in TZConnection.Properties in order to fire the appropriate procedures. Iteration is very inefficient, especially for those server applications that use zeoslib.

Limited by little skill of Delphi and knowledge of so many servers other than PostgreSQL, I hope someone can help or even better, take over this enhancement/repair.

Regards,
CN

[mdaems]

cnliou,

I think you better read this article : http://zeos.firmos.at/kb.php?mode=article&k=14
It's the SQLite specific implementation of encryption functionionality. It shows how you can use code as

Code: Select all

//by krassonkel
var Conn: IZSQLiteConnection;
    Pass: string;
begin
  Pass := 'secret';
  Conn := (ZConnection1.DbcConnection as IZSQLiteConnection);
  Conn.GetPlainDriver.ReKey(Conn.GetConnectionHandle, PChar(Pass), StrLen(PChar(Pass)));
end;

to call db-specific dbc layer functions.

For other databases:
- I know mysql API supports mysql_set_character_set().
- Firebird needs to know the charset at connect time
- Oracle seems to have the same behaviour as FB (connect time)
- SQLite : looks like it's treating strings like raw memory buffers and doesn't link charsets to the data (I might be wrong, I only scanned the API documentation briefly)

What do you think? Can you manage this change for the postgres driver. Maybe I'll give the mysql driver a try afterwards, but I never worked with charsets before. So I need a tester for that...

Mark

[cnliou]

I think you better read this article : http://zeos.firmos.at/kb.php?mode=article&k=14
It's the SQLite specific implementation of encryption functionionality. It shows how you can use code to call db-specific dbc layer functions.

What do you think? Can you manage this change for the postgres driver. Maybe I'll give the mysql driver a try afterwards, but I never worked with charsets before. So I need a tester for that...

Adding SetCodePage() only in ZDbcPostgreSQLConnection.pas requires me the least effort but BCB 6 can't compile the following snippet of my application

Code: Select all

const_cast<IZPostgreSQLConnection *>(ZConnection->DbcConnection)->SetCodePage(PgClientEncoding);

and complains:

Code: Select all

Cannot cast from 'const _di_IZConnection' to 'IZPostgreSQLConnection *'

I have spent a whole day figuring out the correct way of casting constant interface to another interface in C++Builder, but to no avail.

[mdaems]

Don't know BCB6... So this may be a stupid response :-S
Using the 3 step cast as in the example doesn't work either?
- Create IZPostgresSQLConnection object variable
- Assign this variable a value by casting
- Use the var->SetCodePage() function.

[cnliou]

Using the 3 step cast as in the example doesn't work either?
- Create IZPostgresSQLConnection object variable
- Assign this variable a value by casting
- Use the var->SetCodePage() function.

No, this can't compile:

Code: Select all

	IZPostgreSQLConnection *c=static_cast<IZPostgreSQLConnection *>(ZConnection->DbcConnection);

I don't want to see the enhancement be delayed by me. It requires us to send the command "SET CLEINT_ENCODING TO <character set>" to PostgreSQL in order to tell it to switch code page. I notice that mySQL does the same only with minor difference in the command. How about we go this way:

You add SetCodePage() in ZDbcMySQLConnection.pas and test it. If it works for mySQL, then I follow your code and add it in ZDbcPostgreSQLConnection.pas. Then you commit both patches without test run for PostgreSQL driver.

My another concern is the many code bases we have now: There currently are 6.6.x and 7.x trunk, branch, testing, etc. I don't know exactly which code bases need to be patched. Besides, I have only BCB 6, which is incompatible with unicode new features. As such, I would much more appreciate if you can arrange to add this functionality to both mySQL and PostgreSQL and commit the changes. Then I will try to test 6.x in PostgreSQL.