Yes, I am. I did not think to consider that as the problem. Let me try
this on CF8 and CF9 really quick... good call Mark!
Is there something in Transfer that checks the type of the column
(BIGINT, INT, etc) when saving (or during creation of the
definitions)? If so, could something in Railo be preventing Transfer
from seeing the correct data type?
As far as I can tell, this line basically says "if it's numeric, tell
the database to use float":
<cfelseif block.mapparam.type eq "numeric">
<cfqueryparam value="#value#"
cfsqltype="cf_sql_float"
list="#param.list#" null="#param.isNull#">
Does this happen after some type of check for the datatype and it's
just falling back on the default cfsqltype for numeric since it can't
find the actual datatype and the object is defined with "numeric" as
its type?
Like I said, the odd part is that the same query just by copying and
pasting from the debug works perfectly, so Railo has no problem with
these values or with running the query using them.
Plus, Transfer shows the correct query in the debug, except that it's
wrapped in the cfsqltype which the debug doesn't show.
So, whenever Transfer is about to do this insert, it decides to use
cfsqltype="cf_sql_float" based on the code above. I'm assuming then
that Railo is stopping Transfer from getting the data it needs to
correctly determine the type...
Let me test and see.
If this is indeed the problem, what do you suggest?
I know that changing:
<cfqueryparam value="#value#" cfsqltype="cf_sql_float"
list="#param.list#" null="#param.isNull#">
to
<cfqueryparam value="#value#" cfsqltype="cf_sql_bigint"
list="#param.list#" null="#param.isNull#">
Causes it to start working, so what could we put in place here to fix
the Railo issues? (Assuming that's the issue, give me just a minute to
find out)
On Aug 4, 2:52 pm, Mark Mandel <[email protected]> wrote:
> You're also on Railo aren't you?
>
> Mark
>
> On Wed, Aug 5, 2009 at 6:17 AM, whostheJBoss
> <[email protected]>wrote:
>
>
>
>
>
> > By the way, thanks for helping!!! :)
>
> > On Aug 4, 12:31 pm, Jennifer Larkin <[email protected]> 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<[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:
>
> ...
>
> read more »
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
