Prepared Statements with Changing Database
Hi All,
Long time since i've posted but always good to get input from real pro's...even though i'm now doing some work for a company myself :)
Situtation: I have a set of prepared statements that will get executed a varying amount of times dependant on the number of records prepared to be mailed out. I have a hashmap of company ids which i loop through.
I suppose the main thing i'm trying to find out is, is it ok to change a database in a connection but still use the same prepared statements that have been precompiled? ie do i need to re-initialise the prepared statement for each time i change the database or will the prepared statement just use the database that the connection has been set too....
Pretty sure i know the answer but just keen to hear some responses.
Thanks
Wes
[833 byte] By [
wes_614a] at [2007-11-27 11:01:58]
# 3
Hmm i'll pseudo it for you and see if you understand more clearly.
setupPreparedStatements
create hashMap (indexed by company id which i use to get the db name)
loop through the hashmap
set the class localdatabaseconnection to this comapnies database
execute stuff on the statements
end loop
close statements
might i add that at the moment i don't do this....just thought if i can avoid setting up the preapred statements 5 or 10 times dependent on the amount of clients in the queue then i could save some memory/speed
Wes
Message was edited by:
wes_614
# 5
> I suppose the main thing i'm trying to find out is,
> is it ok to change a database in a connection but
> still use the same prepared statements that have been
> precompiled?
Herein lies the problem. The PreparedStatement doesn't compile anything. It's the database that does this. So if you change databases, how could you possibly expect this to work. Somebody earlier said "it depends". I can't see how that would work (maybe it wouldn't break but a recompile would still happen) and I would strongly recommend creating new PreparedStatements if you change database.
# 6
> > I suppose the main thing i'm trying to find out
> is,
> > is it ok to change a database in a connection but
> > still use the same prepared statements that have
> been
> > precompiled?
>
> Herein lies the problem. The PreparedStatement
> doesn't compile anything.
This is not neccessarily correct.
> It's the database that does
> this. So if you change databases, how could you
> possibly expect this to work. Somebody earlier said
> "it depends".
Because it does.
Okay let me try again to cover the options at this point of what the OP wants to do and what is and what is not possible.
Option 1 - Different Connections
The OP wants to use PreparedStatements created in one Connection in another Connection.
This is the option that DrClap thinks the OP is trying to use. As he mentioned and I think everyone agrees with, this will not work. PreparedStatements are tied to the Connection that created them. So this option is a non-starter because it will never work.
Option 2 - Same Connection
The OP wants to use PreparedStatements that he created with one Connection but then issued some sort of change database command over the Connection and switched what the active database/catalog is but the Connection remainds the same.
This is what I think the OP is trying to do. Under this scenario this may or may not work (like I said before depends). If the database and driver implementation of PreparedStatement means that in fact the query is parsed by the driver it will NOT work. If the implementation is done totally on the driver side then it MIGHT work. As an example of a driver that does this parsing itself is the MySQL driver, so it can and does happen.
However even if the OPs situation is such that it will work I strongly recommend not doing this because:
1)it is VERY implementation specific which makes it a bad idea in principle and practice, it could break when even just the driver is updated.
2) the performance benefits the OP is seeking with this are of no real concern or consequence - basically the whole discussion is moot
3) even if the scenario falls under the one I described where it would be possible the performance issue lies mainly in the fact that the query is in fact not pre-parsed or pre-compiled by the database itself and if the OP wanted to improve performance he should be looking into that anyway
Summary
Depending on what the OP actually wants to do, coupled with the database and driver implementation the OP is using what the OP wants to do may or may not be possible. But either way it shouldn't be done.
I though feel it is better not to say something is not possible when it might under some conditions be possible. Because again the possibility of it is not the real concern here, the real concern is that it's a bad idea.
