Home.... | log in.... | Pub my question! | Sign-up....

Java-index >> Database Connectivity >> Java Database Connectivity (JDBC)

Embedded SELECT statement

How can I execute an embedded SELECT statement from the Java?

[68 byte] By [pityq] at [2007-9-27 21:34:17]

«« Creating the JVM from c with debug option fails
»» when is destroy() called

# 1

What kind of statement are you trying to run (SELECT, INSERT, UPDATE etc)? and what is the desired reaction to the statement (a ResultSet, a true/false indicator etc.)?

THancock at 2007-7-7 3:31:01 >

top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...

# 2

I want to run a SELECT statement(s) to obtain a ResulSet.I want to select some records from a database with some conditions into an other database ex:SELECT name,countryid FROM supplier WHERE id = (SELECT id FROM materials WHERE usage = 'raw')

pityq at 2007-7-7 3:31:01 >

# 3

Write a procedure which will do this. Consider you are connecting to Database A. Write a procedure in Database A. In this procedure select data which you want by applying the conditions from Database B. The only requirement is that there should exist a database link between A and B.

SanjeevD at 2007-7-7 3:31:01 >

# 4

You execute that just the way you would execute any other SQL statement. (I am assuming that you already use JDBC and don't need to be told to read the tutorial.) However, if your database doesn't support subselects (as the one I use doesn't) then you will have to redesign that. Often a subselect can be rewritten as a (less beautiful) join.

DrClap at 2007-7-7 3:31:01 >

# 5

> SELECT name,countryid FROM supplier WHERE id = (SELECT

> id FROM materials WHERE usage = 'raw')

If your database supports this, JDBC will support it, too. If it doesn't, JDBC won't do anything magic for you - you'll have to rewrite this as a JOIN, or run two separate SELECTS, and build the outer select dynamically from the result of the inner select..

shankar.unni at 2007-7-7 3:31:01 >

# 6

My source looks like that:

try{

conPnProc = DriverManager.getConnection("jdbc:odbc:pnprocur","","");

conPnSupp = DriverManager.getConnection("jdbc:odbc:pnsupply","","");

conAddress = DriverManager.getConnection("jdbc:odbc:address","","");

Statement pnProcStmt = conPnProc.createStatement();

Statement pnSuppStmt = conPnSupp.createStatement();

Statement addressStmt = conAddress.createStatement();

ResultSet rs = pnProcStmt.executeQuery("SELECT * FROM pnprocur, address");

}catch(SQLException e){}

When I use an other table name then that one the connection was created for he gives me an error that: can not open database.

So I can't do any joins or embedded SELECT statement.

pityq at 2007-7-7 3:31:01 >

# 7

In your SQL statement, "SELECT * FROM pnprocur, address" the syntax for such a statement is "SELECT data FROM table" - but your tables - pnprocur and address in this statement are the namesake of the database which you connect to in your conenction statements :

conPnProc = DriverManager.getConnection("jdbc:odbc:pnprocur","","");

conAddress = DriverManager.getConnection("jdbc:odbc:address","","");

Is there in fact a db named pnprocur, aswell as a table named pnprocur (and adress etc) ?

Also, it is better to split up connection to db and interrogation, that way you know exactly where your code has failed - from your error message, it appears that there is a problem with your connection string... which should look like :

DriverManager.getConnection("jdbc:odbc:DATABASE_NAME","","");

sedj at 2007-7-7 3:31:01 >

# 8

DrClap said> Often a subselect can be> rewritten as a (less beautiful) join.What do you have against joins? Were you bitten by one as small child perhaps?

jschell at 2007-7-7 3:31:01 >

# 9

> DrClap said

> > Often a subselect can be

> > rewritten as a (less beautiful) join.

> What do you have against joins? Were you bitten by

> one as small child perhaps?

No, the subselect version just looks more elegant to me, and I usually find it easier to understand. (It wasn't until I was middle-aged that I was bitten by joins.)

DrClap at 2007-7-7 3:31:01 >

# 10

pnprocur, address are Xbase db. I can create just some dbf files and not exists the concept of tables. I think that's the problem.

pityq at 2007-7-7 3:31:01 >

# 11

> > DrClap said

> >

> > > Often a subselect can be

> > > rewritten as a (less beautiful) join.

> >

> > What do you have against joins? Were you bitten by

> > one as small child perhaps?

> No, the subselect version just looks more elegant to

> me, and I usually find it easier to understand. (It

> wasn't until I was middle-aged that I was bitten by

> joins.)

I find subselects more elegant too, but it could be easier for databases to optimize join statements. As far as I remember, Oracle can optimize subselects by converting them internally into a join.

aschorle at 2007-7-7 3:31:01 >

Java Database Connectivity (JDBC)

Java programming resources: FAQs, tutorials, compiler and browser download sites, documentation, books lists, IDEs, etc.

Embedded SELECT statement

Database Connectivity New Topic

Database Connectivity Hot Topic

Database Connectivity

All Classified