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<[email protected]> 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 <[email protected]> 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<[email protected]> >> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> >> >> > > 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 Foundation\Tomcat >> >> > > 6.0\sites\test\ROOT\handlers\test.cfc >> >> > > Execution Time 0 >> >> > > Recordcount 0 >> >> > > Query UPDATE users SET email = '' ,password = '' ,accountType = >> >> > > 0 ,customID = 1474075992 WHERE userID = 42 >> >> >> > > And the value shows up correctly in the database as 1474075992 >> >> >> > > So, the queries show up IDENTICAL except for the one run through >> >> > > Transfer inserts (or updates, in this case) the wrong value in the >> >> > > database! >> >> >> > > I am doing NOTHING else in my file. It is absolutely impossible that >> >> > > there is a duplicate insert or anything like that. This site has 1 >> >> > > handler with 1 method and you're looking at what it contains. >> >> >> > > Can anyone else reproduce this? >> >> >> > > I'm using InnoDB in MySQL 5 with BIGINT (have also tried INT) as my >> >> > > field type. >> >> >> > > Obviously the field is ok because a regular <cfquery> inserts the >> >> > > value just fine, so this is limited to Transfer. >> >> >> > > Any thoughts?? >> >> >> > > On Jul 31, 9:04 pm, whostheJBoss <[email protected]> wrote: >> >> > > > Nope, like I said, if I copy the EXACT query output from the debug >> >> > > > like so: >> >> >> > > > <cfquery datasource="foo" name="add"> >> >> > > > INSERT INTO users(email,password,accountType,customID,hash) VALUES >> >> > > > ( '' , '' , 0 , 1474075992 , '' ) >> >> > > > </cfquery> >> >> >> > > > And put i right below >> >> >> > > > user = instance.Transfer.new("users.user"); >> >> > > > user.setCustomID(1474075992); >> >> > > > instance.Transfer.save(user); >> >> >> > > > Then two users are created. The one from Transfer has the wrong >> >> > > > value >> >> > > > and the one from the direct query has the right value. >> >> >> > > > Like I said, the value from the debugging shows the sql CORRECT with >> >> > > > the correct values. But when checking the DB it's wrong. The insert >> >> > > > without Transfer shows IDENTICAL SQL in the debug and inserts >> >> > > > correctly. >> >> >> > > > On Jul 31, 8:09 pm, Mark Mandel <[email protected]> wrote: >> >> >> > > > > Transfer just runs a simple <cfquery> sql query. >> >> >> > > > > So if the SQL debug output is saying the right values are going >> >> > > > > in... I >> >> > > > > can't see what else can be done? >> >> >> > > > > I assume there are no triggers on the table, or the like? >> >> >> > > > > Mark >> >> >> > > > > On Sat, Aug 1, 2009 at 1:06 PM, whostheJBoss < >> >> > > [email protected]>wrote: >> >> >> > > > > > Nope, it only shows up the one time. :\ >> >> >> > > > > > The odd thing is that I also have a note field in the database >> >> > > > > > table >> >> > > > > > for users (the table I'm inserting into) and if I set the value >> >> > > > > > of >> >> > > the >> >> > > > > > notes field, which is VARCHAR, the value goes in correctly for >> >> > > > > > that >> >> > > > > > field but incorrectly for the customID field. >> >> >> > > > > > user = instance.Transfer.new("users.user"); >> >> > > > > > user.setCustomID(1474075992); >> >> > > > > > user.setNote(1474075992); >> >> > > > > > instance.Transfer.save(user); >> >> >> > > > > > In this case, the note value comes through correctly and the >> >> > > > > > customID >> >> > > > > > value is wrong. >> >> >> > > > > > Nothing else happens after this save. So, when saving it puts >> >> > > > > > in one >> >> > > > > > value fine and the other incorrectly. If I use the query >> >> > > > > > directly >> >> > > > > > instead of Transfer, the insert works as expected. >> >> >> > > > > > On Jul 31, 11:23 am, Jennifer Larkin <[email protected]> wrote: >> >> > > > > > > That sounds like maybe the value is getting saved to the >> >> > > > > > > database >> >> > > by a >> >> > > > > > > second function. Does the inserted value show up elsewhere in >> >> > > > > > > the >> >> > > > > > > debug? >> >> >> > > > > > > On Fri, Jul 31, 2009 at 10:49 AM, >> >> >> > > > > > > whostheJBoss<[email protected]> wrote: >> >> >> > > > > > > > Ok, so some odd behavior... >> >> >> > > > > > > > When doing the insert via Transfer the debug shows: >> >> >> > > > > > > > Source C:\Program Files (x86)\Apache Software >> >> > > > > > > > Foundation\Tomcat >> >> > > > > > > > 6.0\sites\foo\ROOT\transfer\com\sql\QueryExecution.cfc >> >> > > > > > > > Execution Time 0 >> >> > > > > > > > Recordcount 0 >> >> > > > > > > > Query INSERT INTO >> >> > > users(email,password,accountType,customID,hash) >> >> > > > > > > > VALUES ( '' , '' , 0 , 1474075991 , '' ) >> >> >> > > > > > > > But the value in the database shows up as: 1474076030 >> >> >> > > > > > > > If I copy and paste the query exactly and run it without >> >> > > > > > > > Transfer >> >> > > like >> >> ... >> >> read more » > > > -- "Nothing says mother's love like a giant robotic platypus butt!" Phineas and Ferb Now blogging.... http://www.blivit.org/blog/index.cfm http://www.blivit.org/mr_urc/index.cfm --~--~---------~--~----~------------~-------~--~----~ 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---
