Connection String Attributes
PreviousNext

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.
  1. Search for odbcad32 in the start menu. Click to launch it.
  2. Configure desired properties in the grid and check Query Preview to make sure everything is correct.
  3. Click on [Copy Connection String] button to generate full connection string.

Connection String Examples

Syntax: (Direct Connection string without DSN)
Driver={ZappySys Amazon S3 JSON Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;] 
Syntax: (Using DSN)
DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]

Connection String Attributes

AttributeLabelDescription
AccessMode
(Setting of JsonConfigurationParams)
AccessModeDefines how to read data from file path/URL or direct string

Available Options

Option Description
DirectPath DirectPath
DirectValue DirectValue
ConnectionStringFile
(Setting of JsonConfigurationParams)
ConnectionStringFile
Data
(Setting of JsonConfigurationParams)
DirectValueDirect String you like to use as a datasource. This attribute always takes precedence if both Data and Src defined (i.e. File/Url). You can also use AccessMode=1 along with Data='...' in WITH clause to use Direct String rather than URL/File.
EnableCustomReplace
(Setting of JsonConfigurationParams)
EnableCustomReplaceEnables custom search / replace in the document text after its read from the file/url or direct string. This replace operation happens before its parsed. This option can be useful for custom escape sequence in source document which is causing issue in the parser. You can replace such unwanted characters fore parser starts parsing the text.
IgnoreContentLengthHeaderForGzipResponse
(Setting of JsonConfigurationParams)
IgnoreContentLengthHeaderForGzipResponse
ReplaceWith
(Setting of JsonConfigurationParams)
ReplaceWithString you like to replace with (Only valid when EnableCustomReplace option is turned on). If you added --regex or --regex-ic at the end of your SearchFor string then ReplaceWith can use special placeholders (i.e. $1, $2...) based on regular expression groups. For example you SearchFor=(\w+)(@\w+.com) to search for emails then to mask emails you can something like this for ReplaceWith = ****$2 (where $2 is domain part and $1 is before @)
SearchFor
(Setting of JsonConfigurationParams)
SearchForString you like to search for (Only valid when EnableCustomReplace option is turned on). If you want to enable Regular Expression pattern search then add --regex or --regex-ic (for case-insensitive search)  at the end of your search string (e.g. ORDER-\d+--regex OR ORDER-\d+--regex-ic (case-insensitive search) )
2D Array Settings
ArrayTransColumnNameFilter
(Setting of JsonConfigurationParams)
Array Transformation ColumnName FilterFilter expression to use to extract column names for array transformation.
ArrayTransCustomColumns
(Setting of JsonConfigurationParams)
Columns Names for Array TransformationWhen you have 2D array but don't have column list specified in a separate array then use specify column names here. Use comma separated list (e.g col1,col2,col3 ). Column name Order must match value order.
ArrayTransEnableCustomColumns
(Setting of JsonConfigurationParams)
Use Custom Columns for Array TransformationWhen you have 2D array but don't have column list specified in a separate array then use this option (e.g. { arr: [[10,11],[21,22]] } ). If you selected Column less array or JSON Lines option then this property means Column Names coming from First Line of array.
ArrayTransformType
(Setting of JsonConfigurationParams)
Array Transformation TypeArray Transformation you want to apply. Useful for case when you have 2-Dimensional arrays with rows/columns in separate arrays.

Available Options

Option Description
None None
TransformSimpleTwoDimensionalArray Simple 2-dimensional array (e.g. {cols:[..], rows:[[..],[..]]} )
TransformComplexTwoDimensionalArray Complex 2-dimensional array  (e.g. {cols:[{..},{..}], rows:[{f:[..]},{f:[..]}] )
TransformKeyValuePivot Key/Value to Columns
TransformMultipleColumnsExpressions Multiple columns using expressions
TransformColumnslessArray Columnless array (e.g. [[..],[..]] )
TransformJsonLineArray JSON Lines - Single Dimension Array(s) (i.e. [..][..] )
TransformPivotColumnlessArray Pivot - Columnless array (e.g. [..] )
ArrayTransRowValueFilter
(Setting of JsonConfigurationParams)
Array Transformation Row FilterFilter expression for row values (Not applicable for simple array transformation).
Cache Settings
CacheEntryTtl
(Setting of JsonConfigurationParams)
Cache entry lifetime, secThis property indicates how many seconds data is kept in cache (in case caching is enabled)
CacheFileLocation
(Setting of JsonConfigurationParams)
Cache file locationThis property allows you to set cache file location (you have to set Cache persistence mode to Storage)
CacheStorage
(Setting of JsonConfigurationParams)
Cache storageSpecifies cache storage - in-memory only or local data file

Available Options

Option Description
Memory Memory
File File
CachingMode
(Setting of JsonConfigurationParams)
Cache modeEnables or disables cache usage for metadata / data

Available Options

Option Description
Disabled Caching disabled
Metadata Cache metadata
All Cache all
Encoding / Culture Settings
CharacterSet
(Setting of JsonConfigurationParams)
CharacterSetCharacter set for text (e.g. utf-8 )
Encoding
(Setting of JsonConfigurationParams)
EncodingEncoding of source file

Available Options

Option Description
Default Default
ASCII ASCII
UTF8 UTF-8
UTF16 UTF-16 LE (i.e. Unicode Little Endian)
UTF32 UTF-32
UTF8WithoutBOM UTF-8 Without BOM
UTF32WithoutBOM UTF-32 Without BOM
UTF7 UTF-7
UTF7WithoutBOM UTF-7 Without BOM
UTF16WithoutBOM UTF-16 Without BOM
BigEndian UTF-16 BE (i.e. Unicode Big Endian)
BigEndianWithoutBOM UTF-16 BE Without BOM
Error Handling Settings
ConvErrorHandlingMode
(Setting of JsonConfigurationParams)
Error handling for data conversionDetermines whether some data conversion errors are ignored or stop query processing.

Available Options

Option Description
ThrowError Stop execution on data conversion error
Ignore Ignore data conversion errors
Filter Settings
ElementScopeSeparator
(Setting of JsonConfigurationParams)
Child scope separatorScope separator for nested properties (Child properties under selected filter). For example you may have $.contacts[*] as your filter and for each contact you may have nested node e.g AddressInfo / ZipInfo / Zip. By default column name will be AddressInfo.ZipInfo.Zip (Default separator is dot) but if you want column name like AddressInfo_ZipInfo_Zip in the output then change this property to any custom value (e.g. _). If you dont set this property then dot will be used.
FilterForParentColumnsWithArrayType
(Setting of JsonConfigurationParams)
Filter for parent columns of array typeSet this option to true if you want to output parent columns which are of array type. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property
IncludeParentColumns
(Setting of JsonConfigurationParams)
Include parent columnsIf this option is enabled then parent properties of non-array type will be output along with regular row attributes.
IncludeParentColumnsWithArrayType
(Setting of JsonConfigurationParams)
Include parent columns of array typeSet this option to true if you want to output parent columns which are array. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property
IncludeParentWhenChildNull
(Setting of JsonConfigurationParams)
IncludeParentWhenChildNullBy default child and parent information is not included in the output if children not found for specified expression. For example if you want to extract all orders from all customers nodes then you can type $.Customers[*].Orders[*]. This will fetch all orders from all customers. By default customers records without orders wont be included in the output. If you want to include those customers where orders not found then check this option (Output null information for order attributes). This behavior is similar to LEFT OUTER JOIN in SQL (Left side is parent, right side is child). This option is ***resource intensive*** so only check if you really care about this behavior.
LevelSeparator
(Setting of JsonConfigurationParams)
Parent scope separatorProperty level separator used in generated property name (separator for outer properties - Above selected filter node). Use this if default separator is producing duplicate property name which is conflicting with existing name.
LevelsToScan
(Setting of JsonConfigurationParams)
Maximum levels to scanSpecifies how many nested levels should be scanned. 0 means no limitations to scan.
ParentColumnPrefix
(Setting of JsonConfigurationParams)
Parent prefixValue of this parameter will be used as prefix of parent element. Empty means default prefix will be used.
ThrowErrorWhenNoMatch
(Setting of JsonConfigurationParams)
Throw error on Filter no-matchesDetermines whether error is thrown in case data table name element is missing from response or no data row is returned.
Log Settings
LoggingLevel
(Setting of JsonConfigurationParams)
Log levelSets level of messages logged

Available Options

Option Description
None None
Error Error
Debug Debug
Info Info
LogPath
(Setting of JsonConfigurationParams)
Log file locationSets location for the log file (empty value means no log written)
LogSize
(Setting of JsonConfigurationParams)
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)
GroupName
(Setting of ColumnsMetadata)
Group name
OverrideMode
(Setting of ColumnsMetadata)
OverrideMode
Metadata Settings
MetaScanMode
(Setting of JsonConfigurationParams)
Metadata Scan Mode / MultiplierMetadata 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 JsonConfigurationParams)
Treat string as ASCIIWhen this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode)
RowsToScanForMetadata
(Setting of JsonConfigurationParams)
Number of rows scanned to collect metadataIf metadata isn't defined for query the driver will try to scan some rows to guess fields parameters
TreatBlankBoolAsNull
(Setting of JsonConfigurationParams)
Treat blank BOOLEAN value as NULL
TreatBlankDateAsNull
(Setting of JsonConfigurationParams)
Treat blank DATE/TIME value as NULL
TreatBlankNumberAsNull
(Setting of JsonConfigurationParams)
Treat blank NUMERIC value as NULL
Other Settings
DateParseHandling
(Setting of JsonConfigurationParams)
Date data typeSpecifies how dates are parsed.

Available Options

Option Description
None Keep date as string
DateTime Convert to DateTime (Timezone lost)
DateTimeOffset Convert to DateTimeOffset (Preserve Time zone)
EnableBigNumberHandling
(Setting of JsonConfigurationParams)
Enable Big Number HandlingWhen this option is checked, sytem allows to parse large number (Higher than Int64) in JSON document (i.e. { id: 76000009353950433247780 } ). If you do not turn on this option then it may throw error if large number is encountered. This option is SLOW becuase it has to perform additional checks in order to avoid Large number overflow error so do not turn on unless you getting Large number error.
FloatParseHandling
(Setting of JsonConfigurationParams)
FloatParseHandlingSpecifies how decimal values are parsed when reading JSON. Change this setting to Decimal if you like to have large precision / scale.

Available Options

Option Description
Double Default (Double [~15-17 digits])
Decimal Decimal (High Precision / Scale [~28-29 digits] )
Recursive
(Setting of JsonConfigurationParams)
Scan files recursivelyScan files recursively when you read from local  files (i.e. include files from sub folders).
Output Settings
DateFormatString
(Setting of JsonConfigurationParams)
Date formatSpecifies date format
DefaultRowLimit
(Setting of JsonConfigurationParams)
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.
EnableArrayFlattening
(Setting of JsonConfigurationParams)
Enable Array FlatteningEnables deep array flattening for selected filtered hierarchy. When you turn on this property it will flatten each property of each array item and expose as column (e.g. If you have Filter set as $.customers[*] and for each customer you have an array of Addresses then you may see output columns like Addresses.1.City, Addresses.1.State, Addresses.2.City, Addresses.2.State .... Addresses.N.City, Addresses.N.State). You can control how many array items you want to flatten by setting MaxArrayItemsToFlatten property.
EnablePivot
(Setting of JsonConfigurationParams)
Enable PivotWhen this property is true then Column is converted to Row. Pivoted names will appear under  Pivot_Name column and values will appear under Pivot_Value field.
EnableRawOutputMode
(Setting of JsonConfigurationParams)
Enable Raw Document Output ModeOutput as raw JSON/XML document rather than parsing individual fields. This option is helpful if you have documents stored in a file and you want to pass them downstream as raw JSON string rather than parsing into columns.
EnableRawOutputModeSingleRow
(Setting of JsonConfigurationParams)
Enable Raw Document - Single Row (Disable parser, allow any format)Enable Raw Document Output Mode for any format (i.e. XML, Html, Text, Json). Unlike other option EnableRawOutputMode, this option doesn't invoke parser to extract documents by finding row terminator. It will source string as row value in single row/ single column.
ExcludeColumns
(Setting of JsonConfigurationParams)
Excluded propertiesList of comma separated element names which you want to exclude from output. Specify parent element name to exclude all child nodes.
FileCompressionType
(Setting of JsonConfigurationParams)
FileCompressionTypeCompression format for source file (e.g. gzip, zip)

Available Options

Option Description
None None
GZip GZip
Zip Zip
TarGZip TarGZip
IndentOutput
(Setting of JsonConfigurationParams)
Indent outputIndent JSON output to improve readability.
MaxArrayItemsToFlatten
(Setting of JsonConfigurationParams)
Max Array Items To FlattenMaximum number of array items to flatten. inner array flattening. Adjust this property to control how many columns being generated. This option is ignored if you set EnableArrayFlattening=false
RawOutputDataRowTemplate
(Setting of JsonConfigurationParams)
RawOutputDataRowTemplate
RawOutputExtractMode
(Setting of JsonConfigurationParams)
RawOutputExtractMode

Available Options

Option Description
None None
Json Json
Xml Xml
Regex Regex
RawOutputFilterExpr
(Setting of JsonConfigurationParams)
RawOutputFilterExprUse this property to define one or multiple expressions (i.e. JsonPath,XPath,Regex) to extract information from source. This property is only valid when EnableRawOutputModeSingleRow=true and RawOutputDataRowTemplate is set.
SaveContentAsBinary
(Setting of JsonConfigurationParams)
SaveContentAsBinary
Query Engine Temp Storage
TempStorageMode
(Setting of JsonConfigurationParams)
Intermediate results storageUse [Disk] if number of resulting rows or row data size is large

Available Options

Option Description
Memory Memory
Disk Disk
Request Settings
DataPath
(Setting of JsonConfigurationParams)
Data Source (URL or File Path)Data source URL or File Path (e.g. http://abc.com/api/get --or-- c:\data\cust01.json  --or-- c:\data\cust*.json ). If data source is file then wildcard pattern can be also used to read multiple files (e.g. c:\data\cust*.json will read all files that match wildcard)
Filter
(Setting of JsonConfigurationParams)
FilterSpecifies expression to filter data. Usually this is Array path from which you like to get all records. Example:  $.Customers[*] will get all customers from Customer Array from your document and $.Customers[*].Orders[*] will get all orders for all customers.


Copyrights reserved. ZappySys LLC.