Ok, so I've wasted entirely too much time on this and I'm about ready to go
back to DBI (where I could probably solve this problem in 10 minutes.)
The basic question is what's the ASP/PerlScript way to fetch a row of data
into a hash the way DBI's fetchrow_hashref() does?
If you like that sort of thing here are the gory details.
I've got a database named "Enterprise." Inside the database I have a table
named "Customers." Inside the table I have a bunch of fields named
"FirstName," "LastName," "PhoneNumber," etc.
I've written a generic little form handling function that for any given SQL
table and a corresponding web form would
1) Collect all form field data into a hash, disregarding empty fields
#omitting much code for validation and exception handling
foreach my $field (sort keys %Fields) {
#disregard empty fields
$Fields{$field} = $Request->Form($field)->{Item} if
$Request->Form($field)->{Item};
}
2) With the help of (sort keys %Fields), generate a generic SQL insert
statement that also omits empty fields.
#Compose SQL query to insert a record
# in the pattern "INSERT INTO Customers
# (field1, field2,...) VALUES ('value1', 'value2, ...)
$sql = "INSERT INTO Customers (";
#Compose list of fields
foreach my $field (sort keys %Fields) {
$sql .= "$field, " if $Fields{$field};
}
$sql .= ") VALUES (";
#Compose matching list of values
foreach my $field (sort keys %Fields) {
my $CleanField = $Fields{$field};
$CleanField =~ s/'/''/g; #escape SQL quote character
#Note: Yes there are more sophisticated ways to handle SQL characters
$sql .= "'$CleanField', " if $CleanField;
}
$sql .= ")";
$sql =~ s/, \)/ \)/g; #baldfaced hack to remove the final comma from each
list
So that's pretty cool. At least in my little programming universe this
works with any subset of form and database fields (as long as the form field
names correspond to database fields.)
The key to all this is that (sort keys %Fields) lets me match names with
values. But what do I do if I want the same thing with an SQL Select
statement?
For instance I'd like to be able to do something like
$sql = "SELECT * FROM Customers WHERE KnownValue = $Fields{KnownValue}";
$RS = Conn->Execute($sql);
foreach $dbfield (in (sort keys $RS->FieldsHash )) { #don't even know
correct structure syntax
$Fields{$dbfield} = $RS->FieldsHash{$dbfield}->{Value}; #or something
like
this
}
where $RS->FieldsHash{foo} contains the value for the DB fieldname foo in
the current record.
and then later in the form I could populate a particular field with default
information...
<input type="text" name="FirstName" size="20"
value="<%=$Fields{FirstName}%>">
To summarize, I'm up to my neck in data entry and data update forms. Right
now I'm handcoding too much when the right bit of code could do it all for
me. There's more than one way to do it, but so far they're eluding me. I'd
appreciate your help.
Thanks,
-- David Innes
P.S. it doesn't help that I think I'm missing an answer that's right in
front of my nose.
_______________________________________________
Perl-Win32-Web mailing list
[EMAIL PROTECTED]
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-web
