|
Using Amazon Redshift data access provider with UniDAC in Delphi |
|
|
|
Using Amazon Redshift data access provider with UniDAC in Delphi |
|
This article provides a brief overview of the Amazon Redshift data access provider for UniDAC used to establish a connection to Amazon Redshift from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.
Main features of Amazon Redshift data access provider are:
The full list of provider features can be found on the UniDAC features page.
Both Professional and Standard Editions of UniDAC include the Amazon Redshift provider.
The Amazon Redshift provider and the PostgreSQL provider are included in one package (pgproviderXX.bpl), therefore, they are installed together.
When an application was built without runtime packages (Link with runtime packages set to False in Project Options), you do not need to deploy any BPL files with it. For more information, see Deployment.
Note that UniDAC Trial requires deployment of additional BPL files regardless of Link with runtime packages.
For more information about deployment of UniDAC-based applications, please, refer to the common Deployment topic.
Though UniDAC is components that provide unified interface to work with different database servers, it also lets you tune behaviour for each server individually. For thin setup of a certain database server, UniDAC provides server-specific options. These options can be applied to such components as TUniConnection, TUniQuery, TUniTable, TUniStoredProc, TUniSQL, TUniScript via their SpecificOptions property. SpecificOptions is a string list. Therefore you can use the following syntax to assign an option value:
UniConnection.SpecificOptions.Values['ConnectionTimeout'] := '15';Below you will find the description of allowed options grouped by components.
| Option name | Description |
|---|---|
| ApplicationName | The name of a client application. The default value is the name of the executable file of your application. |
| ConnectionTimeout | The time to wait for a connection to open before raising an exception. |
| HttpAuthenticationType |
Specifies the HTTP authentication method used to access a secure directory. Supported values: atBasic
Uses Basic authentication, sending a username and password in the HTTP request.
atBearerToken
Uses a Bearer token, sending an access token in the HTTP request.
|
| HttpPassword | Specifies the password for HTTP authorization. |
| HttpToken | Specifies the Bearer token sent in the HTTP request for Bearer token authentication. |
| HttpTrustServerCertificate | Specifies whether or not the driver should trust the server certificate when connecting to the server. The default value is False—the driver won't trust the server certificate and will verify validity of the server certificate instead. If set to True, the driver will trust the server certificate. |
| HttpUrl | Specifies the URL of the PHP tunneling script. |
| HttpUsername | Specifies the username for HTTP authorization. |
| IPVersion |
Specifies the Internet Protocol Version. Supported values: ivIPBoth
Specifies that either Internet Protocol Version 6 (IPv6) or Version 4 (IPv4) will be used.
ivIPv4 (default)
Specifies that Internet Protocol Version 4 (IPv4) will be used.
ivIPv6
Specifies that Internet Protocol Version 6 (IPv6) will be used.
Note: When the TIPVersion property is set to ivIPBoth, a connection attempt will be made via IPv6 if it is enabled on the operating system. If the connection attempt fails, a new connection attempt will be made via IPv4.
|
| MessagesCharset | Specifies the character set that will be used to transfer error messages from the server to the client. |
| MultipleConnections | Enables or disables the creation of an additional internal connection for TUniQuery, when necessary. The default value is True. |
| ProxyHostname | Specifies the host name or IP address to connect to the proxy server. |
| ProxyPassword | Specifies the password for the proxy server. |
| ProxyPort | Specifies the port for a TCP/IP connection with the proxy server. |
| ProxyUsername | Specifies the username for the proxy server. |
| SSLCACert | The path to the certificate authority file. |
| SSLCert | The path to the certificate file. |
| SSLChipherList | The list of allowable ciphers to use for SSL encryption. |
| SSLKey | The path to the key file. |
| SSLKeyPassword | The password for the private client key. Leave it empty if the client key is not encrypted. |
| SSLMode | This option determines whether or with what priority an SSL connection will be negotiated with the server. |
| UseHttp | The UseHttp option enables the use of HTTP tunneling to connect to the server. The default value is False. |
| UuidWithBraces | Specifies whether the values of UUID fields are returned with braces. The default value is True. |
| Option name | Description |
|---|---|
| CommandTimeout | The time to wait for a statement to be executed. |
| UnpreparedExecute | If True, the simple execute is used for SQL statement. Statement is not prepared before execute. It allows to add multiple statements separated by semicolon to the SQL property. |
| UseParamTypes | Set this option to True to disable automatic detection of parameter types. When this option is True, data types of parameters are set basing on the DataType property. When this option is False, data types of the parameters are detected by server automatically. |
| Option name | Description |
|---|---|
| AutoDeleteBlob | If True (the default value), the BLOBs are deleted from database automatically when a record that holds these BLOBs' OIDs is deleted from dataset. |
| CacheBlobs | If True (the default value), then local memory buffer is allocated to hold a copy of the BLOB content. |
| CommandTimeout | The time to wait for a statement to execute. |
| CursorWithHold | When this option is False (default), an active transaction is required to open a query in FetchAll=False mode. If there is no active transaction, UniDAC opens additional internal connection and starts transaction on this connection.
When this option is True, UniDAC uses DECLARE CURSOR ... WITH HOLD statement to open the query. In this case, no active transaction is required but this may take additional server resources. |
| DeferredBlobRead | If True, all BLOB values are fetched only when they are explicitly requested. Otherwise entire record set with any BLOB values is returned when dataset is opened. Whether BLOB values are cached locally to be reused later is controlled by the CacheLobs option. |
| ExtendedFieldsInfo | If True, an additional query is performed to get information about returned fields and tables they belong to. The default value is False. |
| FetchAll |
If True, all records of a query are requested from database server when the dataset is being opened. If False, records are retrieved when a data-aware component or a program requests it. The default value is True. |
| KeySequence | Specifies the name of a sequence that will be used to fill in a key field after a new record is inserted or posted to the database. |
| OIDAsInt | If True, OID fields are mapped on TIntegerField. If False, values of OID fields are treated as large objects' OID, and these fields are mapped on TBlobField. |
| PrefetchRow | Specifies the number of rows to prefetch when executing a query. |
| SequenceMode |
Set the SequenceMode property to specify which method is used internally to generate sequenced field. The following values are allowed for this property: smInsert
New record is inserted into the dataset with the first key field populated with a sequenced value.
Application may modify this field before posting the record to the database.
smPost
Database server populates key field with a sequenced value when application posts the record to the
database. Any value put into the key field before post will be overwritten.
|
| UnknownAsString | If True, all Amazon Redshift data types that are fetched as text, and don't have limited field size, are mapped on TStringField with default size 8192. If False, such types are mapped on TMemoField. The TEXT data type is always mapped on TMemoField regardless of this option. |
| UnpreparedExecute | If True, the simple execute is used for SQL statement. Statement is not prepared before execute. It allows to add multiple statements separated by semicolon to the SQL property. |
| UseGeneratedColumns | Enables support for generated columns in Amazon Redshift. To use generated columns, set UseGeneratedColumns to True.
Note: To use this option, set ExtendedFieldsInfo to True. |
| UseParamTypes | Set this option to True to disable automatic detection of parameter types. When this option is True, data types of parameters are set basing on the DataType property. When this option is False, data types of the parameters are detected by server automatically. |
The TUniScript, TUniDump, TUniLoader components have no Amazon Redshift-specific options.
The following table lists the constants for mapping Amazon Redshift data types to Delphi data types. See the Data Type Mapping tutorial for more information.
| Constant | Description |
|---|---|
| pgBigInt | Maps bigint to Delphi data types. |
| pgBigSerial | Maps bigserial to Delphi data types. |
| pgBit | Maps bit to Delphi data types. |
| pgBitVarying | Maps bit varying to Delphi data types. |
| pgBoolean | Maps boolean to Delphi data types. |
| pgBytea | Maps bytea to Delphi data types. |
| pgCharacter | Maps character to Delphi data types. |
| pgCharacterVarying | Maps character varying to Delphi data types. |
| pgDate | Maps date to Delphi data types. |
| pgDoublePrecision | Maps double precision to Delphi data types. |
| pgInteger | Maps integer to Delphi data types. |
| pgMoney | Maps money to Delphi data types. |
| pgNumeric | Maps numeric to Delphi data types. |
| pgReal | Maps real to Delphi data types. |
| pgSerial | Maps serial to Delphi data types. |
| pgSmallint | Maps smallint to Delphi data types. |
| pgText | Maps text to Delphi data types. |
| pgTime | Maps time to Delphi data types. |
| pgTimeStamp | Maps timestamp to Delphi data types. |
| pgTimeStampWithTimeZone | Maps timestamp with time zone to Delphi data types. |
| pgTimeWithTimeZone | Maps time with time zone to Delphi data types. |
| pgUUID | Maps uuid to Delphi data types. |