Re: Storing decimal parts of a second & cfqueryparam
You need to set the scale attribute in the cfqueryparam tag. http://cfquickdocs.com/cf9/?getDoc=cfqueryparam -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 1 March 2011 10:43, Pete Ruckelshaus wrote: > > Primarily because it's easier and more accurate to sort on a single overall > value (inches). > > Of course, if it were up to me, we'd be using metric :) > > On Mon, Feb 28, 2011 at 7:12 PM, Roger Austin wrote: > >> >> On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote: >> > >> > OK, just not my night with decimal values. >> > >> > Trying to store distances (for throwing and jumping events), which are >> > measured in feet and inches. In order to maintain proper sort order, I >> > decided to convert feet and inches (with fractions of an inch as decimal >> > value) to inches with fractions of an inch as decimal values. >> > >> > Again, database is SQL Server 2008, and I'm using cfqueryparam. Data >> type >> > for the column in question is decimal(18, 4), and I'm using >> CF_SQL_DECIMAL >> > as the cfsqltype value. I can see in the debug output that the decimal >> > portion of the value is part of the value to be inserted, for instance, >> > 825.25, but looking at the database table, the stored value is 825. >> >> Why not store two fields; one for feet and one for inches. That seems >> like the easiest way to do it other than just use one float for both. >> The decimal issue is probably more a database issue than a CF issue. >> >> > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342631 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second & cfqueryparam
Primarily because it's easier and more accurate to sort on a single overall value (inches). Of course, if it were up to me, we'd be using metric :) On Mon, Feb 28, 2011 at 7:12 PM, Roger Austin wrote: > > On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote: > > > > OK, just not my night with decimal values. > > > > Trying to store distances (for throwing and jumping events), which are > > measured in feet and inches. In order to maintain proper sort order, I > > decided to convert feet and inches (with fractions of an inch as decimal > > value) to inches with fractions of an inch as decimal values. > > > > Again, database is SQL Server 2008, and I'm using cfqueryparam. Data > type > > for the column in question is decimal(18, 4), and I'm using > CF_SQL_DECIMAL > > as the cfsqltype value. I can see in the debug output that the decimal > > portion of the value is part of the value to be inserted, for instance, > > 825.25, but looking at the database table, the stored value is 825. > > Why not store two fields; one for feet and one for inches. That seems > like the easiest way to do it other than just use one float for both. > The decimal issue is probably more a database issue than a CF issue. > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342629 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second & cfqueryparam
On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote: > > OK, just not my night with decimal values. > > Trying to store distances (for throwing and jumping events), which are > measured in feet and inches. In order to maintain proper sort order, I > decided to convert feet and inches (with fractions of an inch as decimal > value) to inches with fractions of an inch as decimal values. > > Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type > for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL > as the cfsqltype value. I can see in the debug output that the decimal > portion of the value is part of the value to be inserted, for instance, > 825.25, but looking at the database table, the stored value is 825. Why not store two fields; one for feet and one for inches. That seems like the easiest way to do it other than just use one float for both. The decimal issue is probably more a database issue than a CF issue. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342617 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second & cfqueryparam
I think your code is fine, but take a look at the precision value for the column in SQL Server. Ensure that it's set to 2. -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 8:08 PM, Pete Ruckelshaus wrote: > > OK, just not my night with decimal values. > > Trying to store distances (for throwing and jumping events), which are > measured in feet and inches. In order to maintain proper sort order, I > decided to convert feet and inches (with fractions of an inch as decimal > value) to inches with fractions of an inch as decimal values. > > Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type > for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL > as the cfsqltype value. I can see in the debug output that the decimal > portion of the value is part of the value to be inserted, for instance, > 825.25, but looking at the database table, the stored value is 825. > Here's the debug output for that query: > > *insertResult* (Datasource=track, Time=2ms, Records=1) in > C:\inetpub\wwwroot\track\enter_results.cfm @ 21:59:59.059 > > INSERT INTO tblEventResults(eventid, athleteid, time, distance, > competition) > VALUES (?, > ?, > ?, > ?, > ?) > > > Query Parameter Value(s) - > Parameter #1(CF_SQL_INTEGER) = 1 > Parameter #2(CF_SQL_INTEGER) = 1 > Parameter #3(CF_SQL_TIMESTAMP) = > Parameter #4(CF_SQL_DECIMAL) = 825.25 > Parameter #5(CF_SQL_BIT) = NO > > Should I be using a different precision value for the decimal datatype? Or > am I just doing something idiotic? > > Thanks, > > Pete > > On Sat, Feb 26, 2011 at 4:59 PM, Pete Ruckelshaus > wrote: > > > Hey, that did it! Thanks! > > > > Pete > > > > > > On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer < > > charlie.grie...@gmail.com> wrote: > > > > > > > > Hi Pete: > > > > > > Have you tried cf_sql_timestamp as the cfsqltype (as opposed to > > > cf_sql_time)? > > > -- > > > Charlie Griefer > > > http://charlie.griefer.com > > > > > > I have failed as much as I have succeeded. But I love my life. I love my > > > wife. And I wish you my kind of success. > > > On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: > > > > > > > > I'm building an app for keeping track of high school track meet results > > > > using CF9 and MS SQL Server 2008. I am using the "time(7)" data type in > > > the > > > > database, and when doing the insert query, I'm using with > > > a > > > > sqltype of cf_sql_time. However, when entering a time value of, for > > > > example, "00:01:22.05", the value that actually gets inserted drops the > > > > decimal part of the seconds. When I do the insert query without using > > > > , it works fine. I prefer using cfqueryparam for obvious > > > > reasons and am always hesitant to not use it, so is there a way to get > > > it to > > > > work and still have it retain the decimal portion of the second? > > > > > > > > Thanks, > > > > > > > > Pete > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342611 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second & cfqueryparam
OK, just not my night with decimal values. Trying to store distances (for throwing and jumping events), which are measured in feet and inches. In order to maintain proper sort order, I decided to convert feet and inches (with fractions of an inch as decimal value) to inches with fractions of an inch as decimal values. Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL as the cfsqltype value. I can see in the debug output that the decimal portion of the value is part of the value to be inserted, for instance, 825.25, but looking at the database table, the stored value is 825. Here's the debug output for that query: *insertResult* (Datasource=track, Time=2ms, Records=1) in C:\inetpub\wwwroot\track\enter_results.cfm @ 21:59:59.059 INSERT INTO tblEventResults(eventid, athleteid, time, distance, competition) VALUES (?, ?, ?, ?, ?) Query Parameter Value(s) - Parameter #1(CF_SQL_INTEGER) = 1 Parameter #2(CF_SQL_INTEGER) = 1 Parameter #3(CF_SQL_TIMESTAMP) = Parameter #4(CF_SQL_DECIMAL) = 825.25 Parameter #5(CF_SQL_BIT) = NO Should I be using a different precision value for the decimal datatype? Or am I just doing something idiotic? Thanks, Pete On Sat, Feb 26, 2011 at 4:59 PM, Pete Ruckelshaus wrote: > Hey, that did it! Thanks! > > Pete > > > On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer < > charlie.grie...@gmail.com> wrote: > >> >> Hi Pete: >> >> Have you tried cf_sql_timestamp as the cfsqltype (as opposed to >> cf_sql_time)? >> -- >> Charlie Griefer >> http://charlie.griefer.com >> >> I have failed as much as I have succeeded. But I love my life. I love my >> wife. And I wish you my kind of success. >> On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: >> > >> > I'm building an app for keeping track of high school track meet results >> > using CF9 and MS SQL Server 2008. I am using the "time(7)" data type in >> the >> > database, and when doing the insert query, I'm using with >> a >> > sqltype of cf_sql_time. However, when entering a time value of, for >> > example, "00:01:22.05", the value that actually gets inserted drops the >> > decimal part of the seconds. When I do the insert query without using >> > , it works fine. I prefer using cfqueryparam for obvious >> > reasons and am always hesitant to not use it, so is there a way to get >> it to >> > work and still have it retain the decimal portion of the second? >> > >> > Thanks, >> > >> > Pete >> > >> > >> > >> >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second & cfqueryparam
Hey, that did it! Thanks! Pete On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer wrote: > > Hi Pete: > > Have you tried cf_sql_timestamp as the cfsqltype (as opposed to > cf_sql_time)? > -- > Charlie Griefer > http://charlie.griefer.com > > I have failed as much as I have succeeded. But I love my life. I love my > wife. And I wish you my kind of success. > On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: > > > > I'm building an app for keeping track of high school track meet results > > using CF9 and MS SQL Server 2008. I am using the "time(7)" data type in > the > > database, and when doing the insert query, I'm using with > a > > sqltype of cf_sql_time. However, when entering a time value of, for > > example, "00:01:22.05", the value that actually gets inserted drops the > > decimal part of the seconds. When I do the insert query without using > > , it works fine. I prefer using cfqueryparam for obvious > > reasons and am always hesitant to not use it, so is there a way to get it > to > > work and still have it retain the decimal portion of the second? > > > > Thanks, > > > > Pete > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second & cfqueryparam
Hi Pete: Have you tried cf_sql_timestamp as the cfsqltype (as opposed to cf_sql_time)? -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: > > I'm building an app for keeping track of high school track meet results > using CF9 and MS SQL Server 2008. I am using the "time(7)" data type in the > database, and when doing the insert query, I'm using with a > sqltype of cf_sql_time. However, when entering a time value of, for > example, "00:01:22.05", the value that actually gets inserted drops the > decimal part of the seconds. When I do the insert query without using > , it works fine. I prefer using cfqueryparam for obvious > reasons and am always hesitant to not use it, so is there a way to get it to > work and still have it retain the decimal portion of the second? > > Thanks, > > Pete > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342600 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Storing decimal parts of a second & cfqueryparam
I'm building an app for keeping track of high school track meet results using CF9 and MS SQL Server 2008. I am using the "time(7)" data type in the database, and when doing the insert query, I'm using with a sqltype of cf_sql_time. However, when entering a time value of, for example, "00:01:22.05", the value that actually gets inserted drops the decimal part of the seconds. When I do the insert query without using , it works fine. I prefer using cfqueryparam for obvious reasons and am always hesitant to not use it, so is there a way to get it to work and still have it retain the decimal portion of the second? Thanks, Pete ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342599 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
