http://qs321.pair.com?node_id=314689


in reply to Re: Re: Re: DBD::CSV - how to install? (FTP only)
in thread DBD::CSV - how to install? (FTP only)

Hi again,
still struggling here, would appreciate it if someone could help me with the below code which fails.

The below code fails on the line:
$dbh->func( 'users', 'CSV', 'users.csv' 'ad_catalog');

Cheers!

-----------------

#!/usr/bin/perl -w use strict; require 'html.pl'; use lib './MyLib'; use DBI; my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); $dbh->func( 'users', 'CSV', 'users.csv' 'ad_catalog'); my $sth = $dbh->prepare("SELECT surname FROM users"); $sth->execute(); while (my $row = $sth->fetch) { print "@$row\n"; } # define variables header(); print("<table width='90%'>"); print("<TR valign='top'><TD width='30%'>"); print ("Menu<BR>"); print ("====<BR>"); print ("<a href='create-user.cgi'>Create New User</a><BR>"); print ("Login User<BR>"); print ("User Admin<BR>"); print("</TD>"); print("<TD>"); print ("CREATE USER<BR>"); print ("==========<BR>"); print ("This is where the code to create new users will be place +d"); print ("<P>"); print ("USER VARIABLES<HR>"); my %user_data = qw( id blank first_name blank surname blank date_of_birth blank email blank password blank active blank last_login blank create_date blank earnings blank cash blank judoka_limit blank sensei_limit blank dojo_limit blank team_limit blank rank blank ); open (OUTFILE, ">./user_data/test_user"); while (($a, $b) = each(%user_data)){ print ("Key = $a and its value is $b<BR>"); print OUTFILE ("$a\n$b"); } close (OUTFILE); print ("<HR>USERS ON SYSTEM<HR>"); opendir(USERS, "./user_data"); while ( my $name = readdir(USERS)) { if ($name ne '.' and $name ne '..') { print ("<a href='./user_data/$name'>$n +ame</a><BR>"); } } closedir(USERS); print("</TR>"); print("</table>");
Kia Kaha, Kia Toa, Kia Manawanui!
Be Strong, Be Brave, Be perservering!

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)
by jZed (Prior) on Dec 15, 2003 at 05:39 UTC

    The below code fails on the line: $dbh->func( 'users', 'CSV', 'users.csv' 'ad_catalog');

    What is the error or warning message you get?

    Update oh never mind, I just spotted it (although in the future please always show the error message rather than just saying "it failed"). The problem here is that you don't have a comma between 'users.csv' and 'ad_catalog'. If you copied that from the docs and the docs look like that, please let me know where so I can fix it :-).
      Jeff, thanks for your help on this, you'd never guess I am a Noob eh?! Okay, I am almost there, I have read access working and am attempting to add a record/row to the database file and get the following error:
      CREATE NEW USER start of the data about to open the DB SQL ERROR: Bad table or column name '\?QI0?' has chars not alphanumeri +c or underscore! DBD::AnyData::db prepare failed: SQL ERROR: Bad tabl +e or column name '\?QI0?' has chars not alphanumeric or underscore! result = about to insert the new record
      Please ignore the comments they are just print statements I have been using to tell me what is going on. The code I have written is below:
      #!/usr/bin/perl -w # --------------------------------------------- # create-user.cgi - Create by Lance Wicks # e-judo.sourceforge.net # This is free open source software! Released under GPL # # Description: # This script adds a new user to the users database # # History: # ======== # 20 December 2003, Lance Wicks - Created initial file. # use strict; # force strict programming controls use CGI qw(:standard); # use the CGI.PM module use lib './MyLib'; # use the modules in MyLib, this is the DBD::Anydat +a used for database activities use DBI; # This calls the DBI module, which along with the line above +allows us to do database activities print header(), start_html("e-Judo Test Area"), h1("CREATE NEW USER"); + # This line uses CGI.PM to to create the webpage if (param()){ # If there is a parameter(or parameters) then validate, + else show the login screen. # the following lines are excecuted if paramaters HAVE been entered print p("start of the data"); my $ID = param("ID"); # the next few lines alocate the in +fo from the completed form to variables. my $first_name = param("first_name"); my $surname = param("surname"); my $date_of_birth = param("date_of_borth"); my $email = param("email"); my $password = param("ejudopass"); # now give values to the other user data field variables my $active = "NO"; # the user is not immediately active +, we will email them first. my $last_login = "now"; # set the last login time to now my $create_date = "now"; # set the date we created this u +ser to now as well my $earnings = 0; # They have not earned any thing yet so + set it to Zero my $cash = 50; # This is their cash on hand, lets give th +em 50 credits by default my $judoka_limit = 1; # They can create 1 Judoka my $sensei_limit = 0; # They can NOT create a sensei (YET +) my $dojo_limit = 0; # They can not create a Dojo my $team_limit = 0; # They can not create a team my $rank = "Novice"; # They are a novice user to start wi +th # Add in some data validation here!!!! # Next connect to the database and check if they exist al +ready. print p("about to open the DB"); my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); # +tell DBI we want to use the Anydata module in ./MyLibs $dbh->func( 'users', 'CSV', 'data/users.csv', 'ad_catalog +'); # Connect to the users.csv data file my $sth = $dbh->prepare("SELECT id FROM users WHERE id = +'$ID'"); # select the ID field from the database for the ID enetered +by the user $sth->execute(); # excecute the select command above my @result = $sth->fetchrow_array; # this line takes the +results of the select and puts it in the array called RESULTS $dbh->disconnect(); # we are done with the datbase for no +w, so disconnect from it (MAY NOT BE NECESSARY) print p("result = @result"); if (@result) { #if the result array is in existence (i +e we found the username) then... print p("Sorry this ID is in use a +lready"); exit; } else { print p("about to insert the new record"); # if the user does not exist then add them! # so connect to the database and insert a reco +rd my $dbh = DBI->connect('dbi:AnyData(RaiseError +=>1):'); # tell DBI we want to use the Anydata module in ./MyLibs $dbh->func( 'users', 'CSV', 'data/users.csv', +'ad_catalog'); # Connect to the users.csv data file my $sth = $dbh->prepare('INSERT INTO users V +ALUES ( \" $ID, $first_name, $surname, $date_of_birth, $email, $password, $active, $last_login, $create_date, $earnings, $cash, $judoka_limit, $sensei_limit, $dojo_limit, $team_limit, $rank \" ) '); $sth->execute(); # excecute the command abov +e # $dbh->commit(); $dbh->disconnect(); print p("Inserted Record"); } } else { # The following lines are excecuted only if no parameters have been en +tered. (Ie when you first arrive) print hr, start_form; # create a form using CGI.PM print p("User ID: ", textfield("ID"), " - This is the ID you w +ill use to login to the system"); # what username do they want +. print p("First Name: ", textfield("first_name"), " - This is y +our REAL name"); # what is their real name print p("Surname: ", textfield("surname"), " - This is your RE +AL last name"); # what is their real name print p("Date of Birth: ", textfield("date_of_birth"), " - (DD +/MM/YYYY) This is your date of birth, used if you ever need to prove +who you are"); print p("Email address: ", textfield("email"), " - the email a +ddress you would like to use."); print p("Password: ", password_field("ejudopass"), " - Choo +se a password to use on the system"); print end_form, hr; +# end the form } print end_html; # this closes the web page properly
      Again thanks for the help from you and everyone so far, I am slowly getting brain into gear and starting to understand things a little.
      Kia Kaha, Kia Toa, Kia Manawanui!
      Be Strong, Be Brave, Be perservering!
        Hi Lance, I'd recommend that you separate the CGI stuff from the DBI stuff so that you can debug them separately. Make a get_data_from_CGI() method and get_data_from_constants() method that both return a list of values, then feed that list to a do_db_stuff_with_values() mehtod. Make sure it works with the constants ( e.g. $ID = '999' ), then you'll know your DBI stuff is working and you can then check it from CGI input.

        My strong suspicion is that you're not getting a $ID value from the form and that you are essentially trying to insert '' into the database which (because of a bug) produces the odd error message you got.

        The other thing that is tripping you up is the quoting in SQL. A SQL insert looks something like this: INSERT INTO foo (first_col,second_col) VALUES ('string',7). Notice that string has single quotes around it and the number does not. It works the same way with variables - INSERT INTO foo (first_col,second_col) VALUES ( '$string', $number ). Your INSERT statement has no quotes around anything. The best solution is to never have to care about the quotes: USE PLACEHOLDERS:

        my $sth = $dbh->prepare(" INSERT INTO foo (first_col,second_col) VALUES (?,?) "); $sth->execute( $string, $number );
        No quotes, no muss, no fuss. See the DBI docs on placeholders.

        Another good way to catch problems is to always (when debugging) print out the SQL before executing it:

        my $DEBUG = 1; # comment out when not debugging my $sql = "INSERT INTO foo (first_col,second_col) VALUES (?,?)"; my @params = ($string,$number); print "$sql\n[@params]\n" if $DEBUG; my $sth = $dbh->prepare( $sql ); $sth->execute( @params );
        DBI::trace() will also show you that same information, eventually you might want to check it out (see the DBI docs).

        Hopefully all that is enough for you to be able to debug your script. If not, holler.