The method I have used for using CFTRY/CFCATCH is for when I want to update
a column if the record exists, if not then create the record. However I
would only use this if you expect the update to succeed more than fail, the
reason for this is that an entry is made to application.log each time the
update fails. Even though your application.log should be checked and cleared
regularly, I found that in a short space of time the size of the log
increased greatly. Still not sure whether CF should write to the log as the
error has been handled, argument here for whether this is bug in CF. This
was a problem for me due to very high levels of traffic. Here's the code
anyway:
<cftry>
<cfquery>
UPDATE logs
SET DN=DN+#DN# WHERE DT=#DT#</cfquery>
</cftry>
<cfcatch>
<cfquery>
INSERT INTO logs (DN,DT) VALUES (#DN#,#DT#)</cfquery>
</cfcatch>
HTH
Phil.
> -----Original Message-----
> From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> Sent: 22 April 2001 16:59
> To: CF-Talk
> Subject: Re: UPDATE / INSERT
>
>
> Will CFCATCH/CFTRY with DATABASE error be a better solution?
> i.e - TRY TO UPDATE no matter what -> if error occurs (row not exists) ->
> INSERT.
> Is this better then SELECT?
>
> :)
>
> ----- Original Message -----
> From: "Phil Ewington" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Sunday, April 22, 2001 4:29 PM
> Subject: RE: UPDATE / INSERT
>
>
> > OK, u.recordcount could be 0 if the record you are expecting to
> find does
> > not exist or if the value of DT in that record does not match your
> criteria.
> > Your code will create a new record if either of the above is
> true. If this
> > is what you want I can't really see a better way of doing it.
> >
> > Phil.
> >
> > > -----Original Message-----
> > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > Sent: 22 April 2001 15:51
> > > To: CF-Talk
> > > Subject: Re: UPDATE / INSERT
> > >
> > >
> > > something like this.
> > >
> > > Thanks!
> > >
> > > <cfquery name="u">
> > > SELECT DN from logs WHERE DT=#DT#</cfquery>
> > >
> > > <cfif u.recordcount gt 0>
> > >
> > > <cfquery>
> > > UPDATE logs
> > > SET DN=DN+#DN# WHERE DT=#DT#</cfquery>
> > >
> > > <cfelse>
> > >
> > > <cfquery>
> > > INSERT INTO logs (DN,DT) VALUES (#DN#,#DT#)</cfquery>
> > >
> > > </cfif>
> > >
> > >
> > > ----- Original Message -----
> > > From: "Phil Ewington" <[EMAIL PROTECTED]>
> > > To: "CF-Talk" <[EMAIL PROTECTED]>
> > > Sent: Sunday, April 22, 2001 3:20 PM
> > > Subject: RE: UPDATE / INSERT
> > >
> > >
> > > > Please post your current SELECT/UPDATE code so I can see
> > > exactly what you
> > > > are doing.
> > > >
> > > >
> > > > Phil.
> > > >
> > > > > -----Original Message-----
> > > > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > > > Sent: 22 April 2001 15:30
> > > > > To: CF-Talk
> > > > > Subject: Re: UPDATE / INSERT
> > > > >
> > > > >
> > > > > It'll only work if I have all the possible values already placed
> > > > > in COLUM1.
> > > > >
> > > > > ----- Original Message -----
> > > > > From: "Phil Ewington" <[EMAIL PROTECTED]>
> > > > > To: "CF-Talk" <[EMAIL PROTECTED]>
> > > > > Sent: Sunday, April 22, 2001 2:34 PM
> > > > > Subject: RE: UPDATE / INSERT
> > > > >
> > > > >
> > > > > > You need to set your table to have COLUM1 have a value
> 0 assigned
> by
> > > > > > default, this is possible in SQL not sure about other
> dbs. When a
> > > record
> > > > > is
> > > > > > created, COLUM1 will be automatically assigned a value of 0 by
> > > default.
> > > > > So,
> > > > > > if you always do an update, the value will be updated even if
> > > > > you have not
> > > > > > previously assigned a value. Does this make sense?
> > > > > >
> > > > > >
> > > > > > Phil.
> > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > > > > > Sent: 22 April 2001 13:59
> > > > > > > To: CF-Talk
> > > > > > > Subject: Re: UPDATE / INSERT
> > > > > > >
> > > > > > >
> > > > > > > but if the value is not exists in "COLUM" I'll need to use
> INSERT
> > > > > > > to assign
> > > > > > > default values.
> > > > > > > I can't predicte all the possible values, so I can't make a
> > > > > > > "READY-FOR-UPDATE-ONLY" table.
> > > > > > >
> > > > > > >
> > > > > > > ----- Original Message -----
> > > > > > > From: "Phil Ewington" <[EMAIL PROTECTED]>
> > > > > > > To: "CF-Talk" <[EMAIL PROTECTED]>
> > > > > > > Sent: Sunday, April 22, 2001 1:18 PM
> > > > > > > Subject: RE: UPDATE / INSERT
> > > > > > >
> > > > > > >
> > > > > > > > Micheal,
> > > > > > > >
> > > > > > > > Thinking about it, if the value does exist and you
> > > simply want to
> > > > > check
> > > > > > > what
> > > > > > > > that value is before updating, you could always do an update
> > > > > > > irrespective
> > > > > > > of
> > > > > > > > the value, sure, this will mean doing an un-necessary update
> > > > > sometimes,
> > > > > > > but
> > > > > > > > isn't that better than making two connections (select &
> > > > > update) if the
> > > > > > > > update is required. Only a thought.
> > > > > > > >
> > > > > > > >
> > > > > > > > Phil.
> > > > > > > >
> > > > > > > > > -----Original Message-----
> > > > > > > > > From: Phil Ewington [mailto:[EMAIL PROTECTED]]
> > > > > > > > > Sent: 22 April 2001 12:04
> > > > > > > > > To: CF-Talk
> > > > > > > > > Subject: RE: UPDATE / INSERT
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Do you mean if the record exists UPDATE, or if the
> > > value in the
> > > > > column
> > > > > > > is
> > > > > > > > > specified update?
> > > > > > > > >
> > > > > > > > > If the record does not exist, use cftry/cfcatch:
> try UPDATE,
> > > > > > > if fails do
> > > > > > > > > INSERT.
> > > > > > > > >
> > > > > > > > > If the record exists but value is NULL, you are
> > > probably better
> > > > > > > > > off setting
> > > > > > > > > the columns default value to zero, this way you will
> > > > > always be able
> > > > > to
> > > > > > > > > increment the value by 1 using UPDATE.
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > Phil.
> > > > > > > > >
> > > > > > > > > > -----Original Message-----
> > > > > > > > > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > > > > > > > > Sent: 22 April 2001 13:06
> > > > > > > > > > To: CF-Talk
> > > > > > > > > > Subject: UPDATE / INSERT
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > I'm trying to find the best way to have SQL do this:
> > > > > > > > > > IF COLUM1 already contains "VALUE" then UPDATE COLUM2+1
> > > > > > > > > > IF NOT, INSERT COLUM1,COLUM2 VALUES ("VALUE",1)
> > > > > > > > > >
> > > > > > > > > > Currently I'm using SELECT / RecordCount to first find
> > > > > out if the
> > > > > > > > > > VALUE exists.
> > > > > > > > > >
> > > > > > > > > > There must be a better way...
> > > > > > > > > >
> > > > > > > > > > Michael.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
