database design questions

[teedoff]

Hi I have created two tables in MySQL using Navicat. When running an update query, I noticed that first, when I am entering a price for a product, the price is rounded to whole numbers it seems. Secondly, one field that I enter data in to be added to the record doesn't show up at all. When I go and look at my table design I have the price column set as data type "double" with "0" decimal points. I try to change this to 2 but when I click save it goes back to 0. Also, the column that doesnt post at all is a description of the product that I have set as text. When I try to specify a character length, since it's set to 0 by default, it does the same thing...reverts back to 0. I tried setting this column to different data types. Char and varchar throws an error that the dasta is too long for this type. Everything else shows up from previous entries using Access, only the update query seems to not work correctly. Here is my update code:

<cfparam name="URL.id" default="1">
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
  <cfquery datasource="rlbulbs">   
    UPDATE rlbbulbs
SET item=<cfif IsDefined("FORM.item") AND #FORM.item# NEQ "">
<cfqueryparam value="#FORM.item#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, brandID=<cfif IsDefined("FORM.brandID") AND #FORM.brandID# NEQ "">
<cfqueryparam value="#FORM.brandID#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, bulbDesc=<cfif IsDefined("FORM.bulbDesc") AND #FORM.bulbDesc# NEQ "">
<cfqueryparam value="#FORM.bulbDesc#" cfsqltype="cf_sql_clob" maxlength="2147483647">
<cfelse>
''
</cfif>
, cost=<cfif IsDefined("FORM.cost") AND #FORM.cost# NEQ "">
<cfqueryparam value="#FORM.cost#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, price=<cfif IsDefined("FORM.price") AND #FORM.price# NEQ "">
<cfqueryparam value="#FORM.price#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, qtyPerPrice=<cfif IsDefined("FORM.qtyPerPrice") AND #FORM.qtyPerPrice# NEQ "">
<cfqueryparam value="#FORM.qtyPerPrice#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, qtyInStock=<cfif IsDefined("FORM.qtyInStock") AND #FORM.qtyInStock# NEQ "">
<cfqueryparam value="#FORM.qtyInStock#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, wattage=<cfif IsDefined("FORM.wattage") AND #FORM.wattage# NEQ "">
<cfqueryparam value="#FORM.wattage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, voltage=<cfif IsDefined("FORM.voltage") AND #FORM.voltage# NEQ "">
<cfqueryparam value="#FORM.voltage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, acDC=<cfif IsDefined("FORM.acDC") AND #FORM.acDC# NEQ "">
<cfqueryparam value="#FORM.acDC#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, base=<cfif IsDefined("FORM.base") AND #FORM.base# NEQ "">
<cfqueryparam value="#FORM.base#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, glass=<cfif IsDefined("FORM.glass") AND #FORM.glass# NEQ "">
<cfqueryparam value="#FORM.glass#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, filament=<cfif IsDefined("FORM.filament") AND #FORM.filament# NEQ "">
<cfqueryparam value="#FORM.filament#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, avgLife=<cfif IsDefined("FORM.avgLife") AND #FORM.avgLife# NEQ "">
<cfqueryparam value="#FORM.avgLife#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, beamAngle=<cfif IsDefined("FORM.beamAngle") AND #FORM.beamAngle# NEQ "">
<cfqueryparam value="#FORM.beamAngle#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, oldPage=<cfif IsDefined("FORM.oldPage") AND #FORM.oldPage# NEQ "">
<cfqueryparam value="#FORM.oldPage#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, keyWords=<cfif IsDefined("FORM.keyWords") AND #FORM.keyWords# NEQ "">
<cfqueryparam value="#FORM.keyWords#" cfsqltype="cf_sql_clob" maxlength="2147483647">
<cfelse>
''
</cfif>
, image1=<cfif IsDefined("FORM.image1") AND #FORM.image1# NEQ "">
<cfqueryparam value="#FORM.image1#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
WHERE bulbID=<cfqueryparam value="#FORM.bulbID#" cfsqltype="cf_sql_numeric">
  </cfquery>
  <cflocation url="bulbView.cfm">
</cfif>

Thanks for any help!

[rch1231]

Try using either float or decimal as the data type and you will be able to see the values stored with the decimal correct number of places.

[teedoff]

Try using either float or decimal as the data type and you will be able to see the values stored with the decimal correct number of places.

Hi Rodney, thanks for the reply. I have the price field and the cost field set as data type decimal. Its not taking away the decimal places though. When I enter 5.99 for the price, it rounds it up to $6.00 even. Then when I add some data to the bulbDesc field, it doesn't show up at all. I know at least part of my update query is working, because I can see immediate changes to the oldPage field I just tested.

Tony

[teedoff]

I just tried to add instead of 5.99, 6.56 and 6.49. The 6.56 rounded up to 7.00 and of course the 6.49 rounded back down to 6.00! lol I dont know whats cause this to round up or down automatically.

[tesuji]

Hi

I am sure, you know that the precision and scale should be explicitely specified. If not, precision and scale of numeric and decimal DT are 10 and 0, that is default is decimal(10,0) or numeric(10,0). So numbers having decimal places will be automatically rounded. If you have money values, decimal(15,2), scale 2, for example is good idea.

Just out of curiousity, which mysql version are you running, below 5.0.3 ?

-- tesu

[teedoff]

Hi

I am sure, you know that the precision and scale should be explicitely specified. If not, precision and scale of numeric and decimal DT are 10 and 0, that is default is decimal(10,0) or numeric(10,0). So numbers having decimal places will be automatically rounded. If you have money values, decimal(15,2), scale 2, for example is good idea.

Just out of curiousity, which mysql version are you running, below 5.0.3 ?

-- tesu

Hi Tesu, thanks for the help! I am running version 5.1.36. I think I have figured out the number rounding issue! I am using a cold fusion function that sets strings to the dollarFormat with 2 decimal places. For some reason, when MySQL assigned the field of "price" to varChar originally something didnt work. Thats when I started trying to change the data types to decimal and float and anything I could think of. However, it must be a text type so that the dollarFormat doesnt conflict with it I guess. I changed the "price" field to text and it seems to work. The other issue though I'm still having trouble with. The field of bulbnDesc returns an empty string when I do an Update query. Thanks again for any help!

Tony

[tesuji]

I am trying to understand :)

What you can also do is explicit type casting, for example

select '12345.678' as stringnumber, cast(stringnumber as decimal(15,2)) as "now numeric value"

results in 12345.68

cast() function also works for insert and update.

btw, text datatype has some limitations as compared with varchar. There were some postings regarding varchar vs. text recently on daniweb.

Why you need datatpye CLOB for bulbDesc? CLOB is huge string type (4GBYTE ore so) but very restrictive usage. So most standard functions cannot handle clob data.

-- tesu

[teedoff]

Hi Tesu, I guess I don't need CLOB datatype for anything. I think it was the default when using the Update query wizard in Dream Weaver. I change some of them to varChar I think and seems to still work fine. Again, I have the number issue resolved. It was a conflict between the datatype and a cold fusion dollarFormat function. Thats working good now. The updated data for the column "bulbDesc" is still not showing anything though. I go to my update page and type something there and nothing I typed appears. I changed the sglType to longText instead of CLOB and still nothing. In my database, the type is set to longText as well with default value NULL. Still frustrated with this.

Tony