Unload command, fixed width columns
When using the "UNLOAD to File" command, is there an SQL option to prevent Informix from clipping trailing blanks? VP
Good News/Bad News,
While searching through archives at Deja News, I ran across the
following
solution to my original post: (from 1997!!!)
unload to fname
select col1 || "~" || col2 "~" || "~" from table
(where ~ is my chosen delimiter)
Now, to take it one step further... I want to unload ALL the
columns of table,
into a fixed-width file (no clipping of trailing blanks)
As you can guess,
select * || "~"
is not a valid SQL statement :-(
Any SQL only solutions?
VP
> -----Original Message-----
> From: Pachiano, Vince
> Posted At: Thursday, April 08, 1999 11:31 AM
> Posted To: informix
> Conversation: Unload command, fixed width columns
> Subject: Unload command, fixed width columns
>
> When using the "UNLOAD to File" command,
> is there an SQL option to prevent Informix from
> clipping trailing blanks?
>
> VP
> Good News/Bad News,
>
> While searching through archives at Deja News, I ran across the
> following
> solution to my original post: (from 1997!!!)
>
> unload to fname
> select col1 || "~" || col2 "~" || "~" from table>
> (where ~ is my chosen delimiter)
>
> Now, to take it one step further... I want to unload ALL the
> columns of table,
> into a fixed-width file (no clipping of trailing blanks)
> As you can guess,
>
> select * || "~">
> is not a valid SQL statement :-(
>
> Any SQL only solutions?
>
> VP
> -----Original Message-----
> From: Pachiano, Vince
> Posted At: Thursday, April 08, 1999 11:31 AM
> Posted To: informix
> Conversation: Unload command, fixed width columns
> Subject: Unload command, fixed width columns
>
> When using the "UNLOAD to File" command,
> is there an SQL option to prevent Informix from
> clipping trailing blanks?
>
> VP
Write a shell script to write the sql for you.
Create an shell that contains an sql script which returns only column names
for a given table in the correct column sequence i.e.
Select colno, colname
from systables
where tabname = [variable]
ORDER by colno.
(I call mine just_cols, it strips out the colno and the assorted dbaccessmessages).
Then create another shell something like
DBNAME=$1
TBNAME=$2
echo "SELECT \\c"
for COLNAME in `just_cols ${DBNAME} ${TBNAME}`
do
echo ${COLNAME}", ~ , "
done;
pipe the output to a foo.sql, ofcourse you'll have to manually strip out
the last comma, or make the script smart enough to so it.
Good Luck
Terry Hillick
CSCSi
"Pachiano, Vince" wrote:
> Good News/Bad News,
>
> While searching through archives at Deja News, I ran across the
> following
> solution to my original post: (from 1997!!!)
>
> unload to fname
> select col1 || "~" || col2 "~" || "~" from table>
> (where ~ is my chosen delimiter)
>
> Now, to take it one step further... I want to unload ALL the
> columns of table,
> into a fixed-width file (no clipping of trailing blanks)
> As you can guess,
>
> select * || "~">
> is not a valid SQL statement :-(
>
> Any SQL only solutions?
>
> VP
> > -----Original Message-----
> > From: Pachiano, Vince
> > Posted At: Thursday, April 08, 1999 11:31 AM
> > Posted To: informix
> > Conversation: Unload command, fixed width columns
> > Subject: Unload command, fixed width columns
> >
> > When using the "UNLOAD to File" command,
> > is there an SQL option to prevent Informix from
> > clipping trailing blanks?
> >
> > VP
Pachiano, Vince wrote:
>
> Good News/Bad News,
>
> While searching through archives at Deja News, I ran across the
> following
> solution to my original post: (from 1997!!!)
>
> unload to fname
> select col1 || "~" || col2 "~" || "~" from table>
> (where ~ is my chosen delimiter)
>
> Now, to take it one step further... I want to unload ALL the
> columns of table,
> into a fixed-width file (no clipping of trailing blanks)
> As you can guess,
>
> select * || "~">
> is not a valid SQL statement :-(
>
> Any SQL only solutions?
>
> VP
> > -----Original Message-----
> > From: Pachiano, Vince
> > Posted At: Thursday, April 08, 1999 11:31 AM
> > Posted To: informix
> > Conversation: Unload command, fixed width columns
> > Subject: Unload command, fixed width columns
> >
> > When using the "UNLOAD to File" command,
> > is there an SQL option to prevent Informix from
> > clipping trailing blanks?
> >
> > VP
Using a programming language this is trivial. You can write a stored
procedure to print out the unload command by reading through syscolumns
and output the command to a file then execute that. Or write a 4GL
program using the 4GL unload verb or write an ESQL/C program to PREPARE
the SELECT * and DESCRIBE it then format the output as you wish.
Using pure SQL in dbaccess only? No way.
Art S. Kagel
Art S. Kagel wrote:
> Pachiano, Vince wrote:
> >
> > Good News/Bad News,
> >
> > While searching through archives at Deja News, I ran across the
> > following
> > solution to my original post: (from 1997!!!)
> >
> > unload to fname
> > select col1 || "~" || col2 "~" || "~" from table> >
> > (where ~ is my chosen delimiter)
> >
> > Now, to take it one step further... I want to unload ALL the
> > columns of table,
> > into a fixed-width file (no clipping of trailing blanks)
> > As you can guess,
> >
> > select * || "~"> >
> > is not a valid SQL statement :-(
> >
> > Any SQL only solutions?
>
<snip>
>
> Using a programming language this is trivial. You can write a stored
> procedure to print out the unload command by reading through syscolumns
> and output the command to a file then execute that. Or write a 4GL
> program using the 4GL unload verb or write an ESQL/C program to PREPARE
> the SELECT * and DESCRIBE it then format the output as you wish.
>
> Using pure SQL in dbaccess only? No way.
>
> Art S. Kagel
Well,
select col1 || "~" || col2 || "~" from tablename
*is* pure SQL. But if you mean pure SQL that doesn't require you to type out
the column names...
Try this:
output to pipe "echo >>foobar.sql " select "select " from systables where
tabid = 99;
output to pipe "echo >>foobar.sql " select colname||'||"~", ' from
syscolumns where tabid = 100;
output to pipe "echo >>foobar.sql " select " ' ' " from systables where
tabid = 99;
output to pipe "echo >>foobar.sql " select " from tabname" from systables
where tabid = 99;
output to pipe "dbaccess stores7 foobar.sql" select ' ' from systables where
tabid = 99;
(This probably doesn't qualify as "pure SQL", but it might run from within
DBAccess, if that's what you want.) You might need to add a bunch of '\\' to
escape the quotation marks.
I can't try this now because I don't have access to Informix on a Unix box
(alas, what has my life come to?) but someone try it and let me know ;-)
Of course, I should probably have started by asking "Are you on Unix?"
June
--
june_t@hotmail.com
Back in Palo Alto, living on Haagen Daz ice cream bars
Please do not send Informix questions to this account.
I would add 'Please do not send spam to this account'
but I suppose I would be wasting my bits.