[ic] sql insert/update with interchange?

[Kevin Walsh]

T. Stoffels [tstoffels at gmail.com] wrote:
>
> I noticed it's possible to write to my database from within an
> interchange page (to save values passed by a form for example) by
> 'abusing' the query tag:
>
> [query sql=|insert into mytable (myvalue) values(13) | ]  [/query]
> 
> It works, no doubt about that, but I can't imagine it was intended to
> be that way.
>
It is intended to work that way, yes.  The [query] tag can handle any
SQL query allowed by your database userid - including SELECT, UPDATE,
DELETE etc.  It is not an abuse of the [query] tag to use SQL. :-)

>
> The query.../query tag seems to return '1' btw, (which appears as
> output on my page)
>
That's fine as well.  You can use the result code, or just capture
it in a scratch variable: [tmp foo] ... [/tmp].

> 
> So, is there some special interchange tag for writing to a database?
>
[data] is one such tag.

>
> Or maybe interchange provides a ready-to-use DBI-handle which can be
> accessed from within a [perl]...[/perl ] statement?
> 
Try something like the following:

    [perl tables="sometable"]
        my $tablename = 'sometable';
        my $db = $Db{$tablename} or die "Bad table: $tablename";

        my $query = qq{
            INSERT
            INTO    $tablename (fname, lname)
            VALUES  ('Kevin', 'Walsh')
        };
        $db->query($query) or die "Can't INSERT into $tablename";
        undef;
    [/perl]

If that's all you wanted to do then you might as well just use the
[query] tag.  If you have other processing in mind then the above
[perl] snippet might come in handy.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin at cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/