ManyToOne and different key types

[Baz]

Hello,

I have an object with 2 ManyToOne relationships. The first relationship uses an integer key, while the second relationship uses a varchar key. If I try to load up the main object, I receive an MS-SQL error stating something along the lines of it cannot cast the type varchar onto type int (I don't have the exact error right now). A little digging and I noticed that the final query being executed against the database tries to UNION ALL each ID column into one big ID column, regardless of type, thereby causing the error. Has this been encountered before?

System:
- Windows XP
- CF8
- MSSQL 2005 Express

Baz

[Mark Mandel]

So Baz -

You have 2 columns names which have identical names, but are different
data types?

Yeah... I know what causes that... to try and and shorted 'width' of
the SQL, Transfer will line up same named columns in the big UNION
statement.

I figured it was a fair assumption that people would never have 2
columns of the same name of different data types...

Guess I was wrong.

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Mark Mandel]

Simple workaround Baz - stick a lazy='true' on one of the manytoone's,
and you'll be fine.

Mark

[Baz]

Hey Mark, the column names aren't the same actually. Here are the ManyToOne definitions:

     <!-- many to one -->
     <manytoone name="Institution">
          <link to="Guideline.Institution" column="institution_id" />
     </manytoone>
     <manytoone name="Disease">
         <link to="Guideline.Disease" column="disease_id" />
     </manytoone>

Institution_id is an integer, whereas disease_id is a varchar, and theyline-up in the final SQL.

I will look into your suggested fix, thanks!

Baz

[Mark Mandel]

Baz,

Then that shouldn't be a problem then.... I thought it was something else.

If you get a chance, can you forward me the generated SQL, the config,
and the error message?

Thanks!

Mark