on 2010 Feb 26 8:35 PM
I'm running SQLA 9.0.2.3850
Using ISQL, I want to dynamically create a file name, then write the file based on a query. Here is my code...
BEGIN
DECLARE @output_filename char (100);
SET @output_filename = 'C:\\TEMP\\JUNK_' || DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') || '.txt';
SELECT TOP 10 * FROM sys.systable order by table_id;
OUTPUT to @output_filename format ascii;
END
I get "Syntax error near 'OUTPUT'" when I run this. I can't find anything in the docs that says this should not be allowed. And I have to use the BEGIN/END statements in order to use variables, right??
Request clarification before answering.
The OUTPUT statement is processed by the client applicaton dbisql. However, anything you code inside a BEGIN block has to be passed to the database engine to be processed, and the engine doesn't know what an OUTPUT statement is.
The good news is, the UNLOAD SELECT statement is even more powerful, flexible and efficient than OUTPUT. FWIW it's all described in my book which is written for Version 9 http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
BEGIN DECLARE @output_filename char (100); DECLARE @sql LONG VARCHAR; SET @output_filename = 'C:\\TEMP\\JUNK_' || DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') || '.txt'; UNLOAD SELECT TOP 10 * FROM sys.systable order by table_id TO @output_filename FORMAT ASCII; --OUTPUT to @output_filename format ascii; END
There is a potential problem: The UNLOAD statement runs in the engine, and the filespec is relative to the computer running the engine, whereas the dbisql OUTPUT filespec is relative to the computer running dbisql. If the computers are one and the same, so far so good. But if the engine is on another computer AND you want the file sent back to your workstation, you will have to jump through hoops; e.g., use UNC filespecs:
BEGIN DECLARE @output_filename char (100); DECLARE @sql LONG VARCHAR; SET @output_filename = STRING ( '\\\\\\\\192.168.1.105\\\\C\\\\TEMP\\\\JUNK_', DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD'), '.txt' ); -- The slashes are all doubled-up because \\ is an escape character, -- so \\\\ represents one \, and in order to get two backslashes \\\\ -- you have to code four \\\\\\\\. UNLOAD SELECT TOP 10 * FROM sys.systable order by table_id TO @output_filename FORMAT ASCII; --OUTPUT to @output_filename format ascii; END
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
To add to Breck's answer, in version 11 the UNLOAD statement can write its results to a file residing on the client using the TO CLIENT FILE clause - See http://dcx.sybase.com/index.html#1101en/dbreference_en11/unload-statement.html - hence no more hoops are required 🙂
If this is a manually invoked or scheduled iSQL Script you can you command-line parameters to name the output file.
Look at PARAMETERS statement
The filename in your example can easily build on windows with the environment variables. As only the date is in question a not synchronized clock on the client should be not problematic.
PARAMETERS file;
SELECT TOP 10 * FROM sys.systable order by table_id;
OUTPUT to {file} format ascii;
HTH
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
EXCELLENT SUGGESTION! I use PARAMETERS all the time for customizing giant database builds ( {DEBUG_MESSAGES}, {BETA_VERSUS_EVAL_FULL} etcetera ), and to give "template remote databases" their MobiLink personalities ( {GLOBAL_DB_ID} etcetera )... why didn't it occur to me that it would work with the OUTPUT filespec? Doh! 🙂
User | Count |
---|---|
87 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.