numeric maps to using cf_sql_float. cf_sql_float is a parameter that will correctly handle floating points across databases.
It seems that Railo has an issue with how it is mapping cf_sql_float, so it cannot handle BigInt values in the database you are using, whereas on CF it has no such trouble. Mark On Thu, Aug 6, 2009 at 10:07 AM, whostheJBoss <dotfus...@changethings.org>wrote: > > You happen to know what part of Transfer the bug is affecting? I'd be > happy to take it up with the guys at Railo, just need to know what I > should be asking. > > Is Transfer checking the column type of the objects and then using > that type to wrap them with a cfsqltype? Does that mean Railo is not > providing this information to Transfer, so then it is falling back on > the default cfsqltype for numeric, which in this case happens to be > float? > > Basically, is Transfer unable to get the type from Railo so it runs > this: > > <cfelseif block.mapparam.type eq "numeric"> > <cfqueryparam value="#value#" cfsqltype="cf_sql_float" > list="#param.list#" null="#param.isNull#"> > > And sets it to float? > > Thanks! > > On Aug 5, 4:52 pm, Mark Mandel <mark.man...@gmail.com> wrote: > > Sounds like a bug in Railo to me. > > > > Mark > > > > On Wed, Aug 5, 2009 at 8:18 AM, whostheJBoss <dotfus...@changethings.org > >wrote: > > > > > > > > > > > > > You always manage to come in and say something very simple and I go > > > "Oh yeah!!!" > > > > > Anyway, CF9 has no problem with this, so it must be... a Railo thing. > > > Err, a Transfer / Railo thing. > > > > > So, what can be done to have Transfer see the right data type in > > > Railo? > > > > > Thanks!!! > > > > > On Aug 4, 2:52 pm, Mark Mandel <mark.man...@gmail.com> wrote: > > > > You're also on Railo aren't you? > > > > > > Mark > > > > > > On Wed, Aug 5, 2009 at 6:17 AM, whostheJBoss < > dotfus...@changethings.org > > > >wrote: > > > > > > > By the way, thanks for helping!!! :) > > > > > > > On Aug 4, 12:31 pm, Jennifer Larkin <jlar...@gmail.com> wrote: > > > > > > Which version of MySQL are you using? > > > > > > > > OK, so looking again at the two values in question: > > > > > > 1474075992 > > > > > > 1474076030 > > > > > > > > It is certainly feasible that you have encountered a floating > point > > > > > > error. In a ten digit number, the second number is off by 38. The > > > > > > thing is, Transfer doesn't cause floating point errors-- they are > the > > > > > > cause of the software that Transfer runs on, and MySQL has > floating > > > > > > point errors. I've used Transfer on Oracle and as I said, I've > never > > > > > > encountered this problem. > > > > > > > > So, I looked up MySQL floating point errors and discovered this > > > article: > > > > >http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html > > > > > > > > Which leads me to believe that if you are using a version of > MySQL > > > > > > prior to 5.0.5, that you should upgrade to 5.0.5, which has > greater > > > > > > floating point precision. > > > > > > > > On Tue, Aug 4, 2009 at 12:09 PM, whostheJBoss< > > > dotfus...@changethings.org> > > > > > wrote: > > > > > > > > > The column in question here is not a key, it's an additional > field, > > > > > > > nothing special. Just a BIGINT named customID, but it's not the > > > > > > > primary key. The primary key field is userID. > > > > > > > > > As of now, any column with datatype INT or BIGINT is being > saved > > > > > > > incorrectly by Transfer. This one in particular was INT, then I > > > > > > > changed to BIGINT trying to fix the problem. So, as of now it > is > > > > > > > BIGINT and will be staying that way. > > > > > > > > > The same column using the exact same SQL that Transfer > generates in > > > a > > > > > > > regular <cfquery> work fine because it isn't wrapped in > > > > > > > cfsqltype="cf_sql_float" query param which is invisible to the > > > debug > > > > > > > output. (Which is why I didn't see it before.) > > > > > > > > > The column it does work for is VARCHAR, which makes complete > sense. > > > It > > > > > > > just takes the number and inserts it as a string. > > > > > > > > > Transfer has this statement: > > > > > > > > > <cfelseif block.mapparam.type eq "numeric"> > > > > > > > <cfqueryparam value="#value#" cfsqltype="cf_sql_float" > > > > > > > list="#param.list#" null="#param.isNull#"> > > > > > > > > > Which takes any "numeric" property of a defined Transfer object > and > > > > > > > forces it to float, even if the database has it marked as > BIGINT. > > > > > > > > > On Aug 4, 11:17 am, Jennifer Larkin <jlar...@gmail.com> wrote: > > > > > > >> I've used transfer with tons of bigint columns and never had > this > > > > > > >> problem. I would assume that your key is bigint and not float, > but > > > you > > > > > > >> aren't having a problem with the key being set as float. So > the > > > > > > >> question is, why does this work for some columns and not for > > > others. > > > > > > > > >> What is the datatype on the column that is getting saved > correctly > > > and > > > > > > >> the one that is getting saved incorrectly? > > > > > > > > >> On Tue, Aug 4, 2009 at 10:36 AM, whostheJBoss< > > > > > dotfus...@changethings.org> wrote: > > > > > > > > >> > Ok, I've figured it out. > > > > > > > > >> > This was absolutely something in Transfer. > > > > > > > > >> > In: > > > > > > > > >> > transfer.com.sql.QueryExecution > > > > > > > > >> > This line: > > > > > > > > >> > <cfelseif block.mapparam.type eq "numeric"> > > > > > > >> > <cfqueryparam value="#value#" > cfsqltype="cf_sql_float" > > > > > > >> > list="#param.list#" null="#param.isNull#"> > > > > > > > > >> > If I replace it with: > > > > > > > > >> > <cfelseif block.mapparam.type eq "numeric"> > > > > > > >> > <cfqueryparam value="#value#" > cfsqltype="CF_SQL_BIGINT" > > > > > > >> > list="#param.list#" null="#param.isNull#"> > > > > > > > > >> > It works. Obviously I can't leave it this way since I have > other > > > > > > >> > fields that are not BIGINT and will cause the same problem > for > > > those > > > > > > >> > fields, but this is definitely where the problem is. > > > > > > > > >> > So, all numeric values are being set to float. > > > > > > > > >> > On Aug 4, 9:52 am, whostheJBoss <dotfus...@changethings.org > > > > > wrote: > > > > > > >> >> Yes, please see my examples above. > > > > > > > > >> >> Transfer updates the correct record in the correct > database, it > > > > > just > > > > > > >> >> happens to put in the wrong value. > > > > > > > > >> >> I know this for sure because I have another field called > "note" > > > > > that > > > > > > >> >> is a text field, and if I change: > > > > > > > > >> >> user.setCustomID(1474075992); > > > > > > > > >> >> to: > > > > > > > > >> >> user.setNote(1474075992); > > > > > > > > >> >> It puts the correct value in for the note field. (the > "note" > > > field > > > > > is > > > > > > >> >> VARCHAR). If I change it back to customID, I get the wrong > > > value > > > > > > >> >> again. > > > > > > > > >> >> I thought something fishy might be happening, that's why I > ran > > > > > another > > > > > > >> >> query immediately after the Transfer insert by using the > EXACT > > > same > > > > > > >> >> query that the debug showed from Transfer. > > > > > > > > >> >> Running the query through <cfquery> inserts correctly, > running > > > it > > > > > > >> >> through Transfer causes the value to be modified. > > > > > > > > >> >> Are we certain Transfer works properly with BIGINT? > > > > > > > > >> >> So, again, to reiterate so there is no confusion here... > > > > > > > > >> >> I run an insert with Transfer and it puts in the wrong > value, > > > even > > > > > > >> >> though the debug output for the query that Transfer > generates > > > shows > > > > > > >> >> the value correctly in the SQL. > > > > > > > > >> >> If I copy and paste the EXACT SQL from the debug that > Transfer > > > > > tried > > > > > > >> >> and put it into a <cfquery> it updates or inserts the > correct > > > > > value. > > > > > > > > >> >> Something Transfer is doing is causing this. > > > > > > > > >> >> To be sure I created a BLANK SITE. There are NO OTHER FILES > on > > > this > > > > > > >> >> site at all. The only files are Transfer and 1 handler and > an > > > empty > > > > > > >> >> datasource. > > > > > > > > >> >> In the handler I create 1 user object with Transfer and > save > > > it. > > > > > The > > > > > > >> >> customID field comes out incorrectly even though the SQL > output > > > > > shows > > > > > > >> >> the correct query. > > > > > > > > >> >> If I take the exact SQL from the output and replace the > > > > > Transfer.save > > > > > > >> >> () call with a <cfquery> with that same SQL it runs > correctly > > > and > > > > > > >> >> inserts the right value. > > > > > > > > >> >> So, somewhere between Transfer and MySQL the data is being > > > changed. > > > > > > > > >> >> There's nothing else happening, it's an empty site with no > > > other > > > > > files > > > > > > >> >> ANYWHERE. > > > > > > > > >> >> If I manually put the cfquery in, it works fine, if I try > > > > > Transfer... > > > > > > >> >> failure. > > > > > > > > >> >> I'm certain this isn't a datasource name issue. > > > > > > > > >> >> On Aug 4, 3:24 am, Aurélien DELEUSIÈRE < > adeleusi...@gmail.com> > > > > > wrote: > > > > > > > > >> >> > Hello - > > > > > > >> >> > Have you chek the data source name used in your transfer > > > config ? > > > > > This > > > > > > >> >> > problem seems to be database side, I would be surprised > if > > > > > transfer is > > > > > > >> >> > involved there... > > > > > > > > >> >> > Cheers, > > > > > > >> >> > Aurelien > > > > > > > > >> >> > 2009/8/4 whostheJBoss <dotfus...@changethings.org> > > > > > > > > >> >> > > Ok, I've done more testing. > > > > > > > > >> >> > > I run the following: > > > > > > > > >> >> > > <cfscript> > > > > > > >> >> > > user = instance.Transfer.get("users.user", 42); > > > > > > >> >> > > user.setCustomID('1474075992'); > > > > > > >> >> > > instance.Transfer.save(user); > > > > > > >> >> > > </cfscript> > > > > > > > > >> >> > > And the query in debug shows: > > > > > > > > >> >> > > Source C:\Program Files (x86)\Apache Software > > > > > Foundation\Tomcat > > > > > > >> >> > > 6.0\sites\test\ROOT\transfer\com\sql\QueryExecution.cfc > > > > > > >> >> > > Execution Time 0 > > > > > > >> >> > > Recordcount 0 > > > > > > >> >> > > Query UPDATE users SET email = '' ,password = '' > > > ,accountType > > > > > = > > > > > > >> >> > > 0 ,customID = 1474075992 WHERE userID = 42 > > > > > > > > >> >> > > The value for customID in the database shows up as > > > 1474076030 > > > > > > > > >> >> > > If I run: > > > > > > > > >> >> > > <cfscript> > > > > > > >> >> > > user = instance.Transfer.get("users.user", 42); > > > > > > >> >> > > user.setCustomID('1474075992'); > > > > > > >> >> > > instance.Transfer.save(user); > > > > > > >> >> > > </cfscript> > > > > > > > > >> >> > > <cfquery name="test" datasource="test"> > > > > > > >> >> > > UPDATE users SET email = '' ,password = '' ,accountType > = 0 > > > > > ,customID > > > > > > >> >> > > = 1474075992 WHERE userID = 42 > > > > > > >> >> > > </cfquery> > > > > > > > > >> >> > > The query debug shows: > > > > > > > > >> >> > > Source C:\Program Files (x86)\Apache Software > > > > > Foundation\Tomcat > > > > > > >> >> > > 6.0\sites\test\ROOT\transfer\com\sql\QueryExecution.cfc > > > > > > >> >> > > Execution Time 0 > > > > > > >> >> > > Recordcount 0 > > > > > > >> >> > > Query UPDATE users SET email = '' ,password = '' > > > ,accountType > > > > > = > > > > > > >> >> > > 0 ,customID = 1474075992 WHERE userID = 42 > > > > > > > > >> >> > > Source C:\Program Files (x86)\Apache Software > > > > ... > > > > read more » > > > -- E: mark.man...@gmail.com T: http://www.twitter.com/neurotic W: www.compoundtheory.com --~--~---------~--~----~------------~-------~--~----~ Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---