Hi,
Run this query in dbaccess and write the output to a *.sql file. Make sure the single and double quotes are in exactly the same place or exactly the other way around.
Code:
SELECT 'grant select on ' || trim(tabname) || ' to "user" as "', trim(owner) || '";'
FROM systables WHERE tabid > 99;
Here the single quotes mark the literal strings that come in place of columns in the SELECT clause. The double quotes are regarded as stringmembers then and printed out.
If your output comes with "(experssion)" as first field of every line (in UNIX) remove those from the *.sql file before running. This could also be done with following perl script, used as an output pipe:
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);
$text = " " . $text if ($line ne "" && ($quote % 2 == 0));
$quote = 0;
$line = $line . $text;
}
}
elsif ($line !~ /^$/)
{
print "$line\n";
$line = "";
}
}
print "$line\n";
For that make sure a perl interpreter is installed on your system (default on many UNIX/Linux systems) and this perlscript is stored in a PATH-directory. In dbaccess then just choose "Ouput" -> "To-pipe" and fill-in: "scriptname > filename.sql". Finally run the filename.sql in dbaccess.
Regards
dBforums
>
Informix - Grant Statement - newbie doubt
07-17-07, 03:52
Linear Mode
