This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JDBC and the fly likes temporary tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "temporary tables" Watch "temporary tables" New topic
Author

temporary tables

Juan Rolando Prieur-Reza
Ranch Hand

Joined: Jun 20, 2003
Posts: 235
Hello,
Does JDBC support
CREATE TABLE #MyNewTemporaryTable ... ?
If JDBC does support local or global temporary tables (indicated by the prefix #, or ## respectively), then I would have a follow-up question:
Can a temporary table, created at run-time thru JDBC, be used by Entity EJBs, provided that they have the matching deployment descriptors? Creating tables is beyond EJB-QL, to my knowledge, but run-time-only tables would be useful for manupulations, especially if they are short-lived and even more-so if they never get written to disk.
(Ram Golam may recognize this tactic from a discussion in another forum)
Thanks in advance!
[ October 07, 2003: Message edited by: john prieur ]

Juan Rolando Prieur-Reza, M.S., LSSBB, SCEA, SCBCD, SCWCD, SCJP/1.6, IBM OOAD, SCSA
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8898

I think JDBC supports creating temporary table but it will be vendor specific.


Groovy
Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
Java passes the SQL on to the underlying database. If the database supports temporary table creation, then you can execute it through JDBC.


Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
Juan Rolando Prieur-Reza
Ranch Hand

Joined: Jun 20, 2003
Posts: 235
Java passes the SQL on to the underlying database[/QB]

Thanks. I didn't think
executeUpdate( "CREATE TABLE #Temp ...")
would work, 'cus its DDL. It does and I'm surprised.
Now to find out if EJBs can become associated with Temp....
If this works we might be able to do things like this in EJB-QL...
SELECT f
FROM Things f
where f.id IN (SELECT g.id FROM Temp WHERE ?1)
(assuming that you first create Temp via JDBC and populate it with
the Things of interest, the ids are compatible, and that you want to use a large "collection" of target items to select from f. )
Finally, delete the Temp.
Let us know if you think there is a better way of going about all this.
[ October 07, 2003: Message edited by: john prieur ]
Maydene Fisher
Author
Greenhorn

Joined: Jul 22, 2003
Posts: 14
Originally posted by Tom Blough:
Java passes the SQL on to the underlying database. If the database supports temporary table creation, then you can execute it through JDBC.

I just want to emphasize the point Tom makes. The JDBC API simply passes your SQL statement to the DBMS. Whether or not the statement works depends on how the DBMS is implemented. If you pass a correct SQL statement for your DBMS, it will work. It's not a function of the JDBC API; its a function of the DBMS and driver.
Cheers,
Maydene


Maydene Fisher<br />author, "JDBC(tm) API Tutorial and Reference, Third Edition"
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: temporary tables
 
Similar Threads
Clarification
Temporary tables (Oracle) and web based apps
Temporary Tables in Oracle
PL/SQL table values to JDBC
Creating Temp table and querying it