John M. Dlugosz has asked for the wisdom of the Perl Monks concerning the following question:

I've experimented with "Perlscript" on a web page under Microsoft's ASP system (Photo Server) with some success and some issues.

Ideally, this requires a central database. I've tinkered with using ADO via Perl's OLE mechanism, as well as using the DBI:: module under both ADO and ODBC backends.

I like the DBI interface a lot better, probably because it's native Perl in nature. However, I don't see a way to find and update something. It's great for looking up, and for adding whole rows, but how do I modify something? It seems like it would be possible if the database engine had an SQL extension command for that, but Access doesn't.

A big deal in this application is translating between 6-character codes and file names. In a regular perl script, this is trivial—just use a hash. But for the photos and thumbnails, each GET runs the script from scratch and does one such lookup. Populating a hash and holding it between calls would be great, but it looks like ASP and CGI just don't do this normally. So maybe the ASP page can ask a background program to do this? But communicating with such a program also seems like more overhead, and just using the general database would be simpler.

Any thoughts would be appreciated.

—John

Replies are listed 'Best First'.
Re: approaching a database solution
by boo_radley (Parson) on May 18, 2001 at 00:41 UTC
    John,
    I'm a little confused by your question. I've worked access before, and you can script updates in VB without issue.
    In SQL, you should be able to alter existing rows in a table with a statement similar to 
    "UPDATE table SET field='foo' WHERE keyfield='value"

    [download]
    are you specifically saying that this doesn't work with (I assume) DBD::ADO or DBD::ODBC? If that's the case, what statement in your script is generating errors, or what's preventing from executing such a command?
[reply]
[d/l]
Re: approaching a database solution
by cacharbe (Curate) on May 18, 2001 at 01:12 UTC
    If you are within the ASP model Win32::ODBC should be all you need. I understand the want to stay with DBI, but the win32 module is fine for this

    The DSN can point at access, that shouldn't be a problem, though SQL or mySQL would be better.

        use Win32::ODBC;
    my $DSNName = "PHOTODSN";
    local $db = new Win32::ODBC ("DSN=$DSNName;UID=photouser;PWD=photo
+#user") || die "Error: " . Win32::ODBC::Error();

    my $sqlstrs = "UPDATE......";

    if(!$db->Sql($istr)){
        print "Succeeded \n";
        return 1
    }else{
       print "Failed ". Win32::ODBC::Error();
       return 0;
    }

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom