Java and Microsoft SQL

Thread view:

bunka.munka@yahoo.com - 30 Oct 2005 22:22 GMT

Hi !

I am experiencing some problems with MSSQL (ver 8.0).

While I can execute sub-select statement directly on a MSSQL server,
I cannot do the same thing in JDBC.

Like the following statement( JDBC throws exception about mismatched
type)

select idcolumn,a,b,c FROM mytable WHERE idcolumn = (SELECT max
(idcolumn) FROM mytable)

I could get the result other way too (like top 1 and descend sort
etc..), but this would be the fastest way and I want to use it.

I am connecting to database server without any settings, just IP,
username and password.

On mysql, I had a several options I could specify in a connect string,
so the server acts different.

Is there any setting I can specify which can help me ? Where can I
read all parameters I can specify when connecting via JDBC to Microsoft
SQL ?

Thx.

Reply to this Message

Bjorn Abelli - 30 Oct 2005 23:26 GMT

<bunka.munka@yahoo.com> wrote...

> I am experiencing some problems with MSSQL (ver 8.0).

Actually, I think the problem you encounter cannot be referred to as a
problem with MSSQL, other than that the use of the equals operator on a
nonscalar value AFAIK isn't a part of the ANSI-standard for SQL...

> While I can execute sub-select statement directly
> on a MSSQL server, I cannot do the same thing in JDBC.
[quoted text clipped - 4 lines]
> select idcolumn,a,b,c FROM mytable WHERE idcolumn =
> (SELECT max (idcolumn) FROM mytable)

Have you tried this instead: (?)

select idcolumn,a,b,c
FROM mytable
WHERE idcolumn IN
(SELECT max (idcolumn) FROM mytable)

// Bjorn A

Reply to this Message

Robert Klemme - 31 Oct 2005 12:46 GMT

> <bunka.munka@yahoo.com> wrote...
>
[quoted text clipped - 19 lines]
> WHERE idcolumn IN
> (SELECT max (idcolumn) FROM mytable)

I'd prefer a join here:

select idcolumn,a,b,c
FROM mytable
,(SELECT max(idcolumn) as mi FROM mytable) mt
WHERE idcolumn = mt.mi

Kind regards

robert

Reply to this Message

bunka.munka@email.si - 31 Oct 2005 13:13 GMT

> Have you tried this instead: (?)
>
> select idcolumn,a,b,c
> FROM mytable
> WHERE idcolumn IN
> (SELECT max (idcolumn) FROM mytable)

Yes, but doesn't work either. Altough *BOTH* works in SQL Query Analyzer
client,(or any other).
Some time ago, someone told me, that there are some options you can
specify in connect string (like MSSQL7=true or something).

Reply to this Message

Robert Klemme - 31 Oct 2005 13:37 GMT

>> Have you tried this instead: (?)
>>
[quoted text clipped - 7 lines]
> Some time ago, someone told me, that there are some options you can
> specify in connect string (like MSSQL7=true or something).

Can you post the exact error?

robert

Reply to this Message

bunka.munka@yahoo.com - 31 Oct 2005 20:56 GMT

> >> Have you tried this instead: (?)
> >>
[quoted text clipped - 11 lines]
>
> robert

select idcolumn,a,b,c FROM mytable WHERE idcolumn = (SELECT max
(idcolumn) FROM mytable)
is now working. actually it has worked before too.

The mistake I made was actually in the next line ( if rst.next() ....),
where I added wrong column seqence number when retrieving columns.
rst.get...
Usually I catch every statement in own try...catch, but this program is
not so important, so I put all together and therefore I didn't reallize
which statement actually throws exception.
Thanks anyway for support... :)

Reply to this Message

Java and Microsoft SQL

Free Magazines