package WebGUI::Macro::SQLExt;

use strict;
#use WebGUI::Macro;
#use WebGUI::Session;
#use WebGUI::DatabaseLink;
#use WebGUI::SQL;
#use Data::Dumper;
#use DBI;

$WebGUI::Macro::SQLExt::VERSION	= '00.00.02';

#-------------------------------------------------------------------
# Syntax:
# ^SQLExt($statement,$databaseTitle,$format, $statement [,param[,param]]);

sub process {
	my $session = shift;
	my $dbt = shift;
	my $format = shift;
	my $statement = shift;
	my $params = \@_;

        $format = "^0" unless $format;
	
	# get the database link ID
	my @data = $session->dbSlave->quickArray("select databaseLinkID from databaseLink where title=? ;", [$dbt]);

	my $dbLinkID = $data[0];
	return "Invalid database link" unless $dbLinkID;


	# get the database	
	my $dbl = WebGUI::DatabaseLink->new($session, $dbLinkID);


	# see if the query is allowed
	return "Query is not allowed" unless $dbl->queryIsAllowed($statement);	


	my $db = $dbl->db();
	
	# execute query
        my $sth = $db->unconditionalRead($statement,$params);
                
	my $output = "Query failed with unknown error";

	unless ($sth->errorCode < 1)
	{
		$output = "Query failed with error: " . $sth->errorMessage . "  " . $statement . " " . $dbLinkID . $db->errorMessage;
        } 
	else 
	{
        	my $temp;
		my $rownum = 0;
		
		$output = "";

		while (@data = $sth->array)
		{
        		$temp = $format;
                        $temp =~ s/\^(\d+)\;/$data[$1]/g;
                        $rownum++;
                        $temp =~ s/\^rownum\;/$rownum/g;
                        $output .= $temp;
                }
                $sth->finish;
	}


	$dbl->disconnect();

	return $output;
}

1;