How to return more than one column with the READTEXT HELP!!!
-
When using the readtext i would like to also select another column reather than the one from where the readetext is reading from the TEXTPTR.... Here is what i have...
Dim cmdReadBinary As New SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK",cnn)
and i would like to so this..
Dim cmdReadBinary As New SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK, ImgName ",cnn) <-I have added the ...ImgName... to this one.
I am tring to apend the file .ext to the end of the blob...
with the readtext can i not return more than one column?
erik..
Dam again!
-
It looks like you are using VB of some sort (I'm not a big VB user - at least I try not to be
). See that's the case, why not use your client side library to read the BLOB(s) and any other fields as a recordset and access them using the standard SqlCommand objects? For any fields that are in your recordset you can retrieve a nice friendly stream object that you can then work with using more traditional client side approaches. Using READTEXT, etc the way you are doing it will probably cause all sorts of grief and make life a lot more challenging... -
I agree about the VB bit...
But even when using plain-vanilla t-sql the problem is still the same: using readtext I can extract a text field from a table but not the record's ID or any other field _together_ with the blob itself.
I've been trying to find a solution to this one for a while, just like the original poster. Why Books online or the MS Site has NOTHING on this is extraordinary...
Regards,Alain
-
i have got a solution for you... will post it later..
Are you accessing sql2000 with .net script?
erik
Dam again!
-
NO, I'm accessing SQLServer 2000 either 'directly', that is with the Query Analyser (for instance) or oder such front ends, or with php/ODBC.
Still, I'm curious to see your solution. Thanks in advance for your trouble!
Regards,Alain
-
When ever you make your first trip to the database in order to retrieve the @TEXTPTR value and the DATALENGTH value.. AT THIS TIME ALSO RETRIEVE ALL OF THE OTHER COLUMNS THAT YOU WILL NEED AND STRORE THEM IN VARIABLE(S) ( ARE where ever you want to ) IN YOUR APPLICATION so when you have to hit the database for the second time in order to use the @READTEXT function you will already have these column values waiting for you tucked away in a nice little holding area just waiting to be utilized
=======================================================
GET all of the columns that you will need here
Dim cmdGetPointer As New SqlCommand("SELECT @Pointer=TEXTPTR(Picture)," & _
"@Length=DataLength(Picture) , ***more columns here *** FROM Categories WHERE CategoryName='Test'", cnn)
=========================================================
=========================================================
Only send the required values back to the database here
Dim
cmdReadBinary As New SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cnn)
@size will be your DATALENGTH(<COLUMN>
@Offset is the starting postion that you will begin to read the bytes from in the column in sql2000
==============================================================================
============================================
Look onlyline at microsoft and the have a great example on how to read binary values from the sqldatareader
dr.GetBytes(PictureCol, 0, Buffer, 0, SizeParam.Value)
Reading images, and binary column values is not easy to learn and will take a little time to soak in... Unfortunally there is no easier way to do this..
There is also no way to work from the analyer with this type of method..
============================================
HERE IS A START FOR YOU.
===============================
Private Sub SqlChunkBLOB2File(ByVal DestFilePath As String)
Dim PictureCol As Integer = 0 ' position of Picture column in DataReader
Dim BUFFER_LENGTH As Integer = 32768 ' chunk size
Dim cnn As New SqlConnection(Me.SqlCnn)
'
' Make sure that Photo is non-NULL and return TEXTPTR to it.
Dim cmdGetPointer As New SqlCommand("SELECT @Pointer=TEXTPTR(Picture)," & _
"@Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cnn)
Dim PointerOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
Dim LengthOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int)
LengthOutParam.Direction = ParameterDirection.Output
cnn.Open()
cmdGetPointer.ExecuteNonQuery()
If PointerOutParam.Value Is DBNull.Value Then
cnn.Close()
' Add code to deal with NULL BLOB.
Exit Sub
End If
'
' Set up READTEXT command, parameters, and open BinaryReader.
'
Dim cmdReadBinary As New SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cnn)
Dim PointerParam As SqlParameter = cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
Dim OffsetParam As SqlParameter = cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int)
Dim SizeParam As SqlParameter = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int)
Dim dr As SqlDataReader
Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
Dim Offset As Integer = 0
OffsetParam.Value = Offset
Dim Buffer(BUFFER_LENGTH - 1) As Byte
'
' Read buffer full of data and write to the file stream.
'
Do
PointerParam.Value = PointerOutParam.Value
'
' Calculate the buffer size - may be less than BUFFER_LENGTH for the last block.
'
If Offset + BUFFER_LENGTH >= LengthOutParam.Value Then
SizeParam.Value = LengthOutParam.Value - Offset
Else
SizeParam.Value = BUFFER_LENGTH
End If
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
dr.Read()
dr.GetBytes(PictureCol, 0, Buffer, 0, SizeParam.Value)
dr.Close()
fs.Write(Buffer, 0, SizeParam.Value)
Offset += SizeParam.Value
OffsetParam.Value = Offset
Loop Until Offset >= LengthOutParam.Value
fs.Close()
cnn.Close()
End Sub
'
Private Sub ChunkFileUpDate2SqlBLOB(ByVal SourceFilepath As String, ByVal FileName As String, ByVal FileType As String)
Dim BUFFER_LENGTH As Integer = 32768 ' chunk size
Dim cn As New SqlConnection(Me.SqlCnn)
'
' Make sure that Photo is non-NULL and return TEXTPTR to it.
'
Dim cmdGetPointer As New SqlCommand("SET NOCOUNT ON;" & _
"UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" & _
"SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn)
Dim PointerOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
cn.Open()
cmdGetPointer.ExecuteNonQuery()
'
' Set up UPDATETEXT command, parameters, and open BinaryReader.
'
Dim cmdUploadBinary As New SqlCommand("UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn)
Dim PointerParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
Dim OffsetParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int)
Dim DeleteParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int)
DeleteParam.Value = 1
' delete 0x0 character
Dim BytesParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH)
Dim fs As New IO.FileStream(SourceFilepath, IO.FileMode.Open, IO.FileAccess.Read)
Dim br As New IO.BinaryReader(fs)
Dim Offset As Integer = 0
OffsetParam.Value = Offset
'
' Read buffer full of data and execute the UPDATETEXT statement.
'
Dim Buffer() As Byte = br.ReadBytes(BUFFER_LENGTH)
Do While Buffer.Length > 0
PointerParam.Value = PointerOutParam.Value
BytesParam.Value = Buffer
cmdUploadBinary.ExecuteNonQuery()
DeleteParam.Value = 0
' don't delete any other data
Offset += Buffer.Length
OffsetParam.Value = Offset
Buffer = br.ReadBytes(BUFFER_LENGTH)
Loop
br.Close()
fs.Close()
cn.Close()
End Sub
Dam again!
-
I guess the whole thing with displaying query results is that it is up to the application to format the data - query analyser happens to not be built for displaying BLOB data (and nor should it - although a nice little popup wouldn't go astray). I personally wouldn't use the example above as it does limit you to one row at a time and makes things a lot more complicated than they need be - just about all client-side DB libraries have some form of BLOB stream that you can access for the current record pointer in your resultset.
To do things completely in TSQL you'll probably need to use a cursor and loop through the rows. Perhaps we can help with the overall algorithm and structure if you let us know more about your requirements? EG, with PHP + ODBC I am sure you would be able to get a BLOB stream....
-
I would love to see your example on how to retrieve an image a display it to the clieint... I am still really new at this.. and would really like to see the slimmed up version.. on the .net platform..
erik..
Dam again!
-
See this for ADO
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258038
See this for ADO.NET
http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;308042
I haven't actually read the examples in great detail - I usually program in Delphi using ADO. Delphi provides some nice stream abstractions which make things nice & easy... .NET definitely has the same. Not sure bout ordinary VB6? But that code is a lot easier to comprehend in any case
-
Thanks to Erik for this impressive example... I suppose it's in VB, but I cannot tell as I am not at all familiar with MS script languages.
Still, the example allowed me to see how I could do this in php. I'll be giving it a try today and report back.
Regards,Alain
-
Point Made.
Dam again!
-
To display an image to the client
Get the data into a byte array as per Eriks example, e.g. Buffer
Then put the data in a PictureBox
Dim ms AS MemoryStream = new MemoryStream()
ms.Write(MyData, 0, MyData.Length)
Dim bmp AS Bitmap = new Bitmap(ms)
PictureBox1.Image = bmp
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply