[ic] Simple SQL query in Interchange

Russell Mann interchange-users@icdevgroup.org
Fri Aug 2 06:22:02 2002


Hello List,

I'm trying to make a simple SQL query in Interchange and it is not working.
I have a query setup that outputs information, and I need to run a query
using a parameter on that.
Inside the list I need something to do this:

SELECT url FROM table WHERE title = '[absd-param category]' LIMIT 1;

And take the result and put it in a scratch variable.  I've tried using the
[data] tag, a nested [query] tag, and [perl] with $Tag->query.  None of
these approaches seem to work.

Here's the [perl]:

[seti book_name]
[perl tables=table]
my $sql = "SELECT url FROM table WHERE title = '[absd-param category]'";
my $result_array = $Tag->query( { sql => $sql,  }, $body );
my ($same_results, $col_name_hash, $col_name_array) = $Tag->query( { sql =>
$sql,  }, $body );
my $result_hasharray = $Tag->query( { sql     => $sql, hashref =>
'my_results',  }, $body );
return $result_hasharray{url};
[/perl]
[/seti]

I get this in the error:

Safe: no access for database products at
/usr/lib/interchange/lib/Vend/Data.pm line 906.
>
>
> my $sql = "SELECT url FROM table WHERE title = 'Category'";
> my $result_array = $Tag->query( { sql => $sql,  }, $body );
> my ($same_results, $col_name_hash, $col_name_array) = $Tag->query( { sql
=> $sql,  }, $body );
> my $result_hasharray = $Tag->query( { sql     => $sql, hashref =>
'my_results',  }, $body );
> return $result_hasharray{url};
>


Here's the [query] (remember it's embedded):

[query
prefix=absh
table=table
list=1
type=list
st=sql
sql=|
SELECT url
FROM table
WHERE title = '[absd-param category]'|
ml=1
]
[list]
[seti book_name][absh-param url][/seti]
[/list]
[/query]

This bombs out because it's embedded inside another query with a different
prefix (absd) and causes that query's tags to not be interpolated.

Here's the attempt at using [data]:

[data table="table" field="url" column="book" key="[scratch book_name]"]
Assuming Field= the field I want to retrieve, Column=Column to compare Key
to, and Key= value to query from column.
This doesn't work.

Any ideas on how to do this?  In PHP it's really easy :)

Thanks,

R