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

If I attempt to use just @values in the execute, then mysql complains of incorrect sql syntax... mostly due to unescaped characters like "'". The code works beautifully if I use just one data file and hand type the placeholders and the array elements into the insert statements in the code... that though... would take me forever.

Replies are listed 'Best First'.
Re: DBI::mysql
by ferrency (Deacon) on Apr 15, 2002 at 14:00 UTC
    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

[reply]
[d/l]
[select]
      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]

In Section Seekers of Perl Wisdom