[Ver 7.0] Can't set parameter for stored procedure

ManoShu · Post by **ManoShu** » 16.04.2012, 13:49

Hello everyone,

i am trying to execute a stored procedure on a local MS SQL Server instance, but it seems that i am not able to set the SP parameter.
On the ExecProc line i got this exception:

Code: Select all

Project Project1.exe raised exception class EOleException with message 'Procedure or function 'xp_addtest' expects parameter '@name', which was not supplied'.

What's wrong with the code? Thanks in advance.

EDIT: Using Delphi XE2 and Zeos 7.0.0.0

Table Script:

Code: Select all

CREATE TABLE [dbo].[TestTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL
)
GO

Stored Procedure Script:

Code: Select all

CREATE PROCEDURE [dbo].[xp_addtest](@name as varchar(50))
AS
INSERT INTO TestTable (Name) VALUES (@name)

Delphi Code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  conn: TZConnection;
  xsp: TZStoredProc;
  param: TParam;
begin
  conn := TZConnection.Create(Self);
  conn.Protocol := 'ado';
  conn.Database :=
    'Provider=SQLOLEDB.1;Password=TEST;Persist Security Info=True;User ID=LOGTest;Initial Catalog=TestDB;Data Source=.\';
  conn.Connect;

  xsp := TZStoredProc.Create(Self);
  xsp.Connection := conn;

  //xsp.StoredProcName := '';
  xsp.StoredProcName := 'xp_addtest';
  param := xsp.Params.CreateParam(ftString, '@name', ptInputOutput);
  param.Size := 50;
  param.AsString := 'AAAAAAAAA';

  xsp.ExecProc;

  conn.Disconnect;
end;

Post by **EgonHugeist** » 16.04.2012, 22:47

ManoShu,

Nice to see sombody uses Zeos7+MSSQL+XE2. Now i must admit i've minor expirences with this server but i'm starting to rework that plaindriver the next days.

I've several questions and it would be wonderfull if you've time and patience enough to help me that i can help you.

1. Which MS Server version do you use?
2. How did you connect to the server? With ADO or the ntwdblib.dll?
3. did you try to use double @@?
4. May i ask you some questions concerning some MSSQL functionality questions?
5. Are all chars witten well to the db at the moment?
.
.
.

King regards,

Michael

ManoShu · Post by **ManoShu** » 17.04.2012, 01:50

Hello Michael and thanks for the reply.
I am glad Zeoslib have always someone to improve it.

Now, for the question list:

1) Used SQL Server 2008 R2 Express;
2) Using ado protocol; (not using mssql because of the 64-bit limitation)
3) Used double @ on the CreateParam, i will test more tomorrow;
4) I'm not a MSSQL expert, but i will help with anything i can answer;
5) Don't know if i understand this question; if is something like "the written text on the field is the expected?" then yes.

Thanks in advance.

ManoShu · Post by **ManoShu** » 17.04.2012, 14:50

I was able to execute the procedure.

The solution is to add a Result parameter before the input parameters:
(putting it after the input ones causes the same exception)

Code: Select all

...

  xsp := TZStoredProc.Create(Self);
  xsp.Connection := conn;

  xsp.StoredProcName := 'xp_addtest';

  // *** AND THIS IS THE MAGIC LINE ***
  xsp.Params.CreateParam(ftInteger, '@result', ptResult);

  param := xsp.Params.CreateParam(ftString, '@name', ptInputOutput);
  param.Size := 50;
  param.AsString := 'AAAAAAAAA';

  xsp.ExecProc;

  ...

It's kind of odd, because my test stored procedures (INSERTs and SELECTs) always return a zero (0) for result, which looks useless (at least on my case). Is plausible to do this (create this "dummy" parameter) internally?

Also, it looks that ZStoredProc is unable to automatically get the parameter data from the MS SQL proc. Is there some way to do this?

Again, thanks for the help.

Post by **EgonHugeist** » 17.04.2012, 17:52

ManoShu,

thanks for your config details. The character concerned question was a little bit unlucky formed

. Anyway your answer makes me happy. Did you test that with Server Magager Studio too? Which CharacterSet do you use?

concerning the execution of your procedure i must say that workaround is ugly but works. And a suitable result is the target or not??

Did the error message came from Zeos or from the Server? Here we've to find out who is the troublmaker! Zeos or MS Server.

To the TZStoredProcedure: I think here we've a leak, but im not sure. What's going wrong? Our C-Coders are mostly in a good position to bring out some findings.

Like i sayd i'll start some changes the next days and if i have the time for it. Actually i'm concentrating me to a non ADO alternative direct access dll. The FreeTDS driver which is platform portable. But here i've absolutly no suitable results actually.

It would be fine to stay in contact further times (testing -> solving). Our SQL Server users are rare.

Michael

ManoShu · Post by **ManoShu** » 18.04.2012, 19:04

EgonHugeist,

I have tested the SPs with SQL Server Management Studio and they are running like they should. The database have Latin1_General_CI_AS collation.

The zero return value don't matter for my needs, my SPs will execute something internally or retrieve data which is used opening and navigating thought the rows.

The "parameter not supplied" error message is originally from SQL Server itself.
I have noted that passing a empty string ('') as value for parameter makes the proc "ignore" the setting, causing the above error.

Code: Select all

...

param := xsp.Params.CreateParam(ftString, '@name', ptInputOutput);
param.Size := 50;

param.AsString := 'AAA'; 

proc.ExecSQL; //works fine

param.AsString := ''; 

proc.ExecSQL; //"parameter not supplied" error

...

For the TZStoredProc not getting the metadata from MS SQL Server,
i don't even know if it has the capability using ado protocol.

Post by **EgonHugeist** » 19.04.2012, 00:05

ManoShu,

i like C coders. They allways have an answer and suitable results...

nice to see. Minor work for me concering the ADO connection. So what do you think about FreeTDS? Just a personal question.

Mayby your zero-value doesn't matter, and if the server raises his exceptions all is fine for me. (you see i'm starting here..)

For the TZStoredProc not getting the metadata from MS SQL Server,
i don't even know if it has the capability using ado protocol.

That would be great and sticky to find out, or not? We almost get MetaDate from the ADO connection. But here i also don't know if there is a support in any kind.

i'm sorry didn't start yet. Just fixing minor bugs from our free supported servers.

Michael

ManoShu · Post by **ManoShu** » 20.04.2012, 15:30

EgonHugeist,

i have read about FreeTDS and i think this is a good idea to replace zeos's method of accessing MS SQL server using the current dll,
because i just using the ado protocol because of said MSSql dll not work in x64 enviroments.

I tried to understand how the ado protocol get the metadata and found a "bug" in
ZStoredProcedure.pas, SetStoredProcName method, line 324 (testing-egonhugeist branch, revision 1171):

Code: Select all


...

SplitQualifiedObjectName(Value, Catalog, Schema, ObjectName);


ObjectName := Connection.DbcConnection.GetMetadata.AddEscapeCharToWildcards(ObjectName); // <- That one

ResultSet := Connection.DbcConnection.GetMetadata.GetProcedureColumns(Catalog, Schema, ObjectName, '');

...

when using a proc name as "xp_getAllData", this escaping change the name to "'xp/_getAllData'", and don't find the metadata. However, using a initial name like "xpgetAllData" (without the underscore), this escaping do not occour and it is able to automatically get the parameter info!

I don't know what is the impact in avoid this escaping at least on MS SQL Server, but is a start...

Thank you for the help.

EDIT: The empty string parameter value error still occours, i will try to find where it can resolved.

Post by **EgonHugeist** » 20.04.2012, 15:59

That's a good start to fix a bug. Now we've to find out why this strange escaping was done.

Maybe some Documentations can help here.

ManoShu, it's a nightmare with fixing the other plaindrivers. Didn't start with furter lines of freeTDS.

Allways thank you for suitable result...

Post by **EgonHugeist** » 20.04.2012, 18:35

New results of mine:

The function AddEscapeCharToWildcards calls the overiden function of:

function TZAdoDatabaseInfo.GetSearchStringEscape: string;
begin
{ TODO -ofjanos -cgeneral :
In sql server this must be specified as the parameter of like.
example: WHERE ColumnA LIKE '%5/%%' ESCAPE '/' }
Result := '/';
end;

which sets this escape chars. I'm sure everything is fine if that procedure would'nt be called which means to comment it out. But what about behavior of other statements? Is it possible that this escaping has Server Version dependencies?

And it becomes more complicated:
http://stackoverflow.com/questions/2587 ... expression

http://msdn.microsoft.com/en-us/library/ms179859.aspx

ManoShu · Post by **ManoShu** » 20.04.2012, 19:25

EgonHugeist,

i don't know exactly, but any provider (mysql, firebird, postgreSql,...) need some characters to be escaped on the procedure name (not taking the parameter values on account)?

As on the links you give, this escaping is necessary in the LIKE section of a SELECT statement.

For me, it's kind of odd to escape the name of a stored procedure, which is not a direct query to the database.

Also the method used is exclusive to SPs, so i think it will not change the behavior of other areas, just commented this line:

Code: Select all

SplitQualifiedObjectName(Value, Catalog, Schema, ObjectName);

//ObjectName := Connection.DbcConnection.GetMetadata.AddEscapeCharToWildcards(ObjectName);

ResultSet := Connection.DbcConnection.GetMetadata.GetProcedureColumns(Catalog, Schema, ObjectName, '');

My only concern is if this is safe to do without breaking other providers.

Post by **EgonHugeist** » 21.04.2012, 00:50

ManoShu,

this is what we've to find out. But i agree here.

In my mind it isn't wanted to escape an ObjectName with a GetSearchStringEscape Char.
Patch done Rev. 1181 \Testing and Rev. 1182 \testing-egonhugeist

Michael

ManoShu · Post by **ManoShu** » 23.04.2012, 18:39

Ok, with this issue solved, i am just struggling against the "empty parameter error", but i verified it happens when setting any parameter to the type's "default" as:

string = ''
integer = 0
boolean = false
s
i tested the procedure with this 3 types of fields and it just don't use this values.

A workaround for this is set the SPs parameter's default values to those who are being set. But anyway it's so much obvious that something is really wrong.

This issue has happened before and/or with other providers?

Post by **EgonHugeist** » 23.04.2012, 21:43

ManoShu,

it's a terrible way if i don't know for sure what's right or not.

Anyway please test the empty values with String 'NULL' instead. If that works, than we've a suitable workaround which suppresses the Exceptions. But normaly exactly this should be done automatically in the sources. I know this is a part of the TZStatement-behavior for empty values. But i'm not sure what about TZStroredProcedures...

Is 'NULL' supported for procedures as empty value? As far as i know the Procedure gives his parameters to a callable-Statement. My thought is that we've a leak right there, eventually.

Maybe some threads here in the forum can give you more answers then me.

best regards,

Michael

ManoShu · Post by **ManoShu** » 23.04.2012, 22:04

EgonHugeist,

Tested the 'NULL' string. It just write as a normal string.

What is weird is the error happens even when passing a bit (boolean) parameter.
Sending a value as false (0) throws the exception, while if the value is true, the value is passed normally.

In the integer case, any number different from zero (0) is passed to the SPs as it should.

For me it's kind of a strange rule to execute SPs:
never use default parameters values

Did a bit of search, but i don't found something similar.

BUT, i found why the stored procedure needed to be escaped:

Escaping added: http://zeos.firmos.at/viewtopic.php?t=2035
Similar problem with the escaping: http://zeos.firmos.at/viewtopic.php?t=2755