| SQL to access to database with one query [message #11166] |
Wed, 10 March 2004 02:59  |
Stuart
Messages: 7
Registered: September 2000
|
Junior Member |
|
|
Hi,
I'm using SQL with JSP and I want to construct a query that can access to different database:
See my code:-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con34 = DriverManager.getConnection("jdbc:odbc:Database1","**","**");
Statement stmt34 = con34.createStatement();
//This is the statement...
sql = "select * from TABLE1 WHERE vendor IN (select accessKey FROM TABLE2 WHERE USERID = '" + varUserID + "')";
This works fine but what if "TABLE2" is in another database?
please help!!
|
|
|
|
|
|
| Re: SQL to access to database with one query [message #11214 is a reply to message #11166] |
Sun, 14 March 2004 08:50  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> sql = "select * from TABLE1 WHERE vendor IN (select accessKey FROM TABLE2 WHERE USERID = '" + varUserID + "')";
Note that Oracle caches SQL queries to save the overhead of reparsing them. I don't know Java but is there not a method to specify bind variables? Otherwise you not only force the database to parse every new variation of the query, but you can also fill the cache with junk, causing everyone else's queries to be hard-parsed too. How much of a problem this is depends on how many distinct USERIDs are in use and how often the code is called.
One approach might be to encapsulate common functions such as this in a PL/SQL package. Then you could just call e.g. VENDOR.ITEMS(varUserID) and receive back a ref cursor of results.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
