Re: Dynamic Sql Statement in SP
Hi Ming!
Ming Yeung wrote:
> I believe the sessions of the MySql user are not
> being released, the connections are all being
> returned to the Pool. Therefore, user variables
> are accessed by different web users(All web users
> use the same MySql account 'ming').
Hey, that's interesting. I haven't thought of that. I guess that could be a problem after all when you use connection pooling.
If that is the case, how does this combine with transactions? Or would the connection pooling bit be smart enough to take care of that?
I'm a bit of an old-skool raw JDBC kinda user, so I know connection pooling is part of JDBC 3 (or 2 even?) but I've never used it.
>
> I haven't tested it without connection pooling.
By all means do! And please post back the result.
Please, also test what happens to a pending transaction.
> But as I have seen that it is really a matter to
> web applications when using connection pooling.
> Web user 'A' might get the result of web user 'B',
> and user 'B' might get errors as the result is
> being free, etc.
>
> Please prove that I'm wrong, I really want to
> continue using SP with MySql.
I guess, right now, your'e in a better position to prove it either way. I havent got the environment. sorry!
Anyway, it should not be a problem after all. You can easily make your procedures resilient. If you make sure you initialize the user variables each time before using them, there really should not be a problem here. Or is there?
I mean, even with connection pooling on, the separate pooled connections cannot see *each others* user variables.