Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Putting a long form into a database

by bar10der (Beadle)
on Apr 14, 2004 at 14:09 UTC ( #345061=perlquestion: print w/replies, xml ) Need Help??

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


I have an HTM form which has 60-70 fields to be filled by user. My question is -

Is there a simple way of putting all the values from the form into my Oracle Table or do I have to get each form fields in some variable and then put it into database? I am using CGI::Application to process the form.

Any help guidance/will be greatly appreciated.

Replies are listed 'Best First'.
Re: andling form data
by matija (Priest) on Apr 14, 2004 at 14:34 UTC
    So what does the table look like? Does it have all the values as columns in a single row? (And I hope thae columns are named the same as the variables in the web page). Or does it have a "name of the variable" column and a "value" column?

    If the former, something like

    my $col="insert into table mytable ("; my $val="values ("; foreach (param()) { $col.="$_,"; $val.="?,"; push(@arr,param($_)); # Beware! this will break if any variables hav +e multiple values. } $val=~s/,$//; $col=~s/,$//; $statement="$col) $val)"; $sth=$dbh->prepare($statement); $sth->execute(@arr);
    If the table is in name/value pairs, then the code is much simpler:
    my $sth=$dbh->prepare("insert into table mytable (name,value) values ( +?,?)"); foreach (param()) { $sth->execute($_,param($_)); # will only insert the first value if a + variable has multiple values }
      Thanks Matija for your help. My table has column names same as field names in the form. So I think 1st part of the code will work for me. However I am not sure about one thing -

      what happens if a field in the form is left blank? Will $val contain null for that field?

        If the browser doesn't pass the empty field (which most browsers don't I think), then it won't be set at all (that is part of the purpose of enumerating the fieldnames in the INSERT statement) and it's value will be whatever default you set up for the table - most probably NULL or empty string.

        If the browser does pass fields without values entered, then they will be set to empty string in the database.

Re: Handling form data
by freddo411 (Chaplain) on Apr 14, 2004 at 18:58 UTC

    Gernerally speaking, you haven't addressed much of the problem in your question. You had better check the data coming in from the CGI or you could run into trouble very quickly. What problems specifically? Your DB will throw an error if you try to set a column that doesn't exist; if the data is too long your DB will puke, etc. You'll most likely want a slick module like CGI::Application::ValidateRM to help you validate input.

    Also, In my code, I keep an array of valid fields name that make up my DB table and I only pass through params that match those.

    You may also want to wrap your DB routines in an eval to catch the exceptions cleanly and return a friendly HTML page. Or you can:

    use CGI::Carp qw(fatalsToBrowser );

    Check out the tutorials here on PM.

    Good luck.

    Nothing is too wonderful to be true
    -- Michael Faraday

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://345061]
Approved by cfreak
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2021-09-24 10:27 GMT
Find Nodes?
    Voting Booth?

    No recent polls found