Devart Forums

Posted: **Tue 14 May 2013 16:32**

Hello,

I'm attempting to convert an application from the BDE to UniDac components. I'm using the trial edition to determine the issues. If it's relatively painless then we may proceed with our purchase. The conversion has been relatively painless except calling stored procedures via ODBC(SQL Native Client driver). I've been instructed to use ODBC so switching to an SQLProvider is not an option. I tried using the TUniStoredProc component with the following source but keep getting the error "[Microsoft][SQL Native Client][SQL Server]Procedure or function 'ai_nextid' expects parameter '@name', which was not supplied. Any insight to my problem?

client side delphi source code snippet:

frmDataMod1.sp1.Params.Clear;
frmDataMod1.sp1.Params.CreateParam(ftString, '@name', ptInput);
frmDataMod1.sp1.Params.CreateParam(ftInteger, '@nextid', ptInputOutput);
frmDataMod1.sp1.Params[0].AsString := 'CLIENT_ID';
frmDataMod1.sp1.Execute;
lResult := frmDataMod1.sp1.Params[1].AsInteger;

stored procedure definition:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ai_nextid] @name VARCHAR(10), @nextid
INT OUTPUT AS UPDATE AUTOINC SET id = id+1 WHERE name = @name
SELECT @nextid = id FROM AUTOINC WHERE name = @name

Terry

Posted: **Tue 14 May 2013 21:17**

I have discovered through lovely trial & error that if I give the stored procedure parameters default values then the error does not occur. I must also call the Execute method twice on the client to get the output parameter value. This is an ugly workaround which hopefully is fixed in the future unless somebody can point me to a property or setting I need to adjust to correct this behaviour.

Terry

Posted: **Wed 15 May 2013 08:52**

Hello,

The "Procedure or function 'ai_nextid' expects parameter '@name', which was not supplied." error occurs because of a bug in ODBC Driver for SQL Server. You can check it with ADO using the following code:

Code: Select all

begin
  ADOStoredProc1.ProcedureName := 'ai_nextid';
  ADOStoredProc1.Parameters.Clear;
  ADOStoredProc1.Parameters.CreateParameter('@name', ftString, pdInput, 10, Unassigned);
  ADOStoredProc1.Parameters.CreateParameter('@nextid', ftInteger, pdInputOutput, SizeOf(Integer), Unassigned);
  ADOStoredProc1.Parameters[0].Value := 'CLIENT_ID';
  ADOStoredProc1.ExecProc;
  ShowMessage(IntToStr(ADOStoredProc1.Parameters[1].Value));
end;

We cannot influence such ODBC Driver for SQL Server behaviour. Please write about this problem to the Microsoft support. The only workaround is the one you are already found, you should specify default values for all parameters.

We have reproduced the problem with necessity of calling stored procedure twice for getting values of output parameters. We will investigate this problem. As soon as we have any results, we will let you know.

Posted: **Tue 21 May 2013 19:55**

no fix for this yet?

Another annoying issue is that if we use parameters with an insert sql statement, the insert will fail when a parameter is left blank(empty string). Another lovely trial & error hack is to set the parameter size to 1 and then the insert is successful.

Posted: **Wed 22 May 2013 10:06**

We have fixed the problem with necessity of calling stored procedure twice for getting values of output parameters. This fix will be included in the next UniDAC build.
We have reproduced the problem with parameters. We will investigate this problem.

Posted: **Mon 08 Jul 2013 11:15**

We have fixed the problem with parameters. This fix will be included in the next UniDAC build.

Devart Forums

TUniStoredProc accessing SQL Server 2005 via ODBC

TUniStoredProc accessing SQL Server 2005 via ODBC

Re: TUniStoredProc accessing SQL Server 2005 via ODBC

Re: TUniStoredProc accessing SQL Server 2005 via ODBC

Re: TUniStoredProc accessing SQL Server 2005 via ODBC

Re: TUniStoredProc accessing SQL Server 2005 via ODBC

Re: TUniStoredProc accessing SQL Server 2005 via ODBC