Board index » delphi » D2 and problems with Stored Procedure Component on MS SQL Server

D2 and problems with Stored Procedure Component on MS SQL Server

Hello all!!  I am trying to use a stored procedure I created on an MS SQL
server (6.0).  And I can't seem to figure out how to use the stored procedure
component to access it.  Here is the Procedure I have on the server:

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

CREATE PROCEDURE SelectElement (@RETURN_VALUE Int OUTPUT,@DatabaseName char
(30),@OrderNumber char (8))
AS

SELECT DISTINCT *
FROM MasterElement
WHERE MasterElement.Name = @DatabaseName AND MasterElement.Number NOT IN
(SELECT DISTINCT OrderElement.ElementNumber FROM OrderElement
WHERE OrderElement.OrderNumber = @OrderNumber) GO

I am trying to send 2 parameters: DatabaseName, and OrderNumber.  (The
RETURN_VALUE was put in by Delphi when I first tried it, so I added
RETURN_VALUE to the SQL server storedProc to satisfy the error messages. (Can
anyone explain this one to me too?)  Anyway, when I try to set parameters
values at designtime I get a blank result set.  I have tried putting the values
in that I know are there for the params: with and without quotes.  I set their
type to Input with the exception of the RETURN_VALUE which seems to prefer to
be Output.  Also, how do I set these values programatically during run time?  
Please help, the on-line help resources are pretty dismal and I am running into
a wall with respect to what I can find on my own.

TIA to anyone who can steer me in the right direction!!!

Kelly
kgr...@acxiom.com

 

Re:D2 and problems with Stored Procedure Component on MS SQL Server


Quote
kgr...@acxiom.com (Kelly) wrote:

> Hello all!!  I am trying to use a stored procedure I created on an MS SQL
> server (6.0).  And I can't seem to figure out how to use the stored procedure
> component to access it.  Here is the Procedure I have on the server:

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

> CREATE PROCEDURE SelectElement (@RETURN_VALUE Int OUTPUT,@DatabaseName char
> (30),@OrderNumber char (8))
> AS

> SELECT DISTINCT *
> FROM MasterElement
> WHERE MasterElement.Name = @DatabaseName AND MasterElement.Number NOT IN
> (SELECT DISTINCT OrderElement.ElementNumber FROM OrderElement
> WHERE OrderElement.OrderNumber = @OrderNumber) GO

Be strong, I have the same problem, I'm currently waiting for mail from
John Murfy who might be able to solve our problem.

I wil forward everything 2 u.

Bye.

Re:D2 and problems with Stored Procedure Component on MS SQL Server


Hi all...well, I solved my own problem and thought some of you might be
interested in things due to some of the comments and help I have received.  As
it turns out, My stored proc on the MS SQL server had an error in the select
statement which would only return an empty dataset. However, when I corrected
it, the stored proc accepted my parameters and correctly returned the dataset I
needed.  This IS using ODBC as opposed to SQL Links (Some people had written
me saying you can''t use stored procs with ODBC..only with SQL Links).  At
first I didn't know what the mysterious RETURN_VALUE was in the parameter list
of the stored proc component (and with no documentation, I didn't know it was
wrong), so, I just added it as a parameter in my stored proc on the SQL server
side as type OUTPUT and I just send it a value of zero.  Doing this lets the
stored proc component access the data via ODBC.  I can use the ParamByName to
send the values progamatically during runtime and all is well.  If you are
doing this using SQL Links...try it this way as it may be faster.  Thanks to
all for the suggestions and hope this might be of benefit to others!!

Later,

Kelly
kgr...@acxiom.com

Re:D2 and problems with Stored Procedure Component on MS SQL Server


Wow!

I was banging my head into a wall for awhile on
this one!  It looks like you beat out Borland's
Tech Support also with your solution!  Their
solution was to not use TStoredProc!

Thanks for the great work!

--
Bruce Goldstein
Software Engineer
Interactive Strategies, Inc.
br...@centcon.com

Quote
Kelly wrote:

> Hi all...well, I solved my own problem and thought some of you might be
> interested in things due to some of the comments and help I have received.  As
> it turns out, My stored proc on the MS SQL server had an error in the select
> statement which would only return an empty dataset. However, when I corrected
> it, the stored proc accepted my parameters and correctly returned the dataset I
> needed.  This IS using ODBC as opposed to SQL Links (Some people had written
> me saying you can''t use stored procs with ODBC..only with SQL Links).  At
> first I didn't know what the mysterious RETURN_VALUE was in the parameter list
> of the stored proc component (and with no documentation, I didn't know it was
> wrong), so, I just added it as a parameter in my stored proc on the SQL server
> side as type OUTPUT and I just send it a value of zero.  Doing this lets the
> stored proc component access the data via ODBC.  I can use the ParamByName to
> send the values progamatically during runtime and all is well.  If you are
> doing this using SQL Links...try it this way as it may be faster.  Thanks to
> all for the suggestions and hope this might be of benefit to others!!

> Later,

> Kelly
> kgr...@acxiom.com

Re:D2 and problems with Stored Procedure Component on MS SQL Server


Hi again, I just wanted to post a note here I received in response to this
posting.  I can't believe the response Bruce got from Borland.  Why can't
their support efforts live up to the potential of Delphi/Delphi2??

Kelly

Here is the note I received:

Quote
>Wow!
>I was banging my head into a wall for awhile on this one!  It looks like you
>beat out Borland's Tech Support also with your solution!  Their solution was
>to not use TStoredProc!
>Thanks for the great work!
>--
>Bruce Goldstein
>Software Engineer
>Interactive Strategies, Inc.
>br...@centcon.com
Kelly wrote:

> Hi all...well, I solved my own problem and thought some of you might be
> interested in things due to some of the comments and help I have received.  

As > it turns out, My stored proc on the MS SQL server had an error in the
select
Quote
> statement which would only return an empty dataset. However, when I corrected
> it, the stored proc accepted my parameters and correctly returned the dataset

I

Quote
> needed.  This IS using ODBC as opposed to SQL Links (Some people had written
> me saying you can''t use stored procs with ODBC..only with SQL Links).  At
> first I didn't know what the mysterious RETURN_VALUE was in the parameter

list > of the stored proc component (and with no documentation, I didn't know
it was > wrong), so, I just added it as a parameter in my stored proc on the
SQL server > side as type OUTPUT and I just send it a value of zero.  Doing
this lets the
Quote
> stored proc component access the data via ODBC.  I can use the ParamByName to
> send the values progamatically during runtime and all is well.  If you are
> doing this using SQL Links...try it this way as it may be faster.  Thanks to
> all for the suggestions and hope this might be of benefit to others!!

> Later,

> Kelly
> kgr...@acxiom.com

Other Threads