pooled.? PostgreSQL

[hffranzin1]

Hello,
I'm in an ERP refactoring project, and I came across the need to use several simultaneous connections at random times, and I saw that ZEOS has the pooled.postgresql part, but I would like some guidance on how to use it correctly. .
I tried several ways and was unsuccessful...
finally I'm using ZGroupedConnection and ZconnectionGroup
But I don't think it's the right way yet....

Thank you very much

[marsupilami]

Hello,

the pooled driver is a driver that internally manages connection pools. To activate it, you just need to prefix the regular name of your driver with "pooled." In your case that would make you use "pooled.postgresql". A connection pool is internally recognized by its dbc layer connection string (similar to jdbc or oledb connection strings). This means that all your TZConnection objects need to have exactly the same configuration. If you use the pooled driver, calling TZConnection.Open will aquire a connection from the connection pool. Calling TZConnection.Close will release the connection back to the pool.

The pooled driver accepts three additional parameters:

• MaxConnections: The maximum number of connections in the pool.
• Wait: If set to true, Zeos will wait for a connection in the pool to become available if no connection is currently available. If set to false, Zeos will raise an exception if no connection in the pool is available.
• ConnectionTimeout: If set to a non-zero value, Zeos will close connections that have not been used for some time. The timeout is specified in seconds.

To use the pooled driver it is not necessary, to use TZConnectionGroup and TZGroupedConnection. But using them can help. TZConnectionGroup is a container for a database conenction configuration. TZGroupedConnection is a connection object that uses the configuration stored in a TZConnectionGroup. Without the pooled driver, they will behave just in the same way as regular connections will behave. When combined with the pooled driver, a TZGroupedConnection will represent a connection pool and TZGroupedConnection will use connections from that pool.
Unfortunately the TZConnectionGroup and TZGroupedConnection didn't see much maintenance in the past. So some of the configuration that usually should be done in the TZConnectionGroup currently needs to be done in the TZGroupedConnection. We are working on refactoring this. Specifically this means the following properties:

• Catalog
• ClientCodepage
• FormatSettings

Note: Using the Properties property of TZGroupedConnection is a bad idea: These properties only get applied when a connection is created. So they might work as intended without the pooled driver but most probably will not work when used with the pooled driver.

I hope this helps,

Jan

[hffranzin1]

Hi, thanks for the comprehensive explanation.
However, I put pooled.postgresql, and the properties, but without success.
when I put pooled.postgresql it returns an error in any SQL that has (quotes) in the SQL

[marsupilami]

Could you maybe provide a small sample? Meaning a small applicationand database create script?

[hffranzin1]

I don't know how to do this, but I copied the components here below... the way I'm using them in my project


object ZConection: TZGroupedConnection
ControlsCodePage = cGET_ACP
ClientCodepage = 'LATIN1'
Properties.Strings = (
'codepage=LATIN1'
'timeout=30'
'Undefined_Varchar_AsString_Length=100'
'RawStringEncoding=DB_CP'
'MaxConnections=10'
'Wait=true'
'ConnectionTimeout=10000')
TransactIsolationLevel = tiReadCommitted
Connected = True
DesignConnection = True
BeforeConnect = ZGConectionBeforeConnect
DisableSavepoints = False
ConnectionGroup = ZConectionGroup
Left = 164
Top = 130
end
object ZConectionGroup: TZConnectionGroup
Protocol = 'pooled.postgresql'
HostName = '20.0.2.1'
Port = 5432
Database = 'SGDB_STAN'
LibraryLocation = 'libpq12.dll'
OnChange = ZConectionGroupChange
Left = 168
Top = 84
str1 = (
#165#8240#710'PF'#8221#8220'SB'#8240)
str2 = (
#208#222#167#21't'#212#162't'#14#162#223'r'#13#203#25'b'#188#164'k'#7'`'#165#222#203#213#215#214#20'p'#165#163'sa'#213#164'v'#8#215#28)
end