ODBC Input Build Tags

Defines the block for this build description.

For example:

build {

...

}

Deprecated as of Workbench 7.1. Allows a timestamp column in the ODBC source to be recognized as a datetime column. Without this tag, an SQL datetime field is treated as a date type, discarding the time portion of the field.

Datetime columns are now recognized without using this attribute.

For example:

odbc-input {

dsn "bugzilla"

query "select \"lastdiffed\" from \"bugs\" LIMIT 10"

allow-datetime

}

Binds the parameter value to the variable in the SQL query.

If the driver does not support describing parameters, Spectre ODBC reader hands off the parameters as strings (similar to Integrator).

TIP: If you know the SQL where clause is expecting a numeric, use Spectre's parse_integer() function to change each string data-type parameter value into an integer value . For example, change bind "$(INTNUMB)" to bind `parse_integer(param("INITNUMB"))`.

For example:

build {

take-parameter "INTNUMB" default="11167800"

odbc-input limit-rows=100 {

dsn "star-prod"

query """

SELECT INTNUMB ,SEX ,BIRTHDATE

FROM AGEDEMO WHERE INTNUMB = ? """ {

bind "$(INTNUMB)"

}

}

output "/temp/import_star_agedemo.cbase"

}

Assigns a calendar and period type to the column. Valid calendars are:

• standard—Gregorian calendar; period type options are year, year-half, year-quarter, year-tertile, and year-month.
• offset <month>—Fiscal year similar to the standard calendar except starting on a different month; period type options are the same as standard calendars.
• iso 8601—ISO week-numbering system; period type options are year and year-week.
• custom—Custom calendar created from a text file with a contiguous range of dates; required columns are date and year, optional columns are half, quarter, tertile, month, and week.

See General Build Tags for a different use of calendar as a Build tag.

For example:

column "Quarter" type="period" calendar="standard" period="year-quarter"

column "Fiscal Month" type="period" calendar="offset August" period="year-month"

Supports a 'period' type column. Valid calendars are:

• gregorian month—Gregorian year-month
• gregorian quarter—Gregorian year-quarter
• iso8601 week—ISO-8601 year-week
• gregorian+<N> month—Offset Gregorian year-month for fiscal calendars. Specifying "gregorian+6 month" means "add six months to the real year-mo to get the fiscal year-mo", which is like saying "The fiscal year starts in July".
• <custom calendar file>—For more tailored fiscal calendars
• See Spectre Calendars.

For example:

column-override "Quarter" type="period" calendar="gregorian month" format="YYYY/MM" input-format="YYYY/M"

Available in Spectre 7.2(2) and later. Defines the columns for the cBase. This is the same tag as used by text-input. See column for more information.

For example:

column "Order Type" type="string"

column "Order Unit Price" type="fixed100"

As of Spectre 7.2(2), column-override is deprecated. Use column instead.

Specifies the columns for the cBase and overrides the indicated attributes. For example, override the default numeric type to string; specify that the number has 2 decimal places.

NOTE: Use of column-override causes the Spectre build engine to request the given field from the database using a specific type. If the database or its ODBC driver cannot perform that conversion, then an error is generated. Use of column-override does not cause the Spectre build engine to perform any conversions itself.

TIP: The column-override attribute is not intended to be used as a way to do data type casting. For example, in general, column-override does not work with date data in a DB. Either create an SQL query that converts the field, or, once read by the Build, replace the value using an explicit date() function.

column-override "Order ID" type="string"

column-override "Order Unit Price" type="fixed100"

Specifies a binary file to retrieve credentials. This assumes that you previously generated a connect file using Integrator.

For example:

echo "DSN=my-dsn;UID=my-user;PWD=my-password" | integ -save_connect_file my_connect_file.connect

See Options for ODBC Login Credentials.

For example:

odbc-input {

connect-file "my_connect_file.connect"

...

}

Specifies, using one tag, a string with DSN, user, and password.

NOTE: Different ODBC drivers might use other connect string formats.

For example:

odbc-input {

connect-string "DSN=my-dsn;UID=my-user;PWD=my-password" ...

}

Defines custom-properties to associate extra data with a column. The custom-property can be extracted after the build and used in expressions.

For example:

custom-property "Confidence" "High"

Specifies the data source name (DSN) for the ODBC data.

For example:

dsn "Northwind"

dsn "Meditech"

Indicates that the numeric value is meant to be displayed as a time duration. The format is h:mm:ss.

Use with column-override tags in odbc-input blocks.

For example:

column "Visit Duration" type="integer" duration=true

Defines the encoding used in the input and output files. Encoding types supported include: cp-1252, iso-8859-1, utf-16be, utf-16le, utf-8, and gb18030.

NOTE

• Best practice is to specify the encoding of the input data.
• For text inputs, if no encoding is specified, Spectre analyzes the first few megabytes of the file to attempt to determine the encoding.
• For databases, if no encoding is specified, Spectre queries the driver for unicode data; if encoding="utf8" is specified, Spectre accepts the data as-is from the driver; if encoding is specified as something else, Spectre transcodes the bytes from the driver.
• Spectre Build's informational output indicates which encoding is in use.
• Non-Unicode DI clients are Unicode-aware.

• When using the Easysoft ODBC-to-JDBC Bridge, specify encoding="utf-8" to work around a driver bug.

For example:

odbc-input encoding="utf-8" {

...

}

Formats the "date" or "period" data for the cBase. To store dates like July 4, 2013, use format="MMMM D, YYYY".

Use to format currency.

For example:

column "Request Date" type="date" format="YYYY-MM-DD" input-format="YYYY-M-D"

column "Price" format="$#,0.00"

column "Revenue" type="fixed100" format="$#,#.00"

Clarifies the incoming 'date' or 'period' data. The default format is YYYY/MM/DD. Use input-format to define the date format in the source to ensure correct interpretation of date data.

For example:

column "Date" type="date" format="MMM D, YYYY" input-format="YYYY-MM-DD"

Defines the display label used for a column.

For example:

column "Color" type="string" label="favorite"

Directs the build to use a specific number of rows from each input. If multiple inputs are used, the first 100 rows are read from each, ensuring a complete sampling of data values.

TIP: Using a "LIMIT" statement or "TOP X" in your SQL query is the optimal way to limit the number of rows coming from the database.

For example:

limit-rows=100

Sets the ODBC input. Note the following:

• ODBC data source name (DSN) and query are required.
• ODBC user, password, column-override types, calendar, and format are optional.
• Zero or more column-overrides, properties, and custom-properties are allowed.
• A text or variable length field with an unspecified maximum length is capped at 32,767 characters.
• When run, key stages are reported: opening the DSN, running the query, reading the structure of the result, reading the data of the result, and constructing the output table.
• The query can be inline or stored in an external file.

For example:

odbc-input encoding="UTF8" {

dsn "Northwind"

query """

select Orders.OrderID,

Orders.OrderDate,

Orders.ShippedDate

from Orders

"""

}

odbc-input query-path="/data/inventory.sql" {

...

}

Sets the password for the user logging into the ODBC data. This tag is optional.

For example:

password "mysecret"

Verbose way to set label/format/calendar properties.
Deprecation expected.

For example:

property "Function" "Managers"

Specifies the query into the database. The SQL query string takes multiple lines, so triple quotes are needed to open and close the string.

See also query-path. Use query or query-path, but not both.

For example:

query """

SELECT Orders.OrderID

Orders.OrderDate,

Customers.CompanyName as CustomerName,

Customers.ContactName as CustomerContact,

Customers.Phone as CustomerPhone

FROM Orders, Customers
WHERE Orders.CustomerId = Customers.CustomerId
"""

Specifies the path to a query that is saved in an external file.

For example:

odbc-input query-path="/data//revenue-one-query.sql"

odbc-input query-path="/data/queries/$(TYPE)_drg.sql"

Available in Spectre 7.1(30) and later. Sets an integer number of seconds after which the ODBC query times out.

For example:

query-timeout 10

Sets a column to behave like classic Info Fields. In ProDiver, this field is available as a column that you can add to other columns displayed in a dive.

For example:

column "Product Status" type="string" required-dimension="Status"

Sorts the data when processing. When specifying columns, use sort-by to order them based on another column's value.

NOTE

• An alternate sort can only be applied to a string column, and the sort-by column must be a string or an integer column
• Dive and cPlan files have a different sort tag.
• Spectre uses "numeric collation" when sorting strings. When a subset of a string looks like a number, then the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". See also sort-natural.
• Null strings sort to the end.
• If the alternate sort has duplicates, the fall back is the main column.

• If a row has the value null or the value unknown in the column with an alternate sort, then the sort-by column must also have the same value.

• If the sort-by column has type string AND has the value null or the value unknown, then the column to be sorted must have the same value.
• Spectre issues a warning if you attempt to alternate sort null as non-null, or unknown as not-unknown.

For example:

column "Symbol" type="string" sort-by="Number"
column "Number" type="integer"

column "Priority Sort" type="integer"
column "Priority" type="string" sort-by="Priority Sort"

Disables natural string sorting in odbc-input. The default is true—Spectre interprets value of digits as decimal numbers and sorts according to the numeric values in the string.

TIP: This feature only makes sense together with sort-by if the sort-by column is also a string column.

For example:

column "ICD-10-code" type="string" sort-natural=false

add "diagnosis" `value("Diagnosis Code")+" -- "+value("Diagnosis Desc")` sort-natural=false

Defines a time threshold, in seconds, for a cache entry to turn stale. Use with odbc-input.

The example here will re-run the lookup at most once every minute so that people looking at the result see data at most one minute old.

The stale-after attribute can also be used with tunnel-input.

For example:

cplan {

cbase-input "foo.cbase" {

lookup {

odbc-input {

dsn="some dsn"

query="select a, b from foo"

stale-after 60

}

key "a" "a"

column "b"

}

}

}

Allows you to promote (true) or demote (false) a column:

• Set to true to promote a numeric column type to become a diveable dimension and have it appear in the ProDiver console.
• Set to false to demote a column from classification as a dimension so it does not appear in the ProDiver console.
• When set to false, the new dimension does not appear in the console (which requires dimcount processing), but it can still be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model.
• Using required-dimension on a column effectively sets suggested-dimension=false.

For example:

column "Product Class" type="string" suggested-dimension=false

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) are allowed. See Summary Functions.

Most common are sum, info, any, first, and last. The default for numeric data is sum; use kahan_sum if concerned about precision; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calc (that is, not the default one).

For example:

column "Quantity In Stock" type="integer" summary-type="info"

Controls removal of leading and trailing white space before writing strings to the cBase. Defaults to true.

For example:

odbc-input trim=false {

...

}

Specifies the type of data in the column. Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types.

If the type is period, a calendar tag is required.

NOTE: Fields of type bit from SQL Server default to type string in Spectre. You can specify the type as boolean for better performance.

For example:

column "Qty Ordered" type="integer"

column "Telephone Number" type="string"

column "OrderUnitPrice" type="fixed100"

Sets the user to use to log into the ODBC data. This tag is optional.

For example:

user "joey"