first, using DA 10.0.0.1459, Elements 10.0.0.2465, VS 2019 latest release
I have added a stored procedure to my Pervasive.SQL database. I’ve successfully also added that to Relativity Server via dragging it to schema modeler and filling in the required info.
My SP only has a RESULT return parameter, so I added that to SM.
I wrote the following code to try and test this…but I’ve had zero luck getting past the RemoteCommand.Execute call. I know I duplicated my OUT parameter array as the IN parameter array, but I left that in as a test. it made no difference. I’m completely at a loss right now as to what to do.
Help!
Alan
begin
var fDataModule := new lpv8oelib.DataModule;
var InRes := new List<RemObjects.DataAbstract.Server.DataParameter>;
var OutRes := new List<RemObjects.DataAbstract.Server.DataParameter>;
OutRes.Add(new RemObjects.DataAbstract.Server.DataParameter('NextPrebillNum',0));
InRes.Add(new RemObjects.DataAbstract.Server.DataParameter('NextPrebillNum',0));
var SPCommand := new RemoteCommand;
SPCommand.RemoteService := fDataModule.remoteDataAdapter.RemoteService;
var inarray := InRes.ToArray;
var outarray := OutRes.ToArray;
var res := SPCommand.Execute('GetNextPrebillNumber',inarray,out outarray);
writeLn('Result of SP call: '+res.ToString);
writeLn('SP returned: '+Convert.ToInt32(Outarray.First.Value).ToString);
end;
end;
when I run this the exception I get is the following:
IncomingParametersParameter property not set properly
at RemObjects.DataAbstract.RemoteCommand.InternalExecute(String commandName, DataParameter[] parameters, Boolean acceptOutput, DataParameter[]& output)
at RemObjects.DataAbstract.RemoteCommand.InternalExecute(String commandName, DataParameter[] parameters, DataParameter[]& output)
at RemObjects.DataAbstract.BaseCommand.Execute(String commandName, DataParameter[] parameters, DataParameter[]& output)
at PSPTest.Program.Main(String[] args) in D:\dev\miller\PSPTest\Program.pas:line 45
Sorry for the confusion caused (I’ll log an issue to make the exception messages more clear and to provide better default configuration)
The error you see means ‘You have called a schema command and expect to get its OUT parameter values, but you did not configure a way to read these parameter values’
Let’s assume you have a stored procedure that returns something via its RESULT parameter and want to execute that SP via a Schema Command. I will use MS SQL and C# for the sample, but there is nothing language-specific in the sample.
Here is a very simple stored procedure that accepts a string parameter and returns its length as integer result:
CREATE PROCEDURE [TEST_SP_WITH_RESULT]
@pSomeValue NVARCHAR(50)
AS
BEGIN
RETURN LEN(@pSomeValue);
END
Schema Modeler will create 2 parameters for this procedure when in will be added as a Schema Command:
Here is the code used to call this procedure:
var command = new RemoteCommand();
// Take all server connection settings (like server Url, channel type used etc)
command.RemoteService = this.fDataModule.DataAdapter.RemoteService;
// IMPORTANT! Setup extended request that will allow to pass result data back
command.ExecuteCall.SetupExtendedRequest();
// Array of command parameters
var commandParameters = new[] {new DataParameter("pSomeValue", "SOME VALUE")};
// Define variable that will contain values returned by the Stored Procedure
DataParameter[] outputParameters;
// Execute Schema Command
// Result value of the Execute method is NOT a stored procedure result
// This value contains the number of rows affected by the stored procedure
// After all, Stored Procedure can return not only an integer value, but also a date, string, boolean or even binary value
var commandResult = command.Execute("dbo.TEST_SP_WITH_RESULT", commandParameters, out outputParameters);
// At this point the outputParameters array contains stored procedure result as well as any OUT parameter values
As you can see, your code was perfectly fine except of 2 issues:
Missing SetupExtendedRequest() call
Trying to read procedure result from a wrong place (.Execute method result)
Thanks for the reply. I totally missed the need to call SetupExtendedRequest. Anyway, I added this in and still was getting errors. After messing around, I see that Pervasive doesn’t use the RETURN keyword the same, so I decided to revise my SP and use an OUT parameter instead; This would just be a simple INTEGER variable. I made this change, and changed the parameter in SM to be an OUT parameter. Here is my revised client test code:
class method Program.Main(args: array of String): Int32;
begin
// add your own code here
writeLn('Pervasive Stored Procedure Test!');
writeLn('-========================================================');
try
var fDataModule := new lpv8oelib.DataModule;
var InRes := new List<RemObjects.DataAbstract.Server.DataParameter>;
var OutRes := new List<RemObjects.DataAbstract.Server.DataParameter>;
OutRes.Add(new RemObjects.DataAbstract.Server.DataParameter('NPN',0));
var SPCommand := new RemoteCommand;
SPCommand.RemoteService := fDataModule.remoteDataAdapter.RemoteService;
var inarray := InRes.ToArray;
var outarray := OutRes.ToArray;
SPCommand.ExecuteCall.SetupExtendedRequest;
var res := SPCommand.Execute('SP_NEXT_PREBILL_NUMBER',inarray,out outarray);
writeLn('SP returned: '+Convert.ToInt32(Outarray.First.Value).ToString);
except
on ex:Exception do
begin
Writeln(ex.Message);
writeln(ex.StackTrace);
if ex.InnerException<>nil then
begin
writeLn('-------------------');
writeLn(ex.InnerException.Message);
writeLn(ex.InnerException.StackTrace);
end;
end;
end;
end;
But…i’m still getting an exception when I call Execute.
An exception occurred on the server: Pervasive.Data.SqlClient.Lna.w: [LNA][PSQL][SQL Engine]Stored procedure argument requires an output parameter.
at RemObjects.SDK.Message.ProcessException()
at RemObjects.SDK.BinMessage.InternalReadFromStream(Stream stream)
at RemObjects.SDK.Message.ReadFromStream(Stream stream)
at RemObjects.SDK.IpHttpClientChannel.IntDispatch(Stream request, IMessage response)
at RemObjects.SDK.ClientChannel.Dispatch(IMessage message)
at RemObjects.SDK.DynamicRequest.InternalMakeRequest()
at RemObjects.SDK.DynamicRequest.MakeRequest()
at RemObjects.DataAbstract.RemoteCommand.InternalExecute(String commandName, DataParameter[] parameters, Boolean acceptOutput, DataParameter[]& output)
at RemObjects.DataAbstract.RemoteCommand.InternalExecute(String commandName, DataParameter[] parameters, DataParameter[]& output)
at RemObjects.DataAbstract.BaseCommand.Execute(String commandName, DataParameter[] parameters, DataParameter[]& output)
at PSPTest.Program.Main(String[] args) in D:\dev\miller\PSPTest\Program.pas:line 48
This exception is raised on the DB driver level. Could you show stored procedure definition on the database level and its corresponding Schema Command definition?
CREATE PROCEDURE SP_NEXT_PREBILL_NUMBER(OUT :NPN INTEGER);
BEGIN
DECLARE :UpdNPN INTEGER;
START TRANSACTION;
DECLARE c1 CURSOR FOR SELECT NextPrebillNum FROM numseq FOR UPDATE;
OPEN c1;
FETCH NEXT from c1 into :NPN
SET :UpdNPN = :NPN+1;
UPDATE SET NextPrebillNum=:UpdNPN WHERE CURRENT OF c1;
CLOSE c1;
COMMIT;
END
should my input parameter array be an empty array? likewise, my output parameter array should have the one entry? do the names in the schma have to match the SP names exactly? what else could be wrong? Still getting the message ‘SP argument requires an output parameter…’
I just ran a quick test. I revised my SP to not have ANY parameters - not return anything, etc. after updating the SP, updating SM, recompiling my app to call the SP with just the proc’s name…it ran just fine and did what I wanted it to do. therefore, the SP seems to not be the problem, it must be something with calling a SP in Pervasive that is the issue, right?
Well, seems we have a fix. I’ll review and retest it tomorrow and then will provide you an updated Relativity build (it can be just copied over existing Relativity installation so so you won’t have to reinstall anything in your development environment)
