I'm reading SQL out of textfiles and then executing it.
One of the statements in the file is like this:
SELECT * FROM "SCOTT"."EMP";
In Java this translates into SELECT * FROM \"SCOTT\".\"EMP\";.
But this doesn't execute (illegal character).
How can I resolve this?
Thank you.
First, since you're talking about the SCOTT schema, I assume you're using Oracle. If not, please mention it.
Second, the quotes around schema name and table name are only needed if either of them are reserved words, which neither SCOTT nor EMP is. The reserved words for any particular release of Oracle can be found in the V$RESERVED_WORDS table (access to this and other "dictionary" views is controlled via privilege in later versions of Oracle; your DBA may need to grant access.) The JDBC driver is supposed to be able to also provide this list, but the Oracle JDBC driver is broken in tis regard (at least the latest 10gR2 driver is, say it just yesterday).
Third, the SQL string you need to send to the database is:
SELECT * FROM "SCOTT"."EMP"
Note, no semi-colon at the end (it might be harmless in Oracle, I forget), and no embedded backslashes. What may be confusing you is that to code this as a String literal in Java, you have to code:
String SQL = "SELECT * FROM \"SCOTT\".\"EMP\"";
The backslashes tell the compiler to ignore the following character as a possible end-of-string mark, and the backslashes are not part of the internal storage of the string after compilation. If you're reading the SQL from file, you're not compiling it and you don't need to insert backslahes.
