in reply to DBI SQL Syntax Problem

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.

Replies are listed 'Best First'.
Re: Didn't mention if you'd tried . . .
by jerrygarciuh (Curate) on Apr 15, 2002 at 13:19 UTC
    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.

    [download]
    _____________________________________________________
    Think a race on a horse on a ball with a fish! TG
[reply]
[d/l]
      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',

      [download]
      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'

      [download]
      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
 }

      [download]
      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
 }

      [download]
      is not...

      :-)

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

[reply]
[d/l]
[select]
        I agree, I added the commas last night in response to other's suggestions. They are out now and error continues.
        Thx
        jg
        _____________________________________________________
        Think a race on a horse on a ball with a fish! TG
[reply]
[d/l]

In Section Seekers of Perl Wisdom