Stored Proc return value

[sanfilma]

Hello !

I am encountering a problem with Unidac about Stored Proc,

We had in my company on our soft previously SDAC and ODAC, and we mooved on UNIDAC recently
We add and ajust Unicomponents.

With MS SQL it was possible to make the stored proc return a scalar value , used in the code with Proc.ReturnValueName and it was doable with SDAC.
Most of the existing code seems to work with Unidac but i get an erorr when i run the project :

EDatabaseError Exception, Invalid parameter type

This is where i run the storedproc :

Code: Select all

begin
            zProc := LpDefaultConnection.CreateStoredProc(Application);
            try
              zProc.StoredProcName := 'sp_nextval';
              zProc.ReturnParams := True;
              if zProc.Params.Count = 0 then
              begin
                zProc.Params.CreateParam(ftString, 'Ky', ptInput);
                zProc.Params.CreateParam(ftInteger, zProc.ReturnValueName, ptResult);
              end;
              zProc.ParamByName('Ky').AsString := zKyCpteur;
              zProc.ExecProc;
              zValeurChaine := zProc.ParamByName(zProc.ReturnValueName).AsString;
              zOk := zProc. not EstVide(zValeurChaine);
            finally
              zProc := nil;
            end;

It seems that it is not possible anymore to catch this value ?
However i did this :

Code: Select all

TUniStoredProc(DataSet).Options.ReturnParams := True;

I didn't find the second parameter, only the 'Ky'
I don't know if it's a functionnality who is not on Unidac or if i missed something
Thank you in advance

[ViktorV]

In order to get a detailed answer, please provide the DDL of "sp_nextval" stored procedure.

[sanfilma]

There you go !

Code: Select all

USE [vega_test]
GO
/****** Object:  StoredProcedure [dbo].[sp_nextval]    Script Date: 20/06/2017 15:57:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  StoredProcedure [dbo].[sp_nextval]    Script Date: 04/07/2014 14:51:50 ******/
ALTER PROCEDURE [dbo].[sp_nextval]
@Ky varchar(200)
AS
begin
DECLARE @cpteur int
begin tran TransactCompteur
UPDATE F902CPT SET F902CPTEUR = F902CPTEUR+1 WHERE  F902KY = @Ky
if @@ROWCOUNT>0
	select @cpteur = (SELECT F902CPTEUR FROM F902CPT WHERE F902KY = @Ky)
else
	SELECT @cpteur = 0;
commit tran TransactCompteur
SELECT 'RETOUR' = @cpteur
RETURN @cpteur
end

[ViktorV]

To solve the issue, please call the TUniStoredProc.PrepareSQL method after

Code: Select all

zProc.StoredProcName := 'sp_nextval';

or change the order of parameters creation:

Code: Select all

  zProc.Params.CreateParam(ftInteger, zProc.ReturnValueName, ptResult);
  zProc.Params.CreateParam(ftString, 'Ky', ptInput);

[sanfilma]

Thanks you for the solutions , I will try it !
I'm facing an other problem with Unidac
On MSSQL, i try to update a field with a text parameter but the string is (arbitrarily ?) truncated to 4000 characters which obviously causes problems
Until the last moments the query stills good ( the field has the good lenght) but when the query received by the DB is wrong.
It probably fail in the execute :

Code: Select all

procedure TLpCustomQuery.Ouvrir;
var
  zCursor : TCursor;
begin
  zCursor := Screen.Cursor;
  if Assigned(FSession) and FSession.SQLHourGlass then
  begin
    Screen.Cursor := crSQLWait;
  end;

  try
    if FQueryMonitor.Active then
    begin
      FQueryMonitor.SqlQuery := FDataSet.SQL.Text;
      FQueryMonitor.Start;
    end;

    try
      if IsQuery then
      begin
        FDataSet.Open;
      end
      else
      begin
        FDataSet.Execute;
        if FQueryMonitor.Active then
        begin
          UpdateCounters;
        end;
      end;
    finally
      if FQueryMonitor.Active then
      begin
        QueryMonitor.Stop;
      end;
    end;
  finally
    if Assigned(FSession) and FSession.SQLHourGlass then
    begin
      Screen.Cursor := zCursor;
    end;
  end;
end;

We're currently running the demo version ( for the first tests but it will be bought soon for sure ) but it isn't the problem right ?

Thank you in advance

[azyk]

Please provide CREATE scripts for the stored procedure and the tables it uses, as well as the Delphi code to call the stored procedure.

Limitations of the trial version of UniDAC cannot be the cause of the behavior you specified. For more information about the limitations of the trial version of UniDAC, please visit the download page: https://www.devart.com/unidac/download.html

[sanfilma]

Thanks for the quick answer

But my problem is not about stored proc this time ( will try to solve the stored proc problem i had at the beginning later ), Sorry If I wasn't clear !

It's about query now ... :
With this query :

Code: Select all

update F925ZOOM set F925DTMODIF = :pF925DTMODIF, F925REQ = :pF925REQ, L925901DESC = :pL925901DESC, L925901TIT = :pL925901TIT where F925KY = :pF925KY

the parameter pF925REQ is truncated at the end when le Query is sent to my MSSQL server,
The problems appears probably during the TCustomDADataSet.Execute because the query is correct just before

The field is the DB is :

[F925REQ] [text] NOT NULL,

[azyk]

We cannot reproduce the problem you’ve described. Please specify if it is reproduced on our UniDACDemo project (section Text). It is located in the "Demos\UniDacDemo\" folder in the UniDAC demo projects default installation folder. If the problem is not reproduced on our demo, create a small test project to demonstrate it and send it to us using the contact form on our website: http://www.devart.com/company/contactform.html

[sanfilma]

I think it's now resolved, bychecking the previous version of the function SetBlobData ( with SDAC)
and setting like this :

Code: Select all

procedure TLpCustomConnection.SetBlobData(Param: TParam; const Buffer: string);
begin
  Param.AsString := Buffer;
  if param.ParamType = ptUnknown then
  begin
    param.ParamType := ptInput;
  end;
end;

It's a bit weird that if the paramType is Unknown the input value will be truncated ?
Or maybe i simply missed a very basic parameter for this ...

[azyk]

The value of the paramType property should not affect the parameter value or its size. If the above behavior is different in the user code, then create a test project to reproduce it and send it to us using the contact form on our website: http://www.devart.com/company/contactform.html