[ic] Query Tag - how do I get a single value?

[interchange-users@icdevgroup.org (Mike Heins)]

Quoting Mark Gibson (gibsonm@cromwell-tools.co.uk):
> Hi,
> 	I'm using PostgreSQL 7.2.3 with Interchange 4.8.6.
> I've written some custom functions for the database server, and I know
> they work within 'psql' and via DBI/DBD::Pg ok.
> 
> The functions return a single integer value,
> so when I do:
> 
> 	$Tag->query("SELECT custom_function('$body')");
> 
> I would like to get back a single scalar value from this call,
> but I can't work out how to do it. I've only just started to learn
> Perl, and have worked out I can do it with DBI as follows:
> 
> 	my ($result) = $dbh->selectrow_array(...)
> 
> But how do I do this with $Tag->query(), the documentation
> for [query] is quite confusing, and lacks examples of this type.

That is because it really isn't designed to be used within
embedded Perl.

You should use either the standard access method:

	[perl tables=products]
		my $db = $Db{products};
		my $ary = $db->query("SELECT custom_function('$body')");
		my $result;
		$result = $ary->[0][0] if $ary;
		return $result;
	[/perl]

or the DBI you are used to:

	[perl tables=products]
		my $dbh = $Sql{products};
		my $sth = $dbh->prepare("SELECT custom_function('$body')")
			or die "Bad query prepare!";
		my ($result) = $sth->selectrow_array();
		return $result;
	[/perl]

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

Any man who is under 30, and is not liberal, has not heart; and any man
who is over 30, and is not a conservative, has not brains.
 -- Winston Churchill