Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

DBD::mysql trouble

by vendion (Scribe)
on Sep 22, 2009 at 18:20 UTC ( #796790=perlquestion: print w/replies, xml ) Need Help??

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

I'm sure that you heard enough of DBD::mysql but I am wanting to ask a question on a problem and needing someone who knows perl to bounce ideas off of.

First of all the script that I am working on runs fine until it gets the point where it needs to insert data into the mysql server. The error that I get is as follows:

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Dell, Optiplx , 600 , WinXP , None , fixing , Slow , 000-0001 , password' at line 1 at repairs.pl line 110, <STDIN> line 14. DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Dell, Optiplx , 600 , WinXP , None , fixing , Slow , 000-0001 , password' at line 1 at repairs.pl line 110, <STDIN> line 14.

Here is my code as of now

#!/usr/bin/perl use strict; use warnings; use DBI; #Declaire all variables now! my $customer_firstname; #Store the customers first name my $customer_lastname; #Store the customers last name my $customer_phone; #Store the customers main phone number my $customer_email; #Store the customers email address my $customer_date; #date that the entry was made my $customer_AcceptTerm; #Accept Term of use? my $customer_compman; #Store the computer manufaturer my $customer_compmodel; #Store the computer model my $customer_compModelNum; #Store the computer model number my $customer_errormsg; #Store any and all error messages displaied my $customer_os; #Store what OS is being used my $customer_probdesc; #Store the problem description my $customer_loginpw; #Store the password for the admin/root user my $customer_service; #Store what services need to be done. my $customer_barcode; #Store the barcode for the computer. my $database = "Repairs"; #Specify the database to be used my $hostname = "192.168.1.111"; #Connect to the MySQL server my $port = '3306'; #MySQL port my $user = 'csccrepairs'; #MySQL username my $password = 'repairshop'; #MySQL password my $dsn; my $dbh; system("clear"); #system("clear") only works on Unix based systems, ne +ed to come up with #something that works on windows print "Cleveland State CC Computer Repairs\n\n"; print "Version 0.2\n", "Copyleft 2009 Adam Jimerson & Andy Arp\n"; main(); #break here sub main() { print "----------------------------\n", "Main Menu, To make a selection specify the number for that ac +tion\n"; print "1. Add New Computer to Database\n"; print "2. Edit Computer Status in Database\n"; print "3. Remove a Computer from Database\n"; print "4. Look up Computer Information\n"; print "5. List All Repair Requests\n"; print "Action: "; my $option = <STDIN>; chomp($option); if ( $option eq '1' ) { print_form(); } else { die "Unknown Action: $option\n"; } } sub print_form { print "\nCustomer Information\n", "-----------------------\n"; #Begin the customer information f +orm print "Customer First Name: "; $customer_firstname = <STDIN>; chomp($customer_firstname); print "Customer Last Name: "; $customer_lastname = <STDIN>; chomp($customer_lastname); print "Customer Phone #: "; $customer_phone = <STDIN>; chomp($customer_phone); print "Customer Email: "; $customer_email = <STDIN>; chomp($customer_email); print "\nComputer Information\n", "-----------------------\n"; #Begin the computer information f +orm print "Computer Manufacturer: "; $customer_compman = <STDIN>; chomp($customer_compman); print "Computer Model: "; $customer_compmodel = <STDIN>; chomp($customer_compmodel); print "Computer Model #: "; $customer_compModelNum = <STDIN>; chomp($customer_compModelNum); print "Error Message (if any): "; $customer_errormsg = <STDIN>; chomp($customer_errormsg); print "Operating System: "; $customer_os = <STDIN>; chomp($customer_os); print "Problem Description: "; $customer_probdesc = <STDIN>; chomp($customer_probdesc); print "Administrator/Root Login Password: "; $customer_loginpw = <STDIN>; chomp($customer_loginpw); print "Computer Service Needed: "; $customer_service = <STDIN>; chomp($customer_service); print "Barcode Number: "; $customer_barcode = <STDIN>; chomp($customer_barcode); $customer_date = system('date'); db_add(); } sub db_add { #Insert values retreived from print_form() $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; $dbh = DBI->connect($dsn, $user, $password, {RaiseError => 1}); $dbh->do("INSERT INTO Repairs.computers VALUES (compManufacturer, +compModel, compModelNum, OS,errorMsg , serviceNeeded, problemDescript +ion, barcode, password) ($customer_compman, $customer_compmodel , $cu +stomer_compModelNum , $customer_os , $customer_errormsg , $customer_s +ervice , $customer_probdesc , $customer_barcode , $customer_loginpw") +; $dbh->do("INSERT INTO Repairs.customers VALUES(firstname, lastname +, email, phone, date) ($customer_firstname, $customer_lastname, $cust +omer_email, $customer_phone, $customer_date"); $dbh->disconnect(); } exit 0;

As far as what I need to bounce off someone is order to cut down on the number of scalier variables will the use of hashes further mess up DBD::mysql?

Replies are listed 'Best First'.
Re: DBD::mysql trouble
by Joost (Canon) on Sep 22, 2009 at 18:24 UTC
Re: DBD::mysql trouble
by zwon (Abbot) on Sep 22, 2009 at 18:28 UTC

    The error is because values aren't quoted. Rewrite your queries as follows:

    $dbh->do("INSERT INTO Repairs.customers (firstname, lastname, email, p +hone, date) VALUES (?,?,?,?,?)", {}, $customer_firstname, $customer_l +astname, $customer_email, $customer_phone, $customer_date);

      Ok after doing that meathod the error I get now is:

      DBD::mysql::db do failed: Column count doesn't match value count at row 1 at repairs.pl line 112, <STDIN> line 14
      As for the suggestions about using arrays I was thinking about changing to that or a hash once I got the mysql part to work, yes I agree it would be easier and look cleaner but that is the last thing I have to worry about here

Re: DBD::mysql trouble
by leocharre (Priest) on Sep 22, 2009 at 19:52 UTC

    This is off topic.. Your code looks really repetitive.
    This is not a negative criticism- This is a suggestion- that there are ways of doing what you're doing in much more pleasant ways. It appears to me you code perl as you would code bash.. (?)..

    For example in this chunk..

    #Declaire all variables now! my $customer_firstname; #Store the customers first name my $customer_lastname; #Store the customers last name my $customer_phone; #Store the customers main phone number my $customer_email; #Store the customers email address my $customer_date; #date that the entry was made my $customer_AcceptTerm; #Accept Term of use? my $customer_compman; #Store the computer manufaturer my $customer_compmodel; #Store the computer model my $customer_compModelNum; #Store the computer model number my $customer_errormsg; #Store any and all error messages displaied my $customer_os; #Store what OS is being used my $customer_probdesc; #Store the problem description my $customer_loginpw; #Store the password for the admin/root user my $customer_service; #Store what services need to be done. my $customer_barcode; #Store the barcode for the computer. my $database = "Repairs"; #Specify the database to be used my $hostname = "192.168.1.111"; #Connect to the MySQL server my $port = '3306'; #MySQL port my $user = 'csccrepairs'; #MySQL username my $password = 'repairshop'; #MySQL password my $dsn; my $dbh;
    You could do something like this maybe.. (untested)
    my @customer_attributes = qw/firs_tname last_name phone email date Acc +ept_Term comp_man comp_model comp_Model_Num/; my %customer; @customer{@customer_attributes} =(); # Now you have %customer populated with the keys as undef... require String::Prettify; for my $att (@customer_attributes){ printf "%s: ", String::Prettify::prettify($att); my $input = <STDIN>; chomp($input); $customer{$att} = $input; }
Re: DBD::mysql trouble
by Marshall (Canon) on Sep 22, 2009 at 19:53 UTC
    Joost,zwon 's SQL looks fine to me. As a small point, I'll answer your question here:
    system("clear"); #system("clear") only works on Unix based systems, ne +ed to come up with #something that works on windows
    The Windows command is "cls".
Re: DBD::mysql trouble
by Anonymous Monk on Sep 22, 2009 at 19:45 UTC
    I would use Term::Interact like so
    #!/usr/bin/perl -- use strict; use warnings; use Data::Dumper; use Term::Interact; print Dumper( prompt_customer() ); sub prompt_customer { my %customer; my $ti = Term::Interact->new; $customer{firstname} = $ti->get( msg => "\nComputer Information\n-----------------------\n", prompt => "Customer First Name: ", check => [ qr/^\w+$/, ], ); $customer{lastname} = $ti->get( msg => "", prompt => "Customer Last Name: ", check => [ qr/^\w+$/, ], ); $customer{phone} = $ti->get( msg => "", prompt => "Customer Phone #: ", check => [ qr/^[\d\s\-\,]+$/, ], ); $customer{email} = $ti->get( msg => "", prompt => "Customer Email: ", check => [ qr/^\S+$/, ], ); $customer{compman} = $ti->get( msg => "\nComputer Information\n-----------------------\n", prompt => "Computer Manufacturer: ", ); $customer{compmodel} = $ti->get( msg => "", prompt => "Computer Model: ", ); $customer{compModelNum} = $ti->get( msg => "", prompt => "Computer Model #: ", ); $customer{errormsg} = $ti->get( msg => "", prompt => "Error Message (if any): ", ); $customer{os} = $ti->get( msg => "", prompt => "Operating System: ", ); $customer{probdesc} = $ti->get( msg => "", prompt => "Problem Description: ", ); $customer{loginpw} = $ti->get( ReadMode => 2, msg => "", prompt => "Administrator/Root Login Password: ", ); $customer{service} = $ti->get( msg => "", prompt => "Computer Service Needed: ", ); $customer{barcode} = $ti->get( msg => "", prompt => "Barcode Number: ", ); $customer{date} = scalar localtime; print "\n"; return \%customer; } __END__ ### for testing, COPY/PASTE when prompted first last 444 444 444 asdf@email.com manu mode 33 err os prob rootpass compserv barcode ## sample session Computer Information ----------------------- Customer First Name: first Customer Last Name: last Customer Phone #: 444 444 444 Customer Email: asdf@email.com Computer Information ----------------------- Computer Manufacturer: manu Computer Model: mode Computer Model #: 33 Error Message (if any): err Operating System: os Problem Description: prob Administrator/Root Login Password: Computer Service Needed: compserv Barcode Number: barcode $VAR1 = { 'probdesc' => 'prob', 'firstname' => 'first', 'date' => 'Tue Sep 22 12:44:24 2009', 'barcode' => 'barcode', 'lastname' => 'last', 'service' => 'compserv', 'phone' => '444 444 444', 'os' => 'os', 'email' => 'asdf@email.com', 'loginpw' => 'rootpass', 'errormsg' => 'err', 'compModelNum' => '33', 'compman' => 'manu', 'compmodel' => 'mode' };
Re: DBD::mysql trouble
by graff (Chancellor) on Sep 23, 2009 at 01:15 UTC
    As others have suggested, life will be better for you when you use an array for field names, a hash for field values (keyed by the names in the array), placeholders in your sql statements, and loops to keep everything compact and non-repetitive.

    Once you are enjoying the beauty of all that, you'll really want to get acquainted with a cross-platform GUI library, especially for this sort of application. It's just much nicer for a user to fill in fields in a form, and have the ability to review all the values for a record (and maybe fix one or two of them) before submitting the complete record to the database with the click of a button. Take your pick of Tk, Wx, Qt or Gtk. Even Curses would be better than your current approach.

    The worst torture I can imagine for someone doing keyboard entry to a database is having to use the OP's style of irreversible, "one-field-at-a-time" sequential dialog. The likelihood of making a mistake that requires a complete start-over is so high that actually completing a single record entry becomes a daunting and maddening task.

    You can find a lot of self-contained sample GUI programs here at the Monastery that use most of the libraries cited above; also, a lot of the libraries come with very nice demo programs that show you how things work, and how the code is written to do those things.

      I'll keep the UI idea in mind for latter, right now I want to get the core functionality working first them move on to making it look good. Its not to bad considering only 2 people are going to use the program and we don't mind inserting values one at a time for now.

        Then I highly recommend Term::Interact because it will keep prompting until you enter valid input
        Computer Information ----------------------- Customer First Name: () Customer First Name: (ha ha) Customer First Name: Bob Customer Last Name: )()( Customer Last Name: Ricky Customer Phone #: ))) Customer Phone #: Caught a SIGINT at C:/perl/site/5.10.1/lib/Term/I +nteract.pm line 1138
Re: DBD::mysql trouble
by Mr. Muskrat (Canon) on Sep 23, 2009 at 15:24 UTC

    As the others have said, placeholders should be used.

    Using placeholders won't fix your problem although it might make your problem more obvious.

    Here is one of your sql statements:

    INSERT INTO Repairs.computers VALUES (compManufacturer, compModel, com +pModelNum, OS, errorMsg, serviceNeeded, problemDescription, barcode, +password) ($customer_compman, $customer_compmodel, $customer_compMode +lNum, $customer_os, $customer_errormsg, $customer_service, $customer_ +probdesc, $customer_barcode, $customer_loginpw

    Now we switch in the placeholders:

    INSERT INTO Repairs.computers VALUES (compManufacturer, compModel, com +pModelNum, OS,errorMsg , serviceNeeded, problemDescription, barcode, +password) ( ?, ?, ?, ?, ?, ?, ?, ?, ?
    What's this? The VALUES keyword is misplaced and there is a missing closing parenthesis? Oh my.

      Thanks for all the help everyone it is now posting to the database and now I have to get it to pull data from the database, both single entry (Look up a certain computer/customer) and dump the whole database (see how many computers/customers are in the shop). That should be a matter of changing the MySQL query from a INSERT into a SELECT and so forth right?

Re: DBD::mysql trouble
by venkatesan_G02 (Sexton) on Sep 23, 2009 at 11:11 UTC
    I think you can try this code.
    my $dbh = DBI->connect('DBI:mysql:yourmysqldatabasename','root','passw +ord')||die "can't connect to dabase:$DBI::errstr"; my $dbh=$dbh->prepare("UPDATE TableName SET ColumnName=\"$value\" wher +e ColumnName=$value2 "); $dbh->execute(); $dbh->disconnect();
    As mentioned by zwon (Deacon), quote your values.

    Also, the command to clear screan in windows is system("cls")

    Thanks

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (3)
As of 2022-06-26 16:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My most frequent journeys are powered by:









    Results (86 votes). Check out past polls.

    Notices?