Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

DBI SQL Syntax Problem

by jerrygarciuh (Curate)
on Apr 15, 2002 at 05:33 UTC ( [id://159098]=perlquestion: print w/replies, xml ) Need Help??

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

I have been flogging this for 3 hours, I know it is not entirely on topic but will someone please tell me why the code below gets error:

DBD::mysql::st execute failed: You have an error in your SQL syntax near 'month = '3' day = '16' time_start = '12:00 PM' time_end = '01:00 ' at line 4 at db.pl line 653.

I have checked and rechecked Cheetah and Kingfisher manuals and read the pod and checked the data in the arrays, combined the arrays, ditched the arrays and used bind_params just to see the one to one correspondance, added commas between the SET params, and googled my brains out. Help.
TIA
jg
$sth = $dbh-> prepare("UPDATE $calendar_table SET year = ? month = ? day = ? time_start = ? time_end = ? title = ? location = ? description = ? category = ? posted_by = ? post_date = ? b_time = ? e_time = ? WHERE year = ? AND month = ? AND day = ? AND time_start = ? AND time_end = ? AND title = ? ");
_____________________________________________________
Think a race on a horse on a ball with a fish! TG

Replies are listed 'Best First'.
Re: DBI SQL Syntax Problem
by snowcrash (Friar) on Apr 15, 2002 at 06:12 UTC
    i suppose the name-value pairs (year=? month=? etc) should be seperated by commas?
    snowcrash
      Tried it with no change at all.
      Thx
      jg
      _____________________________________________________
      Think a race on a horse on a ball with a fish! TG
Didn't mention if you'd tried . . .
by Fletch (Bishop) on Apr 15, 2002 at 05:43 UTC

    Turn on tracing on the handle (a value of 2 is usually sufficent) and see if there's anything more enlightening in what's being sent to and from the database.

      Here are results of trace. Please note am only using bind_params as a way to be visually clear about what is going where. Schema for mySQL table is below in thread. Current code will be added to end of thread momentarily.
      TIA
      jg
      DBI::db=HASH(0x827427c) trace level set to 2 in DBI 1.14-nothread -> prepare for DBD::mysql::db (DBI::db=HASH(0x8274294)~0x827427c ' +UPDATE hno_calendar SET year = ?, month = ?, day = ?, time_start = ?, time_end = ?, title = ?, location = ?, description = ?, category = ?, posted_by = ?, post_date = ?, b_time = ?, e_time = ?, WHERE year = ?, AND month = ?, AND day = ?, AND time_start = ?, AND time_end = ?, AND title = ?, ') Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x8273478) at db.pl line 611. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 1 '2002') <- bind_param= 1 at db.pl line 634. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 2 '3') <- bind_param= 1 at db.pl line 635. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 3 '16') <- bind_param= 1 at db.pl line 636. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 4 '12:00 PM') <- bind_param= 1 at db.pl line 637. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 5 '01:00 PM') <- bind_param= 1 at db.pl line 638. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 6 'Faciltiative Leadership Training') <- bind_param= 1 at db.pl line 639. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 7 'Incarnate Word') <- bind_param= 1 at db.pl line 640. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 8 'Advanced Training - Strategic Planning') <- bind_param= 1 at db.pl line 641. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 9 'Training') <- bind_param= 1 at db.pl line 642. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 10 'jeanwatts') <- bind_param= 1 at db.pl line 643. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 11 'at 03:28 PM on 04/13/2002.') <- bind_param= 1 at db.pl line 644. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 12 '12:00') <- bind_param= 1 at db.pl line 645. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 13 '01:00') <- bind_param= 1 at db.pl line 646. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 14 '2002') <- bind_param= 1 at db.pl line 647. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 15 '3') <- bind_param= 1 at db.pl line 648. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 16 '16') <- bind_param= 1 at db.pl line 649. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 17 '1:00 PM') <- bind_param= 1 at db.pl line 650. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 18 '12:00 PM') <- bind_param= 1 at db.pl line 651. -> bind_param for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b0 +0 19 'Faciltiative Leadership Training') <- bind_param= 1 at db.pl line 652. -> execute for DBD::mysql::st (DBI::st=HASH(0x8273478)~0x8275b00) -> dbd_st_execute for 08275ad0 Binding parameters: UPDATE hno_calendar SET year = '2002', month = '3', day = '16', time_start = '12:00 PM', time_end = '01:00 PM', title = 'Faciltiative Leadership Training', location = 'Incarnate Word', description = 'Advanced Training - Strategic Planning +', category = 'Training', posted_by = 'jeanwatts', post_date = 'at 03:28 PM on 04/13/2002.', b_time = '12:00', e_time = '01:00', WHERE year = '2002', AND month = '3', AND day = '16', AND time_start = '1:00 PM', AND time_end = '12:00 PM', AND title = 'Faciltiative Leadership Training', You have an error in your SQL syntax near 'WHERE year = '2002', AND month = '3', AND day = '16', AND time_start' at line 17 error 1064 recorded: You ha +ve an error in your SQL syntax near 'WHERE year = '2002', AND month = '3', AND day = '16', AND time_start' at line 17 <- dbd_st_execute -2 rows !! ERROR: 1064 'You have an error in your SQL syntax near 'WHERE y +ear = '2002', AND month = '3', AND day = '16', AND time_start' at line 17' <- execute= undef at db.pl line 653. -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x8275b00)~INNER) <- DESTROY= undef during global destruction. -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x827427c)~INNER) Rollback ineffective while AutoCommit is on error 15 recorded: Rollbac +k ineffective while AutoCommit is on imp_dbh->svsock: 83e6a74 <- DESTROY= undef during global destruction.
      _____________________________________________________
      Think a race on a horse on a ball with a fish! TG
        I believe this
        WHERE year = '2002', AND month = '3', AND day = '16', AND time_start = '1:00 PM', AND time_end = '12:00 PM', AND title = 'Faciltiative Leadership Training',
        should read
        WHERE year = '2002' AND month = '3' AND day = '16' AND time_start = '1:00 PM' AND time_end = '12:00 PM' AND title = 'Faciltiative Leadership Training'
        As in there should be no commas in between the AND clauses.

        Think of a where like an if statement and it becomes obvious

        if (year == '2002' && month == '3' && day = '16' && $time_start eq '1:00 PM' && $time_end eq '12:00 PM' && $title eq 'Faciltiative Leadership Training') { #yada-yada-yada }
        is correct but
        if (year == '2002', && month == '3', && day = '16', && $time_start eq '1:00 PM', && $time_end eq '12:00 PM', && $title eq 'Faciltiative Leadership Training') { #yada-yada-yada }
        is not...

        :-)

        Yves / DeMerphq
        ---
        Writing a good benchmark isnt as easy as it might look.

Re: DBI SQL Syntax Problem
by Ryszard (Priest) on Apr 15, 2002 at 06:49 UTC
    yup, you should seperate your set col=value pair by commas.
      I tried seperating the pairs by commas and got the same results exactly.
      Thx for the reply,
      jg
      _____________________________________________________
      Think a race on a horse on a ball with a fish! TG
Re: DBI SQL Syntax Problem
by dws (Chancellor) on Apr 15, 2002 at 17:39 UTC
    First, jerrygarciuh, shame on you for not originally posting the same code that you're having problems with. Posting different code (minus the commas) wastes peoples' time chasing down the wrong alleys.

    I was in the process of writing an RTFM reply citing the MySQL doc for UPDATE, but found that it begins with

    UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [LIMIT #]
    which is wrong about comma placement (in a subtle way). It should read
    UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [LIMIT #]
    I've sent a note about this to the MySQL folks.

    Update: They've made the correction. Quick turnaround on their part.

      Absolutely true dws Problem is that I did post code I was using and then had various sugestions made (some made in the CB) and abandoned/modified/screamed. I really apologize for wasting folk's time. If I'd only been able to locate an example in Cheetah or Kingfisher or Google with multiple values in UPDATE...SET staement, I'd of worked out what was wrong. Again, mea culpa maxima and...
      Thanks
      jg
      _____________________________________________________
      Think a race on a horse on a ball with a fish! TG
Re: DBI SQL Syntax Problem
by talexb (Chancellor) on Apr 15, 2002 at 14:21 UTC
    It looks like the 'PM' is going to get discarded in your time field. I just tried it out in MySQL, and "3:00 PM" maps itself to "03:00:00".

    My last suggestion via CB was drop the double quotes in the bind_param statements. I'd like to hear if that solved the problem.

    --t. alex

    "Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny

Re: DBI SQL Syntax Problem
by thor (Priest) on Apr 15, 2002 at 12:16 UTC
    In addition to what everyone else said, I don't know if DBI will have problems with the embedded newlines. My guess is 'no', just because of the format of the error message you got (no newlines in it...). Like I said though, I don't know.
Current version of code used to produce trace() results above in thread
by jerrygarciuh (Curate) on Apr 15, 2002 at 13:21 UTC
    Thx for looking!
    jg
    $sth = $dbh-> prepare("UPDATE $calendar_table SET year = ?, month = ?, day = ?, time_start = ?, time_end = ?, title = ?, location = ?, description = ?, category = ?, posted_by = ?, post_date = ?, b_time = ?, e_time = ?, WHERE year = ?, AND month = ?, AND day = ?, AND time_start = ?, AND time_end = ?, AND title = ?, "); $sth->bind_param(1, "$form_data{'year'}"); $sth->bind_param(2, "$form_data{'month'}"); $sth->bind_param(3, "$form_data{'day'}"); $sth->bind_param(4, "@start_time"); $sth->bind_param(5, "@end_time"); $sth->bind_param(6, "$form_data{'title'}"); $sth->bind_param(7, "$form_data{'location'}"); $sth->bind_param(8, "$form_data{'description'}"); $sth->bind_param(9, "$form_data{'category'}"); $sth->bind_param(10, "$form_data{'posted_by'}"); $sth->bind_param(11, "$form_data{'post_date'}"); $sth->bind_param(12, "$b_time"); $sth->bind_param(13, "$e_time"); $sth->bind_param(14, "$form_data{'old_year'}"); $sth->bind_param(15, "$form_data{'old_month'}"); $sth->bind_param(16, "$form_data{'old_day'}"); $sth->bind_param(17, "$form_data{'old_start_time'}"); $sth->bind_param(18, "$form_data{'old_end_time'}"); $sth->bind_param(19, "$form_data{'old_title'}"); $sth->execute();
    _____________________________________________________
    Think a race on a horse on a ball with a fish! TG

      You want to lose the two trailing commas (SQL is so not Perl). Also, where you use AND (in the WHERE clause), you don't want commas at all.

      Any one of these SQL mistakes is a syntax error with any database server I ever tried.

      The Sidhekin
      print "Just another Perl ${\(trickster and hacker)},"

      In the above SQL you (still) have commas in your WHERE clause. the SQL below should be valid, assuming that the values you are binding conform to the column data types. (Note that the last comma after e_time set is invalid preceeding the WHERE.)

      One thing that works well for me when confronted by sql coding problems is to create the SQL standalone and execute it (for instance via SQLPlus (or mySQL equivalent)) until it's syntactically correct, then take that SQL and implement it. I suspect among other things, the '12:00 PM' has a problem with the space-PM, and time_end = '01:00 ' has issues with the trailing space (chomp?).

      $sth = $dbh-> prepare("UPDATE $calendar_table SET year = ?, month = ?, day = ?, time_start = ?, time_end = ?, title = ?, location = ?, description = ?, category = ?, posted_by = ?, post_date = ?, b_time = ?, e_time = ? WHERE year = ? AND month = ? AND day = ? AND time_start = ? AND time_end = ? AND title = ? ");
Re: DBI SQL Syntax Problem
by jerrygarciuh (Curate) on Apr 15, 2002 at 13:01 UTC
    I was asked to supply more info so here is the table schema, the db is mySQL, and I am doing lots of INSERTS and single col+val updates successfully, dunno what is up here.
    Thx for looking!
    jg
    +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | year | int(11) | YES | | NULL | | | month | int(11) | YES | | NULL | | | day | int(11) | YES | | NULL | | | time_start | varchar(20) | YES | | NULL | | | time_end | varchar(20) | YES | | NULL | | | title | varchar(50) | YES | | NULL | | | location | varchar(255) | YES | | NULL | | | description | varchar(255) | YES | | NULL | | | category | varchar(255) | YES | | NULL | | | posted_by | varchar(255) | YES | | NULL | | | post_date | varchar(255) | YES | | NULL | | | b_time | time | YES | | NULL | | | e_time | time | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+
    _____________________________________________________
    Think a race on a horse on a ball with a fish! TG
A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2024-03-29 14:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found