Board index » delphi » Retriving SP Params info in delphi without finging SP

Retriving SP Params info in delphi without finging SP

Hi,

I am positng this message again because I havn;t yet got the solution and
some of u seems to have not understood my question.

Here is my samlpe SP
............
create procedure MyTestPro
(@P1 as varchar(100) OUTPUT,
@P2 as varchar(400) OUTPUT,
@P3 as varchar(50) OUTPUT,
@P4 as varchar(50) OUTPUT,
@p5 as varchar(500) OUTPUT,
@p6 as int output,
@p7 as varchar(500) output)
as

set @p1 = 1
set @p2 = 1
set @p3 = 1
set @p4 = 1
set @p5 = 1
set @p6 = 1
set @p7 = 1

Return @p1

...........

Now I want to retrive  the  SP Params info in delphi without finging SP.

This is what I have tried

procedure Form1.Button1Click(Sender: TObject);
 var
   SP : TStoredProc;
 begin
 SP := TStoredProc.Create(Self);
 try
   SP.DatabaseName := 'DBName';
   SP.StoredProcName := 'DelMessage';
   SP.Prepare;
   ShowMessage( IntToStr(SP.ParamCount)) //.. first problem is this value
isnot comming right.

 finally
  SP.Free
End.

Thanks
Ary

 

Re:Retriving SP Params info in delphi without finging SP


To pick up from previous thread.... I think you problem is with varchars.
BDE is really bad with those. Try to change them to chars and see what
happens. If it's applicable (don't know what's your db backend), you
could/should also try your stored procedure (with varchars) using ADO.

rb

Quote
"Ary" <arus...@rediffmail.com> wrote in message news:3bd1a1d0$1_1@dnews...
> ............
> create procedure MyTestPro
> (@P1 as varchar(100) OUTPUT,
> @P2 as varchar(400) OUTPUT,
> @P3 as varchar(50) OUTPUT,
> @P4 as varchar(50) OUTPUT,
> @p5 as varchar(500) OUTPUT,
> @p6 as int output,
> @p7 as varchar(500) output)

Re:Retriving SP Params info in delphi without finging SP


Hi RB

My Backend if SQL Server.

And I can't  change the dattype

My problem is at design time delphi gives properly all the params and its
related infro in Params window.

So why  can't I get the same at run time.

Thanks
........

Re:Retriving SP Params info in delphi without finging SP


Hi RB

I tried with Char , but still it does not come

here is the new SP

CREATE PROCEDURE MYTest
(@P1 as char(100) OUTPUT,
@P2 as char(400) OUTPUT,
@P3 as char(50) OUTPUT,
@P4 as char(50) OUTPUT,
@p5 as char(500) OUTPUT,
@p6 as int output,
@p7 as int output)
 AS
select @p6 = 0, @p7 = 0
return @p6

...

Here is the code i have tried

procedure TForm1.Button1Click(Sender: TObject);
var
   SP : TStoredProc;
 begin
 SP := TStoredProc.Create(Self);
 try
   SP.DatabaseName := 'MYDB';
   SP.StoredProcName := 'MyTest';
   SP.Prepare;
   ShowMessage( IntToStr(SP.ParamCount)) //.. first problem is this vlaue is
comming as 1
finally
  SP.Free
 end;
end;
End.

...

I don;t know why this ParamCount is always comming as 1

...

Please let me know if any one is able to solve this

Thanks
Ary

Re:Retriving SP Params info in delphi without finging SP


You have to qualify the procedure name with owner:

    sp.StoredProcName = 'dbo.MyTestPro';

This will do the trick for you. There's one more problem I couldn't work
around:

    sp.Prepare;

generates an error (no parameter type for parameter @p1), but it actually
does retrieve everything as expected. I don't know if this is my D6 speaking
or am I forgetting something (did this million times before I switched to
ADO and D6).

Either way, the reason it works at design time is that it *does* show owner
qualifier in drop-down box when picking the procedure. It's very easy to
miss this detail.

Oh, BTW, don't ever return anything but integer from Stored Procedures. It's
not just rule of thumb, it's also in the help (RETURN [integer-expression]).
The reason why it works now is that you feed @p1 with an integer that
implicitly gets converted to varchar and back to integer when assigned to
RETURN.

rb

Quote
"Ary" <arus...@rediffmail.com> wrote in message news:3bd2f164_2@dnews...
> Hi RB

> My Backend if SQL Server.
> So why  can't I get the same at run time.

> Thanks
> ........

Re:Retriving SP Params info in delphi without finging SP


Hi RB

No still it doesn't work.  I have give 'dbo.MyTestPro'.

Well I tired for sp.prepare . It works well only if u create the params if
not already  created.

Probably u might have done at design time.

Her is the code.

   SP.Params.CreateParam(ftstring,'@p1',ptOutput);
   SP.Params.CreateParam(ftstring,'@p2',ptOutput);
   SP.Params.CreateParam(ftstring,'@p3',ptOutput);
   SP.Params.CreateParam(ftstring,'@p4',ptOutput);
   SP.Params.CreateParam(ftstring,'@p5',ptOutput);
   SP.Params.CreateParam(ftinteger,'@p6',ptOutput);
   SP.Params.CreateParam(ftinteger,'@p7',ptOutput);

Ony after doing all this I get the paramcount as  7 and also the prepare and
execute works.

Please let me know how did u got ParamCount without creating params like
above code.

May be u are droping the component at design time. And then u might be
clicking on Params properly. So internally I guess delphi creatrs the parms
like
what i wrote above.

Please let me know u'r code how it works for paramcount.

Thanks

Ary
...................

Quote
"rb" <ra...@killspam-videotron.ca> wrote in message

news:3bd30ea8$1_1@dnews...
Quote

> You have to qualify the procedure name with owner:

>     sp.StoredProcName = 'dbo.MyTestPro';

> This will do the trick for you. There's one more problem I couldn't work
> around:

>     sp.Prepare;

> generates an error (no parameter type for parameter @p1), but it actually
> does retrieve everything as expected. I don't know if this is my D6
speaking
> or am I forgetting something (did this million times before I switched to
> ADO and D6).

> Either way, the reason it works at design time is that it *does* show
owner
> qualifier in drop-down box when picking the procedure. It's very easy to
> miss this detail.

> Oh, BTW, don't ever return anything but integer from Stored Procedures.
It's
> not just rule of thumb, it's also in the help (RETURN

[integer-expression]).

- Show quoted text -

Quote
> The reason why it works now is that you feed @p1 with an integer that
> implicitly gets converted to varchar and back to integer when assigned to
> RETURN.

> rb

> "Ary" <arus...@rediffmail.com> wrote in message news:3bd2f164_2@dnews...
> > Hi RB

> > My Backend if SQL Server.
> > So why  can't I get the same at run time.

> > Thanks
> > ........

Re:Retriving SP Params info in delphi without finging SP


It worked for me as I described - design and run time (I had a test project
going). So it must be that you're doing something wrong. I hate to bug you
(or repeat myself), but typos are usually good candidates where to start
debugging problems like this. For example, your earlier posting stated
CREATE PROCEDURE MyTest. Yet, you keep assigning MyTestPro to SP. Further,
if you logon as Ary and run

    CREATE PROCEDURE MyTest

it will be created as Ary.MyTest. And if your server is case sensitive,
ary.MyTest <> Ary.Mytest <> Ary.MyTest etc.

To ensure all your objects are owned by dbo (or by any other user), qualify
the object with user name when creating it:

    CREATE PROCEDURE dbo.MyTest

rb

Quote
"Ary" <arus...@rediffmail.com> wrote in message news:3bd33c2e$1_1@dnews...
> Hi RB

> No still it doesn't work.  I have give 'dbo.MyTestPro'.

Other Threads