Hi,
I get data from an oracle database (8i) by filling a dataset. <ds.fill>.
When I check the data in Oracle, the lines ends with "0D 0A"(CR/LF).
When I check the data received in the dataset, the lines ends with "0D" (CR).
This might be the cause of a problem I'am having right now.
Does anybody know about why it appens and what can I do to keep "0D 0A"
Thanks,
Sylvain
What is the data type of the column in Oracle?
Which .NET data provider are you using (System.Data.OracleClient, Oracle's ODP.NET, etc)? If you have a simple app where this reproduces, it would help to try both providers so you try to isolate if it's a provider problem, or if it's lower than that, like in the client layer. I assume you are not using either of the System.Data.OleDb or System.Data.Odbc providers, but if you are, you should definitely try with one of the providers designed specifically for Oracle.
If you can reproduce this with System.Data.OracleClient, I can investigate further if you can provide some more information on the data type you're using, and the version of .NET (1.x or 2.0). How was the data inserted into Oracle in the first place?
Thanks,
Sarah
Hi sarah,
Our new development with 10g use ODP but the rest is with .oleDb. So I am using : OleDbCommand,OleDbConnection, OleDbDataAdapter, OleDbParameter and a DataSet.
I have done the test using both OracleClient and ODP and got the same result.
The version of the framework is 1.1
The data type in the database is varchar2
The data is put in the database by an application in smalltalk under visual works using "top link".
thanks
Sylvain
Given the fact that you seem to see this behavior with all managed providers you have tried, it's unlikely to be a bug in any of those providers. Most likely all of the providers you have tried are using Oracle's OCI layer, so it's possible the problem is there, but I tend to believe that's not the case.
However, one thought I have is that this could be related to how .NET represents strings (not sure, that's just an idea off the top of my head). How did you determine that the 0A character was removed from your string? Also, do you have any way to test retreiving the data outside of your .NET app? If you haven't done so, one thing you could do would be to use Oracle's DUMP function in SQL*Plus, to verify the data in the database itself.
Thanks,
Sarah
Hi,
I checked the data in the database using PLSQL DEV. when putting the view in Hexa, I see the 0D 0A in my data.
If I make an export of the data, the 0A is kept.
When I get the data using the framework, I check the result in a editor that show in hexa. In my case, I use Ultra Edit-32.
Even before checking the data, when I open the file with Ultra Edit-32, it's asking me if I want to convert to DOS (CR\LF) format because the file being opened is not compatible with DOS. (CR).
Someone in the office asked me to try with a dataReader instead of using a dataset, got the same result.
Sylvain
How did you get the result into Ultra Edit-32? I.e. how did you get it from the .NET string? If you are outputting it to a file and then viewing the file, we don't know if the character is getting truncated during the file write, or if it's incorrect in the DataSet/DataReader itself. In your original post, you mentioned that the data was incorrect when you check it in the DataSet, so I was trying to determine exactly how you had checked it. I assumed you had looked at the bytes in the debugger somehow, or were using one of the String methods to access the individual bytes.
I had a chance to try this out myself this afternoon, and I'm not seeing any problems with the actual data in the string itself. Here's what I did:
(1) I have a test table in Oracle that was created like this:
CREATE sparraTestCRLF (COL1 VARCHAR2(50))
(2) The following .NET code is being used to both insert the data (to make sure I get the right characters inserted) and to read it back:
using (oraConn = new OracleConnection(@"Data Source=myserver;User Id=myuid;Password=mypwd;"))
{
oraConn.Open();
OracleCommand cmdInsert = new OracleCommand("INSERT INTO sparraTestCRLF VALUES ('Sarah\n\r')", oraConn);
cmdInsert.ExecuteNonQuery();
OracleCommand cmdSelect = new OracleCommand("SELECT * FROM sparraTestCRLF", oraConn);
//DataReader
OracleDataReader dr = cmdSelect.ExecuteReader();
dr.Read();
string stringData = dr.GetString(0);
Console.WriteLine("#{0}#", stringData);
char[] charData = stringData.ToCharArray();
for (int i = 0; i < charData.Length; i++)
{
Console.WriteLine("{0}", (int)charData
);
}
}
(3) The output looks like this:
#Sarah
#
83
97
114
97
104
10
13
The first line of output is surrounded with # characters so I can clearly see if I have a line feed or not in the middle of the data. It shows I do, but that doesn't necessarily prove it's both CR and LF. So I also copied the data to a character array, and printed out the actual character codes for all the characters. The 10 and 13 at the end show the CR and LF. If the data were being truncated here, this would show it.
This is what I suggest you do in your case as well. You have verified that data is okay in the database itself, and you know it's bad by the time it gets to the output file (assuming that is what you are doing). We don't know where in the middle it's going wrong. I think my example proves that in general, this should work and the .NET string should contain the characters. However, we don't know if that is the same in your case, or if maybe the last byte is getting truncated during the file output.
If you can verify that the data is okay in the string itself (like I did here), and you are in fact writing to a file, can you post the code that writes to the file?
Thanks,
Sarah
Hi,
For the dumping to a file, I used dataSet.writeXML(filePath). Alos try with a streamWriter.
I have run a test like yours and got the same problem. Instead of 13 10 I only have 13. (CR/LF is 13 10, not 10 13 )
Here is the code I split the data because it has over 15000 char.
da.SelectCommand = commande
da.Fill(envi.ds)
''
''
Dim i As Integer
Dim stringData As String = envi.ds.GetXml
Dim a As String() = Split(stringData, "telephoneRessourceResponsableBureauTypeAutre,")
Dim b As String() = Split(a(1), "1386 BARLOCHAN RD GRAVENHURST")
Console.WriteLine("#{0}#", b(0))
Dim charData As Char() = b(0).ToCharArray()
For i = 0 To charData.Length - 1
Console.WriteLine("{0}", Microsoft.VisualBasic.AscW(charData(i)))
Next
''
''
Here is the result :
#titreAvisHabitation
""," "," "," ","R R 2 ","#
116
105
116
114
101
65
118
105
115
72
97
98
105
116
97
116
105
111
110
13
34
34
44
34
32
34
44
34
32
34
44
34
32
34
44
34
82
32
82
32
50
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
32
34
44
34
Thanks,
Sylvain
Hi Sylvain,
I tried switching around the CR and LF characters, but I still cannot reproduce the failure. Since I don't have your actual data to investigate with, could you try the simple example I posted before, and see if you still see the same problem, or if this is specific to your data? I'm sure we are not using identical versions of the Oracle client and server, so it's possible that I would never see the problem based on version differences alone. This way, we can make sure we're on the same page as far as the actual data being used in the test.
Another thing to look at is an OCI client trace, to see what's actually coming back from Oracle. Since the data is correct in the database itself, I would assume it's okay once it reaches OCI, but who knows. A simple way to get an OCI trace is to add the following to your sqlnet.ora file (DISCLAIMER: This is how I turn on OCI tracing -- Oracle may have another recommended method which I would defer to, since it's their client):
trace_level_client=16
trace_file_client=client
trace_directory_client=c:\OraCliTraces
trace_unique_client=true
The trace_directory_client folder should already exist before you start tracing. When you run your app, you'll get a .trc file in that location. I find these traces hard to read because I'm not familiar with the format, but you can easily see the actual data coming back from the server. A network trace would serve essentially the same purpose, but this shows you what Oracle's network layer thinks it received. In my test scenario, I see the following in my trace:
(4600) [29-SEP-2006 17:19:43:968] nsprecv: 07 07 53 61 72 61 68 0D |..Sarah.|
(4600) [29-SEP-2006 17:19:43:968] nsprecv: 0A 00 00 00 00 04 01 01 |........|
You can see the 0D 0A are present. If you can spot those bytes in your scenario, that still doesn't tell us exactly where they got dropped, but at least you can verify that OCI recognizes them at this level. With System.Data.OracleClient, we will receive these bytes from OCI into a native buffer, along with the length of the data. We then use the System.Runtime.InteropServices classes to convert that into a string. I'm sure ODP.NET and the other Microsoft managed providers do this in a slightly different manner, but all are having the same issue, which makes me think that the data is probably already truncated by the time they receive it in their row buffers.
We are isolating where the problem might be, but we're not quite there yet. If you can try the simple data I provided, it may help to narrow it down even further.
Thanks,
Sarah
Hi Sarah,
I have run the same test as you but in vb.net and the 0D 0A was kept. So it seems like the problems does not happen when the insert is done with the framework.
So i have done two more tests.
Test 1
Insert the data directly from plSQL dev. When I get the data, I can see in the trace that the 0D (CR) is not in there. Only the 0A (LF) is kept.
Test 2
Since the data is insert for real using Oracle7 in visualWorks, I'have done a test with it.
So I insert a string using visual works.
Now, when I look in the trace, I can see that the 0D is kept and that it is the 0A that is gone.
So the results seem to change depending of who made the insert.
Still searching!
Thanks
Sylvain
Hi Sylvain,
That's very interesting that you can change the behavior depending on how the data is inserted. I can't think of any reason why that would be.
My recommendation at this point is to try to post this in Oracle's forums to see if they have any suggestions. If you are seeing the difference in behavior even in the OCI traces (I assume that's what you mean when you say trace), you are definitely going to see it in any applications built on top of OCI. Since the communcation between OCI and the backend database server is controlled by Oracle components, they may be able to help explain what's happening.
If you get a response from them, it would be great if you could post back here with the information.
Thanks,
Sarah
