Oracle DataReader Issue

[JustTryingMyBest]

I am having a problem with the Oracle DataReader. The issue is that when I have a text field which has the exact same value for a particular field, the datareader is returning garbage in the second and subsequent rows for that value.

Say for instance I have a field called "Description" and in the 7th, 8th and 9th row, the value of "Description" is "Bird Dog". When I read the 7th record and examine the contents of "Description" is see "Bird Dog", but when I read and examine the contents of record 8, I see somethin like the following: "Bir\0 \0\0\0". I see the exact same "garbage" for the 9th row. When I get to the 10th row, it the value returned is what is expected.

This happens on a consistent basis accross a variety of different records being retrieved.

I have run the query in TOAD and examined the results and they are as they should be in "Toad", but when I run that same query in ADO.Net, my results are goofy.

Has anyone experienced this problem? Is there a cure?

 

[cappmgr]

Can you post the code? I'm currious.
Marty

 

[JustTryingMyBest]

OK, here is the code....

You will see a number of "If" statements which are examining the value returned. I know that one of 4 values will be returned and I am examining the first 2 characters being returned because starting with the 4th or so character I start getting the "\0" values being returned.

I also am executing a method on string values which will look at the value being returned to see if it is null and changing the value to " " in those situations. I provided that method as well....

while( dr.Read() )
{
DataRow drow = dt.NewRow();
string val = dr.GetString(0);
if (val.ToUpper() == "USAGE" | val.ToUpper()
== "PERFORMANCE" | val.ToUpper() == "CUSTOMER
SATISFACTION" | val.ToUpper() == "FINANCIAL" |
val.ToUpper() == "BUSINESS OPERATION")
{}
else
{
if (val.Substring(0,2).ToUpper() == "US")
{ val = "Usage"; }
else if (val.Substring(0,2).ToUpper() == "BU")
{ val = "Business Operation"; }
else if (val.Substring(0,2).ToUpper() == "CU")
{ val = "Customer Satisfaction"; }
else if (val.Substring(0,2).ToUpper() == "FI")
{ val = "Financial"; }
else if (val.Substring(0,2).ToUpper() == "PE")
{ val = "Performance"; }
else
val = "Unkown";
}
drow["Metric_Cat"]= val;
drow["Metric"] = RetrieveColumnValue(dr,1);
drow["D_M_1"] = RetrieveColumnValue(dr,2);
drow["T_M_1"] = RetrieveColumnValue(dr,3);
drow["D_M_2"] = RetrieveColumnValue(dr,4);
drow["T_M_2"] = RetrieveColumnValue(dr,5);
drow["D_M_3"] = RetrieveColumnValue(dr,6);
drow["T_M_3"] = RetrieveColumnValue(dr,7);
drow["D_Q_1"] = RetrieveColumnValue(dr,8);
drow["T_Q_1"] = RetrieveColumnValue(dr,9);
drow["D_Q_2"] = RetrieveColumnValue(dr,10);
drow["T_Q_2"] = RetrieveColumnValue(dr,11);
drow["D_Q_3"] = RetrieveColumnValue(dr,12);;
drow["T_Q_3"] = RetrieveColumnValue(dr,13);
drow["D_Q_4"] = RetrieveColumnValue(dr,14);
drow["T_Q_4"] = RetrieveColumnValue(dr,15);
drow["D_YTD"] = RetrieveColumnValue(dr,16);
drow["T_YTD"] = RetrieveColumnValue(dr,17);
drow["AsOfMonth"]= RetrieveColumnValue(dr,18);
drow["C_M_1"] = RetrieveColumnValue(dr,19);
drow["C_M_2"] = RetrieveColumnValue(dr,20);
drow["C_M_3"] = RetrieveColumnValue(dr,21);
drow["C_Q_1"] = RetrieveColumnValue(dr,22);
drow["C_Q_2"] = RetrieveColumnValue(dr,23);
drow["C_Q_3"] = RetrieveColumnValue(dr,24);
drow["C_Q_4"] = RetrieveColumnValue(dr,25);
drow["C_YTD"] = RetrieveColumnValue(dr,26);
drow["Achiv_M1"] = RetrieveColumnValue(dr,27);
drow["Achiv_M2"] = RetrieveColumnValue(dr,28);
drow["Achiv_M3"] = RetrieveColumnValue(dr,29);
drow["Achiv_Q1"] = RetrieveColumnValue(dr,30);
drow["Achiv_Q2"]= RetrieveColumnValue(dr,31);
drow["Achiv_Q3"]= RetrieveColumnValue(dr,32);
drow["Achiv_Q4"] = RetrieveColumnValue(dr,33);
drow["Achiv_YTD"] = RetrieveColumnValue(dr,34);
decimal temp = dr.GetDecimal(35);
drow["Metric_Cat_Sort"] = Convert.ToInt32(temp.ToString());
dt.Rows.Add(drow);
}
dr.Close();
return dt;


private string RetrieveColumnValue (IDataReader dr, int
colNbr)
{
if (dr[colNbr] == DBNull.Value)
{ return ""; }

return StripSpecialCharacters(dr.GetString(colNbr),
false).Trim();

}

 

[cappmgr]

Not sure if it matters when this condition
if (val.ToUpper() == "USAGE" | val.ToUpper()
== "PERFORMANCE" | val.ToUpper() == "CUSTOMER
SATISFACTION" | val.ToUpper() == "FINANCIAL" |
val.ToUpper() == "BUSINESS OPERATION")
{}
is met you do nothing but you do create a new row everytime.
Can you put
DataRow drow = dt.NewRow();
after the else?
Is StripSpecialCharacters part of .NET never seen it before.

for what it's worth here is a code snippet I use just to make sure I am getting back what I think i should be.

DataTable dt = new DataTable();
try 
{
	myConnection.Open();
	OracleDataReader dr;
	dr = myCommand.ExecuteReader(); 
	for (int i = 0; i < dr.FieldCount; ++i)
	{
		dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
	}
	while (dr.Read()) 
	{
		DataRow drow = dt.NewRow();
		for (int i = 0; i < dr.FieldCount; ++i)
		{
			drow[i] = dr.GetValue(i);
		}
		dt.Rows.Add(drow);					
	}
	dr.Close();
	return dt;
}

GetValue will not die if the value is null.
hth,
Marty

 

[JCruz063]

JustTryingMyBest,
You are advised by the literature to use the strongly-typed GetXXXX methods of the DataReader class as they offer better performance. However, just like Marty, I'm a bit puzzled as to why your code would behave like that and nothing comes to my mind except replacing the line:

[tt]string val = dr.GetString(0);[/tt]

with the line
[tt]
string val = dr.GetValue(0).ToString();[/tt]

I would just try it out to see if it makes a difference, although I haven't seen a difference in the past.

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...