troubleshoot Wheels.ColumnNotFound

[Mike Henke]

Is there a way troubleshoot how a query is generated on a
Wheels.ColumnNotFound error? I would like to see the query generated
and maybe other useful stuff.

I have a table on dev findAll() works, moved the code to qa, and it
gives "Wheels.ColumnNotFound". I recreated the xxx table from qa but
called it, xxx2 and point the model cfc to the new table. And the
code works.

Switch to original table, code breaks. Wheels.ColumnNotFound
Wheels looked for the column mapped to the xxxid property but couldn't
find it in the database table.

Thanks,

FYI - Wheels 1.0.3

[Mike Henke]

so far tracked it down to Len(loc.toAppend) being 0 on the original
xxx table but 56 on the xxx2 table.

line 1041 /wheels/model/crud.cfm

[Mike Henke]

Weird, it seems for the original table, wheels tries to create the sql
with extra blank columns.

original xxx table:
SELECT xxx.xxxID AS xxxtypeid,
xxx.xxxtype AS xxxtype,
xxx.DATEUPDATED, , ,
FROM xxx

xxx2 table:
SELECT 0xxx2.xxxtype AS xxxtype,
0xxx2.DATEUPDATED,
0xxx2.xxxID AS xxxtypeid
FROM xxx2

I am using Oracle 10g for the db.

[Mike Henke]

http://code.google.com/p/cfwheels/issues/detail?id=437

[Per Djurner]

Are you sure a reload=true doesn't fix the problem?

On Wed, Apr 7, 2010 at 7:53 PM, Mike Henke <henk...@gmail.com> wrote:
> http://code.google.com/p/cfwheels/issues/detail?id=437
>
> --
> You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
> To post to this group, send email to cfwh...@googlegroups.com.
> To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.
>
>

[Mike Henke]

I tried. I am looking at the $addSelectClause function now in the
crud.cfm. It seems when I dump arguments.sql at the end of the
function I see an array with one position containing the select with
blank columns. This table has 3 columns and when I dumped the loc
variable. PROPERTYLIST and COLUMNLIST seem to have the columns listed
twice. I am guessing some where dups might be removed but the
position maybe being kept resulting in the blank columns.

[Per Djurner]

What's the difference between "dev" and "qa" that you are referring to?
If the CFML code and database schema is the same and you're running in
the same Wheels environment mode it can't really be a Wheels problem,
right?

[Mike Henke]

Thanks for the help/troubleshooting. I have figured out if I replace
the $throw on line 1044 with testing = 1; the page loads fine. I am
not sure why this is getting triggered.

----

I am focusing on the qa db now with the same set of wheels code. I
have the original table and a duplicated table in Oracle. The
duplicated table works but the original doesn't when I change the
<cfset table("xxx") /> in wheels model.

I am not sure if this is part of the issue but noticed for the
original table when dumping the loc in the function $addSelectClause
of /wheels/model/crud.cfm these variables have duplicated columns
listed:
loc.addedproperties
loc.addedpropertiesbymodel.fundingType (correct)
loc.classdata.columnlist
loc.classdata.propertylist
loc.classes[1].columnlist
loc.classes[1].propertylist

[Mike Henke]

the page i am replacing the $throw is /wheels/model/crud.cfm

[Mike Henke]

I can attach any useful debugging info to the ticket, if anyone
requests specific variables. Apparently Len(loc.toAppend) is 0 but
the page still works.

[Mike Henke]

and the application.wheels.showErrorInformation is true.

[Per Djurner]

You said it worked on "dev" right?
What's the difference between "dev" and "qa"?
If you figure that out the problem is hopefully solved :)
(what I mean is there is no use looking inside the framework for a
problem until we know the difference that causes it...)

On Thu, Apr 8, 2010 at 2:23 PM, Mike Henke <henk...@gmail.com> wrote:
> and the application.wheels.showErrorInformation is true.
>

[Mike Henke]

On Apr 8, 10:57 am, Per Djurner <per.djur...@gmail.com> wrote:
> You said it worked on "dev" right?

Yes the code works on dev. It also works on qa but only on the newly
created table. It doesn't work on the original table in qa. I hate to
move the code to production and experience the same issue since drop/
recreating the existing production table doesn't seem to be a good
option.

> What's the difference between "dev" and "qa"?

It seems to be something with how Len(loc.toAppend) is created within
the $addSelectClause. In the working table, it is 56, but 0 in the
not working table.

[Per Djurner]

No, I meant what is the difference between the "dev" and "qa" setups
on your servers?
Wheels version, Wheels environment mode, Railo/Adobe version, database etc etc.

[Mike Henke]

> Wheels version, Wheels environment mode, Railo/Adobe version, database etc etc.

1.0.3, Development, Adobe ColdFusion 8,0,0,176276 , Oracle 10g

Let's forget I mentioned dev. I am working on one database server
with one server instance and codebase. The database had a table which
Wheels doubles the actual columns for loc.classData.propertyList and
arguments.select. I recreated the table with a different name and this
new table doesn't duplicate the columns for those variables. I can't
find anything different with the tables but Wheels is some reason
doubling the columns in loc.classData.propertyList

In /wheels/model/crud.cfm

line 974 arguments.select is blank
line 986 arguments.select has double the actual columns

[Per Djurner]

Ok, can you tell me the table names and post the contents of the init
functions (if you have any)?

[Mike Henke]

CREATE TABLE PM_PROGRAMFUNDINGTYPE (
PM_PROGRAMFUNDINGTYPEID NUMBER(22 , 0) NOT NULL,
PROGRAMFUNDINGTYPE VARCHAR2(1000) NOT NULL,
DATEUPDATED DATE NOT NULL
);

CREATE UNIQUE INDEX PM_PROGRAMFUNDINGTYPE_PK ON PM_PROGRAMFUNDINGTYPE
(PM_PROGRAMFUNDINGTYPEID ASC, PM_PROGRAMFUNDINGTYPEID ASC);

ALTER TABLE PM_PROGRAMFUNDINGTYPE ADD CONSTRAINT
PM_PROGRAMFUNDINGTYPE_PK PRIMARY KEY (PM_PROGRAMFUNDINGTYPEID);

init for cfc mode of this table
<cffunction name="init">
<cfset datasource(get('myapp').mySecondDatabase) />
<cfset table("pm_programFundingType") />
<cfset property(name="fundingtypeid",
column="PM_PROGRAMFUNDINGTYPEID") />
<cfset property(name="fundingtype", column="PROGRAMFUNDINGTYPE") />
</cffunction>

I can email you any dumps of variables offline if you request any.

[wfisk]

I am not really following this discussion but you can get duplicate
columns in Oracle if you have two owners or something like that for
the same table. In order to get the columns dbinfo must be querying
all_tab_columns (or user_tab_columns?). You could try querying
all_tab_columns (where UPPER(table_name) = "YOUR_TABLE_NAME") and see
if the columns appear twice.

William

[Per Djurner]

Interesting... Mike, maybe you can run cfdbinfo against the table and
see if it returns duplicate columns for that table?

[wfisk]

I must admit I would be surprised if cfdbinfo did return duplicates;
but I know that if you are querying all_tab_columns yourself you do
have to include your username (as the "owner" I think).

On Apr 10, 9:01 am, Per Djurner <per.djur...@gmail.com> wrote:
> Interesting... Mike, maybe you can run cfdbinfo against the table and
> see if it returns duplicate columns for that table?
>

[Mike Henke]

Good call William. cfdbinfo does pull back duplicate rows. I am not
much of an oracle person, but it I did a query on all_tab_columns for
my table. It appears the table is created twice with different
owners. I am not sure how to keep CF/Wheels from "jumping" owners?

[Per Djurner]

Wheels relies on cfdbinfo for its info.

If cfdbinfo returns duplicate column names then that is the problem and not any "jumping" of owners, right?

[Mike Henke]

On Apr 12, 10:00 am, Per Djurner <per.djur...@gmail.com> wrote:
> Wheels relies on cfdbinfo for its info.
> If cfdbinfo returns duplicate column names then that is the problem and not
> any "jumping" of owners, right?

I'll submit a ticket to Adobe but is there a way Wheels can fix this
issue until Adobe does?

[Mike Henke]

Ticket for Adobe: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=71479

[Chris Peters]

Maybe you should specify to Adobe that it's happening with Oracle and which version. This doesn't happen to the rest of us with MySQL and SQL Server from what I understand.

[Chris Peters]

Ohh, I guess they do that in the "Steps to Reproduce" tab.
Nevermind. :)

On Apr 12, 10:35 am, Chris Peters <ch...@clearcrystalmedia.com> wrote:
> Maybe you should specify to Adobe that it's happening with Oracle and which
> version. This doesn't happen to the rest of us with MySQL and SQL Server
> from what I understand.
>

> On Mon, Apr 12, 2010 at 10:32 AM, Mike Henke <henkem...@gmail.com> wrote:
> > Ticket for Adobe:

> >http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#...

>
> > On Apr 12, 10:22 am, Mike Henke <henkem...@gmail.com> wrote:
> > > On Apr 12, 10:00 am, Per Djurner <per.djur...@gmail.com> wrote:> Wheels
> > relies on cfdbinfo for its info.
> > > > If cfdbinfo returns duplicate column names then that is the problem and
> > not
> > > > any "jumping" of owners, right?
>
> > > I'll submit a ticket to Adobe but is there a way Wheels can fix this
> > > issue until Adobe does?
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "ColdFusion on Wheels" group.
> > To post to this group, send email to cfwh...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > cfwheels+u...@googlegroups.com<cfwheels%2Bunsu...@googlegroups.com>

[Per Djurner]

Yes, we can get rid of the duplicated columns easily but not sure what to do about the incorrect and/or additional columns it returns.

I don't use Oracle myself so I can't do any testing on it either.

--

[Doug Giles]

Mike,

Could you create an Oracle synonym for the "duplicated" table? If the
table is 'cars', maybe you could create a synonym similar to
'my_app_cars', and then point your model to the synonym. Not pretty,
but you could revert to the table name if/when the issue is resolved.
Just a thought ...

I suspect that renaming the tables is not possible, or you would have
done that already. :-)

[wfisk]

It was just an idea, I've had this problem when I query
all_tab_columns.

cfdbinfo has an attribute "username" and if you set that to the
correct the
username then you should not get duplicates. So this might be back to
cfhweels. If the username is specified somewhere that cfwheels then
maybe cfwheels should include it when it calls cfdbinfo.

[Mike Henke]

The dbms is specified in the ticket for adobe and wheels.

Some how Wheels is returning the correct columns for
loc.addedpropertiesbymodel.fundingType and classdata.properties /
classes.properties .

On Apr 12, 10:00 am, Per Djurner <per.djur...@gmail.com> wrote:
> Wheels relies on cfdbinfo for its info.
> If cfdbinfo returns duplicate column names then that is the problem and not
> any "jumping" of owners, right?

[Per Djurner]

It does include it when the following has been set in config/settings.cfm:

set(dataSourceUserName="x")

set(dataSourcePassword="x")

Mike, have you set those variables?

[Mike Henke]

No I don't have those set anywhere but I do have <cfset
datasource(get('myapp').mySecondDatabase) /> in the init for this
model cfc.

[Per Djurner]

Try passing in "username" and "password" to that datasource function then.

--

[ryanhoppitt]

I was having a different issue a couple of months ago with Oracle and
cfdbinfo, but that was mainly due to trying to use the thin client
JDBC drivers for Oracle rather than the ones Adobe supplies with CF
Enterprise.

It's a completely different issue to yours but I began writing a
plugin to override $dbinfo with a query to the ALL_TAB_COLUMNS table.
It might need some more work but if you are looking to get this
working on Wheels while you wait for Adobe this might give you a
starting point.

You could try adding a condition to check that the Owner field matches
#arguments.username# or something. I haven't uploaded it to the Wheels
website cause it's still a work in progress but feel free to use it if
it's helpful to you:-
http://cfwheels-dbmigrate.googlecode.com/files/OracleDBInfo-0.1.zip