#!/usr/bin/perl

use strict;
use warnings;
use Text::CSV;
use DBI;

sub RunSqlSearch;

my $query = qq(SELECT sid,genotype FROM newCsv.csv WHERE genotype = 'a');   
my ($queryResult,@sidResult) = RunSqlSearch($query);
print "You query $query returned the following result:$queryResult\n";
print "The sid list associated with this result are:  @sidResult\n\n";

sub RunSqlSearch($){
    
    my $query = $_[0]; 
    
    # error check #
    if ($query eq ""){die "No value entered for $query $!\n";}
        
 	# Connect to the database, (the directory containing our csv file(s))
	my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
	      f_dir   					=> ".",
	      f_ext   					=> ".csv/r",
	      f_encoding  				=> "utf-8",
	      RaiseError				=> 1,
	      }) or die $DBI::errstr;
	
	# Output using sql query #
	my $sth = $dbh->prepare($query);
	$sth->execute;
	
	my @row;
	my $queryResult="";
	my @queryResult;
	my @sidResult;
	while (@row = $sth->fetchrow_array) {
		push @queryResult, @row;
		push @sidResult, $row[0].",";
		$queryResult .= join("\t",@row) . "\n";
	}  
		
	# output arguments #
	if ($queryResult eq ""){$queryResult = "No result found";}
	return ($queryResult, @sidResult);
	
	$sth->finish();
	$dbh->disconnect(); 
} 

##</code><code>##

DBD::CSV::st execute failed: 
Execution ERROR: No filename given at /usr/lib64/perl5/DBD/File.pm line 565.
 called from runSQLQuery.pl at 11.

 at /usr/lib64/perl5/DBD/File.pm line 565.
 [for Statement "SELECT sid,genotype FROM newCsv.csv WHERE genotype = 'a'"] at runSQLQuery.pl line 32.
DBD::CSV::st execute failed: 
Execution ERROR: No filename given at /usr/lib64/perl5/DBD/File.pm line 565.
 called from runSQLQuery.pl at 11.

 at /usr/lib64/perl5/DBD/File.pm line 565.
 [for Statement "SELECT sid,genotype FROM newCsv.csv WHERE genotype = 'a'"] at runSQLQuery.pl line 32.