List:General Discussion« Previous MessageNext Message »
From:Frank Piwarski Date:April 4 1999 2:44am
Subject:Re: Redefining default delimeters
View as plain text  
Michael Widenius wrote:
> 
> >>>>> "Frank" == Frank Piwarski <fpiwarsk@stripped> writes:
> 
> Frank> This syntax is ofcourse used with LOAD DATA INFILE
> -> fields terminated by ','
> -> enclosed by '"'
> -> lines terminated by '/r'
> 
> Frank> but can i do a similiar thing in an UPDATE statement to re-define field
> Frank> temination along this line
> 
> Frank> fields-terminated-by '^^'
> 
> Frank> the reason being that i have a file that reads like this
> 
> Frank> UPDATE _remembrance set client = '_429UJ9JF',
> Frank> SetTitle = 'Photo Gallery',
> Frank> CountryCode = 'us',
> Frank> Password = 'passMEthePossum',
> Frank> Visible = 'true',
> Frank> Expires = '12/1/2000',
> Frank> AdGroup = 'zx3',
> Frank> SetCode = '000',
> Frank> Language = 'us',
> Frank> template = 'cloud.jpg',
> Frank> regnum = '_10350005399',
> Frank> regco = 'Honest Johns Funeral Home',
> Frank> regaddress1 = '1712 N. Harold's Ln.',
> Frank> regaddress2 = 'State Rd. 46',
> 
> Frank> and i would like to use syntax like what follows. I am happy to
> Frank> recompile to support this if that is possible.
> 
> Frank> UPDATE _remembrance set client = ^^_429UJ9JF^^
> Frank> SetTitle = ^^Photo Gallery^^
> Frank> CountryCode = ^^us^^
> Frank> Password = ^^pass^^
> Frank> Visible = ^^true^^
> Frank> Expires = ^^12/1/2000^^
> Frank> AdGroup = ^^zx3^^
> Frank> SetCode = ^^000^^
> Frank> Language = ^^us^^
> Frank> template = ^^cloud.jpg^^
> Frank> regnum = ^^_10350005399^^
> Frank> regco = ^^Honest Johns Funeral Home^^
> Frank> regaddress1 = ^^1712 N. Harold's Ln.^^
> Frank> regaddress2 = ^^State Rd. 46^^,
> 
> Hi!
> 
> May I ask you why ?  If you have problems with quoting, you should
> look at the appropriate 'quote' or 'escape' functions in the API you
> are using!

When a field begins with ' and ends with ' you have the matter of all
the ' inbetween the beginning and end. It is a simple matter to escape
any given char, but less so if you have to create exceptions to the
escaping rules. LOAD DATE infile allows one to alter field delimeters on
import, and it seems that an UPDATE statement similiar to the above does
not allow this within the statements conditionals. 

Since we want to escape on the fly by filtering a stream of chars, and
have this method in place

 else {
                rectemp = Utilities.escape (rectemp);
+               rectemp = urlSafe(rectemp);
                sb.append (headmap.substring (ind, marker + 42) + db +
headmap.substring (marker + 55, marker + 58) + table2 +
headmap.substring (marker + 64, marker + 67) + rectemp);
              }


+   public static String urlSafe(String str) throws Exception
+   { str = escape(str, '&', "26");
+     str = escape(str, '=', "3D");
+     str = escape(str, '@', "40");
+     str = escape(str, ':', "3A");
+     str = escape(str, '?', "3F");
+     str = escape(str, '/', "2F");
+     str = escape(str, ';', "3B");
+     return str;
+   }

it is easy to add   

str = escape(str, ''', "27");

so, if the field start and end delimeter is not ' all works simply, so
if redefinition was as simple as adding or setting an option such that
field-delimited-by ='^^' it was a simple fix to a vexing problem.

As always thank you for your most timely reply and informative reply.

/f

> 
> The major problem with fixing this is that you have to patch both the
> 'mysql' program to find 'end of strings', and sql_lex.cc to understand
> that a string can start with ^^.  Neither patch is completely trivial.
> 
> Regards,
> Monty
Thread
Redefining default delimetersFrank Piwarski4 Apr
  • Redefining default delimetersMichael Widenius4 Apr
  • Re: Redefining default delimetersFrank Piwarski4 Apr
  • Re: Redefining default delimetersFrank Piwarski4 Apr