[ic] database (DBI) access within a page

[interchange-users@icdevgroup.org (John Young)]

Barry D. Hassler asked:
> Is it possible to access DBI functions within a page (without usertags, etc)?
> I have a variety of database updates to perform based on a form input,  and it seems to make the most sense to do it right within the page (within a [perl] block).
> 
> I get "Safe: Can't locate object method "connect" via package "DBI" at (eval 216) line 37."

Sure.  You don't need connect, though.  Have a look at this:

[perl tables="userdb"]
    my $db_error = "some HTML yammering on about some kind of generic error";
    my $dbh = $Sql{userdb}
        or do {Log("FAILURE: Couldn't create dbh"); return $db_error;};
    my $sth = $dbh->prepare("UPDATE userdb blah, blah, blah")
        or do {Log("blah, blah, whatever..."); return $db_error;};
    $sth->execute();
    ....

You must share the table using tables="" in the perl tag.  I don't think it actually
matters much which table you specify, actually -- you can play with multiple tables
within the same database while only listing one of them in the perl tag.  So, in the
example above, I could also SELECT, UPDATE, INSERT, or whatever from tables
other than 'userdb'.

Notice that $Sql sort of takes the place of $dbh->connect(), but otherwise it will
look pretty much like regular Perl DBI stuff.

I'm sure there are more elegant ways of doing this (Kevin always has a much
prettier, cleaner suggestion for me on IRC than the Frankenstein kludges I concoct),
but the above works for me.  The 'Log' is something I tacked on in cases where the
UPDATE was uncommon, and I wanted to see it being used via log analysis tools.
The 'return' of an excuse in HTML I tacked on in order to try to fail gracefully and
feed the browser something generic.  I certainly welcome any better suggestions
on that kind of thing.

Take a look at icdatabase.txt for other approaches, and have fun.

HTH,
John Young