Connection String Attributes

Introduction

This page lists all possible attributes you can use in ConnectionString or WITH clause of SQL Query. If you are not sure how to write correct connection string then perform following steps.

Search for odbcad32 in the start menu. Click to launch it.
Configure desired properties in the grid and check Query Preview to make sure everything is correct.
Click on [Copy Connection String] button to generate full connection string.

Connection String Examples

Syntax: (Direct Connection string without DSN)

Driver={ZappySys Api Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]

Syntax: (Using DSN)

DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]

Connection String Attributes

Attribute

Label

Description

ConnectionStringFile
(Setting of ApiConfigurationParams)

ConnectionStringFile

Pwd
(Setting of ApiConfigurationParams)

Pwd

Maps to ODBC 'Password'. Default: Basic auth Password. For OAuth/modern auth without Password, map Uid into another field (e.g. ClientSecret) by setting that field's value to {$pwd$}.

Uid
(Setting of ApiConfigurationParams)

Uid

Maps to ODBC 'User Name'. Default: Basic auth UserName. For OAuth/modern auth without UserName, map Uid into another field (e.g. ClientId) by setting that field's value to {$uid$}.

Cache Settings

CacheEntryTtl
(Setting of ApiConfigurationParams)

Cache entry lifetime, sec

This property indicates how many seconds data is kept in cache (in case caching is enabled)

CacheFileLocation
(Setting of ApiConfigurationParams)

Cache file location

This property allows you to set cache file location (you have to set Cache persistence mode to Storage)

CacheStorage
(Setting of ApiConfigurationParams)

Cache storage

Specifies cache storage - in-memory only or local data file

Available Options

Option	Description
Memory	Memory
File	File

CachingMode
(Setting of ApiConfigurationParams)

Cache mode

Enables or disables cache usage for metadata / data

Available Options

Option	Description
Disabled	Caching disabled
Metadata	Cache metadata
All	Cache all

Error Handling Settings

ConvErrorHandlingMode
(Setting of ApiConfigurationParams)

Error handling for data conversion

Determines whether data conversion errors are ignored or interrupt execution.

Available Options

Option	Description
ThrowError	Stop execution on data conversion error
Ignore	Ignore data conversion errors

TruncationHandlingMode
(Setting of ApiConfigurationParams)

Error handling for data truncation

Determines whether data truncation errors are ignored or interrupt execution. This setting is only used when custom META is supplied or ConvErrorHandlingMode is set to Ignore errors.

Available Options

Option	Description
Truncate	Truncate data (default)
ThrowError	Stop with error on data truncation
Ignore	Ignore data truncation errors

Log Settings

LoggingLevel
(Setting of ApiConfigurationParams)

Log level

Sets level of messages logged

Available Options

Option	Description
None	None
Error	Error
Debug	Debug
Info	Info

LogPath
(Setting of ApiConfigurationParams)

Log file location

Sets location for the log file (empty value means no log written)

LogRollingMode
(Setting of ApiConfigurationParams)

Log rolling mode

Sets log file rolling mode. Log file may be restricted by log file size or by number of latest log files (one log file per day).

Available Options

Option	Description
Size	Size (MB)
Days	Days

LogSize
(Setting of ApiConfigurationParams)

Maximum log file size (Mb)

Sets limit for log file size (0 means no limit, old log entries will be discarded if size limit is reached)

MaxLogFiles
(Setting of ApiConfigurationParams)

Number of log files to keep (0 - keep all, actual for LogRollingMode=Days)

Set a number to keep N latest log files. Actual for LogRollingMode=Days. Set 0 to keep all files.

Metadata Settings

MetaScanMode
(Setting of ApiConfigurationParams)

Metadata Scan Mode / Multiplier

Metadata guess mode / multiplier to detect length by scanning rows defined in

Available Options

Option	Description
Auto	Auto
Strict	Strict - Exact length
Guess2x	Guess2x - 2 times bigger
Guess3x	Guess3x - 3 times bigger
Guess4x	Guess4x - 4 times bigger
TreatAsUnicodeString	Set all columns as string
Guess10x	Guess10x - 10 times bigger
TreatStringAsMaxLength	Set string columns with MAX Length - i.e. DT_WSTR(4000)
TreatStringAsBlob	Set string columns as BLOB - i.e. DT_NTEXT

MetaTreatStringAsAscii
(Setting of ApiConfigurationParams)

Treat string as ASCII

When this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode)

RowsToScanForMetadata
(Setting of ApiConfigurationParams)

Number of rows scanned to collect metadata

If metadata isn't defined for query the driver will try to scan some rows to guess fields parameters

TreatBlankBoolAsNull
(Setting of ApiConfigurationParams)

Treat blank BOOLEAN value as NULL

TreatBlankDateAsNull
(Setting of ApiConfigurationParams)

Treat blank DATE/TIME value as NULL

TreatBlankNumberAsNull
(Setting of ApiConfigurationParams)

Treat blank NUMERIC value as NULL

Other Settings

RegexReplaceListForSql
(Setting of ApiConfigurationParams)

List of regular expressions for SQL modification

Regular expressions and replacement patterns are applied to SQL request executed by the driver, for instance: (?<column>\w+)=True||(?<column>\w+)=False|~|${column}=1||${column}=0 will replace all instances of SomeColumn=True with SomeColumn=1 and all instances of SomeColumn=False with SomeColumn=0 in all SQL requests. You can use regex group name as ${some-group-name} (only if defined as (?<some-group-name>..) anywhere in the regex) or use regex group number in the replacement string (e.g. $1, $2..). Regex pattern match is case-insensitive.

SqlEngineMode
(Setting of ApiConfigurationParams)

SQL Engine Mode

Defines how sql is executed (i.e. at server side or at client side). If you choose ServerSide then SQL must be compatible with provider's SQL standard (Do not use ZappySys syntax / functions). If API connector doesn't support serverside SQL then it will throw error. After you set this to ServerSide make sure to add #ClientSide prefix to queries which are not treated as Server side (e.g. #ClientSide select * from list_datasets)

Available Options

Option	Description
Default	Default
ClientSide	ClientSide
ServerSide	ServerSide

Output Settings

DefaultRowLimit
(Setting of ApiConfigurationParams)

Default data row limit (i.e. LIMIT / TOP clause)

Use this option if you like to limit number of rows fetched by any query. If you use LIMIT clause in the query then this option is ignored.

OutputOption
(Setting of ApiConfigurationParams)

Default Output Option for Insert/Update/Delete

By default Output=1 for WITH clause in INSERT/UPDATE/DELETE when not specified. Use this option to control default option. This is useful when ODBC Bulk INSERT invoked by some ETL Tools where you cannot supply WITH clause and like to mimic Output=0 so some errors are thrown if some insert records failed. By default when Output=1 then errors not thrown rather displayed in the output as rows.

Query Engine Temp Storage

TempStorageMode
(Setting of ApiConfigurationParams)

Intermediate results storage

Use [Disk] if number of resulting rows or row data size is large

Available Options

Option	Description
Memory	Memory
Disk	Disk

AccessToken
(Setting of ISqlDataConnection)

AccessToken

AuthName
(Setting of ISqlDataConnection)

AuthName

AuthScheme
(Setting of ISqlDataConnection)

AuthScheme

CacheToken
(Setting of ISqlDataConnection)

CacheToken

ClientId
(Setting of ISqlDataConnection)

ClientId

ClientSecret
(Setting of ISqlDataConnection)

ClientSecret

ConfigFile
(Setting of ISqlDataConnection)

ConfigFile

ConfigText
(Setting of ISqlDataConnection)

ConfigText

ConnectionString
(Setting of ISqlDataConnection)

ConnectionString

ConnectionStringFile
(Setting of ISqlDataConnection)

ConnectionStringFile

ConnectionStringFull
(Setting of ISqlDataConnection)

ConnectionStringFull

ConnectionStringNonDefaultsOnly
(Setting of ISqlDataConnection)

ConnectionStringNonDefaultsOnly

ConnectionStringNonDefaultsOnlyFull
(Setting of ISqlDataConnection)

ConnectionStringNonDefaultsOnlyFull

DisableCustomUrl
(Setting of ISqlDataConnection)

DisableCustomUrl

DisableSockProxySupport
(Setting of ISqlDataConnection)

DisableSockProxySupport

DisableTest
(Setting of ISqlDataConnection)

DisableTest

DisableThirdPartWebRequest
(Setting of ISqlDataConnection)

DisableThirdPartWebRequest

ExtraHeaders
(Setting of ISqlDataConnection)

ExtraHeaders

HasInnerConnectionSupport
(Setting of ISqlDataConnection)

HasInnerConnectionSupport

MetaBody
(Setting of ISqlDataConnection)

MetaBody

MetaContentType
(Setting of ISqlDataConnection)

MetaContentType

Available Options

Option	Description
None	None
Json	Json
Xml	Xml
Regex	Regex

MetaFields
(Setting of ISqlDataConnection)

MetaFields

MetaFilters
(Setting of ISqlDataConnection)

MetaFilters

MetaHeaders
(Setting of ISqlDataConnection)

MetaHeaders

MetaMethod
(Setting of ISqlDataConnection)

MetaMethod

MetaTemplate
(Setting of ISqlDataConnection)

MetaTemplate

MetaUrl
(Setting of ISqlDataConnection)

MetaUrl

Parameters
(Setting of ISqlDataConnection)

Parameters

Password
(Setting of ISqlDataConnection)

Password

PreferProxyUrl
(Setting of ISqlDataConnection)

PreferProxyUrl

Provider
(Setting of ISqlDataConnection)

Provider

Available Options

Option	Description
Notset	Notset
Custom	Custom Connector (i.e. Search Online -OR- Use Local File)
OData	OData
ZohoCrm	Zoho CRM
AmazonMws	Amazon MWS (Marketplace Webservice) *DEPRECATED (Use SP-API)*
GoogleSheets	Google Sheets
GoogleBigQuery	Google BigQuery
Zendesk	Zendesk
Jira	Jira (Atlassian)
SharePoint	SharePoint Online
GoogleDrive	Google Drive
OneDrive	OneDrive
HubSpot	HubSpot
DropBox	Dropbox
GoogleCalendar	Google Calendar
ElasticSearch	ElasticSearch
Stripe	Stripe
Zoom	Zoom
ManagedEngineServiceDeskPlus	ManageEngine ServiceDesk Plus (By Zoho)
Youtube	Google Youtube
AmazonAds	Amazon Ads
Shopify	Shopify
OutlookMail	Outlook Mail (Office 365)
AzureDevOps	Azure DevOps (TFS Online)
CosmosDB	Cosmos DB
MailChimp	Mailchimp
ServiceNow	ServiceNow
PowerBI	PowerBI
FastSpring	FastSpring
SalesIQ	Zoho SalesIQ
Smartsheet	Smartsheet
AmazonSP	Amazon Selling Partner (SP-API)
Asana	Asana
GoogleAds	Google Ads
GoogleSearchConsole	Google Search Console
Gmail	Gmail

RefreshToken
(Setting of ISqlDataConnection)

RefreshToken

RefreshTokenFilePath
(Setting of ISqlDataConnection)

RefreshTokenFilePath

RetainSameConnection
(Setting of ISqlDataConnection)

RetainSameConnection

RetryCountMax
(Setting of ISqlDataConnection)

RetryCountMax

RetryMessageList
(Setting of ISqlDataConnection)

RetryMessageList

RetryMode
(Setting of ISqlDataConnection)

RetryMode

Available Options

Option	Description
None	Do not retry
RetryAny	Retry for any web, io, timeout errors
RetryWhenStatusCodeMatch	Retry for throttling or matching HTTP status code(s) (e.g. 429\|501)
RetryWhenMessageMatch	Retry if error message contains specified string(s) (e.g. nessage1\|message2)

RetryMultiplyWaitTime
(Setting of ISqlDataConnection)

RetryMultiplyWaitTime

RetryStatusCodeList
(Setting of ISqlDataConnection)

RetryStatusCodeList

RetryWaitTimeMs
(Setting of ISqlDataConnection)

RetryWaitTimeMs

ReturnUrl
(Setting of ISqlDataConnection)

ReturnUrl

ServiceUrl
(Setting of ISqlDataConnection)

ServiceUrl

StreamReadWriteTimeoutMs
(Setting of ISqlDataConnection)

StreamReadWriteTimeoutMs

SupportDualProxyMode
(Setting of ISqlDataConnection)

SupportDualProxyMode

Timeout
(Setting of ISqlDataConnection)

Timeout

TrustedDomains
(Setting of ISqlDataConnection)

TrustedDomains

Url
(Setting of ISqlDataConnection)

Url

UseEmbeddedConfig
(Setting of ISqlDataConnection)

UseEmbeddedConfig

UserName
(Setting of ISqlDataConnection)

UserName

UseThirdPartyWebRequest
(Setting of ISqlDataConnection)

UseThirdPartyWebRequest

UseWebProxy
(Setting of ISqlDataConnection)

UseWebProxy