Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    13

    Unanswered: dbaccess/isql - how to get horizontal ouput

    Hi

    I am executing isql/dbaccess and dumping the select output to a file. But the records are appearing vertically like this -

    col1 val1
    col2 val2
    col3 val3
    .....

    I want to arrange the output in the following way -
    col1 col2 col3 ...
    val1 val2 val3 ...

    That is each row of the output file will represent a record of the table.

    How we can accomplish this? What flag I have to use?

    Please help me out ..

    Thanks

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    for this purpose I use the following perlscript on Linux. Just name the script as you like, store it in a PATH-directory and make it executeable. Then use it in dbaccess by selecting Output - To-pipe and entering the name of the script.
    It's not perfect in the way that with empty columns in the output it starts a newline but I only use it to generate new SQL statements with a query and for that it works fine.
    In this form it doesn't work on Windows but I guess it's possible to make it work.
    Code:
    #!/usr/bin/perl
    
    use warnings;
    use strict;
    
    sub quotes
    {
        my $string = shift;
        my $cnt = 0;
        my $idx = -1;
        if (($idx = index($string, "\"")) >= 0)
        {
            $cnt = $cnt + 1 + quotes(substr($string, $idx + 1));
        }
        return $cnt;
    }
    
    my $line = "";
    my $quote = 0;
    
    while (<>)
    {
        my $i = (my @inp = split);
        if ($i > 1)
        {
            for (my $j = 1; $j < $i; $j++)
            {
                my $text = $inp[$j];
                chomp $text if ($j == $i);
                $quote = quotes($line);
                if ($line ne "")
                {
                    if ($quote &#37; 2 == 0 ||
                        (substr($line, -1) !~ "\"" && substr($text, 0, 1) !~ "\""))
                    {
                        $text = " " . $text;
                    }
                }
                $quote = 0;
                $line = $line . $text;
            }
        }
        elsif ($line !~ /^$/)
        {
            print "$line\n";
            $line = "";
        }
    }
    print "$line\n";
    Regards,
    Hans
    Last edited by Tyveleyn; 12-12-09 at 03:26.

  3. #3
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, probably a more convenient way to accomplish what you want is to use the UNLOAD statement to write to file. Like:
    Code:
    UNLOAD TO unload.file DELIMITER ' '
    SELECT col1, col2, col3 FROM table WHERE ...
    Regards,
    Hans
    Last edited by Tyveleyn; 12-12-09 at 19:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •