Page 1 of 1
Stored Proc return value
Posted: Tue 20 Jun 2017 14:06
by 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
Re: Stored Proc return value
Posted: Tue 20 Jun 2017 14:32
by ViktorV
In order to get a detailed answer, please provide the DDL of "sp_nextval" stored procedure.
Re: Stored Proc return value
Posted: Tue 20 Jun 2017 15:21
by 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
Re: Stored Proc return value
Posted: Wed 21 Jun 2017 12:04
by 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);
Re: Stored Proc return value
Posted: Wed 19 Jul 2017 07:02
by 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
Re: Stored Proc return value
Posted: Wed 19 Jul 2017 07:51
by 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
Re: Stored Proc return value
Posted: Wed 19 Jul 2017 09:06
by 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,
Re: Stored Proc return value
Posted: Wed 19 Jul 2017 10:14
by 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
Re: Stored Proc return value
Posted: Thu 20 Jul 2017 09:21
by 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 ...
Re: Stored Proc return value
Posted: Fri 21 Jul 2017 10:53
by 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