Board index » delphi » Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP

Frank H. Shaw

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP

Can some look at the following mess and help me with this I am having real
trouble doing this for the first time. I need some guidence on doing this -
One of the problems is do not know if I am calling the actual stored
procedure sitting on the server - When I do a Open in the function
GetUniqueCaseNo in delphi - I get the message General Sql error - Is their a
way to tell if the actual stored procedure is getting called and does the
following code make any senece - Is the return value from the stored
proceduere correct coded and should my function be able to trap that return
value. More imporant did I set up the stored procedure TStoredProc in delphi
correctly or not. What I want to do at this point is pass NO ARGUMENTS to
the stored procedure in sql server on the server but GET A RETURN VALUE back
to my function in delphi. What type of error handling can I do or should I
do in both my function and stored procedure. Note I want to create the
TStoredProc on the fly like I have done below. THANK YOU -
FS...@Runestones.com

Function GetUniqueCaseNo: Integer;
Var
   SProc: TStoredProc;

Begin
   try
     SProc := TStoredProc.Create(Nil);
     SProc.DatabaseName := 'MyDataBase';
     SProc.StoredProcName := 'dbo.GenUniqueCaseID';
     SProc.Params.CreateParam(ftInteger, 'Result', ptOutPut);
     SProc.Open;
     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the value
that was returned from Stored Procedure

   finally
     SProc.Free;

   End;

End;

if exists (select * from sysobjects where id =
object_id('dbo.GenUniqueCaseID') and sysstat & 0xf = 4)
 drop procedure dbo.GenUniqueCaseID
GO

CREATE PROCEDURE GenUniqueCaseID
@UID int
As
Begin
 Begin Tran
 update SystemInfo
 set Last_Case_No = Last_Case_No + 1
 Select @UID = Last_Case_No
 From SystemInfo
 Commit Tran
 Return @UID

End
GO

 
Tomislav Kard

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP


Hi Frank!

On Tue, 18 Apr 2000 13:00:48 -0400, "Frank H. Shaw"

Quote
<fs...@runestones.com> wrote:
>One of the problems is do not know if I am calling the actual stored
>procedure sitting on the server - When I do a Open in the function
>GetUniqueCaseNo in delphi - I get the message General Sql error - Is their a
>way to tell if the actual stored procedure is getting called and does the
>following code make any senece -

put the following line in the stored procedure and if you see the
exception message in the program then your SP is executed.

raiserror ('Stored procedure executed!',16,-1)

Quote
> Is the return value from the stored
>proceduere correct coded and should my function be able to trap that return
>value.

Personaly, i usualy do not use Result parameter for anything else
except 0 - indicating success, or > 0 as error code indicating
failure.

Quote
> Note I want to create the
>TStoredProc on the fly like I have done below.

Why doing that on the fly. Let the procedure be there as a part of the
database. My big project has over 300 stored procedures. Stored
procedures are part of your database logic and your exe program is a
front end process that only access database and initiates task that
database should do. This is a good programming practice.

Quote
>Function GetUniqueCaseNo: Integer;
>Var
>   SProc: TStoredProc;

>Begin
>   try
>     SProc := TStoredProc.Create(Nil);
>     SProc.DatabaseName := 'MyDataBase';
>     SProc.StoredProcName := 'dbo.GenUniqueCaseID';

put this:
  SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';

Quote
>     SProc.Params.CreateParam(ftInteger, 'Result', ptOutPut);
>     SProc.Open;

call ExecProc instead of Open:
  SProc.ExecProc;

Quote
>     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the value
>that was returned from Stored Procedure

>   finally
>     SProc.Free;

>   End;

>End;

tomi.
Frank H. Sha

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP


Can I Use the Print ' This Is the new CaseNo Created by Server :' @UID

But how do I add the @UID on the end of the string I tryied a + and I get a
error and not allowed to save the stored procedure.

Quote
> put this:
>   SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';

What does the ;1 Mean and why do I need to use it. - I Do not understand
THANKS

I figured that mastake after I posted the question - THANKS

Quote
> call ExecProc instead of Open:
>   SProc.ExecProc;

My Stored procedure has gotton a little better it now does not require a
argument passed to it which is what I wanted but how do I print out the
@UID - I tried a cast but that did not work do not know how to use the
convert function tried that also - note: I use SQL SERVER 6.5 -THANK

if exists (select * from sysobjects where id =
object_id('dbo.GenUniqueCaseID') and sysstat & 0xf = 4)
 drop procedure dbo.GenUniqueCaseID
GO

CREATE PROCEDURE GenUniqueCaseID
As
Begin

 Declare @UID Int

 Print 'Stored Procedure - GENUNIQUECASEID'

 Begin Tran
 update SystemInfo
 set Last_Case_No = Last_Case_No + 1
 Select @UID = Last_Case_No
 From SystemInfo
 Commit Tran
 Print cast(@UID as varchar(50))

 Return @UID

End
GO

Quote
Tomislav Kardas <nomail@sorry> wrote in message

news:38fdda00.25286486@forums.inprise.com...
Quote
> Hi Frank!

> On Tue, 18 Apr 2000 13:00:48 -0400, "Frank H. Shaw"
> <fs...@runestones.com> wrote:

> >One of the problems is do not know if I am calling the actual stored
> >procedure sitting on the server - When I do a Open in the function
> >GetUniqueCaseNo in delphi - I get the message General Sql error - Is
their a
> >way to tell if the actual stored procedure is getting called and does the
> >following code make any senece -

> put the following line in the stored procedure and if you see the
> exception message in the program then your SP is executed.

> raiserror ('Stored procedure executed!',16,-1)

> > Is the return value from the stored
> >proceduere correct coded and should my function be able to trap that
return
> >value.

> Personaly, i usualy do not use Result parameter for anything else
> except 0 - indicating success, or > 0 as error code indicating
> failure.

> > Note I want to create the
> >TStoredProc on the fly like I have done below.

> Why doing that on the fly. Let the procedure be there as a part of the
> database. My big project has over 300 stored procedures. Stored
> procedures are part of your database logic and your exe program is a
> front end process that only access database and initiates task that
> database should do. This is a good programming practice.

> >Function GetUniqueCaseNo: Integer;
> >Var
> >   SProc: TStoredProc;

> >Begin
> >   try
> >     SProc := TStoredProc.Create(Nil);
> >     SProc.DatabaseName := 'MyDataBase';
> >     SProc.StoredProcName := 'dbo.GenUniqueCaseID';

> put this:
>   SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';

> >     SProc.Params.CreateParam(ftInteger, 'Result', ptOutPut);
> >     SProc.Open;

> call ExecProc instead of Open:
>   SProc.ExecProc;

> >     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the
value
> >that was returned from Stored Procedure

> >   finally
> >     SProc.Free;

> >   End;

> >End;

> tomi.

Frank H. Sha

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP


Hi - I am Haveing trouble with General Sql Error Message - When The
SProc.ExecProc happens what could I be missing. Is my function correct - Can
someone send me snibilt of code that they use for call a stored procedure
from delphi. I did The changes that were sugusted and I think I am a lot
closer to solving my problem I think my problem lays some where in my
function my stored procedure does exec in a ISQL tool so I think my print
problem is solved. THANKS

Function GetUniqueCaseNo: Integer;
Var
   SProc: TStoredProc;

Begin
   try
     SProc := TStoredProc.Create(Nil);
     SProc.DatabaseName := 'MyDataBase';
     SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';
     SProc.Params.CreateParam(ftInteger, 'Result', ptOutPut);
     SProc.ExecProc;
     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the value
that was returned from Stored Procedure

   finally
     SProc.Free;

   End;

End;

if exists (select * from sysobjects where id =
object_id('dbo.GenUniqueCaseID') and sysstat & 0xf = 4)
 drop procedure dbo.GenUniqueCaseID
GO

CREATE PROCEDURE GenUniqueCaseID
As
Begin

 Declare @UID Int
 Declare @MessageString VarChar(255)

 Print 'Stored Procedure - GENUNIQUECASEID'

 Begin Tran
 update SystemInfo
 set Last_Case_No = Last_Case_No + 1
 Select @UID = Last_Case_No
 From SystemInfo
 Commit Tran
 Select @MessageString = 'This is the New CaseNo Created from the server :'
+ CONVERT(Char(20),@UID)
 Print @MessageString

 Return @UID

End
GO

Frank H. Shaw <fs...@runestones.com> wrote in message
news:8dkois$caj5@bornews.borland.com...

Quote
> Can I Use the Print ' This Is the new CaseNo Created by Server :' @UID

> But how do I add the @UID on the end of the string I tryied a + and I get
a
> error and not allowed to save the stored procedure.

> > put this:
> >   SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';

> What does the ;1 Mean and why do I need to use it. - I Do not understand
> THANKS

> I figured that mastake after I posted the question - THANKS

> > call ExecProc instead of Open:
> >   SProc.ExecProc;

> My Stored procedure has gotton a little better it now does not require a
> argument passed to it which is what I wanted but how do I print out the
> @UID - I tried a cast but that did not work do not know how to use the
> convert function tried that also - note: I use SQL SERVER 6.5 -THANK

> if exists (select * from sysobjects where id =
> object_id('dbo.GenUniqueCaseID') and sysstat & 0xf = 4)
>  drop procedure dbo.GenUniqueCaseID
> GO

> CREATE PROCEDURE GenUniqueCaseID
> As
> Begin

>  Declare @UID Int

>  Print 'Stored Procedure - GENUNIQUECASEID'

>  Begin Tran
>  update SystemInfo
>  set Last_Case_No = Last_Case_No + 1
>  Select @UID = Last_Case_No
>  From SystemInfo
>  Commit Tran
>  Print cast(@UID as varchar(50))

>  Return @UID

> End
> GO

> Tomislav Kardas <nomail@sorry> wrote in message
> news:38fdda00.25286486@forums.inprise.com...
> > Hi Frank!

> > On Tue, 18 Apr 2000 13:00:48 -0400, "Frank H. Shaw"
> > <fs...@runestones.com> wrote:

> > >One of the problems is do not know if I am calling the actual stored
> > >procedure sitting on the server - When I do a Open in the function
> > >GetUniqueCaseNo in delphi - I get the message General Sql error - Is
> their a
> > >way to tell if the actual stored procedure is getting called and does
the
> > >following code make any senece -

> > put the following line in the stored procedure and if you see the
> > exception message in the program then your SP is executed.

> > raiserror ('Stored procedure executed!',16,-1)

> > > Is the return value from the stored
> > >proceduere correct coded and should my function be able to trap that
> return
> > >value.

> > Personaly, i usualy do not use Result parameter for anything else
> > except 0 - indicating success, or > 0 as error code indicating
> > failure.

> > > Note I want to create the
> > >TStoredProc on the fly like I have done below.

> > Why doing that on the fly. Let the procedure be there as a part of the
> > database. My big project has over 300 stored procedures. Stored
> > procedures are part of your database logic and your exe program is a
> > front end process that only access database and initiates task that
> > database should do. This is a good programming practice.

> > >Function GetUniqueCaseNo: Integer;
> > >Var
> > >   SProc: TStoredProc;

> > >Begin
> > >   try
> > >     SProc := TStoredProc.Create(Nil);
> > >     SProc.DatabaseName := 'MyDataBase';
> > >     SProc.StoredProcName := 'dbo.GenUniqueCaseID';

> > put this:
> >   SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';

> > >     SProc.Params.CreateParam(ftInteger, 'Result', ptOutPut);
> > >     SProc.Open;

> > call ExecProc instead of Open:
> >   SProc.ExecProc;

> > >     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the
> value
> > >that was returned from Stored Procedure

> > >   finally
> > >     SProc.Free;

> > >   End;

> > >End;

> > tomi.

Tomislav Kard

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP


Hi Frank!

On Wed, 19 Apr 2000 12:56:31 -0400, "Frank H. Shaw"

Quote
<fs...@runestones.com> wrote:
>Can I Use the Print ' This Is the new CaseNo Created by Server :' @UID

Do not use Print in stored procedures, use raiserror as I said. You
can not catch print's from Delphi, at least not that I know.
But when you use raiserror then you get exception in Delphi so SP
parameter processing on the return is broken. You can not even read
Result param no matter if you ignore exception.

So you put raiserror only to test it the stored procedure works, and
then if you are satisfied with the result, remove the line and try
again to see if delphi's part works OK.

Quote
>But how do I add the @UID on the end of the string I tryied a + and I get a
>error and not allowed to save the stored procedure.

like this:
        raiserror ('Stored Procedure - GENUNIQUECASEID %d',16,-1, @UID)

Quote
>What does the ;1 Mean and why do I need to use it. - I Do not understand

Doesn't matter, you just put it there.

tomi.

Tomislav Kard

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Exectuting a Stored procedure within delphi - SQL SERVER 6.5 - I NEED SOME HELP


Hi Frank!

On Wed, 19 Apr 2000 16:10:15 -0400, "Frank H. Shaw"

Quote
<fs...@runestones.com> wrote:
>Function GetUniqueCaseNo: Integer;
>Var
>   SProc: TStoredProc;

>Begin
>   try
>     SProc := TStoredProc.Create(Nil);
>     SProc.DatabaseName := 'MyDataBase';
>     SProc.StoredProcName := 'dbo.GenUniqueCaseID;1';
>     SProc.Params.CreateParam(ftInteger, 'Result', ptOutPut);

Result is of ptResult type, not ptOutput

Quote
>     SProc.ExecProc;
>     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the value
>that was returned from Stored Procedure

>   finally
>     SProc.Free;

>   End;

>End;

tomi.

Other Threads

1. Stored Procedure MS Sql Server 6.5 and Delphi 2.0

2. MS SQL Server 6.5, Stored Procedures, Temp Tables and Delphi 4 C/S

3. Missing Records with Stored Procedures and SQL Server 6.5

4. Error Stored Procedure (SQL server 6.5)

5. TUpdateSQL and Stored Procedures on SQL Server 6.5

6. SQL Server 6.5 and Views & Store Procedures

7. Help! TDBMemo, Stored Procedures and SQL 6.5

8. Huge problem with Delphi 2.0 and SQL 6.5 Stored procedures

9. MS SQL 6.5,Delphi 2,Stored Procedures

10. Debuging MS SQL 6.5 stored procedures with Delphi