ntext Field Type & SqlDataReader.GetBytes()

[Maan Bsat]

Hi,

I have a table with an ntext column. Calling the
SqlDataReader.GetBytes() functions says that it's length 0 (when giving
it a Nothing buffer) and doesn't give any data when I give it a buffer.
If I change the column type to text instead of ntext, it works fine! Is
there anything I'm missing here?

Using GetChars() is even stranger: it says I'm giving it the wrong
column number no matter what number I give it... Thanks,

Maan

 

[Jon Skeet [C# MVP]]

I have a table with an ntext column. Calling the
SqlDataReader.GetBytes() functions says that it's length 0 (when giving
it a Nothing buffer) and doesn't give any data when I give it a buffer.
If I change the column type to text instead of ntext, it works fine! Is
there anything I'm missing here?

Calling GetBytes on an ntext field sounds like a bad idea - character
and binary data shouldn't be mixed, IME.

Using GetChars() is even stranger: it says I'm giving it the wrong
column number no matter what number I give it... Thanks,

Now that's odd. I'd certainly expect that to work.

 

[Maan Bsat]

I should have noted that I'm using SequentialAccess. Calling GetChars()
with the proper column index systematically gives me:

Invalid attempt to read from column ordinal '2'. With
CommandBehavior.SequentialAccess, you may only read from column ordinal
'3' or greater.

at line

_lLength = _oDR.GetChars(2, 0, Nothing, 0, 0)
(to obtain the length of the data in the field).

The query used is:

SELECT TemplateId, Name, TemplateXML FROM Template

which obviously only has 3 columns (0, 1 and 2).

If instead I give it column number 3:

Index was outside the bounds of the array.

(which isn't too surprising).

So right now I'm forced to use normal (non-sequential access), which
works, but I don't like the idea that it's all in memory at once (which
will happen on a server with an already fairly high load). Is there
something wrong in my approach?

Thanks,

Maan

 

[Jon Skeet [C# MVP]]

I should have noted that I'm using SequentialAccess. Calling GetChars()
with the proper column index systematically gives me:

Invalid attempt to read from column ordinal '2'. With
CommandBehavior.SequentialAccess, you may only read from column ordinal
'3' or greater.

Have you previously read from column 2 at all (with GetBytes, for
instance)?

If you could post a short but complete program which demonstrates the
problem, I'd be happy to set up a small test database on my box to try
to reproduce it.

 

[Maan Bsat]

Jon,

Thanks for your help. Here is a little example. First, the DB creation:

CREATE DATABASE TempBlobTest
USE TempBlobTest

CREATE TABLE Table1 (
NormalString VARCHAR(100),
BigNText NTEXT
)

INSERT INTO Table1 VALUES ('Test 1', N'This is supposed to be a very
long unicode string...')
INSERT INTO Table1 VALUES ('Test 2', N'This is supposed to be another a
very long unicode string...')

Then, the following VB.NET code (for a console app):

Sub Main()
Dim oCmd As New SqlCommand
Dim oDR As SqlDataReader
Dim b() As Char = New Char(15) {}

oCmd.Connection = New SqlConnection(_sConnString)
oCmd.Connection.Open()

oCmd.CommandText = "SELECT NormalString, BigNText FROM Table1"
oDR = oCmd.ExecuteReader(CommandBehavior.SequentialAccess)

While oDR.Read
Console.WriteLine("Name: " & oDR.GetString(0))

' Read the first 16 bytes (total string length is > 50)
Console.WriteLine("Read: " & oDR.GetChars(1, 0, b, 0, 16))
' Read the next 16 bytes, fails on this call
Console.WriteLine("Read: " & oDR.GetChars(1, 16, b, 0, 16))
End While

oDR.Close()
oCmd.Connection.Close()
End Sub

The first call to GetChars() works fine, but the second one, to retrieve
the next 16 bytes, throws this exception:

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Invalid attempt to read from column ordinal '1'.
With CommandBehavior.SequentialAccess, you may only read from column
ordinal '2' or greater.

For information, this is on Win XP with .Net 1.1, connection to an SQL
2k server running on Windows 2k3.

Thanks,

Maan

 

[Jon Skeet [C# MVP]]

Thanks for your help. Here is a little example. First, the DB creation:

<snip>

Thanks very much - I've reproduced it. Interestingly, the docs *do*
say:

<quote>
Note You cannot use GetChars to read a VarChar column in chunks if
CommandBehavior is set to SequentialAccess.
</quote>

.... but obviously, this isn't actually a VarChar column.

It seems a bit of a mystery to me, I'm afraid. Do you particularly want
to only read part of the data? If you want the whole of it, you can use
GetString, of course.

 

[Maan Bsat]

Indeed, it's not a varchar column... Anyway, I've resigned myself to not
use Sequential Access. I'm not happy about it, but oh well...I have to
give in.

I could indeed use getstring(), but that's essentially equivalent to not
using sequential access, isn't it? Since you can't specify ranges, it
will have to load the whole ntext field into memory.

Thanks for all your help, in any case,

Maan

 

[Jon Skeet [C# MVP]]

Indeed, it's not a varchar column... Anyway, I've resigned myself to not
use Sequential Access. I'm not happy about it, but oh well...I have to
give in.

I could indeed use getstring(), but that's essentially equivalent to not
using sequential access, isn't it? Since you can't specify ranges, it
will have to load the whole ntext field into memory.

Well, using SequentialAccess may still be beneficial if the row
contains *lots* of large columns - instead of loading *all* of them at
once, you get to see one at a time (or presumably ignore columns you're
not interested in).