I'm trying to put together an SQL statement to write some data to an access database. Some of the fields I want to write are multi line, its windows so the data contains carridge return line feed (ascii values 13 and 10). Unfortunately the sql statement regards this as a return character so it only processes half the line eg...
$a = "aaaaa\n";
$a = $a."bbbbb";
$sql="insert into test (field1) value '$a';
produces
insert into test (field1) value 'aaaaa
bbbbb'
what it actually runs is
insert into test (field1) value 'aaaaa
Which obviously doesn't work. What I need to do (I think) is to replace the cr/lf characters in the text with ' + Chr(13) + Chr(10) + '
$sql="insert into test (field1) value 'aaaaaaa' + Chr(13) + Chr(10) +
+'bbbbbbbb';
works correctly.
My question is....
how do I work the substitution.
$a =~ s/chr(13)chr(10)/' + Chr(13) + Chr(10) + '/;
doesn't work. As far as I can see it is interpreting + Chr(13) + Chr(10) and putting it back in as ascii value 13 and ascii value 10 rather than padding it out the way I need.
Any help would be greatly appreciated.