No, in our case a there is only a 1-to-1 mapping between Person / Users.
Now, this isnt a normalized design and I re-did my DB/reactor.xml to use a
"UserPersonLink" table and I got this to work in Reactor (but it kind of
sucks to have to use a "getUserPersonLinkIterator.getQuery()" when you know
you are only getting one row from this link table and then shove this
"UserID" off to the UserRecord.setID() method....
I wanted to get my original design going because it seems so simple and its
better to start small, right?
Anyways, I referred back to my original reactor.xml and put the cfabort in
the abstractGateway.cfc method, this is what it returns:
SELECT [Person].[ID] AS [ID] , [Person].[FirstName] AS [FirstName] ,
[Person].[MiddleInit] AS [MiddleInit] , [Person].[LastName] AS [LastName] ,
[Person].[BirthDate] AS [BirthDate] , [Person].[Gender] AS [Gender] ,
[Person].[CreationDate] AS [CreationDate] , [Person].[Approved] AS
[Approved] , [Person].[Active] AS [Active] FROM [appName].[dbo].[Person] AS
[Person] WHERE [Person].[ID] = ?
so it doesnt really return anything useful because it bombs out on the first
gateway lookup and not the second one that we want (the lookup into the User
table)...attached is my debugging output for all the queries ran..
Thanks alot!
/Cody
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Doug
Hughes
Sent: Monday, February 20, 2006 5:47 PM
To: [email protected]
Subject: [Spam] RE: [Reactor For CF] [Spam] Mapping question
Now, first off, wouldn't a person have many Users? And a User would haveOne
Person?
I'd try this:
<object name="Person">
<hasMany name="User">
<relate from="ID" to="PersonID" />
</hasMany >
</object>
But honestly, this shouldn't make a difference. There must be something
simple that's going wrong here. Like the personID is really in the person
table? It maybe it's crossing the two generic "ID" columns.
Any chance you can do this: Go into the abstractGateway.cfc and throw a
cfabort right before the </cfquery> this should cause the query to be
output. I'd love to know if there's anything weird about this.
Doug
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cody
Caughlan
Sent: Monday, February 20, 2006 8:20 PM
To: [email protected]
Subject: [Spam] [Reactor For CF] [Spam] Mapping question
Hello,
This is concerning MSSQL. I have 2 tables, "Person" and "User".
Person
------
ID (int, primary key, identity)
FirstName, etc...
User
----
ID (int, primary key, identity)
PersonID (foreign key back to Person table) UserName, etc...
My reactor.xml looks like this:
<object name="Person">
<hasOne name="User">
<relate from="ID" to="PersonID" />
</hasOne>
</object>
This *doesnt* work, for some unknown reason. When I add a non-primary key
field to my Person table (I tried "UserID") and I manually populate it with
the appropriate primary key ID from the "Users" table and switch my
reactor.xml to:
<relate from="UserID" to="ID" />
it *does* work. Its almost as if, if I use a non-primary key in the "object"
XML declaration it will work, but if I specify its identity column it will
not work.
When I say it "does not" work, I mean that that when I call the
PersonRecord.getUserRecord() method, the returned UserRecord is always empty
and doesnt contain the appropriate data. Yes, I made sure the keys are
correct (correct values in the DB). Note, that I *can* read the correct data
from the PersonRecord, it just breaks down when trying to read the relevant
UserRecord.
I am sure I am doing something stupid but I have been looking at this for
quite some time to no availl.
Thanks
/Cody
-- Reactor for ColdFusion Mailing List -- [email protected]
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
-- Reactor for ColdFusion Mailing List -- [email protected]
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
-- Reactor for ColdFusion Mailing List -- [email protected]
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
SQL Queries
qFields (Datasource=JabicoInside_Meridian, Time=0ms, Records=9) in
E:\Inetpub\wwwroot\Meridian_M1\Reactor\data\mssql\ObjectDao.cfc @ 18:01:50.050
SELECT
col.COLUMN_NAME as name,
CASE
WHEN colCon.CONSTRAINT_NAME IS NOT NULL
THEN 'true'
ELSE 'false'
END as primaryKey,
CASE
WHEN
columnProperty(object_id(col.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity') > 0
THEN 'true'
ELSE 'false'
END AS [identity],
CASE
WHEN col.IS_NULLABLE = 'No' THEN 'false'
ELSE 'true'
END as nullable,
col.DATA_TYPE as dbDataType,
CASE
WHEN
ISNUMERIC(col.CHARACTER_MAXIMUM_LENGTH) = 1 THEN col.CHARACTER_MAXIMUM_LENGTH
ELSE 0
END as length,
col.COLUMN_DEFAULT as [default]
FROM INFORMATION_SCHEMA.COLUMNS as col LEFT JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tabCon
ON col.TABLE_NAME = tabCon.TABLE_NAME
AND tabCon.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as
colCon
ON col.COLUMN_NAME = colCon.COLUMN_NAME
AND col.TABLE_NAME = colCon.TABLE_NAME
AND colCon.CONSTRAINT_NAME =
tabCon.CONSTRAINT_NAME
WHERE col.TABLE_NAME = ?
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = Person
qGet (Datasource=JabicoInside_Meridian, Time=0ms, Records=1) in
E:\Inetpub\wwwroot\Meridian_M1\Reactor\base\abstractGateway.cfc @ 18:01:51.051
SELECT
[Person].[ID] AS [ID]
, [Person].[FirstName] AS [FirstName]
, [Person].[MiddleInit] AS [MiddleInit]
, [Person].[LastName] AS [LastName]
, [Person].[BirthDate] AS [BirthDate]
, [Person].[Gender] AS [Gender]
, [Person].[CreationDate] AS [CreationDate]
, [Person].[Approved] AS [Approved]
, [Person].[Active] AS [Active]
FROM
[Meridian].[dbo].[Person] AS [Person]
WHERE
[Person].[ID] =
?
Query Parameter Value(s) -
Parameter #1(cf_sql_integer) = 3
qFields (Datasource=JabicoInside_Meridian, Time=0ms, Records=5) in
E:\Inetpub\wwwroot\Meridian_M1\Reactor\data\mssql\ObjectDao.cfc @ 18:01:51.051
SELECT
col.COLUMN_NAME as name,
CASE
WHEN colCon.CONSTRAINT_NAME IS NOT NULL
THEN 'true'
ELSE 'false'
END as primaryKey,
CASE
WHEN
columnProperty(object_id(col.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity') > 0
THEN 'true'
ELSE 'false'
END AS [identity],
CASE
WHEN col.IS_NULLABLE = 'No' THEN 'false'
ELSE 'true'
END as nullable,
col.DATA_TYPE as dbDataType,
CASE
WHEN
ISNUMERIC(col.CHARACTER_MAXIMUM_LENGTH) = 1 THEN col.CHARACTER_MAXIMUM_LENGTH
ELSE 0
END as length,
col.COLUMN_DEFAULT as [default]
FROM INFORMATION_SCHEMA.COLUMNS as col LEFT JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tabCon
ON col.TABLE_NAME = tabCon.TABLE_NAME
AND tabCon.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as
colCon
ON col.COLUMN_NAME = colCon.COLUMN_NAME
AND col.TABLE_NAME = colCon.TABLE_NAME
AND colCon.CONSTRAINT_NAME =
tabCon.CONSTRAINT_NAME
WHERE col.TABLE_NAME = ?
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = User
qGet (Datasource=JabicoInside_Meridian, Time=0ms, Records=0) in
E:\Inetpub\wwwroot\Meridian_M1\Reactor\base\abstractGateway.cfc @ 18:01:51.051
SELECT
[User].[ID] AS [ID]
, [User].[PersonID] AS [PersonID]
, [User].[UserName] AS [UserName]
, [User].[Password] AS [Password]
, [User].[ChangePassOnLogin] AS [ChangePassOnLogin]
FROM
[Meridian].[dbo].[User] AS [User]
WHERE
[User].[ID] =
?
Query Parameter Value(s) -
Parameter #1(cf_sql_integer) = 0
-- Reactor for ColdFusion Mailing List -- [email protected]
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
