This is a list of the standard Driver Parameters supported by the pgExpress Driver (please check your Delphi help on Driver Parameters for more information on each of them).
Table 2.2. Driver Parameters
Parameter | Obs. |
---|---|
DriverName | |
Database | Used also to provide Special Params to the
pgExpress Driver. This parameter
can also be used as an URL for providing extra host and port info:
host:port/databasePlease check the Installation section for more details. |
User_Name | |
Password | See also the Authentication section. |
BlobSize | |
LocaleCode | See also the Multibyte and Locales section. |
RowsetSize | Only works if the BlockRead param is True. |
Host | Name can instead be specified as a member of the Database paramater. |
RoleName | Will set a PostgreSQL 7.3+ schema (namespace) to log in. Please
check here for more details. Multiple
Schemas can be specified (just as in PostgreSQL) if separated by collons, such as:
RoleName = public,schema1,schema2 |
ServerCharset | See also Multibyte and Locales. |
Custom String | This param allows to supply Special Params through a way other then the Database. D7+ only. |
ServerPort |
Allows setting the port to connect to. D7+ only. Can instead be specified as a member of the Database paramater. |
Connection Timeout | Delphi 7 only. The ConnectionTimeout Special Param can be used on Delphi 6/7 too, or Kylix . |
The pgExpress Driver has a few special parameters that can be used to customize its behaviors; we refer to them as "Special Params". Default values are formatted like this.
Table 2.3. Special Params
Key | Values | Meaning | |||||
---|---|---|---|---|---|---|---|
TextAsBlob |
|
If True, the
PostgreSQL Text field will be mapped as Blob (Memo); if
False (default), it will be mapped
as String.
Obs:If mapped as string, a Text field will be padded at the maximum string length imposed by dbExpress™ (~32Kb). | |||||
MapUnknownAsString |
|
If True, unknown/unsupported field types such as point will be mapped as string. | |||||
ArrayAsString |
|
If True, arrays will be mapped as strings. If False, they will be ignored (hidden). | |||||
Int8Mode |
|
Notes:
| |||||
QuoteObjects |
|
If True, quote objects
names. To this setting to take effect, the connection must be
reopened (if already opened of course). An example of unquoted and
quoted query:
Example 2.1. Quoted and unquoted queries select typname from pg_type; -- unquoted version select "typname" from "pg_type"; -- quoted version Please note that quoted objects names are case sensitive for PostgreSQL. The query above written like this would fail: db =# select "typname" from "Pg_type"; -- quoted version; the correct spell would be "pg_type" ERROR: Relation "Pg_type" does not exist | |||||
TypesCachingMode |
|
This setting will control how often the types from
PostgreSQL will be loaded and mapped into
VCL/CLX types. Of course the less times they are
loaded, the faster the application will be. The default setting
should be OK for most applications (unless you do types
manipulation).
Obs:The PostgreSQL database has a flexible types support; that means we must retrieve the types information before we know what each type name means. This information is retrieved from the pg_type catalog and then mapped into actual dbExpress™/VCL types by the pgExpress driver. Please refer to your PostgreSQL documentation for more details. | |||||
GetExtendedFieldsInformation |
|
If True, will retrieve extra information about the fields in a table (for single table queries). That will allow the pgExpress Driver to determine if a field can be null (ISQLCursor.IsNullable()) or are automatically generated by a sequence the server (ISQLCursor.IsAutoIncrement()). Few applications should need this setting and they can be manually added to the TFields list representing each physical field. Since this will add a new query overhead for each cursor (TDataset) retrieved, slowing down the operation, it's adviced not to use it unless you need the information (for instance, on situations where you don't know the structure of the table that is going to be open). | |||||
ServerDecimalSeparator | (char) |
The pgExpress Driver should automatically detect your locale settings and use the appropriate decimal separator. However, if you want to force some specific value, this options is available. Set it to the char you want to be the decimal separator (usually either "." or ","). However, since the "," is the options separator, you'll need to escape that char, like this: ServerDecimalSeparator = \, Another exemple: ServerDecimalSeparator = . | |||||
BlockRead |
|
If True, the pgExpress Driver will use Fetch Cursors to retrieve the records from the server. The standard RowsetSize param (check your Kylix / Delphi documentation) will determine the number of rows retrieved on each operation. It is required to use this parameter to read the records in blocks since the pgExpress Driver will have to use a totally different method to retrieve the records from the server. Please refer to the section for more details. | |||||
UseQualifiedNames |
|
If True, the pgExpress Driver will return qualified names on metadata retrieving, such as public.MyTable, pg_catalog.abstime, etc. This option will only work PostgreSQL 7.3 which supports schemas. | |||||
RestrictedNameSpace |
|
Controls which the metadata is retrieved. If True, only objects in the current namespace search path will be retrieved. If False, all objects will be retrieved, regardless of which namespace they are in. | |||||
RequireSSL |
|
If True, will only make a connection to the server in SSL mode. This option will only work with a SSL enabled libpq (currently only under Linux). | |||||
ShowOID |
|
If True, the pgExpress Driver will return the OID field in the list of fields for a table (ISQLConnection.getColumns()). See also this FAQ entry on OIDs usage with pgExpress. | |||||
ConnectionTimeout | X | Allows to specify connection timeouts (X in seconds). It needs libpq v7.3 or above (included with pgExpress Driver). This parameter is an integer value. | |||||
AutoRecover |
|
If True, the
pgExpress Driver will
automatically try to detect if the connection and been broken and
recover it automatically and continue processing. If it is
sucessful, the whole operation is fully transparent to the user. If
not, an appropriate error message will be displayed.
NoteThis feature is avaiable only on the registered version of the driver. | |||||
RetainCursor |
|
Controls the Retaining Cursors behavior. If True, the pgExpress Driver will retain the cursor for the previous SELECT statement and return it if a previous dataset record is acessed again, instead of running the whole query again (dbExpress behavior). No matter how many times the same query is run again, always the same result set is returned (from the internal cache). NoteThis setting will be ignored if Fetch Cursors are being used. | |||||
BlobWriting |
|
If True, the pgExpress Driver will allow Blob (Large Objects) writes. Blob writing is a delicate issue; just activate this setting if you read Section 4.7, "Large Objects (BLOBs)". |
On older pgExpress Driver versions, these parameters used go into the dbxdrivers(.ini) file. But since version 1.5, they go after the Database parameter in the dbxconnections(.ini) parameter, separated by colons. On Delphi 7 or later, they can also go on the Custom String parameter.
Example 2.2. Special Parameters usage
[OfficeServer] Database = sales,TextAsBlob = False, Int8Mode = AsString [PGEConnection] Database = sales, ArrayAsString = False, QuoteObjects = True, GetExtendedFieldsInformation = False
On Delphi 7, using the new Custom String section (works the same way as above, but this section was made for using with special params):
Example 2.3. Special parameters usage with the Custom String param
[PGEConnection] Database = sales Custom String = ArrayAsString = False, QuoteObjects = True, GetExtendedFieldsInformation = False
If you don't want to use any special parameters (as most users will), specify the Database parameter alone, normally:
[OfficeServer] Database = sales
Of course these parameters could also go in the TSQLConnections. Params property as any other dbxconnections(.ini) parameter:
SQLConnection1.Params.Add('Database = sales,TextAsBlob = False,Int8Mode = AsString');
Or on Delphi 7, using the new Custom String section:
SQLConnection1.Params.Add('Custom String = ArrayAsString = False, QuoteObjects = True, GetExtendedFieldsInformation = False ');
Unhappily, due to the way dbExpress™ was designed on Delphi 6 and Kylix 1/2/3 , there can't be real custom parameters from VCL/CLX to the driver, so misusing the Database parameter was a "hack" that we had to do to allow custom params. Even on Delphi 7 only a single Custom parameter ("Custom String") can be specified.
"Custom Info" are methods that can be used to retrieve information from the driver that otherwise could not be retrieved. They will only work with Delphi 7 or newer which implements the eConnCustomInfo interface.
For easy implementation of these interfaces, please use the corresponding functions from the pgeDriverUtils.pas unit.
Table 2.4. Custom Info Interfaces
Information | Meaning | Function |
---|---|---|
GetPGVersion | Returns the PostgreSQL server version as a float. | GetPGVersion |
TTY | Allows setting or retrieving file or TTY for optional debug output from the backend server. Set before connecting. | GetTTY |
BackendPID | Will retrieve the PID of the process of the connection in the backend server. | GetBackendPID |
Online | Will return True if a connection to the server exists. This implements the eCommOnline interface. | GetOnline |