The Daily WTF: Curious Perversions in Information Technology

brandonh6k · 2005-03-03 Reply Admin

codewulf:
With wonderful, beautiful .NET, even if you specify column names in your select statement you will sometimes get random column reordering unless you use GetOrdinal("columnName") to retreive the column index.

What?!? I find that hard to believe. I've been using .NET for a while now and I've never had to worry about which order my columns came back in if I used explicit column names in the query (or stored proc).

Regarding the anonymous post about bit columns, SQL server does have a bit column type and stores up to 8 bit columns together (well look at that, a byte) to save on storage space. You can't tell the difference from outside the DB though... It's amazing what's actually in Books Online... [^o)]

2005-03-07 Reply Admin

Well... I’ve found the following normally works pretty well (particularly when there is a different team writing the stored procs and all things db like)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

if the storedproc only returns a single row, something like..

[code language="c#"]
<FONT color=#000080 size=1>someString = dbReader.GetString(dbReader.GetOrdinal("myCol"));</FONT>
[/code]

But when populating a collection, to get around the problems of repetitive coloumn lookups.

[code language="c#"]
<FONT color=#000080 size=1>int myColIndex = 
dbReader.GetOrdinal("myCol"))</FONT>
<FONT color=#000080 size=1>using (dbReader)</FONT>
<FONT color=#000080 size=1>{</FONT>
<FONT color=#000080 size=1>   while (dbReader.Read)</FONT>
<FONT color=#000080 size=1>   {</FONT>
<FONT color=#000080 size=1>      SomeObject someObject = new SomeObject();</FONT>
<FONT color=#000080 size=1>      someObject.SomeString = dbReader.GetString(myColIndex);</FONT>
<FONT color=#000080 size=1>      ... add object to list / array / hashtable.. </FONT>
<FONT color=#000080 size=1>   }</FONT>
<FONT color=#000080 size=1>}</FONT>
[/code]

I've also worked on a system, in which an OR Mapping data layer was auto-generated with each DB release, as the data layer was generated by the DB it directly used the correct column indexes, which caused no problems as the DB was released to the Dev's in controlled releases.<o:p></o:p>

2005-03-07 Reply Admin

When using Oracle DB and Oracle forms, sorting the table columns is not such a bad idea.
Especially when compiling the Form against another database than running it against.
Imagine
   1) database A has table columns (A, B, C) all numbers
   2) database B has table columns (A,C, B) all numbers

Example data for table is (a=3, b=4, c=5).

We now create a form which calculates and displys following formula : (A+B)*C

The form compiled and run against A comes up with : 35
The form compiled and run against B comes up with : 35

BUT

The form compiled against A and run against B comes up with : 32   !!

During compilation oracle forms appearantly uses offsets instead of names.....

In this case alphabetical sorting is the only way of making sure that both A & B have the same ordering....

(I do not like this either, but sometimes things are not so easy or 'nice')...

Regards, Fred

P.S. Ofcourse there are workarounds for this.....

Database Ch-Ch-Ch-Ch-Changes

Leave a comment on “Database Ch-Ch-Ch-Ch-Changes”