Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: sql_param_data_available error


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 4 - Last Post: Jul 29, 2017 12:45 AM Last Post By: Dmitry Arefiev
William Egge

Posts: 98
Registered: 11/29/06
sql_param_data_available error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 13, 2017 10:59 PM
I am calling a stored procedure with an XML output parameter in sql server and it seems to run but then I get the error message below.

"---------------------------
Debugger Exception Notification

Project PrismAPIDev.exe raised exception class EMSSQLNativeException with message '[FireDAC][Phys][ODBC][sqlncli11.dll] SQL_PARAM_DATA_AVAILABLE'.

Break Continue Help
"

When I close the application, I get this error. (possibly when closing the connection):


Debugger Exception Notification

Project PrismAPIDev.exe raised exception class EMSSQLNativeException with message '[FireDAC][Phys][ODBC][Microsoft][ODBC Driver Manager] Function sequence error'.

Break Continue Help

Here is the stored procedure code, the relevant form dfm parts as text, the code that executes the proc, and the place in the firedac code where the error is raised.

ALTER procedure [api].[sp_getApiEventChanges]
  @ApiId uniqueidentifier,
  @InstanceId uniqueidentifier,
  @SinceEventId bigint = 0,
  @Changes xml output
as
begin
  set nocount on;
 
  declare @sql nvarchar(max);
  declare @ParamDef nvarchar(max) = '@InstanceId uniqueidentifier, @SinceEventId bigint, @Changes xml out';
 
  select
    @sql = NewEvents
  from
    api.ApiLoad
  where
    ApiId = @ApiId;
 
  exec sp_executesql @sql, @ParamDef,
    @InstanceId = @InstanceId,
    @SinceEventId = @SinceEventId,
    @Changes = @Changes out
  ;
 
  return 0;
end;

  object FDConnection1: TFDConnection
    Params.Strings = (
      'SERVER=(local)'
      'OSAuthent=Yes'
      'ApplicationName=PrismAPIDev'
      'DATABASE=RADIS_PRISMHR'
      'MARS=yes'
      'DriverID=MSSQL')
    FetchOptions.AssignedValues = [evLiveWindowFastFirst]
    FetchOptions.LiveWindowFastFirst = True
    ResourceOptions.AssignedValues = [rvServerOutput, rvAutoReconnect]
    ResourceOptions.ServerOutput = True
    ResourceOptions.AutoReconnect = True
    Connected = True
    LoginPrompt = False
    Left = 312
    Top = 267
  end
  object FDStoredProc1: TFDStoredProc
    Connection = FDConnection1
    ResourceOptions.AssignedValues = [rvDirectExecute]
    ResourceOptions.DirectExecute = True
    StoredProcName = 'RADIS_PRISMHR.api.sp_getApiEventChanges'
    Left = 424
    Top = 355
    ParamData = <
      item
        Position = 1
        Name = '@RETURN_VALUE'
        DataType = ftInteger
        ParamType = ptResult
        Value = 0
      end
      item
        Position = 2
        Name = '@ApiId'
        DataType = ftGuid
        ParamType = ptInput
        Size = 38
        Value = '{d8025765-e109-4180-9491-8f7407ef035c}'
      end
      item
        Position = 3
        Name = '@InstanceId'
        DataType = ftGuid
        ParamType = ptInput
        Size = 38
        Value = '{8E1193B3-E089-46BA-B9A3-270F675EAF29}'
      end
      item
        Position = 4
        Name = '@SinceEventId'
        DataType = ftLargeint
        ParamType = ptInput
        Value = '0'
      end
      item
        Position = 5
        Name = '@Changes'
        DataType = ftDBaseOle
        FDDataType = dtXML
        ParamType = ptInputOutput
      end>
  end

procedure TForm19.dxBarLargeButton1Click(Sender: TObject);
begin
  FDStoredProc1.ExecProc;
end;

{-------------------------------------------------------------------------------}
procedure FDException(AObj: TObject; AEx: EFDException
  {$IFDEF FireDAC_Monitor}; ATracing: Boolean {$ENDIF});
var
  oHndlr: IFDStanErrorHandler;
  oMAIntf: IFDMoniAdapter;
  oEx: Exception;
  oObj: IFDStanObject;
begin
  if (AEx is EFDDBEngineException) and
     Supports(AObj, IFDMoniAdapter, oMAIntf) and
     (oMAIntf.SupportItems * [ikSQL, ikParam] = [ikSQL, ikParam]) then
    EFDDBEngineException(AEx).SetSQLAndParams(oMAIntf);
  if Supports(AObj, IFDStanObject, oObj) then
    AEx.SetFDObjectName(oObj);
  oEx := Exception(AEx);
  if Supports(AObj, IFDStanErrorHandler, oHndlr) then
    try
      oHndlr.HandleException(oObj, oEx);
    except
      on E: Exception do begin
        if (E <> oEx) and (oEx <> nil) then
          FDFree(oEx);
        raise;
      end;
    end;
  if oEx <> nil then begin
{$IFDEF FireDAC_Monitor}
    // The monitor client messages queue may be not empty. When an exception
    // is raised and an IDE is stopped, the queue remains not empty and
    // FDMonitor does not show error message. So, give the CPU to other threads.
    if ATracing then
      Sleep(50);
{$ENDIF}
    raise oEx;  <---- ERROR RAISED HERE
  end;
end;
William Egge

Posts: 98
Registered: 11/29/06
Re: sql_param_data_available error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 13, 2017 11:01 PM   in response to: William Egge in response to: William Egge
PS - The error actually begins here, could it be that FireDac should be handling this as opposed to raising an error?

function TODBCStatementBase.MoreResults: Boolean;
var
  iRes: SQLReturn;
begin
  Result := False;
  if FNoMoreResults then
    Exit;
  iRes := Lib.SQLMoreResults(FHandle);
  case iRes of
  SQL_SUCCESS:
    begin
      Result := True;
      FResultCols := -1;
    end;
  SQL_SUCCESS_WITH_INFO:
    begin
      Result := True;
      FResultCols := -1;
      Check(iRes);
    end;
  SQL_NO_DATA,
  // ASA: SQLMoreResults cannot return SQL_NEED_DATA, but ASA driver
  // returns. It seems, it is a bug in the ODBC driver.
  SQL_NEED_DATA:
    begin
      FNoMoreResults := True;
      FResultCols := 0;
    end;
  else
    FNoMoreResults := True;
    FResultCols := 0;
    Check(iRes);
  end;
end;
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: sql_param_data_available error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 14, 2017 2:42 AM   in response to: William Egge in response to: William Egge
Could you please provide FireDAC environment report:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/DBMS_Environment_Reports_(FireDAC)

--
With best regards,
Dmitry
William Egge

Posts: 98
Registered: 11/29/06
Re: sql_param_data_available error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 28, 2017 2:51 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
================================
Connection definition parameters
================================
SERVER=(local)
OSAuthent=Yes
ApplicationName=Enterprise/Architect/Ultimate
Workstation=VMEGC
DATABASE=RADIS_PRISMHR
MARS=yes
DriverID=MSSQL
================================
FireDAC info
================================
Tool = RAD Studio 10.2
FireDAC = 16.0.0 (Build 88974)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
  Loading odbc32.dll driver manager
  Creating ODBC environment handle
  Searching for ODBC driver ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 11.0] ...
      Found [SQL Server Native Client 11.0]
Driver Manager version = 03.80.7601.0000
================================
Session info
================================
Checking session ...
  Warning: SQL NC 2012 and MS ODBC 11 fail to work with TVP.
Current catalog = 
Current schema = dbo
Driver name = sqlncli11.dll
Driver version = 11.00.5643
Driver conformance = 3
DBMS name = Microsoft SQL Server
DBMS version = 12.00.5000
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: sql_param_data_available error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 29, 2017 12:45 AM   in response to: William Egge in response to: William Egge
Thank you. Please provide ApiLoad table DDL and sample data, or try to simplify sp_getApiEventChanges to avoid any dependencies and provide it DDL.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02