jerrygarciuh has asked for the wisdom of the Perl Monks concerning the following question:

Dread Monks,
I am contemplating laziness today and wish advice on this sketch of a program for updating a db table via DBI regardless of how many or few fields are supplied by the cgi form. Here is the idea:
  1. All db fields and form inputs for db fields are prefaced with id characters like so: db_foo
  2. Script parses input and creates @column_names containing the db fields this form is supplying
  3. The question marks for inserts can be done like so: my @question_marks = ('?') x @column_names;  #Thx [Zaxo]
  4. And the $sth->execute() can contain an @ of the values of the keys from %form_data which begin with db_
What do folks think? I am still contemplating how best to structure the UPDATE queries as the above strategy would work better for a straight INSERT than an UPDATE, I think.

Opinions requested and appreciated!
TIA
jg
_____________________________________________________
Think a race on a horse on a ball with a fish! TG

Replies are listed 'Best First'.
Re: Flexible Database Updates
by Arguile (Hermit) on Sep 08, 2002 at 19:24 UTC

    You might also want to check out chromatic’s perl.com article DBI is Okay.

    Generalised INSERT and UPDATE functions are very common stuff.

    Addendum: While I'm personally not fond of much of the DBIx CPAN namespace, there are many modules dealing with this and more ‘programmatic’ alternatives to dealing directly with SQL.

[reply]
Re: Flexible Database Updates
by dws (Chancellor) on Sep 08, 2002 at 19:51 UTC
    If you stop at   my @question_marks = ('?') x @column_names;  #Thx [Zaxo] you're going to generate unhappy queries. Try something like  my $question_marks = join(',', ('?') x @column_names); You could try to remember do local $" = ','; before interpolating @question_marks, but that seems to me to be an uneccessary, error-prone step.

[reply]
[d/l]
[select]
      You are, of course, correct. I have been setting $" locally to deal with that problem.
      Thanks for the tip, I'll definitely use it.
      Peace,
      jg
      _____________________________________________________
      Think a race on a horse on a ball with a fish! TG
[reply]
[d/l]
        A scheme that requires that for the thing you did over here to work, you also need to do something over there is problematic. In computer sciency terms, you've introduced a form of "coupling". (Remember the mantra "Cohesion is good, coupling is bad.")

        One risk is that someone will copy/paste from your solution, pick up @questions_marks, neglect to also copy the change to $", and then wonder why what works for you doesn't work for them (and then burn up their coworkers' time (and then post to PM)).

[reply]
[d/l]
[select]
Re: Flexible Database Updates
by jerrygarciuh (Curate) on Sep 08, 2002 at 19:18 UTC
[reply]
Re: Flexible Database Updates
by kschwab (Vicar) on Sep 08, 2002 at 22:23 UTC
    You might want to give Class::DBI a try. It goes a little further with the abstraction.
[reply]
Re: Flexible Database Updates
by rir (Vicar) on Sep 08, 2002 at 20:13 UTC
    I've just been looking at this from the static code generation side.

    Consider if your database may have arrays. This can
    double the complexity of building your statements.

[reply]
      Arrays or extents (or what ever your RDMS wants to call them) are bad from a database design standpoint and should not be used, unless you have one helluva good reason (I personally have never had a good reason to use them, ever).

      Extents break 1NF and are the equivalent of either:

      1. Table CD: cd_name, artist, song1, song2, song3,...

      2. comma delimited strings in fields

      This makes for bad code - code which has to cover for bad database design.

      So it is not a question of 'This can double the complexity of building your statements'. Array/extents will add unneeded complexity of your code, period.

      An article on Database Normalization should explain why this is bad



      grep
      Mynd you, mønk bites Kan be pretti nasti...
[reply]
        I think grep & I agree that using the array mechanisms
        of RDMSes is usually a bad thing.
        I disagree that "Array/extents will add unneeded complexity"
        to your code period.

        I do believe that fixed size arrays can be and often are
        best put in a single record. Some examples are so often
        done that they are invisible.

        First, middle, and last- names are an example that could be

        create table name (
   id   keytype,  -- constrained to parent
   seq   integer, -- place in name set
   name text
);

        [download]

        but who wants to deal with that?

        The usage of address_line_1 and address_line_2 can be
        viewed as an array embedded in a record.
        Financial applications show arrays for the 12 or 13 months
        of the year.
        I used a pair of arrays to describe a set of items. There
        could be upto 5 dimensions & 5 dimension_names.
        All of these belonged in one record.

        That the fullname or address should be one field is a
        excellent alternative, but not always do-able.
        Printing envelopes with a single address string may
        be problematic.

        Using a single fullname may induce the use of a nickname,
        possibly recreating the initial issue.

        The solution that I am currently implementing/trying is
        to use arrays on the Perl side and separate fields on the
        SQL side. A naming convention maps $o->{elem}[0] to elem_0,
        and $o->{address1} to address1.

[reply]
[d/l]
[select]
Re: Flexible Database Updates
by rdfield (Priest) on Sep 09, 2002 at 08:28 UTC
    Be careful with the ordering of the column_names array: it's very easy to parse input params and make a list of columns to be updated, but you need to make sure that the columns mentioned in the 'where' clause are ordered correctly and at the end of @column_names.

    rdfield

[reply]
[d/l]

Back to Seekers of Perl Wisdom