Returning ADO recordset and output parameters in one stored procedure call
-
Let me start by saying I am not a VB expert. I'm working with a VB developer using ADO (not .NET) that needs to return chunks of an ntext column without using GetChunk. He passes and offset and chunksize, and I return the remaining size; however, while the ntext is returned, the InputOutputParameter remains unchanged. Here's an example call:
execute GetMyText @TextID, @Offset, @Remaining output
The procedure uses a READTEXT and calculates the remaining size. In SQL Query Analyzer I would call it this way:
declare @TextID int, @Offset int, @Remaining int
select @TextID = 1, @Offset = 0, @Remaining = 1000000 output
execute GetMyText @TextID, @Offset, @Remaining output -- Returns the first chunk of Text using READTEXT
select @Remaining -- used to determine if there is more text to retrieve.
This will return a recordset of the text and the @Remaining variable will contain the expected size; however, in VB calls to ADO it doesn't change the InputOutputParameter value for the size. It always stays the same.
Is there a way to call ADO to return both the Readtext and the @Remaining output parameter?
Note: Before someone chimes in, you cannot use a variable with the readtext (i.e. @MyText = READTEXT). It violates sql syntax.
-
I suggest that you look at using ADODB.Stream:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjstream.asp
Andy
-
Forget ReadText. Just use SubString.
-
Not if the output is > 8000 bytes
The problem is due to the consumption of the output recordsets, as in the following from MSDN
"If a command returns a single result that is a recordset, output parameter data is available at the time the recordset is completely released. If a command returns multiple results, output parameter data is available when the NextRecordset method returns the next recordset or the multiple results object is completely released, whichever occurs first. Before the output parameter data is available, the consumer's bound memory is in an indeterminate state."
Therefore you need to close any recordsets before getting return and output parameters.
Far away is close at hand in the images of elsewhere.
Anon. -
Agree the 8k limit but he is getting chunks so I assume that this will be ok.
-
Not sure if this will help you out, its VB6 not SQL but its what i use to read a Text column into a variable. you can compute the amount remaining in the while loop and process the chunk as well or snag the whole thing and process it after you have it in one variable.
VB6 using MDAC 2.8
Private Const ChunkSize As Long = 8192 ' for fetching chunks of TEXT fields from the database
Dim ProdRS As ADODB.Recordset
Dim TextSize As Long
Dim CurrentPointer As Long
Dim ProdText As String
Dim Remaining As Long
TextSize = ProdRS(YourTextColumnName).ActualSize
CurrentPointer = 0
While CurrentPointer < TextSize
ProdText = ProdText & ProdRS(YourTextColumnName).GetChunk(ChunkSize)
CurrentPointer = CurrentPointer + ChunkSize
Remaining = TextSize - CurrentPointer
' process the chunk here
Wend
Hope this helps
Bill
-
Well, the chunking is occurring in 1Mb in general, so the 8K limit won't work for us. Also, we are coding to not use GetChunk. ADO.Net eliminates AppendChunk and GetChunk so we are trying to make these work as we transition the code into ADO.Net later.
David Burrows idea makes sense; however, we tried releasing the recordset (RS = Nothing) and the output parameters still do not get updated. I presume it should be automatic upon release of the recordset, correct?
-
I'm not entirely sure what is going on here. Could we see the stored procedure and the relevant VB, please?
-
we tried releasing the recordset (RS = Nothing) Try closing the recordset (and any additional ones if present) instead of setting it to nothing. Not sure if the recordsets need to be consumed (read) or not. I think this has to do with the number streams used by ADO and when they are processed.
Far away is close at hand in the images of elsewhere.
Anon. -
I couldn't say why the output variable isn't being returned. However, would it be possible to simply select the number you want as an output variable into another record set? Once back on the VB side, you can work with your primary record set as normal, and just access the second record set as that output variable.
On the SQL side it should be pretty standard, select your record chunk as normal, the at the end of the procedure just do an extra:
SELECT remaning number size as Remaining
Back on the VB side this should be accessable as a second table in the record set. I've pretty much forgotten VB6, been using .NET for a while now (if you can, switch. It's a lot nicer.). In .NET it would look like
... (declarations, and filling a dataset)
MyData.Tables(0).However you want to access your data 'This would be your real data
Remaining = Cint(MyData.Tables(1).rows(0).item(0).ToString) 'This would get the number remaining into a convient variable
... (Whatever else you plan on doing)
-
I don't know if there's something special about the ntext column that's causing your problem, but here's the VB6 code I use to get a list of towns in a recordset, as well as the count of records in the recordset and the SP's return code.
<%'Working storage for stp_MileageTownsListGet
'There are no input parameters passed to the stored procedure
'These are the values returned from the stored procedure
'Move to appropriate program fields after calling stp_MileageTownsListGetExecute
dim stp_MileageTownsListGetParmValueRecordCount
dim stp_MileageTownsListGetParmValueReturnError
'if no records are returned, stp_MileageTownsListGetParmValueReturnError is changed
'from 0 to "X"
'This is the array that is built from the recordset
dim stp_MileageTownsListGetArray
'These are the column numbers for the array created from the recordset
dim stp_MileageTownsListGetTownColumn
dim stp_MileageTownsListGetColumnMax
stp_MileageTownsListGetTownColumn = 0
stp_MileageTownsListGetColumnMax = 0
%>
<!--Call this subroutine once the input parms have been supplied // -->
<% Sub stp_MileageTownsListGetExecute(substrActiveConnection, Err)
On Error Resume Next
dim cmdSubCommand
dim rsSubRecordSet
dim cnSubConnection
stp_MileageTownsListGetParmValueRecordCount = ""
stp_MileageTownsListGetParmValueReturnError = ""
stp_MileageTownsListGetArray = ""
Err.Clear
set cnSubConnection = Server.CreateObject("ADODB.Connection")
cnSubConnection.CursorLocation = adUseClient
cnSubConnection.Open substrActiveConnection
set rsSubRecordSet = Server.CreateObject("ADODB.Recordset")
rsSubRecordSet.CursorType = adOpenStatic
rsSubRecordSet.LockType = adLockReadOnly
set cmdSubCommand = Server.CreateObject("ADODB.Command")
if Err.Number > 0 then
exit sub
end if
With cmdSubCommand
.CommandType = adcmdStoredProc
.CommandText = "stp_MileageTownsListGet"
.ActiveConnection = cnSubConnection
.Parameters.Append .CreateParameter("ReturnError", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("RecordCount",adInteger,adParamOutput)
set rsSubRecordSet = .Execute()
.ActiveConnection = nothing
stp_MileageTownsListGetParmValueRecordCount = .Parameters("RecordCount").Value
stp_MileageTownsListGetParmValueReturnError = .Parameters("ReturnError").Value
End With
if stp_MileageTownsListGetParmValueReturnError = 0 then
if stp_MileageTownsListGetParmValueRecordCount > 0 then
stp_MileageTownsListGetArray = rsSubRecordSet.GetRows(-1)
else
stp_MileageTownsListGetParmValueReturnError = "X"
end if
end if
rsSubRecordSet.Close
cnSubConnection.Close
set cmdSubCommand = nothing
set rsSubRecordSet = nothing
set cnSubConnection = nothing
End Sub%>
This is the stored procedure:
CREATE PROCEDURE stp_MileageTownsListGet
@RecordCount int output as
BEGIN
DECLARE @ReturnError int
SELECT Town1 as Town FROM TownMaster
UNION
SELECT Town2 as Town FROM TownMaster
ORDER BY Town
SELECT @RecordCount = @@RowCount, @ReturnError = @@Error
RETURN @ReturnError
END
GO
Mattie
-
Can you make the Remaining parameter a field in the returned recordset?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply