This should get you started (UNTESTED):

use DBI;
my $dbh = DBI->connect( @some_connect_info, { RaiseError => 1 }  );

my ($count) = $dbh->fetchrow_array("COUNT * FROM table");

my $sth = $dbh->prepare("SELECT col1,col2 FROM table");
$sth->execute();
while (my $res = $sth->fetchrow_hashref) {
  # in this block
  # value1 == $res->{col1};
  # value2 == $res->{col2};
}
[download]

I know how to open a file,write query to it and execute using isql through perl. I just dont know how to implement the above pseudo code.

If the DBI and appropriate DBD module are not in place for the perl you're using (and installing them is "outside your scope" -- nothing to be ashamed of), then your initial approach of using a known, working command line tool (like isql) is fine.

As for how to handle the data within the perl script, your pseudo-code suggests that you're not familiar with perl data types and data structures. I could point you to the "perldata" man page, but if you have trouble with that, try perldsc (data structures cookbook), or any basic book on learning perl, to get a handle on scalars, arrays and hashes, and more elaborate structures built from combinations of arrays and/or hashes. Once you understand how the data storage and structuring works, it can be pretty easy to figure out what sort of structure you need for your app, and how to build it up from (and apply it to) your database queries.

So taking the command-line "isql" tool as the data source, one way to get started would be:

# you already have this part:

my $qry_file = "some_name.sql";  # or whatever
open( QRY, ">$qry_file" ) or die "can't write $qry_file: $!";
print QRY "select foo from bar where baz='x'\n";
close QRY;

# Run isql by opening it as a pipeline
# (I'm guessing at the command-line usage):

my @fnames, @lnames;  # query output will be stored here

# update: using the correct variable name for the query file:
open( DB, "isql $qry_file |" ) or die "can't run isql: $!";

while (<DB>) {  # assuming the tool prints one line per row
    chomp; # remove newline
    my ( $lname, $fname ) = split(/\t/); # assuming fields are tab-del
+imited

    push @fnames, $fname;
    push @lnames, $lname;
}
close DB;

# now you can do whatever you want, iterating over
# the contents of @lnames and/or @fnames

for my $i ( 0..$#fnames ) {
   print "Row = $i, Full_name = $fnames[$i] $lnames[i]\n";
}
[download]

Assuming that this is Sybase (maybe MS-SQL has it as well) you should use the "-s" command-line argument to specify a column separator that is unlikely to occur in the data. You should also specify the "-w" argument with a large integer to make sure that isql doesn't wrap any of the output lines. So something like this could work:

open(TMP, ">/tmp/t$$.sql") || die "can't open temp file: $!";
# Set nocount to avoid the "xxx rows affected" message in isql
print TMP "set nocount on\ngo\nselect * from the_table\ngo\n";
close(TMP);
# Note: -b flag suppresses column headers.
open(ISQL, "isql -Ujoe -Pjoespwd -Smyserver -s'%%^^%%' -w 1024 -b -i /
+tmp/t$$.sql") || die "Can't run isql: $!";
while(<ISQL>) {
    chomp;
    my @row = split(/%%^^%%/, $_);
    .... do something with the @row of data....
}
close(ISQL);
[download]

Michael

my $cmd="isql $connect-string";
open ISQL, "|$cmd" or die "couldn't open $cmd:$!\n";
print ISQL <<end;
select * from whatever;
...
[download]

my $cmd="exec_isql.pl";
open OUT, "$cmd|" or die "couldn't open $cmd:$!\n";
my @results = <OUT>;
print "First row is " . $results[0] ."\n";
[download]

Yes, definitely use DBI.

Here's a few lines from one of my old scripts that read from a sybase. The environment stuff is important. IIRC, it will be the same even if you're using the freetds libs.

use DBI;
BEGIN {
    # path to sybase or freetds libs
    $ENV{'SYBASE'}    = "/opt/sybase";
    # Sybase server as listed in your 'interfaces' file
    $ENV{'DSQUERY'}   = "server.host.name"; 
}
my (@available_drivers, $dbh, $sql_statement, $sth, @array) = ();
my $username = "dbusername";
my $password = "dbpassword";
my $dbh = DBI->connect('dbi:Sybase:', $username, $password);
[download]

After that, you're good to go with normal DBI methods straight from the docs.

my $qrycmd = '-S[Server] -U[userid] -P[password] -h-1 -n -s:';
# -h-1 suppresses headers
# -n removes numbering and the prompt symbol (>) from input lines
# -s: sets the field seperator to a colon, :, this eliminates a space
#     between fields and in the first position of the line (tabs, \t,
#     doesn't work reliably

open( DB, "isql $qrycmd -iperlmonk34.sql |" ) or die "can't run isql: 
+$!";

while (<DB>) {  # assuming the tool prints one line per row
    chomp; # remove newline
    my ( $lname, $fname ) = split(/:/); 

# the if statement accounts for the last two lines of the isql output
# which consists of a blank line followed by a line listing rows 
# affected
    if(defined $fname){
        push @fnames, $fname;
        push @lnames, $lname;
    }
}
close DB;
[download]