Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple statements on single connection?

 
Vijay Kashyap
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am just wondering if multiple statements can be opened and executed simultaneouly on a single connection. If yes, will they be sequential or not? Also will there be any issue involved even if the queries being fired doesn't require any transaction control( they are simple select query).
Regards,
Vijay
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Statements are executed on the server in the order they were executed on the client. You can batch up statements to ship them as a block. That cuts down on network traffic, but they still have a defined order. If you literally want simultaneous independent statement execution then you want multiple connection objects, typically managed via a connection pool.
Note: Oracle does have the notion of a "connection concentrator" that can be set up in certain database configurations. I haven't used it, but from what I understand it is intended to funnel activity from multiple logical connections into a single physical connection. I'd still have to expect you have sequential behaviour at some level.
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Reid M. Pinchback:
If you literally want simultaneous independent statement execution then you want multiple connection objects, typically managed via a connection pool.

Not true, at least according to the specification:
JDBC 3.0 Specification, Final Release
13.1.1 Creating Statements
Statement objects are created by Connection objects..... Each Connection object can create multiple Statement objects that may be used
concurrently by the program.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yup, true, but I think it is important to compare the wording of the spec to the wording of the original question. The spec says you can have multiple objects that can be used concurrently. It doesn't talk about how concurrency is implemented. "Concurrent" is not the same as "simultaneous". The spec doesn't guarantee that execute() invocations are interleaved in some esoteric fashion that would make them all safe if one of them contained a run-away query.
Perhaps in later Oracle releases (9i) they are protected; it would be interesting to try and test. Lord knows there is a lot of whacky thread synchronization going on in Oracle JDBC drivers; I could believe just about anything of them. :-)
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Reid said, if you use one Connection then you'll have just one network connection to the database and all Statements will share that one channel. But that distinction is in most cases academic; you can usually treat the statements as executing simultaneously and independently.
Beware though that not all drivers support multiple Statements per Connection, most notably the JDBC-ODBC bridge (or has that limitation finally been removed now?)
- Peter
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Been awhile since I looked, but I believe the limitation depended on the version of ODBC you had installed. 3.0 was something of a transition point for ODBC functionality. Prior to that you definitely could only have a single thread of execution against the database.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic