How to insert long text(ex:Html) from a variable into MySQL using linux bash?

Question

I want to insert html into a mysql longtext collumn but I keep hitting an error :

/usr/bin/mysql: Argument list too long

when the string size is to big (at least that's what I think)

this is the code:

link="7red.no"    
result=$( { stdout=$(wget -T10 -t1 "$link" -v -O -) ; } 2>&1; echo "--SePaRaToR--"; echo "$stdout"); 
dowload_stat=${result%$'\n'--SePaRaToR--*}; 
html=${result#*--SePaRaToR--$'\n'}; 
printf -v dowload_stat "%q" "$dowload_stat"
printf -v html "%q" "$html"
sqlHtml='INSERT INTO `'"${tableHtml}"'` (`extracted_link_ID`, `response_header`, `html`, `download_start`, `download_finish`) VALUES ('"'${link_id}'"', '"${dowload_stat:1}"', '"${html:1}"', '"'${start}'"', '"'${finish}'"');'
mysql -u$dbUser -p$dbPass -h$dbHost -P$dbPort -D$dbName --default_character_set utf8 -A -e "$sqlHtml"

I made the code using hacks as some things like mySQL-escaping and outputting stderr and stdout in different variables are not available by default in bash.
I plan to use the code in a worker script for web-indexing/caching so speed is important. Which brings me to my second question: Is there a faster way of doing this in bash?

Answer 1

you should be able to do something like

mysql -u$dbUser -p$dbPass -h$dbHost -P$dbPort -D$dbName --default_character_set utf8 -A <<EOS 
    INSERT INTO \"${tableHtml}\" ('extracted_link_ID', 'response_header', 'html', 
       'download_start', 'download_finish') 
    VALUES (\"${link_id}\", \"${dowload_stat:1}\", \"${html:1}\", \"${start}\", \"${finish}\");
 EOS

Read up about 'here documents' and you should be OK. (Here docs are the <

If this doesn't work, and you still get arg list too long, then you don't have everything quoted correctly. I don't have mysql available, but given the testing I was able to do, this should work, and the here document can be really long (although I think even they have limits at very large values).

EOS can be any string, but is meant to be self-documenting short-hand for EndOfScript.

I'm assuming that mySQL allows columns to be quoted with the bck-quote char. While you can do it because you start off your example with single-quote, not a good shell-fu.

I hope this helps.