Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

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

by lwicks (Friar)
on Dec 21, 2003 at 11:01 UTC ( [id://316164]=note: print w/replies, xml ) Need Help??


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

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!

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)
by jZed (Prior) on Dec 21, 2003 at 19:05 UTC
    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.

      Hi Jeff

      Cheers for another useful reply.
      As is obvious I am a newbie PERL coder, so my work is a bit ropey. Never used DBI or done stuff with SQL really, so the sum of it all is just plain scary!

      Anyway, I shall take what you have kindly written and see what sort of mess I can create. Watch this space!

      Lance

      P.s. If you think my code at the moment is bad, you should have seen it before I started from scratch! :-)

      Kia Kaha, Kia Toa, Kia Manawanui!
      Be Strong, Be Brave, Be perservering!
        Hi all,
        Here is my ammended code, does not work however, can someone help?

        Starts okay I get the form, but when I fill the form in I get stuck. Basically all I get is the following messages on the screen:

        CREATE NEW USER

        Start main block

        parameters: ID first_name surname date_of_birth email ejudopass submit button

        As you can see the param() function seems to be where the problem is, what am I doing wrong?
        Cheers

        Lance

        #!/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. # 23 December 2003, Lance Wicks - On the suggestion of Jeff Zucker, am + re-writing this code to use more sub-routines and some other good su +ggestions. my $DEBUG = 1; # If this is set to 1 then we see the debug messag +es. # The following 4 lines set strict PERL coding then load the CGI DBI a +nd DBD::Anydata modules. # -------------------------------------------------------------------- +------------------------- 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 # Sub routines # -------------- sub collect_user_input { # This routine collects the data entered by the user from + the form and adds the initial values and returns the @user_data arra +y # ------------------------------------------------------- +-------------- print p("Start of collect_user_input") if $DEBUG; my @user_data; # initialise the array we will use to sto +re all these pieces of data $user_data[0] = param("ID"); # the next few lines alocate + the info from the completed form to variables. $user_data[1] = param("first_name"); $user_data[2] = param("surname"); $user_data[3] = param("date_of_borth"); $user_data[4] = param("email"); $user_data[5] = param("ejudopass"); # now give values to the other user data field variables $user_data[6] = "NO"; # ($active) the user is not immed +iately active, we will email them first. $user_data[7] = "now"; # ($last_login)set the last login +time to now $user_data[8] = "now"; #($create_data) set the date we cr +eated this user to now as well $user_data[9] = 0; # ($earnings) They have not earned any + thing yet so set it to Zero $user_data[10] = 50; # ($cash) This is their cash on hand +, lets give them 50 credits by default $user_data[11] = 1; # ($judoka_limit) They can create 1 J +udoka $user_data[12] = 0; # ($sensei_limit) They can NOT create + a sensei (YET) $user_data[13] = 0; # ($dojo_limit) They can not create a + Dojo $user_data[14] = 0; # ($team_limit)They can not create a +team $user_data[15] = "Novice"; # ($rank) They are a novice us +er to start with return @user_data; print p("END of collect_user_input") if $DEBUG; } # end of subroutine collect_user_data sub validate_user_input { # This sub routine receives the @user_data ar +ray from collect_user_input sub-routine, it validates it to make sure + it is safe for the database, then return the @user_data array # ------------------------------------------- +--------------------------------------- print p("Start of validate_user_input") if $D +EBUG; my @internal_user_data = @_; # add some data validation of user input here return @internal_user_data; print p("END of validate_user_input") if $DEB +UG; } # end of sub routine validate_user_input sub add_user_input_to_db { # This routine receives @user_data from validate_user_inp +ut and then adds it to the Database using SQL # ------------------------------------------------------- +--------------------------------------------- # Next connect to the database and check if they exist al +ready. print p("Start of add_user_input_to_db") if $DEBUG; my @internal_user_data = @_; # This line takes the user_d +ata passed to us from the previous routine (@_) and allocates it to o +ur internal user data array my $entered_id = $internal_user_data[0]; # just so that +it is clearer later we create a variable from the passed data # Use DBI to connect to the users.csv datafile #----------------------------------------------- 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 # select from the datafile the id for the user ID from th +e array paased from the previous sub routine my $sql = "SELECT id FROM users WHERE id = ?"; # th +is is the SQL command we want to execute my @params = ($entered_id); # Th +eese are the parameteres we will use in the SQL command above print "$sql\n[@params]\n" if $DEBUG; # if + we are in debug mode print the SQL statement my $sth = $dbh->prepare( $sql ); # pr +epare the SQL command $sth->execute( @params ); # ex +cecute the SQL using our parameters 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 $DEBUG; # Pr +ints the result of our SQL command if we are in debug mode. 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"); } else { print p("about to insert the new record")if $D +EBUG; # if the user does not exist then add them! # so connect to the database 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 # add the data into a new record my $sql = "INSERT INTO users VALUES ( ?,?,?,?, +?,?,?,?,?,?,?,?,?,?,?,? )"; # this is the SQL command we want t +o execute my @params = (@internal_user_data); + # Make the parameters those passed to us from the previous rou +tine (and originally from the user) print "$sql\n[@params]\n" if $DEBUG; + # if we are in debug mode print the SQL statement my $sth = $dbh->prepare( $sql ); + # prepare the SQL command $sth->execute( @params ); + # excecute the SQL using our parameters print p("Inserted Record")if $DEBUG; + # just a reference in debug mode } print p("END of add_user_input_to_db") if $DEBUG; } # end of sub routine add_user_input_to_db sub print_user_data_form { # This subroutine create the user data web pa +ge which is filled in, the data is then used by other routines. print p("START of print_user_data_form") if $ +DEBUG; print hr, start_form; # create a for +m using CGI.PM print p("User ID: ", textfield("ID"), " - Thi +s is the ID you will use to login to the system"); # what user +name do they want. print p("First Name: ", textfield("first_name +"), " - This is your REAL name"); # what is their real name print p("Surname: ", textfield("surname"), " +- This is your REAL 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 ev +er need to prove who you are"); print p("Email address: ", textfield("email") +, " - the email address you would like to use."); print p("Password: ", password_field("ejud +opass"), " - Choose a password to use on the system"); print submit(-name=>'submit button'); print end_form, hr; + # end the form print p("END of print_user_data_form") if $DE +BUG; } # end of print_user_data_form # end of subroutines # ------------------- # main code block # --------------- print header(), start_html("e-Judo Test Area"), h1("CREATE NEW USER"); + # This line uses CGI.PM to to create the webpage print p("Start main block") if $DEBUG; print p("parameters: ", param()) if $DEBUG; print p("no parameters so print the form") if !(param()); 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 my @temp_variable = collect_user_data(); print p("collect user data gave us: @temp_variable") if + $DEBUG; my @temp_variable2 = validate_user_input(@temp_variable +); print p("validate user data gave us: @temp_variable2") +if $DEBUG; my @temp_variable3 = add_user_input_to_db(@temp_variabl +e2); print p("add user data to db gave us: @temp_variable3") + if $DEBUG; } else { # if there are no parameters (the form has not yet be +en filled in then.... print_user_data_form(); } print end_html; # this closes the web page properly print p("End of main block") if $DEBUG;
        Kia Kaha, Kia Toa, Kia Manawanui!
        Be Strong, Be Brave, Be perservering!
      Just a small addition to this.
      I have added a line that shows me the parameters returned from param()

      It just lists the fields from the CGI form as opposed to the values.

      If someone can help on this I'd appreciate it.

      Lance

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://316164]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others sharing their wisdom with the Monastery: (6)
As of 2024-03-28 08:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found