insert....select

Thread view:

Larry Coon - 19 Mar 2006 17:47 GMT

I'm using JDK 1.5.0.3, Sybase JConnect for JDBC version 5.5,
connecting to Sybase Adaptive Server Enterprise version 12.5.
Here's a SSCCE:

import java.sql.*;

public class TestJDBC {
public static void main(String[] args) {
String URL = "jdbc:sybase:Tds:xxx.xxx.xxx.xxx:4100/xxxxxx";
String UID = "xxx";
String PW = "xxxxxx";

try {
DriverManager.registerDriver((Driver)
Class.forName("com.sybase.jdbc2.jdbc.SybDriver").
newInstance());

Connection c = DriverManager.getConnection(URL, UID, PW);
Statement s = c.createStatement();

String sql = "insert into mytable(mycol) select \"a value\"";

s.execute(sql);

s.close();
c.close();
} catch (Exception x) {
x.printStackTrace();
}
}
}

Running this gives the following stack trace:

com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name 'a value'.

at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2846)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2168)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
at
com.sybase.jdbc2.jdbc.SybStatement.executeLoop(SybStatement.java:1766)
at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStatement.java:1758)
at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStatement.java:815)
at TestJDBC.main(TestJDBC.java:19)

I assume this means the insert...select syntax isn't supported
somewhere, but I didn't see it documented. Can somebody verify
if this is true, and suggest a workaround?

(Note: Using "values ()" rather than "select" isn't an option. My
actual problem is a much more complicated query, which involves a
complex select from several tables.)

Larry Coon
University of California

Reply to this Message

Gerd Nachtsheim - 19 Mar 2006 18:01 GMT

Larry Coon wrote, On 19.03.2006 17:47:

> I'm using JDK 1.5.0.3, Sybase JConnect for JDBC version 5.5,
> connecting to Sybase Adaptive Server Enterprise version 12.5.
> Here's a SSCCE:
> [...]
> String sql = "insert into mytable(mycol) select \"a value\"";

this expands to

INSERT INTO mytable(mycol)
SELECT "a value"

double quotes denoting delimited identifier, i.e. you are trying to
bring the result of a query with the field "a value" into another
table's (mytable) column mycol.
However, the corresponding query is missing an important thing:- the
name of the table

if you rephrased it into

INSERT INTO mytable(mycol)
SELECT "a value"
FROM <the_table_whose_name you forgot>

that would make sense to me.

FWIW

Gerd

Reply to this Message

Larry Coon - 19 Mar 2006 18:51 GMT

> this expands to
>
[quoted text clipped - 14 lines]
>
> that would make sense to me.

Thanks for the reply. What I posted was a minimal
example to illustrate the problem, and my real query
does contain a FROM clause. That notwitstanding, in
Sybase ASE a FROM is not required when you're not
actually getting data from a table -- as in this case
where I'm selecting a literal. I.e., there's no
equivalent to Oracle's DUAL table needed in Sybase.

So the statement:

INSERT INTO mytable(mycol)
SELECT "a value"

works just fine. It's definitely the JDBC implementation.

BTW, I get the same problem in JDBC even when I use a
version of the insert that -does- contain the FROM clause,
so it's not a case of JDBC not liking it even though Sybase
allows it.

Larry Coon
University of California

Reply to this Message

Bill Karwin - 19 Mar 2006 22:52 GMT

> Sybase ASE a FROM is not required when you're not
> actually getting data from a table -- as in this case
[quoted text clipped - 3 lines]
>
> works just fine. It's definitely the JDBC implementation.

In standard SQL, double-quotes indicate delimited identifiers, as Gerd
pointed out.

In Sybase, double-quotes are used for delimited identifiers only if you use
"set quoted_identifier on".
See
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookText
View/1347,
in the section on Delimited Identifiers.

Perhaps the JDBC implementation is setting that option by default in its
connection to Sybase? That would explain why the same query works in
another interface (where, presumably the quoted_identifier option is not
turned on).

Anyway, while that option is turned on, you can still use single-quotes to
indicate string literals. So try using single-quotes instead of
double-quotes:

INSERT INTO mytable(mycol)
SELECT 'a value'

Regards,
Bill K.

Reply to this Message

Larry Coon - 20 Mar 2006 03:59 GMT

> In standard SQL, double-quotes indicate delimited identifiers, as Gerd
> pointed out.
>
> In Sybase, double-quotes are used for delimited identifiers only if you use
> "set quoted_identifier on".

Dangit -- I use that so rarely that I forgot about it entirely
(in additon of following my first inclination of blaming JDBC...).
Actually setting it OFF was what worked. Thanks!

Larry Coon
University of California

Reply to this Message

jcsnippets.atspace.com - 19 Mar 2006 23:09 GMT

> I'm using JDK 1.5.0.3, Sybase JConnect for JDBC version 5.5,
> connecting to Sybase Adaptive Server Enterprise version 12.5.
> Here's a SSCCE:

> (Note: Using "values ()" rather than "select" isn't an option. My
> actual problem is a much more complicated query, which involves a
> complex select from several tables.)

Perhaps you can post this query - my initial reaction after reading the
reply Gerd already gave you, was "why isn't he using values()", until I read
your original question again...

If possible, it may be easier to adapt your insert statement to your code
than the other way round.

Best regards,

JayCee
--
http://jcsnippets.atspace.com/
a collection of source code, tips and tricks

Reply to this Message

Larry Coon - 20 Mar 2006 15:54 GMT

> Perhaps you can post this query - my initial reaction after reading the
> reply Gerd already gave you, was "why isn't he using values()", until I read
> your original question again...

There actually isn't a query that can be called "the" query. I'm
doing an ad-hoc search feature, and the tables, WHERE conditions,
etc. depend on which search criteria were provided, and whether
this is a first search or a "search within results." I have a
method that generates the search query based on a set of criteira.
A "typical" search might involve five or six tables (ranging from
about 100,000 to about 7.7M rows each) and ten-or-so WHERE
conditions. When my first tests failed, I was able to distill it
down to the SCCEE I posted.

> If possible, it may be easier to adapt your insert statement to your code
> than the other way round.

Per my other reply, the solution involved setting the
quoted_identifier flag OFF.

Thanks for the reply.

Larry Coon
University of California

Reply to this Message

insert....select

Free Magazines