DBISQLC.EXE is the utility used to connect to sample/local database for DML, DDL operations. But how do we export the results of a query to a file. Use “OUTPUT” command to export the queried results in dbisqlc.exe. Here is the syntax.
Syntax:
OUTPUT TO file-name
[ APPEND ]
[ VERBOSE ]
[ FORMAT output-format ]
[ ESCAPE CHARACTER character ]
[ ESCAPES { ON | OFF }
[ DELIMITED BY string ]
[ QUOTE string [ ALL ] ]
[ COLUMN WIDTHS (integer, ...) ]
[ HEXADECIMAL { ON | OFF | ASIS } ]
[ ENCODING encoding ]
output-format:
ASCII | DBASEII | DBASEIII | EXCEL | FIXED | FOXPRO | HTML | LOTUS | SQL | XML
encoding: string or identifier
Parameters
APPEND: This optional keyword is used to append the results of the query to the end of an existing output file without overwriting the previous contents of the file. If the APPEND clause is not used, the OUTPUT statement overwrites the contents of the output file by default. The APPEND keyword is valid if the output format is ASCII, FIXED, or SQL.
VERBOSE: When the optional VERBOSE keyword is included, error messages about the query, the SQL statement used to select the data and the data itself are written to the output file. Lines that do not contain data are prefixed by two hyphens. If VERBOSE is omitted (the default) only the data is written to the file. The VERBOSE keyword is valid if the output format is ASCII, FIXED, or SQL.
FORMAT: Allowable output formats are:
ASCII: The output is an ASCII format file with one row per line in the file. All values are separated by commas, and strings are enclosed in apostrophes (single quotes). The delimiter and quote strings can be changed using the DELIMITED BY and QUOTE clauses. If ALL is specified in the QUOTE clause, all values (not just strings) are quoted.
Three other special sequences are also used. The two characters \n represent a newline character, \\ represents a single \, and the sequence \xDD represents the character with hexadecimal code DD. This is the default output format.
DBASEII: The output is a dBASE II format file which includes column definitions. Note that a maximum of 32 columns can be output. Column names are truncated to 11 characters, and each row of data in each column is truncated to 255 characters.
DBASEIII: The output is a dBASE III format file which includes column definitions. Note that a maximum of 128 columns can be output. Column names are truncated to 11 characters, and each row of data in each column is truncated to 255 characters.
EXCEL: The output is an Excel 2.1 worksheet. The first row of the worksheet contains column labels (or names if there are no labels defined). Subsequent worksheet rows contain the actual table data.
FIXED: The output is fixed format with each column having a fixed width. The width for each column can be specified using the COLUMN WIDTHS clause. No column headings are output in this format.
If the COLUMN WIDTHS clause is omitted, the width for each column is computed from the data type for the column, and is large enough to hold any value of that data type. The exception is that LONG VARCHAR and LONG BINARY data default to 32 KB.
FOXPRO: The output is a FoxPro format file which includes column definitions. Note that a maximum of 128 columns can be output. Column names are truncated to 11 characters. Column names are truncated to 11 characters, and each row of data in each column is truncated to 255 characters.
HTML: The output is in the Hyper Text Markup Language format.
LOTUS: The output is a Lotus WKS format worksheet. Column names are put as the first row in the worksheet. Note that there are certain restrictions on the maximum size of Lotus WKS format worksheets that other software (such as Lotus 1-2-3) can load. There is no limit to the size of file Interactive SQL can produce.
SQL: The output is an Interactive SQL INPUT statement required to recreate the information in the table.
XML: The output is an XML file encoded in UTF-8 and containing an embedded DTD. Binary values are encoded in CDATA blocks with the binary data rendered as 2-hex-digit strings. The INPUT statement does not accept XML as a file format.
ESCAPE CHARACTER: The default escape character for characters stored as hexadecimal codes and symbols is a backslash (\), so \x0A is the linefeed character, for example.
This can be changed using the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, you would enter
... ESCAPE CHARACTER '!'
New line characters can be included as the combination \n, other characters can be included in data as hexadecimal ASCII codes, such as \x09 for the tab character. A sequence of two backslash characters ( \\ ) is interpreted as a single backslash. A backslash followed by any character other than n, x, X, or \ is interpreted as two separate characters. For example, \q inserts a backslash and the letter q.
ESCAPES: With ESCAPES turned on (the default), characters following the backslash character are recognized and interpreted as special characters by the database server. With ESCAPES turned off, the characters are written exactly as they appear in the source.
DELIMITED BY: The DELIMITED BY clause is for the ASCII output format only. The delimiter string is placed between columns (default comma).
QUOTE: The QUOTE clause is for the ASCII output format only. The quote string is placed around string values. The default is a single quote character. If ALL is specified in the QUOTE clause, the quote string is placed around all values, not just around strings.
COLUMN WIDTHS: The COLUMN WIDTHS clause is used to specify the column widths for the FIXED format output.
HEXADECIMAL: The HEXADECIMAL clause specifies how binary data is to be unloaded for the ASCII format only. When set to ON, binary data is unloaded in the format 0xabcd. When set to OFF, binary data is escaped when unloaded (\xab\xcd). When set to ASIS, values are written as is, that is, without any escaping—even if the value contains control characters. ASIS is useful for text that contains formatting characters such as tabs or carriage returns.
ENCODING: The encoding argument allows you to specify the encoding that is used to write the file. The ENCODING clause can only be used with the ASCII format.
We have the "Add & Go" and "Search & Go" functionality in Siebel 8. At times, there may arise situations where you need to manipulate the search expression for "Search & Go" applet. Let's consider the following scenario.
We have a "Search & Go" functionality for FAQs on homepage of eService application. This applet has 2 fields - FAQ and Answer. The out of box functionality suffixes the search string of each field with wildcard "*" and also doesn't support field level case insensitivity on VBCs. There are no declarative configurations available currently for this scenario. Hence scripting approach is being used here.
Virtual BC: FAQ Home Search Virtual
Underlying BC: Top 5 FAQ
Fields: FAQ, Answer
Applet: FAQ Home Search Virtual Applet (based on Virtual BC)
In the WebApplet_PreInvokeMethod() of FAQ Home Search Virtual Applet, write the following
if (MethodName == "Mirror Search GotoView")
{
TheApplication().SetSharedGlobal("FAQSearchVirtual", "Y");
}
In the BusComp_PreQuery method of the underlying BC write the following code:
var strSearchExpr = "";
var strNewSearchExpr = "";
var strPattern = "";
if (TheApplication().GetSharedGlobal("FAQSearchVirtual") == "Y")
{
strSearchExpr = this.GetSearchExpr();
strPattern = /(LIKE\s\")/g;
strNewSearchExpr = strSearchExpr.replace(strPattern, "~LIKE \"*");
this.SetSearchExpr(strNewSearchExpr);
TheApplication().SetSharedGlobal("FAQSearchVirtual", "N");
}
The vanilla search expression would like:
[FAQ] LIKE "computer*" AND [Answer] LIKE "electronic*"
This expression returns all the FAQ whose question starts with word "computer" and answer starts with word "electronic". Both are case sensitive.
The above script replace the (LIKE ") with (~LIKE "*). The tilde (~) operator enforces the case insensitivity and * prefix would make the final search expression look like:
[FAQ] ~LIKE "*computer*" AND [Answer] ~LIKE "*electronic*".
This search expression returns all the FAQ where questions case insensitive word "computer" and answer contains case insensitive word "electronic".
More info on Regular Expression for patterns can be found at:
http://www.zvon.org/other/reReference/Output/index.html
Comments/Suggestions are most welcome.