Board index » delphi » ADO & MSSQL Stored proc problem

ADO & MSSQL Stored proc problem

Hi !

At first time sorry for my English - this is not my native language as
Object pascal :-)

I have a problem with TADOStoredProc & MSSQL7 with OLE DB Provider for MS
SQL

If I use optimiser hints in stored procedure then MSSQL generate a message
"Warning: The join order has been enforced because a local join hint is
used." on first procedure call. OLE DB provider returned this message as
RecordSet with State = adStateClosed and data in next recordset.

In TCustomADODataSet.OpenCursor I see:

=== cut ===

    Recordset.Open(Source, ActiveConnection,
      CursorTypeValues[FCursorType], LockTypeValues[FLockType],
      CommandTypeValues[CommandType] + ExecuteOptionsToOrd(ExecuteOptions));
    if Recordset.State = adStateClosed then begin
      DatabaseError(SNoResultSet, Self);
    end;

=== cut ===

and generated by server message bring ADO to DatabaseError :-)

at second attempt procedure is prepared and cursor opened.

IMHO this is not good behavior. May be better implementation of OpenCursor
is:

=== cut ===

var
  RS: _RecordSet;
  Dummy: OleVariant;

[skip]

    Recordset.Open(Source, ActiveConnection,
      CursorTypeValues[FCursorType], LockTypeValues[FLockType],
      CommandTypeValues[CommandType] + ExecuteOptionsToOrd(ExecuteOptions));
    while Recordset.State = adStateClosed do begin
      // skip to first Non-closed dataset - it contain data
      RS := RecordSet.NextRecordSet(Dummy);
      if Assigned(RS) then
        RecordSet := RS
      else
        DatabaseError(SNoResultSet, Self);
    end;

=== cut ===

This implementation also solves same problem with PRINT and RAISERROR with
severity < 10 problem.

BTW - is anyone know how to extract message text from recordser with State =
adStateClosed ?

--
Bye ...
Tolik Tentser
to...@katren.ru
ICQ 15925834

 

Re:ADO & MSSQL Stored proc problem


Quote
Tolik Tentser <to...@katren.nsk.ru> wrote in message

news:7ue70r$g712@forums.borland.com...

Quote
> IMHO this is not good behavior. May be better implementation of OpenCursor

Thanks for the info, I've captured your message for further investigation.  In the
meantime you should be able to work around the problem by using TADOCommand and
calling NextRecordset and assigning the value to the RecordSet property of
TADODataSet.

Mark

Re:ADO & MSSQL Stored proc problem


Hi !

Quote
> Thanks for the info, I've captured your message for further investigation.
In the
> meantime you should be able to work around the problem by using
TADOCommand and
> calling NextRecordset and assigning the value to the RecordSet property of
> TADODataSet.

Thank`s but my current workaround is :

=== cut ===

procedure TkStoredProc.OpenCursor(InfoQuery: Boolean);
var
  Counter: Integer;
begin
  Counter := 0;
  while Counter < 2 do begin
    try
      inherited;
      Counter := 2;
    except
      on E: EDatabaseError do begin
        if (Counter = 0) and
           (Pos(SNoResultSet, E.Message) = Length(E.Message) -
Length(SNoResultSet) + 1) then begin
          Inc(Counter);
          RecordSet := NIL;
        end else
          raise;
      end;
    end;
  end;
end;

=== cut ===

--
Bye ...
Tentser A.L.
to...@katren.ru
ICQ 15925834

Other Threads