|
From: Robert M. <rob...@gm...> - 2009-04-11 14:01:20
|
In my reading of the SQL-92 standard, I found the following syntax
rules which seem to address embedded quotes in string literals:
<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character
representation>... ] <quote> }... ]
<character representation> ::=
<nonquote character>
| <quote symbol>
<quote symbol> ::= <quote><quote>
>From this specification, it seems that "\'" (<backslash><quote>) is
not allowed as a "character representation" in a "character literal
string". A "character representation" can only be:
1. a non-quote character, or
2. a quote symbol (two adjacent quote characters)
Given that your script was generated by mysqldump, I find it most
unfortunate that the MySQL implementers decided to ignore the
specification here.
Perhaps you can file a bug report with Sun to have them fix mysqldump
to support following the standard for interoperability with other
tools. Or perhaps they have a "standards compliance" mode that will
emit SQL that adheres to the standard. Let us know if you decide to
pursue this with Sun and what you discover.
Rob
On Mon, Apr 6, 2009 at 6:00 AM, Teijo Aulin <tab...@gm...> wrote:
> Worked fine, thanks :)
>
> On Sun, Apr 5, 2009 at 4:32 AM, Robert Manning <rob...@gm...>
> wrote:
>>
>> I think I know what the problem is. In your script, the url varchar
>> column values has an embedded single quote char. The tokenizer *does
>> not* correctly handle embedded quotes where the quote is escaped with
>> a backslash - such as \'. This occurs in the first statement in
>> column 593 (... Let\'s pretend (2:56) ...). The tokenizer however
>> *does* correctly handle embedded quotes where the quote is escaped
>> with a another single quote - such as ''. So, you can probably work
>> around this by doing a Replace (Ctrl-H) in SQuirreL and using \' for
>> "Find What:" and '' (two single quote characters) for "Replace With"
>> and click "Replace All". Once you do that it should execute when you
>> select all statements and run them (at least it worked for me with
>> your two statements). So, there is a work-around until we develop a
>> bug-fix.
>>
>> Rob
>>
>>
>> On Sat, Apr 4, 2009 at 11:56 AM, Teijo Aulin <tab...@gm...> wrote:
>> > hi
>> >
>> > I can run your example fine too. Here's two lines from my dump with
>> > create
>> > table script:
>> >
>> > CREATE TABLE "albums"
>> > (
>> > id int PRIMARY KEY NOT NULL,
>> > band int,
>> > description text,
>> > title varchar(255),
>> > link text,
>> > released varchar(255),
>> > genres text,
>> > tracks text,
>> > credits text,
>> > label varchar(255),
>> > num_of_tracks smallint,
>> > cds smallint,
>> > status smallint NOT NULL,
>> > link_small text
>> > )
>> >
>> > INSERT INTO albums VALUES (2,11825,NULL,'Anderson Bruford Wakeman
>> > Howe','<iframe
>> >
>> > src=\"http://rcm.amazon.com/e/cm?t=0c-20&o=1&p=8&l=as1&asins=B000002VGG&fc1=000000&=1&lc1=0000ff&bc1=000000&lt1=_blank&IS2=1&bg1=ffffff&f=ifr\"
>> > style=\"width:120px;height:240px;\" scrolling=\"no\" marginwidth=\"0\"
>> > marginheight=\"0\" frameborder=\"0\"></iframe>','01/01/1989','Classic
>> > Rock','1. Themes (5:58)\r\n2. Fist of Fire (3:27)\r\n3. Brother of Mine
>> > (10:18)\r\n4. Birthright (6:02)\r\n5. The meeting (4:21)\r\n6. Quartet
>> > (9:22)\r\n7. Teakbois (7:39)\r\n8. Order of the Universe (9:02)\r\n9.
>> > Let\'s
>> > pretend (2:56)','John Anderson / vocals\r\nBill Bruford / drums\r\nRick
>> > Wakeman / keyboards\r\nSteve Howe / guitar\r\nTony Levin / bass, vocals
>> > and
>> > stick bass\r\nMilton McDonald / guitar (rhythm)','Bmg',9,1,1,NULL);
>> >
>> > INSERT INTO albums VALUES (3,581,NULL,'Californication','<iframe
>> >
>> > src=\"http://rcm.amazon.com/e/cm?t=0c-20&o=1&p=8&l=as1&asins=B00000J7JO&fc1=000000&=1&lc1=0000ff&bc1=000000&lt1=_blank&IS2=1&bg1=ffffff&f=ifr\"
>> > style=\"width:120px;height:240px;\" scrolling=\"no\" marginwidth=\"0\"
>> > marginheight=\"0\" frameborder=\"0\"></iframe>','June
>> > 8,1999','Rock/Pop','1.
>> > Around the World 3:58\r\n2. Parallel Universe 4:30\r\n3. Scar Tissue
>> > 3:37\r\n4. Otherside 4:15\r\n5. Get on Top 3:18\r\n6. Californication
>> > 5:21\r\n7. Easily 3:51\r\n8. Porcelain 2:43\r\n9. Emit Remmus
>> > 4:00\r\n10. I
>> > Like Dirt 2:37\r\n11. This Velvet Glove 3:45\r\n12. Savior 4:52\r\n13.
>> > Purple Stain 4:13\r\n14. Right on Time 1:52\r\n15. Road Trippin\'
>> > 3:25',null,'Warner Bros',15,1,1,NULL);
>> >
>> > If I run them separately, they go though fine, but when I select both it
>> > fails to:
>> >
>> > Error: ERROR: unterminated quoted string at or near "'<iframe
>> >
>> > src=\"http://rcm.amazon.com/e/cm?t=0c-20&o=1&p=8&l=as1&asins=B00000J7JO&fc1=000000&=1&lc1=0000ff&bc1=000000&l"
>> > SQLState: 42601
>> > ErrorCode: 0
>> > Error occured in:
>> > INSERT INTO albums VALUES (2,11825,NULL,'Anderson Bruford Wakeman
>> > Howe','<iframe
>> >
>> > src=\"http://rcm.amazon.com/e/cm?t=0c-20&o=1&p=8&l=as1&asins=B000002VGG&fc1=000000&=1&lc1=0000ff&bc1=000000&lt1=_blank&IS2=1&bg1=ffffff&f=ifr\"
>> > style=\"width:120px;height:240px;\" scrolling=\"no\" marginwidth=\"0\"
>> > marginheight=\"0\" frameborder=\"0\"></iframe>','01/01/1989','Classic
>> > Rock','1. Themes (5:58)\r\n2. Fist of Fire (3:27)\r\n3. Brother of Mine
>> > (10:18)\r\n4. Birthright (6:02)\r\n5. The meeting (4:21)\r\n6. Quartet
>> > (9:22)\r\n7. Teakbois (7:39)\r\n8. Order of the Universe (9:02)\r\n9.
>> > Let\'s
>> > pretend (2:56)','John Anderson / vocals\r\nBill Bruford / drums\r\nRick
>> > Wakeman / keyboards\r\nSteve Howe / guitar\r\nTony Levin / bass, vocals
>> > and
>> > stick bass\r\nMilton McDonald / guitar (rhythm)','Bmg',9,1,1,NULL);
>> > INSERT INTO albums VALUES (3,581,NULL,'Californication','<iframe
>> >
>> > src=\"http://rcm.amazon.com/e/cm?t=thetabworld0c-20&o=1&p=8&l=as1&asins=B00000J7JO&fc1=000000&=1&lc1=0000ff&bc1=000000&l
>> >
>> > I have about 500 of these and each time I select > 1 line it fails like
>> > this.It's as if it tries to end the statement to the semicolon, and then
>> > complains about the missing quote, since if I remove the semicolon near
>> > "118;" it then complains about the next semicolon at "116;".
>> >
>> > If I try to change the statement separator to a different char, Squirrel
>> > just hangs eating up all cpu for several minutes and does nothing.
>> >
>> > Running version 2.6.4, Postgres 8.3, JRE 1.6
>> >
>> > Thanks for any help :)
>> >
>> > On Sat, Apr 4, 2009 at 5:57 PM, Robert Manning
>> > <rob...@gm...>
>> > wrote:
>> >>
>> >> I wasn't able to reproduce it with this script (';' near the end of
>> >> the url - a;2 ) :
>> >>
>> >> create table testurl (myid integer, myurl varchar(255))
>> >>
>> >> INSERT INTO testurl (MYID,MYURL) VALUES
>> >> (0,'http://foo.com:8080/bug.cgi?key=1&val=a;2');
>> >> INSERT INTO testurl (MYID,MYURL) VALUES
>> >> (1,'http://foo.com:8080/bug.cgi?key=1&val=a;2');
>> >> INSERT INTO testurl (MYID,MYURL) VALUES
>> >> (2,'http://foo.com:8080/bug.cgi?key=1&val=a;2');
>> >> INSERT INTO testurl (MYID,MYURL) VALUES
>> >> (3,'http://foo.com:8080/bug.cgi?key=1&val=a;2');
>> >>
>> >> What am I doing wrong?
>> >>
>> >> Rob
>> >>
>> >> On Sat, Apr 4, 2009 at 10:47 AM, Teijo Aulin <tab...@gm...>
>> >> wrote:
>> >> > Hi
>> >> >
>> >> > I'm importing a dump from my old mysql db and I've ran into some
>> >> > problems
>> >> > with semicolon and Squirrel. The problem is that in the dump (which
>> >> > is
>> >> > just
>> >> > a bunch of insert statements) I have a column that has urls. Some
>> >> > urls
>> >> > have
>> >> > semicolons in them. Each insert statement ends with a semicolon as
>> >> > well.
>> >> >
>> >> > When I execute the statements one by one, I have no errors. When I
>> >> > select
>> >> > all of the lines and run, it fails on the first line that has an url
>> >> > with a
>> >> > semicolon in it (and yes, I can execute that line by itself without
>> >> > problems).
>> >> >
>> >> > Helb ? Thanks.
>> >> >
>> >> >
>> >> >
>> >> > ------------------------------------------------------------------------------
>> >> >
>> >> > _______________________________________________
>> >> > Squirrel-sql-users mailing list
>> >> > Squ...@li...
>> >> > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>> >> >
>> >> >
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Webmaster
>> > http://www.thetabworld.com
>> >
>
>
>
> --
> Cheers,
> Webmaster
> http://www.thetabworld.com
>
|
