ansaurus

Question

Answer 1

+3 A:

It's hard to see for sure what's going wrong, but I think Andreas Bonini is correct that you've got quoting problems.

However, he has accidentally referred you to a PHP function ;-)

You can use DBI's quote method for the same effect. (Update: oops, you used it already for the data items. But perhaps you need to use it - or placeholders - for the table name variables too?)

However, it's considered better practice by most people to use placeholder values:

while (something) {
    # I've imagined you're looping over the data

    $dbh->do("INSERT INTO $headerTable ( headData, headDataOutput ) 
      VALUES ( $headContents, $headContents )")
        or die " error: Couldn't connect to database: " . DBI->errstr;
}

becomes:

my $sth = $dbh->prepare("INSERT INTO ? ( headData, headDataOutput ) 
                  VALUES ( ?, ? )") 
                    or die " error: Couldn't prepare : " . DBI->errstr;)

# later...
while (something) {
    # loop over the data

    $sth->execute($headerTable, $headContents, $headContents) 
      or die "couldn't execute: " . $sth->errstr;
}

Untested... sorry ;-)

I'm not 100% sure you can use a placeholder for the table name, but I don't really see why not, so I suggest trying it. If not, you should quote() the table name as described above.

Assuming your code runs in a loop, you can prepare the statement handler ($sth) once outside the loop and run execute several times, which should save some time if you have many inserts to do.

HTH

FalseVinylShrub 2010-02-07 08:38:57

sorry, you did quote()... missed it looking too closely at the errors not the code;-)Anyway, placeholders are still (usually) a good thing so I guess I'll leave it.

FalseVinylShrub 2010-02-07 08:42:44

Thanks for the time to write the above! will try the place holders. And also I tried using quote for the table name but gave me an error and couldnt get round it ( which i thought was odd). I'll post back soon.

Phil Jackson 2010-02-07 08:53:29

Do you know if using the above method automatically adds quotes as the data in my DB has quotes around it?

Phil Jackson 2010-02-07 09:03:53

It would get double-quoted if you're still calling the quote() method whilst using placeholders, I think... To be honest, though it's worth trying I'm less convinced that quoting is the problem and think you should log or trace your SQL to see what's wrong with it. The cause will probably be obvious when you see the broken SQL. Either add logging statements, or turn on tracing (I think tracing can be done at DBI level and probably MySQL level too).

FalseVinylShrub 2010-02-07 09:26:35

have you a link to hand where i can find info on logging/tracing?

Phil Jackson 2010-02-07 09:50:38

have edited main question...

Phil Jackson 2010-02-07 10:06:02

ansaurus

tags:

views:

answers:

Uploading HTML into database - PERL

related questions