TZStoredProc trouble with output parameter, MSSQL

alpinistbg · Post by **alpinistbg** » 09.02.2018, 16:39

Hello there,

I'm currently experiencing a problem with TZStoredProc calling a MSSQL stored procedure with an output parameter.
Using Lazarus 1.7, FPC 3.1.1, i386-win32-win32, Zeos 7.2.1-rc (fpcdeluxe v1.4.0k for i386-win32), MSSQL 2008 (SQL Server 11.0.2100)

The SP was defined as:

Code: Select all

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE test_sp1 
	@p1 int = 0, 
	@p2 VARCHAR(16) output
AS
BEGIN
	SET NOCOUNT ON;
	SELECT @p1, @p2
END
GO

The connection/sp components were defined as (exrpt from .lfm file):

Code: Select all

object ZConnection1: TZConnection
    ControlsCodePage = cCP_UTF8
    ClientCodepage = 'WINDOWS-1251'
    Properties.Strings = (
      'AutoEncodeStrings=ON'
      'codepage=WINDOWS-1251'
      'controls_cp=CP_UTF8'
    )
    Connected = True
    HostName = '192.168.0.5'
    Port = 0
    Database = 'ePark'
    User = 'alpi'
    Password = 'passwd'
    Protocol = 'FreeTDS_MsSQL>=2005'
    LibraryLocation = 'C:\AI\_\test1\sybdb.dll'
  end
  object ZStoredProc2: TZStoredProc
    Connection = ZConnection1
    Params = <    
      item
        DataType = ftInteger
        Name = '@RETURN_VALUE'
        ParamType = ptResult
        Value = 0
      end    
      item
        DataType = ftInteger
        Name = '@p1'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = '@p2'
        ParamType = ptInputOutput
      end>
    StoredProcName = 'test_sp1'
    Left = 482
    Top = 446
    ParamData = <    
      item
        DataType = ftInteger
        Name = '@RETURN_VALUE'
        ParamType = ptResult
        Value = 0
      end    
      item
        DataType = ftInteger
        Name = '@p1'
        ParamType = ptInput
      end    
      item
        DataType = ftString
        Name = '@p2'
        ParamType = ptInputOutput
      end>
  end

When trying to activate the SP it gives me an Debugger Exception Notification:

Code: Select all

[Debugger Exception Notification]

Project project1 raised exception class 'EZVariantException' with message:
Types mismatch

 In file 'C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\core\ZVariant.pas' at line 567:
raise EZVariantException.Create(STypesMismatch);


[Ignore this exception type]

[Break] [Continue]

The exception doesn't show when the @p2 parameter is an int.
Any hints will be greatly appreciated!

Regards,

Post by **EgonHugeist** » 09.02.2018, 20:57

Any stacktrace?
Which lines did call RaiseTypeMismatchError before?

alpinistbg · Post by **alpinistbg** » 09.02.2018, 21:46

EgonHugeist wrote:Any stacktrace?
Which lines did call RaiseTypeMismatchError before?

AFAIR ZVariant.pas line 718 with Value.VType = vtRawByteString (line 690)
Funny thing is that with ZeosLib 7.1.4-stable there is no such error.

alpinistbg · Post by **alpinistbg** » 10.02.2018, 15:21

EgonHugeist wrote:Any stacktrace?
Which lines did call RaiseTypeMismatchError before?

Here is the invocation and the stack trace:

Code: Select all

procedure TForm1.Button4Click(Sender: TObject);
begin
  ZStoredProc2.Params.ParamValues['@p2'] := 'abc';
  ZStoredProc2.Active := True;
end;

--- Stack trace ---

#0 fpc_raiseexception at :0
#1 TZSOFTVARIANTMANAGER__RAISETYPEMISMATCHERROR(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\core\ZVariant.pas:567
#2 TZSOFTVARIANTMANAGER__CONVERT({VTYPE = VTRAWBYTESTRING, VSTRING = 0x0, VANSISTRING = 0x0, VRAWBYTESTRING = 0x504bf3c 'abc', ' ' <repeats 252 times>, VUTF8STRING = 0x0, VUNICODESTRING = 0x0, VBYTES = 0x0, VINTERFACE = {}, VBOOLEAN = 148, VINTEGER = 1168296637588, VUINTEGER = 1168296637588, VFLOAT = <invalid float value>, VDATETIME = 5.7721523278407197e-312, VPOINTER = 0x3e7f494, VCHARREC = {LEN = 65533076, P = 0x110, CP = 62088}, VARRAY = {VARRAY = 0x3e7f494, VARRAYTYPE = 16, VARRAYVARIANTTYPE = 65532552, VISNULLARRAY = 0x411c47, VISNULLARRAYTYPE = 0, VISNULLARRAYVARIANTTYPE = 65532552}}, VTSTRING, <error reading variable>, {VTYPE = VTSTRING, VSTRING = 0x0, VANSISTRING = 0x0, VRAWBYTESTRING = 0x0, VUTF8STRING = 0x0, VUNICODESTRING = 0x0, VBYTES = 0x0, VINTERFACE = {}, VBOOLEAN = 28, VINTEGER = 30064771100, VUINTEGER = 30064771100, VFLOAT = <invalid float value>, VDATETIME = 1.4853970550590744e-313, VPOINTER = 0x1c, VCHARREC = {LEN = 28, P = 0x7, CP = 799}, VARRAY = {VARRAY = 0x1c, VARRAYTYPE = 7, VARRAYVARIANTTYPE = 4260639, VISNULLARRAY = 0x825000, VISNULLARRAYTYPE = 28, VISNULLARRAYVARIANTTYPE = VTFLOAT}}) at C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\core\ZVariant.pas:1868
#3 TZSOFTVARIANTMANAGER__GETASSTRING({VTYPE = VTRAWBYTESTRING, VSTRING = 0x0, VANSISTRING = 0x0, VRAWBYTESTRING = 0x504bf3c 'abc', ' ' <repeats 252 times>, VUTF8STRING = 0x0, VUNICODESTRING = 0x0, VBYTES = 0x0, VINTERFACE = {}, VBOOLEAN = 148, VINTEGER = 1168296637588, VUINTEGER = 1168296637588, VFLOAT = <invalid float value>, VDATETIME = 5.7721523278407197e-312, VPOINTER = 0x3e7f494, VCHARREC = {LEN = 65533076, P = 0x110, CP = 62088}, VARRAY = {VARRAY = 0x3e7f494, VARRAYTYPE = 16, VARRAYVARIANTTYPE = 65532552, VISNULLARRAY = 0x411c47, VISNULLARRAYTYPE = 0, VISNULLARRAYVARIANTTYPE = 65532552}}, <error reading variable>, 0x0) at C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\core\ZVariant.pas:1087
#4 TZABSTRACTCALLABLESTATEMENT__GETSTRING(3, <error reading variable>, 0x0) at C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\dbc\ZDbcStatement.pas:2735
#5 TZSTOREDPROC__RETRIEVEPARAMVALUES(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\component\ZStoredProcedure.pas:269
#6 TZSTOREDPROC__INTERNALOPEN(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeos\src\component\ZStoredProcedure.pas:314
#7 DB$_$TDATASET_$__$$_DOINTERNALOPEN at :0
#8 DB$_$TDATASET_$__$$_OPENCURSOR$BOOLEAN at :0
#9 DB$_$TDATASET_$__$$_SETACTIVE$BOOLEAN at :0
#10 TFORM1__BUTTON4CLICK(0x5128028, <error reading variable>) at unit1.pas:106

Post by **EgonHugeist** » 12.02.2018, 17:04

Hi,

could you please update from SVN \testing-7.2 and tell me if the issue is resolved?
Patch done R4183

alpinistbg · Post by **alpinistbg** » 12.02.2018, 18:48

It seems that the issue has been resolved. My test cases work as expected. Thanks a lot!

Kind regards,

alpinistbg · Post by **alpinistbg** » 13.02.2018, 14:07

Sorry to say that, but still have trouble with the output parameter.
Actually, I have modified the previous SP with one input, one output parameters and no result set like that (note the comment on select):

Code: Select all

ALTER PROCEDURE [dbo].[test_sp1] 
	@p1 int = 0, 
	@p2 VARCHAR(16) output
AS
BEGIN
	SET NOCOUNT ON;
	SET @p2 = 'def_' + @p2 + '_ghi'
	--SELECT @p1 as p1, @p2 as p2 
END

If I call ExecProc then exception raises:

Code: Select all

Project project1 raised exception class 'Exception' with message:
DBError : [20019] : Attempt to initiate a new Adaptive Server operation with results pending

 In file 'C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\plain\ZPlainDbLibDriver.pas' at line 712:
raise Exception.Create(S);

--------------- Stack trace ------------------

#0 fpc_raiseexception at :0
#1 TZDBLIBABSTRACTPLAINDRIVER__CHECKERROR(0x15c868, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\plain\ZPlainDbLibDriver.pas:712
#2 TZDBLIBCONNECTION__CHECKDBLIBERROR(LCOTHER, 0x83664c 'FETCHRESULTS', <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\dbc\ZDbcDbLib.pas:403
#3 TZDBLIBCALLABLESTATEMENT__FETCHROWCOUNT(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\dbc\ZDbcDbLibStatement.pas:645
#4 TZDBLIBCALLABLESTATEMENT__EXECUTEPREPARED(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\dbc\ZDbcDbLibStatement.pas:1047
#5 TZDBLIBCALLABLESTATEMENT__EXECUTEQUERYPREPARED(<error reading variable>, {<IUNKNOWN> = {}, }) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\dbc\ZDbcDbLibStatement.pas:694
#6 TZABSTRACTRODATASET__CREATERESULTSET(0x50b10ac 'test_sp1'#13#10, -1, <error reading variable>, {<IUNKNOWN> = {}, }) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\component\ZAbstractRODataset.pas:3401
#7 TZABSTRACTDATASET__CREATERESULTSET(0x50b10ac 'test_sp1'#13#10, -1, <error reading variable>, {<IUNKNOWN> = {}, }) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\component\ZAbstractDataset.pas:343
#8 TZABSTRACTRODATASET__INTERNALOPEN(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\component\ZAbstractRODataset.pas:3430
#9 TZSTOREDPROC__INTERNALOPEN(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\zeoslib-testing-7.2\src\component\ZStoredProcedure.pas:312
#10 DB$_$TDATASET_$__$$_DOINTERNALOPEN at :0
#11 DB$_$TDATASET_$__$$_OPENCURSOR$BOOLEAN at :0
#12 DB$_$TDATASET_$__$$_SETACTIVE$BOOLEAN at :0
#13 TFORM1__BUTTON4CLICK(0x50a8038, <error reading variable>) at unit1.pas:116

When select is not commented, everything is fine. My problem is that I have a legacy system based on SP's just like this (w/o RS) and I want to migrate it.

Best regards,

Post by **EgonHugeist** » 21.02.2018, 08:25

Hi, should be resolved by R4225 \testing-7.2

alpinistbg · Post by **alpinistbg** » 21.02.2018, 14:48

EgonHugeist wrote:Hi, should be resolved by R4225 \testing-7.2

Resolved.

When no resultset, then ExecProc must be called, not Open, but IMHO that is the way it should be.

Regards,

Post by **EgonHugeist** » 21.02.2018, 18:35

It is...

Zeoslib Portal

TZStoredProc trouble with output parameter, MSSQL

TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL

Re: TZStoredProc trouble with output parameter, MSSQL