ODBC Input Build Tags
A Spectre Build script is defined using a build tag with optional tags between open and close { } braces. This topic describes the build tags that can be used within the odbc-input code block. The tags are listed in alphabetical order in the following table.
ODBC Input Build Tags
Tags | Examples | Notes |
---|---|---|
build |
build { ... } |
Defines the block for this build description. |
allow-datetime |
odbc-input { dsn "bugzilla" query "select \"lastdiffed\" from \"bugs\" LIMIT 10" allow-datetime } |
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. |
bind |
take-parameter "ClientID" default="X999999" odbc-input { dsn "SQLProd" query """ SELECT [Master ID], [Payer Category], [Payer Class] FROM PayMaster where [Master ID] = ? """ { bind "$(ClientID)" } } ... |
Binds the parameter value to the variable in the SQL query. |
calendar (7.0(35) and later) |
column-override "Quarter" type="period" calendar="standard" period="year-quarter" column-override "Fiscal Month" type="period" calendar="offset August" period="year-month" |
Assigns a calendar and period type to the column. Valid calendars are:
See General Build Tags for a different use of calendar as a Build tag. |
calendar (Deprecated) |
column-override "Quarter" type="period" calendar="gregorian month" format="YYYY/MM" input-format="YYYY/M" | Supports a 'period' type column. Valid calendars are:
|
column-override |
column-override "Order ID" type="string" column-override "Order Unit Price" type="fixed100" |
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. |
connect-file |
odbc-input { connect-file "my_connect_file.connect" ... } |
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 |
connect-string |
odbc-input { connect-string "DSN=my-dsn;UID=my-user;PWD=my-password" ... } |
Specifies, using one tag, a string with DSN, user, and password. NOTE: Different ODBC drivers might use other connect string formats. |
custom property | custom-property "Confidence" "High" | Defines custom-properties to associate extra data with a column. The custom-property can be extracted after the build and used in expressions. |
dsn |
dsn "Northwind" dsn "Meditech" |
Specifies the data source name (DSN) for the ODBC data. |
encoding |
odbc-input encoding="utf-8" { ... } |
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. ![]()
|
format |
column-override "Request Date" type="date" format="YYYY-MM-DD" input-format="YYYY-M-D" column-override "Price" format="$#,0.00" column-override "Revenue" type="fixed100" format="$#,#.00" |
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. |
input-format | column-override "Date" type="date" format="MMM D, YYYY" input-format="YYYY-MM-DD" | 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. |
label | column-override "Color" type="string" label="favorite" | Defines the display label used for a column. |
limit-rows | limit-rows=100 |
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. |
odbc-input |
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 ODBC input. Note the following:
|
password | password "mysecret" | Sets the password for the user logging into the ODBC data. This tag is optional. |
property | property "Function" "Managers" | Verbose way to set label/format/calendar properties. Deprecation expected. |
query |
query """ SELECT Orders.OrderID Orders.OrderDate, Customers.CompanyName as CustomerName, Customers.ContactName as CustomerContact, Customers.Phone as CustomerPhone FROM Orders,
Customers |
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. |
query-path |
odbc-input query-path="/data//revenue-one-query.sql"
odbc-input query-path="/data/queries/$(TYPE)_drg.sql" |
Specifies the path to a query that is saved in an external file. |
required-dimension | column-override "Product Status" type="string" required-dimension="Status" | 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. |
sort-by |
column-override "Symbol" type="string" sort-by="Number"
column-override "Priority Sort" type="integer" |
Sorts the data when processing. When specifying columns, use sort-by to order them based on another column's value. ![]()
|
sort-natural |
column-override "ICD-10-code" type="string" sort-natural=false add "diagnosis" `value("Diagnosis Code")+" -- "+value("Diagnosis Desc")` sort-natural=false |
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. |
stale-after |
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" } } } |
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. |
suggested-dimension | column-override "Product Class" type="string" suggested-dimension=false | Allows you to promote (true) or demote (false) a column:
|
summary-type | column-override "Quantity In Stock" type="integer" summary-type="info" |
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). |
trim |
odbc-input trim=false { ... } |
Controls removal of leading and trailing white space before writing strings to the cBase. Defaults to true. |
type |
column-override "Qty Ordered" type="integer" column-override "Telephone Number" type="string" column-override "OrderUnitPrice" type="fixed100" |
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. |
user | user "joey" | Sets the user to use to log into the ODBC data. This tag is optional. |
See also: