• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Multiple statements on single connection?

 
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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. :-)
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic