in reply to Re: Re: DBI:mysql
in thread DBI:mysql

In that case, I suggest that you post a log transcript of the actual query being prepared and executed, and the error messages you receive, both with execute(@values) and with execute($values). This would make it a lot easier to see what's going wrong.

Alan

Replies are listed 'Best First'.
Re: Re: DBI::mysql
by Dalin (Sexton) on Apr 15, 2002 at 14:34 UTC
    Here is the trace and error output from execute($values)
      DBI::db=HASH(0x81f9b24) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9aa0)~0x81f9b24 '
+INSERT INTO advertisements VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9bf0) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x8160a04)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9b24)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 3.
&imp_dbh->mysql: 81fb534
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9bb4) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x815da24)~0x81f9bb4 '
+INSERT INTO affiliate VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9c44) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9b84)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9bb4)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 6.
&imp_dbh->mysql: 81dd5b4
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9c5c) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9a7c)~0x81f9c5c '
+INSERT INTO affiliatedetail VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9cb0) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9b00)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9c5c)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 9.
&imp_dbh->mysql: 81dd5b4
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9c44) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81ef9c4)~0x81f9c44 '
+INSERT INTO affiliatepayment VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9b9c) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9b78)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9c44)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 12.
&imp_dbh->mysql: 81dd5b4
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9cb0) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9c80)~0x81f9cb0 '
+INSERT INTO affiliatereports VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9b24) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9af4)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9cb0)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 15.
&imp_dbh->mysql: 81dd5b4
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9b9c) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9ce0)~0x81f9b9c '
+INSERT INTO affiliatestatus VALUES (?,?,?,?)')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9bb4) at hobby_update line 58
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9bb4)~0x81f9c4
+4 1 '1' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9bb4)~0x81f9c4
+4 2 'Affiliate has not been approved at this time.')
    <- bind_param= 1 at hobby_update line 74
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9bb4)~0x81f9c4
+4 3 'Pending Approval')
    <- bind_param= 1 at hobby_update line 74
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9bb4)~0x81f9c4
+4 4 '1' 4)
    <- bind_param= 1 at hobby_update line 71
    -> execute for DBD::mysql::st (DBI::st=HASH(0x81f9bb4)~0x81f9c44 '
+1,Pending Approval,Affiliate has not been approved at this time.,1')
    !! ERROR: -1 'called with 1 bind variables when 4 are needed'
    <- execute= undef at hobby_update line 80
DBD::mysql::st execute failed: called with 1 bind variables when 4 are
+ needed at hobby_update line 80, <CSV2> line 17.
DBD::mysql::st execute failed: called with 1 bind variables when 4 are
+ needed at hobby_update line 80, <CSV2> line 17.
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9c44)~INNER)
    <- DESTROY= undef
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9b9c)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect(), <CSV2> line 17.
&imp_dbh->mysql: 81dd5b4
    <- DESTROY= undef

    [download]
    And here is the trace and error output from execute(reverse(@values)) 
       DBI::db=HASH(0x81f9b9c) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9b18)~0x81f9b9c '
+INSERT INTO advertisements VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9c68) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x8160a04)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9b9c)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 3.
&imp_dbh->mysql: 81fb434
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9c2c) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x815da24)~0x81f9c2c '
+INSERT INTO affiliate VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9cbc) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9bfc)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9c2c)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 6.
&imp_dbh->mysql: 81efd6c
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9cd4) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9af4)~0x81f9cd4 '
+INSERT INTO affiliatedetail VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9d28) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9b78)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9cd4)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 9.
&imp_dbh->mysql: 81efd6c
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9cbc) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81ef95c)~0x81f9cbc '
+INSERT INTO affiliatepayment VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9c14) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9bf0)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9cbc)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 12.
&imp_dbh->mysql: 81efd6c
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9d28) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9cf8)~0x81f9d28 '
+INSERT INTO affiliatereports VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9b9c) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9b6c)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9d28)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 15.
&imp_dbh->mysql: 81efd6c
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9c14) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f9d58)~0x81f9c14 '
+INSERT INTO affiliatestatus VALUES (?,?,?,?)')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9c2c) at hobby_update line 58
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9cb
+c 1 '1' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9cb
+c 2 'Affiliate has not been approved at this time.')
    <- bind_param= 1 at hobby_update line 74
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9cb
+c 3 'Pending Approval')
    <- bind_param= 1 at hobby_update line 74
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9cb
+c 4 '1' 4)
    <- bind_param= 1 at hobby_update line 71
    -> execute for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9cbc '
+1' 'Pending Approval' 'Affiliate has not been approved at this time.'
+ '1')
    -> dbd_st_execute for 081f9c80
      Binding parameters: INSERT INTO affiliatestatus VALUES (1,'Pendi
+ng Approval','Affiliate has not been approved at this time.',1)
    <- dbd_st_execute 1 rows
    <- execute= 1 at hobby_update line 79
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9cbc)~INNER)
    <- DESTROY= undef at hobby_update line 90
    -> commit for DBD::mysql::db (DBI::db=HASH(0x81f9d58)~0x81f9c14)
    <- commit= 1 at hobby_update line 91
    -> disconnect for DBD::mysql::db (DBI::db=HASH(0x81f9d58)~0x81f9c1
+4)
&imp_dbh->mysql: 81efd6c
    <- disconnect= 1 at hobby_update line 92
readline() on closed filehandle main::CSV2 at hobby_update line 47.
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9c14)~INNER)
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81f9b9c) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81ef9c8)~0x81f9b9c '
+INSERT INTO bannerlink VALUES ()')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81fc588) at hobby_update line 58
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9cc8)~INNER)
    <- DESTROY= undef at hobby_update line 48
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81f9b9c)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at hobby_update line 18, <CSV2> line 3.
&imp_dbh->mysql: 81efd6c
    <- DESTROY= undef at hobby_update line 18
    DBI::db=HASH(0x81fc54c) trace level set to 2 in DBI 1.21-nothread
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x80fc834)~0x81fc54c '
+INSERT INTO basket VALUES (?,?,?,?,?,?,?,?,?,?,?)')
Setting mysql_use_result to 0
    <- prepare= DBI::st=HASH(0x81f9c2c) at hobby_update line 58
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 1 'undef')
    <- bind_param= 1 at hobby_update line 74
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 2 '0' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 3 '1' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 4 '')
    <- bind_param= 1 at hobby_update line 74
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 5 '0' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 6 '2002-03-31 23:08:31-05' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 7 '0' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 8 '15' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 9 '10081' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 10 '1168280448' 4)
    <- bind_param= 1 at hobby_update line 71
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf
+0 11 '160' 4)
    <- bind_param= 1 at hobby_update line 71
    -> execute for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf0 '
+160' '1168280448' '10081' '15' '0' '2002-03-31 23:08:31-05' '0' '' '1
+' '0' 'undef')
    -> dbd_st_execute for 081f9bfc
      Binding parameters: INSERT INTO basket VALUES ('160',1168280448,
+10081,'15',0,2002-03-31 23:08:31-05,0,,1,0,undef)
You have an error in your SQL syntax near '23:08:31-05,0,,1,0,undef)' 
+at line 1 error 1064 recorded: You have an error in your SQL syntax n
+ear '23:08:31-05,0,,1,0,undef)' at line 1
    <- dbd_st_execute -2 rows
    !! ERROR: 1064 'You have an error in your SQL syntax near '23:08:3
+1-05,0,,1,0,undef)' at line 1'
    <- execute= undef at hobby_update line 79
DBD::mysql::st execute failed: You have an error in your SQL syntax ne
+ar '23:08:31-05,0,,1,0,undef)' at line 1 at hobby_update line 79, <CS
+V2> line 5.
DBD::mysql::st execute failed: You have an error in your SQL syntax ne
+ar '23:08:31-05,0,,1,0,undef)' at line 1 at hobby_update line 79, <CS
+V2> line 5.
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f9bf0)~INNER)
    <- DESTROY= undef
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81fc54c)~INNER)
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect(), <CSV2> line 5.
&imp_dbh->mysql: 81fc324
    <- DESTROY= undef

    [download]
[reply]
[d/l]
[select]
      Thanks.

      Looking at the first attempt, the error is clear: you're passing one bind value (a string containing comma-separated values), and it's expecting 4 bind values (a list of the 4 values, instead of the string).

      In the second attempt, sending @values to execute(), the first problem does not occur- you successfully execute with the list of 4 @values that crashed the first example. Instead, you have another problem further along:

      -> execute for DBD::mysql::st (DBI::st=HASH(0x81f9c2c)~0x81f9bf0 '160'
+ '1168280448' '10081' '15' '0' '2002-03-31 23:08:31-05' '0' '' '1' '0
+' 'undef')
-> dbd_st_execute for 081f9bfc
    Binding parameters: INSERT INTO basket VALUES ('160',1168280448,10
+081,'15',0,2002-03-31 23:08:31-05,0,,1,0,undef)
You have an error in your SQL syntax near '23:08:31-05,0,,1,0,undef)' 
+at line 1 error 1064 recorded: You have an error in your SQL syntax n
+ear '23:08:31-05,0,,1,0,undef)' at line 1

      [download]
      This is a little bit weird- it looks like the execute() statement isn't correctly quoting your date. The insert statement doesn't have quotes around the date. Most likely SQL is interpreting 2002-03-31 as a subtraction of 3 integers, and then getting confused when it gets to the 23:08:31-05.

      I'd try manually quoting the date "2002-03-31 23:08:31-05" before sending it in as an execute() value. Unfortunately, it's not obvious where this can be done in your code.

      Alan

[reply]
[d/l]
[select]
        I can see my error now with the string, thanks. I think the best thing to do will be to check for the timestamp and do a bind_param. How would you build a regex to check for the timestamp? 
        /\d\d\d\d\-\d\d\-\d\d \d\d:\d\d:\d\d\-\d\d/

        [download]
        Possibly??
        Thank you for all of you help today. Bradley
[reply]
[d/l]

In Section Seekers of Perl Wisdom