CFQUERY Question

[WestSide]

Hi,

I have never seen this happen but it seems to be happening to me.  I have a query that does an insert into my database but for some reason ColdFusion is putting the values in the wrong columns.  Example:

INSERT INTO TableA (

first_name,

last_name,

email

)

VALUES (

'john',

'doe',

'jdoe@test.com'

)

For some reason strange reason my data gets inserted into the wrong column.  For example, the first_name column gets the email address as opposed to 'john'.

I dumped the SQL statement that CF is trying to execute and you can clearly see the values are going to the wrong columns, I just don't know why.

I am using CF8.

Anyone seen anything like this?

-westside

[ilssac]

I've never heard of this in insert statements.  But I have heard of behavior like this in select statements when the "SELECT *" syntax is uses.

What happens there is the when SELECT * is use the JDBC database driver will cache the columns.  Then if the database schema is modified, the columns will no longer match the cached order and strange results can occur.

It is one of the prime reasons that experienced developers will almost always advise against using the SELECT * syntax.

[Dan_Bracuk]

If you run the same query outside coldfusion, does the same thing happen?

[-__cfSearching__-]

INSERT INTO TableA (

first_name,

last_name,

email

)

VALUES (

'john',

'doe',

'jdoe@test.com'

)

How are those the wrong column/value pairs? Is the first_name not 'Joe', etcetera?

[ilssac]

My understanding is that the OP is saying they are NOT getting those expected name value pairs.  But rather, for some reason, the e-mail value is ending up in the first_name column.

[-__cfSearching__-]

Yep,  I just realized my mistake. I was not paying attention and assumed they would post an example of the _bad_ values.  Well that is what I get for assuming ... except this time is just me .. no "u".

[yui8979]

Hi Westside, Firstly, what database you're using?

Secondly, are you sure your sql statement is syntatically correct?

INSERT INTO TableA (

first_name,

last_name,

email

)

VALUES (

'john',

'doe',

mailto:'jdoe@test.com'

)

[Adam Cameron.]

First things first: CF doesn't insert anything into your DB.  All it does is pass an string (containing an SQL statement) and any parameters to the DB driver, which passes it on to the DB for the DB to do the work.  If you are passing that exact string (the insert query you cite) to the DB, then it's the DB buggering it up.  CF doesn't touch it.

I suspect you are not telling us the full story here.  I do not believe that you simply have that SQL statement within a <cfquery> tag and the insert is going wrong in the DB.  Can you post the code that you're running which is causing this problem...

--

Adam

[Owainnorth]

Got to agree with Adam here, this looks far more like you've labelled your form fields incorrectly or similar. If a database engine was as screwed as this would imply I'm pretty sure it wouldn't be running

[JR__Bob__Dobbs-qSBHQ2]

Corrected message.  Sorry for the duplication.

Questions:

1. What database are you using?

2. Does your database server have a profile or trace feature that allows you to examine the commands that the database is executing?

3. Are you using CFQUERY or CFINSERT?

4. Can you post your CF code?

[BKBK]

What happens with

INSERT INTO TableA (email, first_name, last_name)
VALUES ('jdoe@test.com', 'john', doe')

[WestSide]

Sorry for the long delay in responding.  The query I originally posted lived in a CFC and the parameters that i was passing into the function that did the database insert were being associated with the wrong fields.  I ended up just using named parameters for my function call. Next time I will just post the whole code.

Thanks

[TLC-IT]

One variation of this issue that I have to deal with around here has to do with MS SQL Views.

We have several databases around here (of course), and most of the important result-sets that we routinely have to deal with are defined as SQL "views."  The trick is that sometimes one database contains a view that refers to what's supposed to be the same view in another database.

If you change the definition of the view in the second database, you have to "refresh the view" in the first.  Otherwise, the columns do not correspond, and the wrong values (sometimes "nonsense" values!) show up in the wrong columns.  Refreshing the views (that refer to the other views) solves the problem.

For whatever reason, the views aren't paying attention to the column names.  The behavior certainly is incorrect, but our DBAs assure me that it's "a known issue." 

So... if you know that you are referring to the correct columns, and you know that you are supplying the correct values in the correct positions, but the values are not winding up in the right places (especially if they seem to be "offset"), then I'd wager that you're dealing with a similar problem.