Re: opinion solicited

On Sat, 10 Jul 2004 08:38:42 +0100, Mark Rittman <mark_at_rittman.net> wrote:

>Ed,
>

<snip>
> . . . and what I
>guess we're actually discussing is more the emphasis than anything else.

True.

And thanks for participating. This whole discussion has clarified and sharpend my understanding, which was what I was after in the first place.

(Just as a point of explanation/clarification of where I'm coming from: My first exposure to Oracle was also my first DBA job. Prior to that I was applications, and my primary languages were Cobol and Rexx with primary data storage of VSAM, IMS, and sequential files. As a result, I don't have much experience with interfacing languages to Oracle, especially the more popular languages today -- Java and VB. I'm trying to get a better understanding of this, so that I can be of more help to our developers.)

>As you suggest, I'll reword the relevant part to make it a bit clearer.
>
>Thanks again for the feedback
>
>kind regards
>
>Mark
>
>
>
>"
>
>
>
>
>
>
>Ed Stevens wrote:
>> Mark,
>>
>> I think if I were editing the article, I'd eliminate statements that
>> say or imply that the programming environment (whatever it is) "takes
>> care of it for you." It would be more accurate to say that the
>> programming environment "supports the use . . . .". (And I'm not
>> aware of any that don't). The Big "IF" (tm) is "IF you write your
>> code properly" and don't assume they are writing it properly.
>> Imagine a freshly-minted programmer who thinks the world revolves
>> around Java and databases are an evil imposed on him by
>> 'unenlightened' dinasaurs - "I can do it all in my java code, thank
>> you very much . . . ".
>>
>>
>> On Fri, 09 Jul 2004 19:32:54 +0100, Mark Rittman <mark_at_rittman.net>
>> wrote:
>>
>>
>>>Hi Everyone,
>>>
>>>Original poster of the blog article here.
>>>
>>>First of all, genuine thanks for the feedback on the article. I'll be
>>>summarising the points made and adding them to the article, if they make
>>> the article easier to understand.
>>>
>>>Without requoting the article again, the points I was trying to get
>>>across were-
>>>
>>>1. Bind variables are important if you want your application to scale
>>>beyond a couple of concurrent users.
>>>
>>>2. If you use PL/SQL, it makes all of your program variables bind
>>>variables as a matter of course, and therefore in the vast majority of
>>>cases (i.e. when you're using anything except dynamic SQL) you are using
>>>bind variables by default. I was trying to get this point across, as I
>>>know that I (for example) often worried about using bind variables, when
>>>in fact PL/SQL takes care of this for you by default (a benefit of using
>>>PL/SQL over other languages)
>>>
>>>3. The only time with PL/SQL you really have to explicitely worry about
>>>bind variables is if you use dynamic SQL
>>>
>>>4. With Java, VB and so on, if you code using the database access APIs,
>>>there is explicit support for the use of bind variables (as bind
>>>variables aren't just an Oracle thing, they also are found in SQL Server
>>> and so on). You just have to make sure you use these APIs correctly,
>>>and not just concatenate SELECT statements together and fire them at the
>>>database. As I'm not a Java or VB developer, if in fact this could do
>>>with clarifying (i.e. you have to set certain parameters) then I'd be
>>>more than happy to amend this statement if someone fills me in on the
>>>details.
>>>
>>>I think, reading the article back, this is what it says, although I
>>>appreciate that other readers might not have found it so clear.
>>>Apologies if this is the case, and if anyone has a suggestion as to how
>>>to word it better, please let me know and I'll update it with a
>>>clarification.
>>>
>>>regards
>>>
>>>Mark Rittman
>>>
>>>
>>>Anurag Varma wrote:
>>>
>>>>"Ed Stevens" <nospam_at_noway.nohow> wrote in message
>>>>news:2dete0ploue440mleko24bd1cc6skaqf38_at_4ax.com...
>>>>
>>>>
>>>>>On Fri, 09 Jul 2004 14:22:26 GMT, "Anurag Varma" <avdbi_at_hotmail.com>
>>>>>wrote:
>>>>>
>>>>>
>>>>>
>>>>>>"Ed Stevens" <nospam_at_noway.nohow> wrote in message
>>>>>>news:me3te0h1o11p1m7pmqarlv0kk6a45j841v_at_4ax.com...
>>>>>>
>>>>>>
>>>>>>>Yesterday I made a presentation to a group of our developers. The
>>>>>>>subject was the use of bind variables to reduce parsing and latch
>>>>>>>waits. I presented several demos based on those presented in Tom
>>>>>>>Kyte's book, and provided coding examples for several different
>>>>>>>programmnig environments.
>>>>>>>
>>>>>>>Later, one of the participants sent me a link to a website whose
>>>>>>>author acknowledged the problems of not using bind variables but
>>>>>>>appeard to be making a case that, in practice, the various coding
>>>>>>>environments (PL/SQL, java, etc.) took care of it for you. He (the
>>>>>>>wesite author) went on to give coding examples where - oh, gee, he was
>>>>>>>coding to use bind variables -- and saying that as long as you coded
>>>>>>>this way, the programming environment would just take care of it for
>>>>>>>you. I thought it was pretty misleading and gave the distinct
>>>>>>>impression to a casual reader that the programmer really didn't need
>>>>>>>to concern himself with the subject.
>>>>>>>
>>>>>>>Link is at http://www.rittman.net/archives/000832.html
>>>>>>>
>>>>>>>I'd be curious to hear other people's take.
>>>>>>
>>>>>>hmm .. I don't really agree that the statements in that article/blog are misleading.
>>>>>>He did give an example where even in PL/SQL you can write SQL which will
>>>>>>not utilize bind variables.
>>>>>>For Java/VB etc he says <quote>All of these APIs have built-in support for bind variables,
>>>>>>and it's just a case of using this support rather than just concatenating a string yourself and
>>>>>>submitting it to the database</quote>
>>>>>>
>>>>>>Which pretty much states that if you do not use the built in support for bind variables then
>>>>>>you are *not* using bind variables.
>>>>>>
>>>>>>
>>>>>>
>>>>>>Anurag
>>>>>>
>>>>>
>>>>>True enough, but also makes statements like this:
>>>>>
>>>>>"Taking PL/SQL first of all, the good news is that PL/SQL itself takes
>>>>>care of most of the issues to do with bind variables, to the point
>>>>>where most code that you write already uses bind variables without you
>>>>>knowing. "
>>>>>
>>>>>Well. I guess that depends on what "most code that you write "
>>>>>looks like. A lot of programmers write a lot of code by simply
>>>>>replicating bits of (potentially bad) example code they found
>>>>>somewhere. Heck, a lot of DBA's create a lot of databases by simply
>>>>>replicating (potentially bad) examples they found somewhere.
>>>>>
>>>>>I've actually heard a highly-paid consultant/instructor state that (1)
>>>>>Java doesn't support bind variables and (2) therefore, one should
>>>>>always set cursor_shareing=force. Well, to paraphrase a line from The
>>>>>Music Man, "I don't know much about Java, but I do know it supports
>>>>>bind variables." That may seem tangential to the discussion at hand,
>>>>>but I can easily see where some programmers would develop as their
>>>>>standard (and thinking it was a universal standard), practices that do
>>>>>not support bind variables.
>>>>>
>>>>>Please don't think I'm trying to slam you or the blog author. I
>>>>>thought what he wrote was factually correct, but parts were a bit
>>>>>mis-leading, even if unintentional. Thought some commentary might be
>>>>>useful to lurkers, and certainly serves to sharpen my own thinking.
>>>>
>>>>
>>>>
>>>>I guess its just two different opinions. Little knowledge about a subject can be dangerous.
>>>>The article does not go into detail, so just reading that alone might confuse the reader
>>>>rather than enlightening him. However, the links that that blog provides at the end should
>>>>help in explaning the authors point of view in more detail.
>>>>
>>>>Worse still I recently encountered a vendor which coded its sql statements like this:
>>>>
>>>>select t1.* from table1 t1 where t1.x = :var;
>>>>select t2.* from table1 t2 where t2.x = :var;
>>>>select t3.* from table1 t3 where t3.x = :var;
>>>>...
>>>>
>>>>Its using bind variables ... yoo hooo! ... But it went ahead and assigned different table aliases
>>>>for each sql statement (building sql on the fly and probably using a memory sequence to
>>>>set the table alias).
>>>>.. thus essentially defeating the whole purpose of bind variables ...
>>>>
>>>>Thus, if one does not understand why bind variables should be used and what the main goal of using
>>>>bind variables is ...
>>>>then there are numerous ways to misinterpret the article.
>>>>
>>>>Anurag
>>>>
>>>>
>>>>
>>
>>
Received on Mon Jul 12 2004 - 07:58:18 CDT