[Squirrel-sql-users] Problem using squirrel with DB2

[David Rosenstrauch]

Hi. Been using squirrel for a few months now and love it. (Kudos to
the developers!)

I've used it with no problems on Oracle, Sybase, SQL Server, and MySQL.
But I just started using it with DB2 and ran into an issue. I hope
someone here has a solution, because it's a really annoying problem.
(i.e., is making Squirrel unusable with DB2 for me)

With every other database, I'm able to send multiple SQL statements to
the database all in one fell swoop. I just highlight the statements I
want to send, and then hit Ctrl-Enter. For example, I often send
multiple update statements simultaneously, like this:

UPDATE foo SET bar = 5;
UPDATE home SET bart = 3;

However, DB2 (or at least the version that I'm work with - UDB 8.1)
barfs when I try to do this. I get an error like this:


Query 1 of 2 elapsed time (seconds) - Total: 0.004, SQL query: 0.004,
Building output: 0
Error: com.ibm.db2.jcc.b.SQLException: The character "
" following "BEGIN-OF-STATEMENT" is not valid., SQL State: 42601, Error
Code: -7
Error occured in:

UPDATE home SET bart = 3


It looks like either Squirrel or DB2 or JDBC - or some combination
thereof - doesn't like the line feed between the 2 SQL statements. So
the first statement appears to execute fine, but then it gives me an
error when it sees the line-feed before the 2nd statement.

Anyone know if there's any way to fix or work around this? I've got way
too many SQL statements to execute to send them one at a time!

TIA,

DR

[Gerd Wagner]

Hi David,

I couldn't reproduce your problem on my UDB 8.2. No matter what I set SQuirreL's
statement separator to. My UDB 8.2 itself seems to accept ';' as statement
separator.

The pointer I can give you is to check SQuirreL's statement separator. See
Session Properties --> Tab SQL --> Statement Separator. If you set the separator
to ';' and execute

UPDATE foo SET bar = 5;
UPDATE home SET bart = 3;

SQuirreL should send the following two statements to your DB:

1. UPDATE foo SET bar = 5
2. UPDATE home SET bart = 3

(Note, no ';' at the end.) The DB should be able to execute these statements.

Hope it helps.

Gerd

Post by David Rosenstrauch
Hi. Been using squirrel for a few months now and love it. (Kudos to
the developers!)

Thanks a lot!

Post by David Rosenstrauch
I've used it with no problems on Oracle, Sybase, SQL Server, and MySQL.
But I just started using it with DB2 and ran into an issue. I hope
someone here has a solution, because it's a really annoying problem.
(i.e., is making Squirrel unusable with DB2 for me)
With every other database, I'm able to send multiple SQL statements to
the database all in one fell swoop. I just highlight the statements I
want to send, and then hit Ctrl-Enter. For example, I often send
UPDATE foo SET bar = 5;
UPDATE home SET bart = 3;
However, DB2 (or at least the version that I'm work with - UDB 8.1)
Query 1 of 2 elapsed time (seconds) - Total: 0.004, SQL query: 0.004,
Building output: 0
Error: com.ibm.db2.jcc.b.SQLException: The character "
" following "BEGIN-OF-STATEMENT" is not valid., SQL State: 42601, Error
Code: -7
UPDATE home SET bart = 3
It looks like either Squirrel or DB2 or JDBC - or some combination
thereof - doesn't like the line feed between the 2 SQL statements. So
the first statement appears to execute fine, but then it gives me an
error when it sees the line-feed before the 2nd statement.
Anyone know if there's any way to fix or work around this? I've got way
too many SQL statements to execute to send them one at a time!
TIA,
DR
_______________________________________________
Squirrel-sql-users mailing list
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

[David Rosenstrauch]

Thanks very much for the response, Gerd. But I already have ";" set as
the statement separator. (If I didn't, then maybe DB2 would be throwing
a syntax error on the ";" character?)

Again, the problem isn't the ";", but rather the line feed. It looks
from here like what Squirrel is doing is just stripping out the ";", but
then sending the entire block - including line feeds - to db2 all at
once, rather than breaking it into separate statements like you
described below.

That doesn't seem to be a problem with any of the other DB's I've worked
with, but apparently DB2 has a problem with it. Doesn't seem to be any
way to tweak this in Squirrel (or DB2) that I can see, though,
unfortunately.

Anyway, thanks for the suggestion. Looks like I'm stuck with executing
one statement at a time for now though. Ugh. If anything else comes to
mind, please do let me know.

Thanks,

DR

Post by Gerd Wagner
Hi David,
I couldn't reproduce your problem on my UDB 8.2. No matter what I set
SQuirreL's statement separator to. My UDB 8.2 itself seems to accept ';'
as statement separator.
The pointer I can give you is to check SQuirreL's statement separator.
See Session Properties --> Tab SQL --> Statement Separator. If you set
the separator to ';' and execute
UPDATE foo SET bar = 5;
UPDATE home SET bart = 3;
1. UPDATE foo SET bar = 5
2. UPDATE home SET bart = 3
(Note, no ';' at the end.) The DB should be able to execute these statements.
Hope it helps.
Gerd

[David Rosenstrauch]

Thanks very much for the patch, Gerd. I don't have time to test this
out right now, but I'll test it for you ASAP and let you know if that
fixes the problem.

Thanks again,

DR

I had another look at it. What is send to the DB is
1. UPDATE foo SET bar = 5
2. \nUPDATE home SET bart = 3
Where \n means a line feed. Maybe this line feed is the problem. I
attached a zip with a class file (and source file too) where the line
feed is removed. You may replace this class in your fw.jar. Please make
sure that the path inside the jar is kept. You can do this using JDK jar
command. Unfortunately I don't know the correct syntax right now.
If you managed to update your fw.jar it would be nice if you could tell
whether the fix works. If so I'll commit it to CVS and it will be in
coming versions.
Thanks.
Gerd

[David Rosenstrauch]

Cool! I just tested it out and it looks like that solves the problem.
So feel free to check it in.

Thanks again big time for the quick patch! This is a huge help!

DR

Post by David Rosenstrauch
Thanks very much for the patch, Gerd. I don't have time to test this
out right now, but I'll test it for you ASAP and let you know if that
fixes the problem.
Thanks again,
DR

I had another look at it. What is send to the DB is
1. UPDATE foo SET bar = 5
2. \nUPDATE home SET bart = 3
Where \n means a line feed. Maybe this line feed is the problem. I
attached a zip with a class file (and source file too) where the line
feed is removed. You may replace this class in your fw.jar. Please
make sure that the path inside the jar is kept. You can do this using
JDK jar command. Unfortunately I don't know the correct syntax right now.
If you managed to update your fw.jar it would be nice if you could
tell whether the fix works. If so I'll commit it to CVS and it will be
in coming versions.
Thanks.
Gerd

[David Cook]

I'm just an (innocent) bystander, so forgive my
'wildass' guessing. And, I also don't have ANY real
knowledge about the context of your question as
it relates to Squirrel.

Having said that, I'm just trying to imagine that (probably)
you already HAVE (HAD) a file full of SQL cmds (from somewhere)
that already have line-feeds/new-lines/carriage-returns or whatever
separating
each line, right?

If so, does it make sense that you could just write a small program/script
to REMOVE any/all such line-feeds/new-lines/car-returns from that
file. Then bring up that file in an editor, COPY the body (a whole bunch of
SQL cmds)
into the 'clipboard', and then PASTE them all into the appropriate
input field (i.e. into Squirrel)?

Sorry if I'm way off base, guessing.

Cheers...

Dave




-----Original Message-----
From: squirrel-sql-users-***@lists.sourceforge.net
[mailto:squirrel-sql-users-***@lists.sourceforge.net]On Behalf Of
David Rosenstrauch
Sent: Tuesday, June 06, 2006 12:55 PM
To: squirrel-sql-***@lists.sourceforge.net
Subject: [Squirrel-sql-users] Problem using squirrel with DB2


Hi. Been using squirrel for a few months now and love it. (Kudos to
the developers!)

I've used it with no problems on Oracle, Sybase, SQL Server, and MySQL.
But I just started using it with DB2 and ran into an issue. I hope
someone here has a solution, because it's a really annoying problem.
(i.e., is making Squirrel unusable with DB2 for me)

With every other database, I'm able to send multiple SQL statements to
the database all in one fell swoop. I just highlight the statements I
want to send, and then hit Ctrl-Enter. For example, I often send
multiple update statements simultaneously, like this:

UPDATE foo SET bar = 5;
UPDATE home SET bart = 3;

However, DB2 (or at least the version that I'm work with - UDB 8.1)
barfs when I try to do this. I get an error like this:


Query 1 of 2 elapsed time (seconds) - Total: 0.004, SQL query: 0.004,
Building output: 0
Error: com.ibm.db2.jcc.b.SQLException: The character "
" following "BEGIN-OF-STATEMENT" is not valid., SQL State: 42601, Error
Code: -7
Error occured in:

UPDATE home SET bart = 3


It looks like either Squirrel or DB2 or JDBC - or some combination
thereof - doesn't like the line feed between the 2 SQL statements. So
the first statement appears to execute fine, but then it gives me an
error when it sees the line-feed before the 2nd statement.

Anyone know if there's any way to fix or work around this? I've got way
too many SQL statements to execute to send them one at a time!

TIA,

DR

[David Rosenstrauch]

No, you're absolutely right. I could very easily just whip up a quick
shell script (program? I don't need to write a stinking program! :-)
that would do this.

Good suggestion. Sometimes I get so focused on trying to fix the
problem at hand, I overlook an easy workaround.

Thanks,

DR

Post by David Cook
I'm just an (innocent) bystander, so forgive my
'wildass' guessing. And, I also don't have ANY real
knowledge about the context of your question as
it relates to Squirrel.
Having said that, I'm just trying to imagine that (probably)
you already HAVE (HAD) a file full of SQL cmds (from somewhere)
that already have line-feeds/new-lines/carriage-returns or whatever
separating
each line, right?
If so, does it make sense that you could just write a small program/script
to REMOVE any/all such line-feeds/new-lines/car-returns from that
file. Then bring up that file in an editor, COPY the body (a whole bunch of
SQL cmds)
into the 'clipboard', and then PASTE them all into the appropriate
input field (i.e. into Squirrel)?
Sorry if I'm way off base, guessing.
Cheers...
Dave