TUniStoredProc accessing SQL Server 2005 via ODBC

[thebert]

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

[thebert]

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

[AndreyZ]

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.

[thebert]

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.

[AndreyZ]

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.

[AndreyZ]

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