I am going mad trying to figure out how to automatically escape characters for inclusion into a mysql database. I want to insert raw rtf data into a blob field. I need to do this via the shell. But doing seraches on Google or the documentation for mysql has been no help. All tutorials on the subject are about web interfaces. I just need to pipe an rtf file into some kind of script which will escape the characters and then I could pipe it back to a new temporary file which I could use later in another script to put into mysql. Any ideas on how to do this? I'm using 10.2.2.
mysql escape characters
- Thread starter chief
- Start date
michaelsanford
Translator, Web Developer
If you're inserting raw data into a blob field, what do you need to escape? You could just as esily pipe a jpeg into a blob, and there aren't any escape characters for jpegs as far as I know 
I'm not at my terminal, and my university's firewalled (working on that ) but I'll try it myself when I get back tonight.
I'm not at my terminal, and my university's firewalled (working on that
) but I'll try it myself when I get back tonight.Even when I try to pipe the data in (unless I am doing it wrong), all of the forward slashes in an rtf file end up missing when I get MySQL to display the data. I tried using insert and I think I also tried using batch. The MySQL docs say that you should escape certain characters, but they suggest using perl that has been compiled with MySQl support or PHP and that's not what I want. Honestly, I am a novice. Maybe I am missing something obvious, but if you can get it to work and show me how, I would be extremely enthusiastic.
michaelsanford
Translator, Web Developer
Gosh I'm an idiot!
You're completely right that you have to escape characters, and I should have known that...guess I wasn't thinking. And you can't upload jpegs to a database, also don't know what I was thinking there...very embarassing.
SO ok, why do you want to keep RTFs in the database? The standard procedure for something like this is to upload a file, parse the file with PHP or something, and then keep the link to the local file in the database.
You're completely right that you have to escape characters, and I should have known that...guess I wasn't thinking. And you can't upload jpegs to a database, also don't know what I was thinking there...very embarassing.
SO ok, why do you want to keep RTFs in the database? The standard procedure for something like this is to upload a file, parse the file with PHP or something, and then keep the link to the local file in the database.
jesustoast
We hope that you choke
I'm not sure if this will work with mysql but usually to escape characters you just use \ before it.
michaelsanford
Translator, Web Developer
what do you mean by "this", escaping an RTF or storing a link to the RTF in the database?
You can parse an RTF (or any text file) with a script to find and replace tabs, quotes, slashes, etc but I don't know enough PERL do to that
You can parse an RTF (or any text file) with a script to find and replace tabs, quotes, slashes, etc but I don't know enough PERL do to that
I was trying to escape the rtf characters for a complete copy of the file within MySQL and NOT for web purposes. I am trying to use this mostly on my machine locally. I also would like it if I could find a non-Perl solution. However, anything would help. I just need something relatively standard on OS X. I would use Applescript, but anything relatively large would take quite a while.
I'm actually trying to write an Applescript Application for myself and Applescript isn't good for large data manipulation, thus comes MySQL. My little program interacts well enough so far with MySQL using the "do shell" commands with some Applescript variables. I may have to just do file links, but that's really not what I wanted.
I'm actually trying to write an Applescript Application for myself and Applescript isn't good for large data manipulation, thus comes MySQL. My little program interacts well enough so far with MySQL using the "do shell" commands with some Applescript variables. I may have to just do file links, but that's really not what I wanted.
michaelsanford
Translator, Web Developer
I'm still curious, what is your application? Why are you processing the data for, maybe I can think of another solution.
I promise I won't lead you as horribly astray as I did above
I promise I won't lead you as horribly astray as I did above
I'm trying to write a program to help me organize all of the text-based files I have. Some I have written, some are downloaded. But I have thousands and need a customized organization system (My database has 20 fields so far). So I thought I could use a MySQL database to not only store plain text versions for indexing, but RTF copies for formatting which would keep all of my copies in one place and also allow for a quick and easy backup using mysqldump. By using RTF and MySQL I was hoping to have a solution that would last a long time. Also, I wanted something relatively standards-based so I could maybe connect to my home machine from work without using web applications which don't do what I want because they are horrible word processors which rely on Browsers (which aren't great in general.) But also, by not pointing to other files on my computer, I could avoid allowing acces over the web to anything but the Web Directory and MySQL.
michaelsanford
Translator, Web Developer
Hmm perhaps what you could do is have a sift through the PHP code fpr phpBB2, which is an open-source PHP/MySQL forum.
You might get som ideas, or be able to copy routines, because they no doubt have to escape many of the same characters you have to to save posts in a MySQL database...
www.phpbb.com
Apart from that, I'm afraid I don't know what to tell you.
I actually have a very similar project going, but luckily, I'm starting with my RTF database at zero, and people add to it. So saving the files to the HD is entirely feasible for me.
What's stopping you organizing them all by folder (which hopefully they already are), and then concatenating the directory structure into a MySQL database with a shell command like find exec?
You might get som ideas, or be able to copy routines, because they no doubt have to escape many of the same characters you have to to save posts in a MySQL database...
www.phpbb.com
Apart from that, I'm afraid I don't know what to tell you.
I actually have a very similar project going, but luckily, I'm starting with my RTF database at zero, and people add to it. So saving the files to the HD is entirely feasible for me.
What's stopping you organizing them all by folder (which hopefully they already are), and then concatenating the directory structure into a MySQL database with a shell command like find exec?
Folders aren't enough. Find file isn't enough. I wanted the database so that I could use my own organization system including tags (fields) like draft status, author and countless others I have I mind. But even then, folder structures assume that something belongs to only one family.
Also, I wanted to be able to do everything from one interface.
However, as you said, I may have to give this up and use file links. It's probably what I will do.
I just thought of something, my main resistence to using folder structures (or anything based on the normal file system structure) was the inherent limitations to it. As in, file names have to be under a certain length, and folders imply that something belongs to one category. MySQL doesn't care if I have 10,000 or more entries which are all duplicates. It doesn't care if I have 10,000 entries which all have the same "title" field, but the OS does care if a file has the same name in the same folder. Maybe I could write the program to automatically assign file names as a unique number. If I did that, I can't imagine being so prolific that I would create too many files to hit the file name limitations. It would bypass trying to think of a way to name different versions of the same story in a descriptive way, because I could use the fields in the database as my descripters. Then I could also just write a short backup script to tar, gzip the files.
It's not what I envisioned, but I think it would work. Thanks for the input.
Also, I wanted to be able to do everything from one interface.
However, as you said, I may have to give this up and use file links. It's probably what I will do.
I just thought of something, my main resistence to using folder structures (or anything based on the normal file system structure) was the inherent limitations to it. As in, file names have to be under a certain length, and folders imply that something belongs to one category. MySQL doesn't care if I have 10,000 or more entries which are all duplicates. It doesn't care if I have 10,000 entries which all have the same "title" field, but the OS does care if a file has the same name in the same folder. Maybe I could write the program to automatically assign file names as a unique number. If I did that, I can't imagine being so prolific that I would create too many files to hit the file name limitations. It would bypass trying to think of a way to name different versions of the same story in a descriptive way, because I could use the fields in the database as my descripters. Then I could also just write a short backup script to tar, gzip the files.
It's not what I envisioned, but I think it would work.
Thanks for the input.