http://qs1969.pair.com?node_id=82229

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

When attempting to insert data into an access database I have discovered that none of the fields can be left blank. I have worked arounded by providing values that are displayed in the form for the user to typeover. Why do I need to provide values and is there a better way to handle this? This is tweaked code. I'm a novice perl writer.
#!/perl/5.005/bin/MSWin32-x86-object/ # Use the module use CGI; # Use the ODBC module use Win32::ODBC; # Create a connection to the form $q = new CGI; # Define the DSN $dsn = "helpdeskevaldb"; # Create a connection to the DSN $db = new Win32::ODBC($dsn); # Output the header and the start of the HTML print $q -> header(); print $q -> start_html(-title=>'Evaluation Form'); # Query the form fields $ID = $q -> param('ID'); $STFN = $q -> param('STFN'); $STLN = $q -> param('STLN'); $SRTLookup = $q -> param('SRTLookup'); $ToPRLookup = $q -> param('ToPRLookup'); $PSKLookup = $q -> param('PSKLookup'); $OSRLookup = $q -> param('OSRLookup'); $AC = $q -> param('AC'); $FN = $q -> param('FN'); $LN = $q -> param('LN'); $Dept = $q -> param('Dept'); $Phone = $q -> param('Phone'); # Save the form field data to the database my $rc = $db -> Sql("INSERT INTO EVAL (ID, STFN, STLN, SRTLookup, T +oPRLookup, PSKLookup, OSRLookup, AC, FN, LN, Dept, Phone) VALUES ('$ID','$STFN','$STLN','$SRTLookup','$ToPRLookup','$PSKLooku +p','$OSRLookup','$AC','$FN','$LN','$Dept','$Phone')"); # Return a success message print $q -> h1("Thank You for participating in our survey!"); print $q -> p("Data saved to database."); # Close the connection to the DSN $db -> Close(); # End the HTML and exit the script print $q -> end_html(); exit(0);

Replies are listed 'Best First'.
Re: Perl Access Database
by jeroenes (Priest) on May 22, 2001 at 18:39 UTC
    Lynn, acces accepts NULL for empty fields.

    Jeroen
    "We are not alone"(FZ)

Re: Perl Access Database
by fs (Monk) on May 22, 2001 at 20:39 UTC
    For this type of scenario, I usually store individual column names and values in a pair of arrays and join them, something like this
    if(is_valid($form{'field1'})){ push(@fields, 'field1'); push(@values, $form{'field1'}); } if(is_valid($form{'field2'})){ push(@fields, 'field2'); push(@values, $form{'field2'}); } # repeat, with appropriate validity tests, for # each field/column $query = $dbh->prepare("insert into table (" . join(',',@fields) . ") values (" . join(',', @values) . ")"); $query->execute;
      Prepared DBI queries are even nicer--you don't have to worry about doing the escaping of the values. Just use a ? for each value in @values in the SQL statement and then pass @values into $query->execute().
Re: Perl Access Database
by rucker (Scribe) on May 22, 2001 at 22:05 UTC
    You could probably solve the problem by changing your assignment statements to give a value when the user input is not defined. e.g. $AC = $q->param('AC') || "NULL"; If the user doesn't input anything for that variable, then you'll get the string "NULL" instead of undef.
Re: Perl Access Database
by SageMusings (Beadle) on May 23, 2001 at 00:10 UTC
    You have at least two options:
    1. You can edit the properties of your MS Access database in the design window. Fields can be designated to accept blank values. However, for referential integrity when relating to other tables, this is not an elegant solution.
    2. The best solution is to test a field for an undefined value prior to inserting data into the table. Simply, insert a text value "NULL". Here is an example of code I wrote to prevent just such an occurence:

    sub validate { $valid_data = 1; @parameters = qw/LASTNAME FIRSTNAME RANK BILLET EMAIL ERO UNIT DSN RUC NOMEN NSN SERNO COMPANY CONDITION DEFECT SHIP +MENT CORRECT COMMENTS/; @req_param = qw/LASTNAME FIRSTNAME RANK BILLET EMAIL ERO UNIT DSN RUC NOMEN NSN SERNO COMPANY/; foreach $response (@parameters) { print h3($response, " = ", uc(param($response))), hr; $CGI_hash{$response} = param($response); # remove punctuation( ' )which will crash the SQL $CGI_hash{$response} =~ s/'//g; # remove hyphens from numbers for consistency $CGI_hash{"NSN"} =~ s/-//g; # database demands non-blank values if ($CGI_hash{$response} eq "") { $CGI_hash{$response} = "NULL"; } $accumulator = $accumulator . uc($CGI_hash{$response}) . "','" +; } &parse_nulls; chop($accumulator); chop($accumulator); $NSN_len = length($CGI_hash{"NSN"}); if ($NSN_len != 13) { $valid_data = 0; } return;


    I hope this helps you.
      It's beginning to look like I need to know more about Access Databases. BTW what is handling the file/recordlock when I'm using ODBC? Thanks for you help! Also is there a proper way to thank everyone who has posted solutions?
Re: Perl Access Database
by cforde (Monk) on May 23, 2001 at 02:59 UTC
    Lynn, you should understand the data model before you start writing an application that references the data. Talk to the Data Administrator on why those columns are defined as they are; there should be a good reason for the definitions being used. Ideally, these definitions would be in a document somewhere you can easily get at. This document should include the data type definitions and the referential contraints (the rules on how the data in one table affects the data in another table). If you start messing about with the data before you understand what is allowed and what isn't, you will cause yourself unnecessary grief.

    Unless there is a very good reason to do otherwise, columns should be defined as NOT NULL (as in your case). Allowing NULLs permits sloppy data management which should be discouraged. Too often, NULL is abused to mean "default value" or "I don't know yet" or "the programmer missed that one" or "the customer didn"t answer that question" or ... There have been harsh words exchanged over what NULL really means. For now, just understand that they should be avoided unless you have an excellent reason for using them. And maybe not even then.

    ps. did you remove the error checking after the SQL calls?

    Have fun,
    Carl Forde

      Thank you for your reply. I also created the database and what I know about Access is even less than what I know about Perl. The fields that are probably giving me problems are text fields. The numeric fields are related radio buttons. Remove error checking? Not even sure I included error checking. I'm also missing a die statement incase it doesn't post to the database. That alone would have saved me hours. I should have visited here weeks ago. Lynn
Re: Perl Access Database
by MrCromeDome (Deacon) on May 23, 2001 at 01:00 UTC
    What I'm saying isn't much different than what others have already posted, but why I'm saying it is:

    MS Access can be set to accept NULL values. This is your easiest solution.

    Being a perl novice as well, I prefer this approach, though I recognize this is not a substitute for proper programming. I am not, however, a newbie to database programming, and while you can make your database accept NULL values, I don't recommend it. If you plan on doing any sort of analysis or statistics of the data you are collecting, having or allowing NULLs may bite you in the rear.

    Hope this helps!