[Squirrel-sql-users] Semicolon problem

Discussion:

Teijo Aulin

2009-04-04 14:47:01 UTC

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.

Robert Manning

2009-04-04 14:57:05 UTC

Permalink

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

Post by Teijo Aulin
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
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

Teijo Aulin

2009-04-04 15:56:08 UTC

Permalink

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&&#108
"
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&&#108

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

Post by Robert Manning
I wasn't able to reproduce it with this script (';' near the end of
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'<http://foo.com:8080/bug.cgi?key=1&val=a;2%27>
);
INSERT INTO testurl (MYID,MYURL) VALUES
(1,'http://foo.com:8080/bug.cgi?key=1&val=a;2'<http://foo.com:8080/bug.cgi?key=1&val=a;2%27>
);
INSERT INTO testurl (MYID,MYURL) VALUES
(2,'http://foo.com:8080/bug.cgi?key=1&val=a;2'<http://foo.com:8080/bug.cgi?key=1&val=a;2%27>
);
INSERT INTO testurl (MYID,MYURL) VALUES
(3,'http://foo.com:8080/bug.cgi?key=1&val=a;2'<http://foo.com:8080/bug.cgi?key=1&val=a;2%27>
);
What am I doing wrong?
Rob

Post by Teijo Aulin
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

Post by Teijo Aulin
a bunch of insert statements) I have a column that has urls. Some urls

have

Post by Teijo Aulin
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

Post by Teijo Aulin
semicolon in it (and yes, I can execute that line by itself without
problems).
Helb ? Thanks.

------------------------------------------------------------------------------

Post by Teijo Aulin
_______________________________________________
Squirrel-sql-users mailing list
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

--
Cheers,
Webmaster
http://www.thetabworld.com

Robert Manning

2009-04-05 00:44:47 UTC

Permalink

I was able to reproduce this issue in both 3.0.1 and the latest
snapshot. I don't know why - at the moment - but the tokenizing logic
believes that the ending ";" of the first SQL statement is inside of a
string literal, so it starts consuming characters from the next
statement, ending what it thinks is the literal string, after the
beginning of the next statement. Please file a bug report where I
can record the outcome of my examination with the debugger. Thank you
for bringing this to our attention.

Rob

Post by Teijo Aulin
hi
I can run your example fine too. Here's two lines from my dump with create
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
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&&#108"
SQLState: 42601
ErrorCode: 0
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&&#108
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 :)

Post by Robert Manning
I wasn't able to reproduce it with this script (';' near the end of
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

--
Cheers,
Webmaster
http://www.thetabworld.com

Robert Manning

2009-04-05 01:32:57 UTC

Permalink

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

Post by Robert Manning
I wasn't able to reproduce it with this script (';' near the end of
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

--
Cheers,
Webmaster
http://www.thetabworld.com

Robert Manning

2009-04-11 14:01:10 UTC

Permalink

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>