[Zope-DB] newbie question getting newlines into database
Zope Admin
zope@search-engine.nu
Sun, 10 Feb 2002 20:36:12 +1300
--------------070301040202070900020401
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
hi,
I'm pretty new to zope and python and am wondering what the easiest
solution is for what i guess must be a pretty common problem.
I'm trying to get data from a <textarea> form field into a database
(mysql via Z MySQL Database Connection) without loosing the \n new lines
or possible converting them into <br>'s before storing into database.
When I include the follow code in my Z SQL Menthod I get the following
troubles. to highlight the relavant code I've included <----> before
and after.
INSERT INTO iqp
(type, listing, author, catID1, catID2, catID3, rating, votes)
VALUES
(
<dtml-sqlvar type type="string">,
<---->
<dtml-sqlvar listing type="string">,
<---->
<dtml-sqlvar author type="string">,
<dtml-sqlvar catID1 type="int">,
<dtml-sqlvar catID2 type="int">,
<dtml-sqlvar catID3 type="int">,
<dtml-sqlvar rating type="float">,
<dtml-sqlvar votes type="int">
);
totally removes '\n' newlines so the formatted data is stored in the
database as one long line.
INSERT INTO iqp
(type, listing, author, catID1, catID2, catID3, rating, votes)
VALUES
(
<dtml-sqlvar type type="string">,
<---->
<dtml-var listing newline_to_br>,
<---->
<dtml-sqlvar author type="string">,
<dtml-sqlvar catID1 type="int">,
<dtml-sqlvar catID2 type="int">,
<dtml-sqlvar catID3 type="int">,
<dtml-sqlvar rating type="float">,
<dtml-sqlvar votes type="int">
);
ends up giving me an error...
Error Type: OperationalError
Error Value: (1064, "You have an error in your SQL syntax near '\n
'r',\n 0,\n 0,\n 0,\n 5,\n 1\n)' at line 12")
Even if the <dtml-var...> code did work I would not be very happy about
using it as it opens up security problems by allowing possible suspect
SQL code through to my database unchecked.
I was thinking perhaps I will need to run the 'listings' variable
through a python script and convert all '\n' to '<br>' before it is
passed to the Z SQL Method.
I have spent about couple hours having a hunt around the web for a
python script I can "recycle" into doing this for me by no luck yet.
Two questions;
1) is my idea of processing the field with a script so i can get new
line data from a <textarea> form through to a mysql 'text' field along
the correct path? Or is there something simpler that I have overlooked
to do this without needing scripting.
2) Assuming the script is the right thing to do does anyone have a
script that already does this they can email me?
Thanks for anyone help on this wee problem of mine
Robert Anderson
code monkey
eCommerce Magic
Wellington
New Zealand
--------------070301040202070900020401
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<html>
<head>
</head>
<body>
hi,<br>
<br>
I'm pretty new to zope and python and am wondering what the easiest solution
is for what i guess must be a pretty common problem.<br>
<br>
I'm trying to get data from a <textarea> form field into a database
(mysql via Z MySQL Database Connection) without loosing the \n new lines
or possible converting them into <br>'s before storing into database.<br>
<br>
When I include the follow code in my Z SQL Menthod I get the following troubles.
to highlight the relavant code I've included <----> before and after.<br>
<br>
INSERT INTO iqp<br>
(type, listing, author, catID1, catID2, catID3, rating, votes)<br>
VALUES<br>
(<br>
<dtml-sqlvar type type="string">, <br>
<----><br>
<dtml-sqlvar listing type="string">,<br>
<----><br>
<dtml-sqlvar author type="string">,<br>
<dtml-sqlvar catID1 type="int">,<br>
<dtml-sqlvar catID2 type="int">,<br>
<dtml-sqlvar catID3 type="int">,<br>
<dtml-sqlvar rating type="float">,<br>
<dtml-sqlvar votes type="int"><br>
);<br>
<br>
totally removes '\n' newlines so the formatted data is stored in the database
as one long line.<br>
<br>
<br>
INSERT INTO iqp<br>
(type, listing, author, catID1, catID2, catID3, rating, votes)<br>
VALUES<br>
(<br>
<dtml-sqlvar type type="string">, <br>
<br>
<----><br>
<dtml-var listing newline_to_br>,<br>
<----><br>
<br>
<dtml-sqlvar author type="string">,<br>
<dtml-sqlvar catID1 type="int">,<br>
<dtml-sqlvar catID2 type="int">,<br>
<dtml-sqlvar catID3 type="int">,<br>
<dtml-sqlvar rating type="float">,<br>
<dtml-sqlvar votes type="int"><br>
);<br>
<br>
ends up giving me an error...<br>
<br>
<strong>Error Type: OperationalError</strong><br>
<strong>Error Value: (1064, "You have an error in your SQL syntax near '\n
'r',\n 0,\n 0,\n 0,\n 5,\n 1\n)' at line 12")</strong><br>
<br>
Even if the <dtml-var...> code did work I would not be very happy about
using it as it opens up security problems by allowing possible suspect SQL
code through to my database unchecked.<br>
<br>
I was thinking perhaps I will need to run the 'listings' variable through
a python script and convert all '\n' to '<br>' before it is passed
to the Z SQL Method.<br>
<br>
I have spent about couple hours having a hunt around the web for a python
script I can "recycle" into doing this for me by no luck yet.<br>
<br>
Two questions;<br>
<br>
1) is my idea of processing the field with a script so i can get new line
data from a <textarea> form through to a mysql 'text' field along the
correct path? Or is there something simpler that I have overlooked to do
this without needing scripting.<br>
<br>
2) Assuming the script is the right thing to do does anyone have a script
that already does this they can email me?<br>
<br>
<br>
<br>
Thanks for anyone help on this wee problem of mine<br>
<br>
<br>
<br>
Robert Anderson<br>
<br>
code monkey<br>
eCommerce Magic<br>
Wellington <br>
New Zealand<br>
</body>
</html>
--------------070301040202070900020401--