Textareas and SQL (Gadfly)
I've been hitting my head on the wall for hours over this... I've got a large collection of forms feeding into a gadfly database without a problem, and then a few that don't work at all. The only difference I can see is that the ones that fail have <textarea> data and the ones that work don't. I'm getting a: Error Type: LexTokenError Error Value: Lexical token not found near :: "s of Bloodax',\012"*"'A determined c" What I _think_ is happening is that gadfly can't handle carriage returns and line feeds in the string input. (Which is why I was trying things like "virtual" to perhaps get rid of them.) Hmmm... Actually, just tried something else, the below versions actually work, but _only_ if you delete all linefeeds from the textarea and let it wrap all by it's lonesome... Something I can't guarantee my users will do. Is there a way I can remove the carriage returns prior to hitting the sql? I was hoping to use some kind of formatting command rather than trying some weird kind of _.string.sub call. Thanks. Evan Gibson (below is the demo info) The form has fields: Name: <input type="text" name="name:string" size="40" value=""><br> Description: <textarea name="description:text" wrap="virtual" cols="50" rows="5"></textarea><br> (I've tried description with wrap=On/Off/Virtual and with and without the :text typecasting.) Sql is called by: <dtml-call "item_create(item_name=name,description=description)"> Sql query has: Arguments- description:string name:string insert into item_table (name, description) values (<dtml-sqlvar item_name type=string>, <dtml-sqlvar description type=string optional>) -- Evan ~ThunderFoot~ Gibson ~ nihil mutatem, omni deletum ~ May the machines watch over you with loving grace.
I use <dtml-sqlvar "_.string.replace(textarea_field,_.chr(10),'')" type=string> ----- Original Message ----- From: Evan Gibson <egibson@connect.com.au> To: <zope@zope.org> Sent: Tuesday, February 08, 2000 9:53 AM Subject: [Zope] Textareas and SQL (Gadfly) I've been hitting my head on the wall for hours over this... I've got a large collection of forms feeding into a gadfly database without a problem, and then a few that don't work at all. The only difference I can see is that the ones that fail have <textarea> data and the ones that work don't. I'm getting a: Error Type: LexTokenError Error Value: Lexical token not found near :: "s of Bloodax',\012"*"'A determined c" What I _think_ is happening is that gadfly can't handle carriage returns and line feeds in the string input. (Which is why I was trying things like "virtual" to perhaps get rid of them.) Hmmm... Actually, just tried something else, the below versions actually work, but _only_ if you delete all linefeeds from the textarea and let it wrap all by it's lonesome... Something I can't guarantee my users will do. Is there a way I can remove the carriage returns prior to hitting the sql? I was hoping to use some kind of formatting command rather than trying some weird kind of _.string.sub call. Thanks. Evan Gibson (below is the demo info) The form has fields: Name: <input type="text" name="name:string" size="40" value=""><br> Description: <textarea name="description:text" wrap="virtual" cols="50" rows="5"></textarea><br> (I've tried description with wrap=On/Off/Virtual and with and without the :text typecasting.) Sql is called by: <dtml-call "item_create(item_name=name,description=description)"> Sql query has: Arguments- description:string name:string insert into item_table (name, description) values (<dtml-sqlvar item_name type=string>, <dtml-sqlvar description type=string optional>) -- Evan ~ThunderFoot~ Gibson ~ nihil mutatem, omni deletum ~ May the machines watch over you with loving grace. _______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Jim Sanford wrote:
is that gadfly can't handle carriage returns and line feeds in the string input. (Which is why I was trying things like "virtual" to perhaps get rid of them.)
This is a bug in Gadfly. If DC ever had time to read the Collector it might've been in by now. I'm eagerly awaiting the day when they start handing out parts of Zope to the public. Or at the very least make a list of people who's patches are always read. You can fix it by replacing charstre = "'[^\n']*'" with charstre = "'[^']*'" in lib/python/Products/ZGadflyDA/gadfly/sqlgen.py -- Itamar S.T. itamars@ibm.net
odbc to a VisualFoxPro table doesn't like the line feeds, but CRs are ok Could use this complementary pieces of dtml for Gadfly to save and retrieve a variable with embedded CRs and LFs: <dtml-sqlvar "_.string.replace(_.string.replace(dtml_variable,_.chr(13),'<CR>'),_.chr(10),'<LF>')" type=string> and <dtml-var "_.string.replace(_.string.replace(gadfly_field,'<CR>',_.chr(13)),'<LF>',_.chr(10))"> ----- Original Message ----- From: Itamar Shtull-Trauring <itamars@ibm.net> To: <zope@zope.org> Cc: Jim Sanford <jsanford@atinucleus.com> Sent: Tuesday, February 08, 2000 10:44 AM Subject: Re: [Zope] Textareas and SQL (Gadfly) Jim Sanford wrote:
is that gadfly can't handle carriage returns and line feeds in the string input. (Which is why I was trying things like "virtual" to perhaps get rid of them.)
This is a bug in Gadfly. If DC ever had time to read the Collector it might've been in by now. I'm eagerly awaiting the day when they start handing out parts of Zope to the public. Or at the very least make a list of people who's patches are always read. You can fix it by replacing charstre = "'[^\n']*'" with charstre = "'[^']*'" in lib/python/Products/ZGadflyDA/gadfly/sqlgen.py -- Itamar S.T. itamars@ibm.net
On Wed, 9 Feb 2000, Evan Gibson wrote:
What I _think_ is happening is that gadfly can't handle carriage returns and line feeds in the string input. (Which is why I was trying things like "virtual" to perhaps get rid of them.)
This is an all-too-frequently asked question. First, pay no attention to people who say it's a bug in gadfly. The fundamental problem is that there is no SQL standard for including linebreaks in strings. Any solution for including the linebreaks that works with one database is pretty much guaranteed not to work with another. You don't want to be locked in to using one database (I know: I've been forced to move from gadfly so that non-python tools can get to the data).
Is there a way I can remove the carriage returns prior to hitting the sql? I was hoping to use some kind of formatting command rather than trying some weird kind of _.string.sub call.
I use a simple whitespace cleaner: _.string.join(_.string.split(textfield)) This is the simplest thing if you don't care about the original linebreaks (I warn my users that linebreaks will be ignored). If your text is destined only for HTML and you want to preserve line breaks, you could instead use _.string.join(_.string.split(textfield, '\n'), '<BR>'). I decided to keep HTML out of my database; you may decide otherwise. Hope this helps. joe
On Tue, Feb 08, 2000 at 09:25:53AM -0800, Joseph Pallas wrote:
On Wed, 9 Feb 2000, Evan Gibson wrote:
What I _think_ is happening is that gadfly can't handle carriage returns and line feeds in the string input. (Which is why I was trying things like "virtual" to perhaps get rid of them.)
This is an all-too-frequently asked question. First, pay no attention to people who say it's a bug in gadfly. The fundamental problem is that there is no SQL standard for including linebreaks in strings. Any solution for including the linebreaks that works with one database is pretty much guaranteed not to work with another. You don't want to be locked in to using one database (I know: I've been forced to move from gadfly so that non-python tools can get to the data).
I'm halfway through moving to oracle, but for the moment I'm still in gadfly land. One thing I've found _immensely_ useful when working with gadfly is the gfplus.py script I found while trolling deja. It provides you with an interactive shell for Gadfly along the lines of sqlplus. It's at http://endeavor.med.nyu.edu/~jeff/gfplus/ But that's changing the topic... Since it's not a bug, but rather a lack of a standard, then the datbase isn't the right place to fix it. It shouldn't be left the way it is, though, since it's a big pain for newbies and pretty hard to debug (for the longest time I thought there was something wrong with my query. I'd found another place where I did dmtl instead of dtml and was looking for something similar.) I think the proper place for a fix would be Zope's typecasting features. Instead of just ":text" making the output text with linefeeds we could add ":text_clean" which takes all the text from the textarea and replaces linefeeds with single spaces, and ":text_html" which converts them to <br> [like the linefeed_to_br function, but without adding a linefeed back in after the br!]
I use a simple whitespace cleaner: _.string.join(_.string.split(textfield))
Thanks for that. And thanks to the other guys who helped too.
This is the simplest thing if you don't care about the original linebreaks (I warn my users that linebreaks will be ignored). If your text is destined only for HTML and you want to preserve line breaks, you could instead use _.string.join(_.string.split(textfield, '\n'), '<BR>'). I decided to keep HTML out of my database; you may decide otherwise.
Nod. We need formatting methods to cover both decisions.
Hope this helps. joe
-- Evan ~ThunderFoot~ Gibson ~ nihil mutatem, omni deletum ~ May the machines watch over you with loving grace.
participants (4)
-
Evan Gibson -
Itamar Shtull-Trauring -
Jim Sanford -
Joseph Pallas