OT: placeholders and DBI
I have a query that executes many many times that I would like to optimize using placeholders. One of the fields that it will insert into is a 'text' field and I am having a problem with DBI (or the db) escaping '\n' characters so when they are inserted into the database they become '\\n' (ie a '\' followed by 'n'). I want it to keep the newlines in tact when inserted. I have run into this behavior using both DBD::mysql and DBD::Pg. Does anyone have advice on how this could be done while still using placeholders so I dont need to prepare the query more than once? Thanks for the help. ryan
Re: OT: placeholders and DBI
ryc wrote: ... I am having a problem with DBI (or the db) escaping '\n' characters so when they are inserted into the database they become '\\n' (ie a '\' followed by 'n'). Does anyone have advice on how this could be done while still using placeholders so I dont need to prepare the query more than once? $sql .= '\Q$text\E' This is also known as the quotemeta function. You might want to investigate the use of the statement handle method quote. quote $sql = $dbh-quote($value); $sql = $dbh-quote($value, $data_type); Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks. $sql = sprintf SELECT foo FROM bar WHERE baz = %s, $dbh-quote(Don't); Taken straight from the perldocs for DBI. mike808/ -- perl -le $_='7284254074:0930970:H4012816';tr[0-][ BOPEN!SMUT];print
Re: OT: placeholders and DBI
On Sat, Sep 01, 2001 at 02:38:05PM -0500, Mike808 wrote: ryc wrote: ... I am having a problem with DBI (or the db) escaping '\n' characters so when they are inserted into the database they become '\\n' (ie a '\' followed by 'n'). Does anyone have advice on how this could be done while still using placeholders so I dont need to prepare the query more than once? $sql .= '\Q$text\E' Ack, no! This is for escaping characters for use in a regular expression, not in a database query. Besides, the question was about how to use placeholders, not how to embed a data string directly into an SQL query. It's far better to use placeholders, especially if you're using a database like Oracle that supports them natively, providing a huge performance boost. -Ken
Re: OT: placeholders and DBI
Great diagnosis! Turns out somewhere in the code newlines were being converted to '\n' (two characters) in order to prepare the data for mysqlimport. Sorry about the noise on the list for such a silly error.. placeholders do in fact work exactly as expected, but not when combined with faulty code heh. Thanks everyone, ryan Please make sure that you are indeed trying to put a newline into the database. The string, '\n', is a 2-character string. The string, \n, is a 1-character string (a newline). So if your text variable is something like 'hello\nworld', it's no wonder the DBI interface tries to preserve the \. Perl itself preserves the slash. If you text variable is hello\nworld, then I would indeed be surprised if what you describe is really happening. i.e. this is a Perl quoting problem... not a DBI problem, and not a mod_perl problem. Stephen At 12:49 PM 9/1/2001 -0500, ryc wrote: I have a query that executes many many times that I would like to optimize using placeholders. One of the fields that it will insert into is a 'text' field and I am having a problem with DBI (or the db) escaping '\n' characters so when they are inserted into the database they become '\\n' (ie a '\' followed by 'n'). I want it to keep the newlines in tact when inserted. I have run into this behavior using both DBD::mysql and DBD::Pg. Does anyone have advice on how this could be done while still using placeholders so I dont need to prepare the query more than once? Thanks for the help. ryan
