Board index » delphi » TStoredProc Param Usage In MSSql Server 7???

TStoredProc Param Usage In MSSql Server 7???

  I'm trying to execute a stored procedure on our Sql Server using the
TStoredProc component and I'm having a small problem. First the header
for the stored procedure is as follows:
        CREATE procedure dbo.up_TPVUPDT
            @TPVFile varchar(10)

so really this is just one parameter and the procedure does not (or
should not, according to the programmer who wrote it) return any values.
When I go into the properties of the stored procedure component and open
the parameter editor I see the following:
        0-RETURN VALUE
        1-@TPVFile

the instruction book I have really does not go into the proper use of
this component and initially I just thought I had to assign a value to
parameter 1 (since it seems to know the name and number of parameters),
so I tried:
        StoredProc1.Params[1].AsString := UpperCase(Copy( Name, 1,
Pos('.',Name)-1));
which is really just a file name without the period or extenstion. When
I tried this I got an index out of range message so I tried adding:
StoredProc1.Params.Add;   StoredProc1.Params.Add;
before the assignment to add two items to the parameters list. When I
ran this I got this message:

        Project X raised exception class EDatabaseError with message
        'StoredProc1:Field''is of an unknown type' etc.

but not at the actual assignment but on the statement:

          StoredProc1.Prepare;

what is the correct way to pass this parameter to the StoredProc1.Param
property?

Thanks,
Chris LeFebvre

 

Re:TStoredProc Param Usage In MSSql Server 7???


The parameter list you see in Delphi's object inspector is essentially
correct. You can see the first and only input parameter called @TPVFile. The
return value is always displayed, even if you don't use it in the actual
code of your sp. What you need to do is this :

1)Set the name of the strored proc. Remove the " ;1 " after the name in the
object inspector. If that doesn't work, either clear the name and retype it
(or copy, clear, go to another property, come back to the property and
paste) or right-click on your form or data-module, select "view as text",
edit the name property value for StoredProc1, right-click again and select
"view as form".
2)Set the type of @TPVFile to input. (params property of TStoredProc)
3)In your Delphi code, type :

StoredProc1.ParamByName('@TPVFile').AsString := MyString;
StoredProc1.ExecProc;

MyString can be any expression of type string. You might want to do a simple
test like :

Edit1.Text := UpperCase(Copy( Name, 1, Pos('.',Name)-1));

to make sure your string expression is syntactically correct.

HTH

--
Alain Quesnel

P.S. : Remove the [brackets] on each side of my address to reply by e-mail

Chris LeFebvre a crit dans le message <3874D286.2DD75...@CBRLD.Com>...

Quote
>  I'm trying to execute a stored procedure on our Sql Server using the
>TStoredProc component and I'm having a small problem. First the header
>for the stored procedure is as follows:
>        CREATE procedure dbo.up_TPVUPDT
>            @TPVFile varchar(10)

>so really this is just one parameter and the procedure does not (or
>should not, according to the programmer who wrote it) return any values.
>When I go into the properties of the stored procedure component and open
>the parameter editor I see the following:
>        0-RETURN VALUE
>        1-@TPVFile

>the instruction book I have really does not go into the proper use of
>this component and initially I just thought I had to assign a value to
>parameter 1 (since it seems to know the name and number of parameters),
>so I tried:
>        StoredProc1.Params[1].AsString := UpperCase(Copy( Name, 1,
>Pos('.',Name)-1));
>which is really just a file name without the period or extenstion. When
>I tried this I got an index out of range message so I tried adding:
>StoredProc1.Params.Add;   StoredProc1.Params.Add;
>before the assignment to add two items to the parameters list. When I
>ran this I got this message:

>        Project X raised exception class EDatabaseError with message
>        'StoredProc1:Field''is of an unknown type' etc.

>but not at the actual assignment but on the statement:

>          StoredProc1.Prepare;

>what is the correct way to pass this parameter to the StoredProc1.Param
>property?

>Thanks,
>Chris LeFebvre

Re:TStoredProc Param Usage In MSSql Server 7???


        Alain:
  Using a little test program I wrote, I followed your instructions step by step
and unfortunatly I'm still having the same 'Field is of unknown type' problem
when the Prepare method is executed. Examining the parameter properties in the
parameter editor I see that the datatype is set to ftstring and the paramtype is
set to ptinput. If I view the form as text this is what I see:

     object Form1: TForm1
       Left = 794
       Top = 232
       Width = 634
       Height = 426
       Caption = 'Test TPV Stored Proc'
       Color = clBtnFace
       Font.Charset = DEFAULT_CHARSET
       Font.Color = clWindowText
       Font.Height = -16
       Font.Name = 'Gourmand'
       Font.Style = []
       OldCreateOrder = False
       PixelsPerInch = 96
       TextHeight = 19
       object Button1: TButton
         Left = 56
         Top = 24
         Width = 75
         Height = 25
         Caption = 'Test'
         TabOrder = 0
         OnClick = TestClick
       end
       object dbCBRLD: TDatabase
         AliasName = 'CBRLD'
         Connected = True
         DatabaseName = 'CBRLD'
         LoginPrompt = False
         Params.Strings = (
           'USER NAME=XXXXX'
           'PASSWORD=XXXXX'
           '')
         SessionName = 'Default'
         Left = 134
         Top = 232
       end
       object tblTPV: TTable
         DatabaseName = 'CBRLD'
         TableName = 'dbo.tpv'
         Left = 186
         Top = 232
       end
       object DataSource1: TDataSource
         DataSet = tblTPV
         Left = 239
         Top = 232
       end
       object Query1: TQuery
         DatabaseName = 'CBRLD'
         DataSource = DataSource1
         Left = 291
         Top = 232
       end
       object StoredProc1: TStoredProc
         DatabaseName = 'CBRLD'
         StoredProcName = 'up_TPVUPDT'
         Left = 336
         Top = 232
         ParamData = <
           item
             DataType = ftInteger
             Name = 'RETURN_VALUE'
             ParamType = ptResult
           end
           item
             DataType = ftString
             Name = '@TPVFile'
             ParamType = ptInput
           end>
       end
     end

and the only real code I use to test the stored procedure is as follows:

       dbCBRLD.Connected := True;
       tblTPV.Active := True;

       StoredProc1.Params.Add; StoredProc1.Params.Add;
       StoredProc1.Params[1].AsString := UpperCase('wt000104');
       StoredProc1.Prepare;

       { StoredProc1.ExecProc; }

       tblTPV.Active := False;
       dbCBRLD.Connected := False;

I've spent the weekend looking at this and thinking about it and I just can't
see a problem, do you see anything that I'm missing, any ideas?

Thanks,
Chris LeFebvre

Re:TStoredProc Param Usage In MSSql Server 7???


Do you know which line creates the Access violation. Try putting a
breakpoint at the first line, then step through your code by pressing F8 to
execute the next line.

--

Alain Quesnel
alainnos...@argosoftware.com

Quote
Chris LeFebvre wrote in message <3879DEC6.8C876...@CBRLD.Com>...
>        Alain:
>  Using a little test program I wrote, I followed your instructions step by
step
>and unfortunatly I'm still having the same 'Field is of unknown type'
problem
>when the Prepare method is executed. Examining the parameter properties in
the
>parameter editor I see that the datatype is set to ftstring and the
paramtype is
>set to ptinput. If I view the form as text this is what I see:

>     object Form1: TForm1
>       Left = 794
>       Top = 232
>       Width = 634
>       Height = 426
>       Caption = 'Test TPV Stored Proc'
>       Color = clBtnFace
>       Font.Charset = DEFAULT_CHARSET
>       Font.Color = clWindowText
>       Font.Height = -16
>       Font.Name = 'Gourmand'
>       Font.Style = []
>       OldCreateOrder = False
>       PixelsPerInch = 96
>       TextHeight = 19
>       object Button1: TButton
>         Left = 56
>         Top = 24
>         Width = 75
>         Height = 25
>         Caption = 'Test'
>         TabOrder = 0
>         OnClick = TestClick
>       end
>       object dbCBRLD: TDatabase
>         AliasName = 'CBRLD'
>         Connected = True
>         DatabaseName = 'CBRLD'
>         LoginPrompt = False
>         Params.Strings = (
>           'USER NAME=XXXXX'
>           'PASSWORD=XXXXX'
>           '')
>         SessionName = 'Default'
>         Left = 134
>         Top = 232
>       end
>       object tblTPV: TTable
>         DatabaseName = 'CBRLD'
>         TableName = 'dbo.tpv'
>         Left = 186
>         Top = 232
>       end
>       object DataSource1: TDataSource
>         DataSet = tblTPV
>         Left = 239
>         Top = 232
>       end
>       object Query1: TQuery
>         DatabaseName = 'CBRLD'
>         DataSource = DataSource1
>         Left = 291
>         Top = 232
>       end
>       object StoredProc1: TStoredProc
>         DatabaseName = 'CBRLD'
>         StoredProcName = 'up_TPVUPDT'
>         Left = 336
>         Top = 232
>         ParamData = <
>           item
>             DataType = ftInteger
>             Name = 'RETURN_VALUE'
>             ParamType = ptResult
>           end
>           item
>             DataType = ftString
>             Name = '@TPVFile'
>             ParamType = ptInput
>           end>
>       end
>     end

>and the only real code I use to test the stored procedure is as follows:

>       dbCBRLD.Connected := True;
>       tblTPV.Active := True;

>       StoredProc1.Params.Add; StoredProc1.Params.Add;
>       StoredProc1.Params[1].AsString := UpperCase('wt000104');
>       StoredProc1.Prepare;

>       { StoredProc1.ExecProc; }

>       tblTPV.Active := False;
>       dbCBRLD.Connected := False;

>I've spent the weekend looking at this and thinking about it and I just
can't
>see a problem, do you see anything that I'm missing, any ideas?

>Thanks,
>Chris LeFebvre

Re:TStoredProc Param Usage In MSSql Server 7???


        Alain:
  Ok, I have this figured out but it's really strange and I'm going to have to
try it a couple of times to see if the behavior of the StoredProc component is
consistent. Here's what I believe happened. I initially created the form and
placed my data access components on it and set all the properties then in my
code I simply tried to set the single parameter like this:

            StoredProc1.Params[1].AsString := UpperCase('wt000104');
            StoredProc1.Prepare;
            StoredProc1.ExecProc;

with no other code accessing the StoredProc1 component in any way. When I ran
this I got an Index Out Of Bounds error (I don't have the exact message) when I
assigned the parameter (as in the first statement above), so I figured that the
parameters don't exist unless you add them. Sort of like a ListBox doesn't have
any items unless you add them, so as in the second piece of test code I gave
you:

            dbCBRLD.Connected := True;
            tblTPV.Active := True;

            StoredProc1.Params.Add; StoredProc1.Params.Add;
            StoredProc1.Params[1].AsString := UpperCase('wt000104');
            StoredProc1.Prepare;

            { StoredProc1.ExecProc; }

            tblTPV.Active := False;
            dbCBRLD.Connected := False;

I used StoredProc1.Params.Add to add two parameters to the parameters list. This
seemed to work once but afterwards I kept getting the Field Is Of Unknown Type
error. I then checked StoredProc1.Params.Count in the watch list and it showed
more than 2 parameters. What I think happens is that once the parameters are
added they are maintained between sessions. For example the process that I'm
running is initiated by a Start Button (TButton) and tracing right at the start
of the OnClick Event of that button StoredProc1.Params.Count = 4 and this is
after having exited Delphi completely and started it again reopened the project,
rebuilt etc. and then started tracing. So to get this working I just added code
to check the parms count and add params if there's less than two and delete them
if there's more than two. Strange but it seems to have worked once and I'll have
to try it again tommorow.

Thanks,
Chris LeFebvre

Re:TStoredProc Param Usage In MSSql Server 7???


An index out of bounds occurs when you attempt to access an item in a list
(TList, TColumnList, TParams, TStringList, etc.) with an index that is
higher than the list's count - 1. May I suggest using
TQuery.ParamByName('@TPVFile'), since this method doesn't care about the
index of the parameter.

--

Alain Quesnel
alainnos...@argosoftware.com

Quote
"Chris LeFebvre" <Ch...@CBRLD.Com> wrote in message

news:387C87A2.BC659BDA@CBRLD.Com...
Quote
>         Alain:
>   Ok, I have this figured out but it's really strange and I'm going to
have to
> try it a couple of times to see if the behavior of the StoredProc
component is
> consistent. Here's what I believe happened. I initially created the form
and
> placed my data access components on it and set all the properties then in
my
> code I simply tried to set the single parameter like this:

>             StoredProc1.Params[1].AsString := UpperCase('wt000104');
>             StoredProc1.Prepare;
>             StoredProc1.ExecProc;

> with no other code accessing the StoredProc1 component in any way. When I
ran
> this I got an Index Out Of Bounds error (I don't have the exact message)
when I
> assigned the parameter (as in the first statement above), so I figured
that the
> parameters don't exist unless you add them. Sort of like a ListBox doesn't
have
> any items unless you add them, so as in the second piece of test code I
gave
> you:

>             dbCBRLD.Connected := True;
>             tblTPV.Active := True;

>             StoredProc1.Params.Add; StoredProc1.Params.Add;
>             StoredProc1.Params[1].AsString := UpperCase('wt000104');
>             StoredProc1.Prepare;

>             { StoredProc1.ExecProc; }

>             tblTPV.Active := False;
>             dbCBRLD.Connected := False;

> I used StoredProc1.Params.Add to add two parameters to the parameters
list. This
> seemed to work once but afterwards I kept getting the Field Is Of Unknown
Type
> error. I then checked StoredProc1.Params.Count in the watch list and it
showed
> more than 2 parameters. What I think happens is that once the parameters
are
> added they are maintained between sessions. For example the process that
I'm
> running is initiated by a Start Button (TButton) and tracing right at the
start
> of the OnClick Event of that button StoredProc1.Params.Count = 4 and this
is
> after having exited Delphi completely and started it again reopened the
project,
> rebuilt etc. and then started tracing. So to get this working I just added
code
> to check the parms count and add params if there's less than two and
delete them
> if there's more than two. Strange but it seems to have worked once and
I'll have
> to try it again tommorow.

> Thanks,
> Chris LeFebvre

Other Threads