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 (Deprecated) |
odbc-input { dsn "bugzilla" query "select \"lastdiffed\" from \"bugs\" LIMIT 10" allow-datetime } |
This tag is deprecated. Previous usage allowed a timestamp column in the ODBC source to be recognized as a datetime column. Without this tag, an SQL datetime field was treated as a date type, discarding the time portion of the field. Datetime columns are now recognized without using this attribute. |
bind |
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" } |
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"))`. |
calendar |
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. |
duration |
column-override "Visit Duration" type="integer" duration=true |
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. |
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. |
query-timeout (7.1(30) and later) |
query-timeout 10 | Sets an integer number of seconds after which the ODBC query times out. |
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: