I am trying to query Sybase database using DBD::Sybase. Can someone please clarify what is the difference between using syb_nsql vs prepare(...) - execute(..) calls?
A:
syb_nsql (as a direct port of Sybase::DBlib's nsql which is a fairly high level wrapper) supports all of the nsql's advanced functionality which DBD::Sybase's prepare-execute does not (IIRC) have natively and for which you have to write the wrapper code yourself (basically, you pretty much have to re-implement your own version of nsql anyway):
MaxRows functionality to limit the # of returned rows to conserve memory usage
optional deadlock retry logic
error checking (using its companion error and message handlers - I don't recall DBD::Sybase having message handlers)
and several options for the format of the return values.
In addition, the data can either be returned to the caller in bulk (thus no need to write your own loop for fetch_arrayref etc...), or processes line by line via a callback subroutine passed as an argument (this functionality is similar to the r_sql() method).
Considering the fact that nsql is actually implemented using prepare/execute/fetchrow_arrayref; it will pretty much have no functional difference from any normal prepare-execute functionality you use directly. It seems to even support placeholders as per the code comments though it doesn't appear to be documented.
So the difference is that nsql allows you to avoid rolling your own wrapper code to support any of the advanced features like deadlock retry or MaxRows that are listed above.
DVK
2010-10-23 09:32:42
That is not correct, DBD:: provides every single one of the listed functions, without a "wrapper". In fact the DBI:: does. (I have not programmed sybperl for a few years, nsql came out since then, so I cannot comment on the DIFFERENCE.)
PerformanceDBA
2010-10-25 15:36:33
@PerformanceDBA - could you please provide the specific DBD API which you can supply with an SQL statement and which will return an arrayref of (optionally up to N) selected rows? If there is such an API, I would agree with the above comment.
DVK
2010-10-25 16:09:51
@PerformanceDBA - also, how is deadlock retry provided? As per POD, "Deadlock detection will be added to the $dbh->do() method in a future version of DBD::Sybase."
DVK
2010-10-25 16:10:22
@DVK/All items except Deadlock. Let me get this straight: person A posts incorrect answer; person B points that out; person A requests person B to confirm ?!?!? As per standing practice in such circumstances, since documentation is provided by the suppliers, person A is obliged to provide confirmation. However, this once, I will provide confirmation of my comments. I trust you are aware that the Sybase DBD is built on top of the perl DBI; that one of the great strengths of perl is its array handling; that result sets returned in array would be very powerful. Here is a link to a public DBI
PerformanceDBA
2010-10-25 22:14:52
... proving that that entire set of features existed in the DBI, before DBD existed. Of course, DBD provided all that from the outset, nothing to do with nsql (thousands, including me from 1993 to 2000, used it, without nsql). http://docstore.mik.ua/orelly/linux/dbi/ch05_06.htm Likewise error handling, etc. You can look up the doco for the rest yourself. And post info to support your post, which is now in question.
PerformanceDBA
2010-10-25 22:15:40
@DVK/Deadlock. We already know from the other thread that you have no knowledge or experience re Deadlocks. Therefore you will not know the constructs available from Sybase, and in perl (specific releases/functions), to handle/detect/mitigate Deadlocks, and the limitations therein. You are aware that your other program halted (killed) upon Deadlock; that it is not possible to trap the 1205, yes ? so we are talking about something different here. "deadlock retry" is a config parm; it has nothing to do "deadlock detection [using the new functionality available] in a future release".
PerformanceDBA
2010-10-25 22:28:14
@PerformanceDBA - you posted a whole bunch of words. 4 loooong comments. NONE of them contained an example of an API - in case I wasn't very clear, I meant ONE FUNCTION - which as input takes an SQL statement and as return value produces an arrayref of result sets. Which - having 1 convinient function call - is the reason for having a wrapper called `nsql`, as I have explained in my answer.
DVK
2010-10-26 02:32:25
@PerformanceDBA - Yes, every single feature needed to write that wrapper is avialable outside `nsql`. The whole point of my answer which you clearly didn't bother reading was: **Considering the fact that nsql is actually implemented using prepare/execute/fetchrow_arrayref; it will pretty much have no functional difference from any normal prepare-execute functionality you use directly**. Emphasis on **no functional difference**. ... **the difference is that nsql allows you to avoid rolling your own wrapper code to support any of the advanced features like deadlock retry that are listed above.**
DVK
2010-10-26 02:33:11
@PerformanceDBA - I don't know whether "deadlock detection" in POD was meant as "detecting deadlock error and retrying" or real deadlock detection. Even if you're right and they meant the latter, my main point stands - outside of `nsql`, if you want to retry your query in case it was a deadlock victim, you have to roll your own retry code. Not having to roll your own wrapper code is **precisely** the benefit of `nsql`
DVK
2010-10-26 02:37:07
@PerformanceDBA - so, please get your thinking straight. (1) Either you agreed with 100% of what my original answer said (e.g. `nsql` provides no conceptually new functionality, BUT it provides a neat little wrapper around existing functionality (which is pretty much the standard "SQL in, data out, retry on deadlock" wrapper any data access code using DBD::Sybase would have implemented on its own if `nsql` didn't exist).
DVK
2010-10-26 02:44:38
Since you obviously seem to agree with the first part about no new functionality (to quote you, *provides every single one of the listed functions*), the only other part you could have objected to with your "That is not correct" was that outside of `nsql`, there are other convenient wrappers around the functionality. Yet, when I ask you to provide an example of such a wrapper, you can't (as far as I know, there isn't one, which is precisely why `nsql` was added)
DVK
2010-10-26 02:46:26
So, please specify EXACTLY which parts of my answer - especially the bolded summary - you disagree with when you say "not correct".
DVK
2010-10-26 02:47:31
P.S. The link you provided was a discussion of `selectrow_*` (which allows avoidance of prepare/execute but **only** returns 1 row) and `fetchall_*` (which returns the full data structure avoiding the need for a loop BUT doesn't avoid prepare/execute). Neither provide MaxRows or deadlock retry, to boot. So they can't be used as a legit example of something that makes `nsql` not needed.
DVK
2010-10-26 02:57:54
By the way, not to sound snarky, but the community in Perl tag here frowns upon links to pirated O'Reilly books which this `.ua` web site unfortunately happens to be (I had that pointed out to me a while back).
DVK
2010-10-26 02:59:06
You've done something really dishonest. You have edited the body of your post, so that it now does not say what it said initially, you have changed the meaning, 3 times. My comments were made re the original post. You removed the meat of your incorrect posting and left a bunch of straw; then you challenge me. Nothing can be resolved with such dishonest people, no honest resolution can be had, so forgive me but I will not respond further (feel free to posts more er knowledge). Please please yourself: fight with your dishonest fight alone. Disgusting. Shame on you.
PerformanceDBA
2010-10-26 03:23:52
*sigh*. It's called editing for clarity. Since you obviously didn't understand what I originally wrote, I tried to clarify it. Feel free to edit it back if it offends you so. And you STILL did not answer my very simple question - since you agree with my assertion #1 (no functional differences), AND you didn't provide an example of a one-stop-shopping sql-in-datastructure-out-with-extra-candy-options wrapper replacing `nsql`, just WHICH part of my (original or clarified) answer was "not correct".
DVK
2010-10-26 04:41:24
I do not interact with dishonest people, nor do I answer their contrived, set up "simple" questions. you will have to fight your dishonest straw man fights with yourself.
PerformanceDBA
2010-10-30 05:37:09