MySQL question

Karla

Anybody know how to allow BLOBs that contain single quotes into a record? At the moment I have to strip them from the incoming text and I can't see any reliable way of putting them back

Here's the problem:

---

$text = "It's a way of life";

$sth = $dbh->prepare("INSERT INTO table(text) VALUES('$text')");

---

The single quote inside the string is interpolated. I was thinking maybe I could replace them with something else before I put them into the record and then swapping back when I take it out of the record, but what could I use? Would it be safe to use some weird ASCII character for this?

Thanks


Doh! I could just wrap the SQL statement in single quotes couldn't I?


[This message has been edited by Karla (edited 30-01-2000).]

kayos

ok first thing first why are you tring to put a string in a image field. Well here it goes any who. If you want to put single quotes into a string field you would use syntax as follows.
Insert
TableName
(
FieldName
)
Values
(
'It''s a way of life'
)
Note that It's has two single quotes and not a double quote. Well thats how you do a string field in SQL Server so it should work for you. If you need any more help just give us a shout.

Kayos

[This message has been edited by kayos (edited 31-01-2000).]

Karla

Huh? I wasn't putting it into an image field.

Anyway I found a way around it, since I'm using MySQL on the Web I just had to replace every apostrophe with the escape character & # 039 ; like this:

$text =~ s/'/\&\#039\;/g;

Works just fine

Thanks anyway

kayos

A blob field is an image field in SQL 6.5

Karla

That's MS SQL Server right?

I'm pretty sure that a BLOB is for loads of text too yeah?

kayos

Blob stands for Binary Large Object and should not really be used for just straight text. The way you would use a blob to store large amounts of text would to be write the text out to a file and then insert that file into a blob field. Then to get the text back onpen a file of binary access and write the blob to it and then opne and read the file. If you want I'll mail you some sample code (VB6).

KaYoS

DIM KaYoS As DataObject

Karla

Yeah, cheers that would be great

My email addy is bracken@webmongers.org

Binary Large Object eh? Oops

So would a TEXT datatype be ok for a message board-type comment? If not what would you suggest? Or should I use separate files like you said.

I'm kinda finding my way along in the dark here so forgive me

Thanks a lot