2010年6月16日星期三

Re: [fw-db] Need help REGARDING ZEND_DB MAX(ID)

On 06/16/2010 04:34 PM, Y D wrote:
> Hi All,
>
> Thank you very much for being concerned and replying to solve my
> problem, I truly appreciate it. Using Zend db Adapter rang a bell and i
> searched for it. Finally after going through few trials n errors; my
> requirement is complete and i am able to proceed further now. I can now
> send query to catch the data...for my frends who might find problem
> working with zend im attaching my code with this email..hope it helps
> some1 :)
>
> thanx once again ya all :)
>
> Tc of urselves :)
> Regards,
> YD
>
> On Tue, Jun 15, 2010 at 2:47 AM, Bill Karwin <bill@karwin.com
> <mailto:bill@karwin.com>> wrote:
>
> On Jun 14, 2010, at 9:09 AM, Y D wrote:
>
>> the thing im facing problem is ZEND_DB....i find making queries in
>> ZEND complicated...is there a way to simply make a query as a
>> STRING and then execute it?
>
> Yes, certainly. There's no requirement to use Zend_Db_Table or
> Zend_Db_Select. You can submit plain SQL queries simply using the
> Zend_Db_Adapter interface. This interface is based on PDO, with a
> few new convenience methods for quickly getting results. For example:
>
> $db = Zend_Db::factory(...options...);
> $query = "SELECT NVL(MAX(INBOX_NUMBER),0)+1 AS MAX_INBOX_NUMBER
> FROM ARC_DOCUMENT";
> $result = $db->fetchRow($query);
> $max = $result->MAX_INBOX_NUMBER;
>
> Just be sure to define a column alias in your SELECT query.
>
> These methods are documented here:
> http://framework.zend.com/manual/en/zend.db.adapter.html
>
>> /*Message: Specified column "INBOX_NUMBER" is not in the row*/
>
> You didn't give an alias for your MAX() expression. In SQL, the
> column name of an expression does not implicitly take the name of
> one of the columns involved in the expression. Besides, what if the
> expression were to reference more than one column? This is not
> related to Zend_Db, this is standard SQL behavior and you would run
> into this problem in any framework or language.
>
> You can either declare an alias explicitly, or else refer to the
> column of the query result by its ordinal position instead of its
> name. But Zend_Db_Table_Row only indexes fields by their name.
>
>> but the table ARC_DOCUMENT has the column named
>> INBOX_NUMBER..where as INBOX_NUMBER is not the PRIMARY KEY of the
>> table ARC_DOCUMENT...and i don't want to put a sequence on it or
>> anything..
>
> Well, that's going to be a problem if two people are using your app
> concurrently. Both people will see a form contain
> MAX(INBOX_NUMBER)+1, and they'll both try to submit this value when
> they get around to submitting the form.
>
> This is a logic problem in your application workflow, not a fault of
> Zend_Db. You would have the same problem no matter what framework
> or language you used.
>
> Regards,
> Bill Karwin
>
>
>
>
> --
> Regards,
> YD

You've found the solution, its good, but I still recommend using a
sequence instead of this MAX(id)+1 because this can produce wrong
results when there are lots of clients at the same time.
Consider that a million clients are using your application.
Out of that for one client, you fetch MAX(id)+1 and just after a fermi
second (1e-15) before this client's insert has happened another client
has inserted the data, now when you insert the data for this client
Oracle will give you an error.

--
Regards,
Nilesh Govindarajan
Facebook: http://www.facebook.com/nilesh.gr
Twitter: http://twitter.com/nileshgr
Website: http://www.itech7.com
Cheap and Reliable VPS Hosting: http://j.mp/arHk5e

没有评论: