Board index » delphi » D4/sql 6.5 to D5/sql 6.5 creates Tstoredproc error

D4/sql 6.5 to D5/sql 6.5 creates Tstoredproc error

Delphi newsgroup,
I am in the process of moving a delphi 4 app using sql 6.5 to delphi 5 using
sql 7.0. Specifically, the TStoreProc is not working for sql stored
procedures with parameters. The application works fine in delphi 4/sql 6.5.
Using Delphi 4 client server & Delphi 5 Enterprise.

Any help you can help with this would be greatly appreciated. My IT unit
will soon pull the plug on the sql 6.5 server and I need to move all
applications over sql 7.0.
Furthermore, I would like to use some of the features of Delphi 5.

Question:
1) on sql 7.0 does the BDE handle stored procedures with parameters
different than those without parameters?....(Tstoredproc failed on stored
procedure with
parameters, whereas Tstoredproc without parameters opened successfully)

(scenario)
Layout:
Delphi 4 app using sql 6.5...works fine
Delphi 5 app using sql 7.0...uses same layout as delphi 4 app, but crashes
when Tstoredproc with parameters is set to true.

App uses a data module with one(1) TDatabase component and
several ttable & tstoredproc components pointing to that TDatabase
component. All settings on the TDatabase component point to the sql 7.0
server and
connected=true...successful. Attempt to make ttable components
active...successful. Attempt to make Tstoredproc (without
parameters)...successful. However, attempt to make Tstoredproc (with
parameters)...I receive the BDE error message
"Project jcorp.exe raised exception class EDBEngineError with message
'General SQL error. Could not find stored procedure 'dbo.SumticketAmount;1'
"

BDE ERROR: 13059
SQL ERROR: 2812

I checked to make sure each Tstoredproc param setting was correct(see proc
below)
Tstoredproc.name = @ipssn
Tstoredproc.datatype = ftstring
Tstoredproc.paramtype = ptInput

To troubleshoot:
looked in delphi's sql explorer and made sure all stored procedures were
created on sql 7.0...successful; used Enter SQL tab to test the stored
procedures...tested ex 1(see below) with a parameter...successful; tested ex
2...successful.

used MSSQl analyzer to test stored procedures and both examples(see below)
worked successfully on sql 7.0.

Stored proc examples:
ex 1:
create procedure SumTicketAmount
@ipssn varchar(11) as
select sum(amt_ticket) as totalticketamount
from tkttran
where
id_ssn=@ipssn

ex 2:
create procedure GetAdmissionCounselors as
SELECT a.nme_Lname, a.nme_Fname, a.adr_Addr1, a.adr_Addr2,
a.adr_City, a.adr_State, a.adr_Zip, a1.Type,
a1.Number, a.cde_acid
FROM addrbook a, addr_ph a1
WHERE
(a1.Participant =* a.id_Participant)
AND ((a1.Type = 'Phone') OR (a1.Type = 'Fax'))
AND (a.cde_type = 'A')
and (a.cde_active = 'A')
ORDER BY a.adr_city, a.nme_Lname, a1.Type DESC

--chris knight
tallahassee, FL
chris.kni...@awi.state.fl.us
cdni...@electro-net.com

 

Re:D4/sql 6.5 to D5/sql 6.5 creates Tstoredproc error


Hi Chris,

You are making a choice to continue using the TStoredProc. You're better off
using the Ado version of the TStoredProc component (I forget it's exact
name). If you have D5 C/S, you will have this component.

The TStoredProc uses the BDE which uses DB-LIB, and this interface is no
longer being enhanced to support the new functionality of MSSQL7 & SQL2K.

If you really want to use the TStoredProc anyways, just eliminate the ';1'
at the end of the stored procedure name property in the object inspector.
This might correct the problem.

Quote
"Chris Knight" <cdni...@electro-net.com> wrote in message

news:980ltv$8co6@bornews.inprise.com...
Quote
> Delphi newsgroup,
> I am in the process of moving a delphi 4 app using sql 6.5 to delphi 5
using
> sql 7.0. Specifically, the TStoreProc is not working for sql stored
> procedures with parameters. The application works fine in delphi 4/sql
6.5.
> Using Delphi 4 client server & Delphi 5 Enterprise.

> Any help you can help with this would be greatly appreciated. My IT unit
> will soon pull the plug on the sql 6.5 server and I need to move all
> applications over sql 7.0.
> Furthermore, I would like to use some of the features of Delphi 5.

> Question:
> 1) on sql 7.0 does the BDE handle stored procedures with parameters
> different than those without parameters?....(Tstoredproc failed on stored
> procedure with
> parameters, whereas Tstoredproc without parameters opened successfully)

> (scenario)
> Layout:
> Delphi 4 app using sql 6.5...works fine
> Delphi 5 app using sql 7.0...uses same layout as delphi 4 app, but crashes
> when Tstoredproc with parameters is set to true.

> App uses a data module with one(1) TDatabase component and
> several ttable & tstoredproc components pointing to that TDatabase
> component. All settings on the TDatabase component point to the sql 7.0
> server and
> connected=true...successful. Attempt to make ttable components
> active...successful. Attempt to make Tstoredproc (without
> parameters)...successful. However, attempt to make Tstoredproc (with
> parameters)...I receive the BDE error message
> "Project jcorp.exe raised exception class EDBEngineError with message
> 'General SQL error. Could not find stored procedure

'dbo.SumticketAmount;1'

- Show quoted text -

Quote
> "

> BDE ERROR: 13059
> SQL ERROR: 2812

> I checked to make sure each Tstoredproc param setting was correct(see proc
> below)
> Tstoredproc.name = @ipssn
> Tstoredproc.datatype = ftstring
> Tstoredproc.paramtype = ptInput

> To troubleshoot:
> looked in delphi's sql explorer and made sure all stored procedures were
> created on sql 7.0...successful; used Enter SQL tab to test the stored
> procedures...tested ex 1(see below) with a parameter...successful; tested
ex
> 2...successful.

> used MSSQl analyzer to test stored procedures and both examples(see below)
> worked successfully on sql 7.0.

> Stored proc examples:
> ex 1:
> create procedure SumTicketAmount
> @ipssn varchar(11) as
> select sum(amt_ticket) as totalticketamount
> from tkttran
> where
> id_ssn=@ipssn

> ex 2:
> create procedure GetAdmissionCounselors as
> SELECT a.nme_Lname, a.nme_Fname, a.adr_Addr1, a.adr_Addr2,
> a.adr_City, a.adr_State, a.adr_Zip, a1.Type,
> a1.Number, a.cde_acid
> FROM addrbook a, addr_ph a1
> WHERE
> (a1.Participant =* a.id_Participant)
> AND ((a1.Type = 'Phone') OR (a1.Type = 'Fax'))
> AND (a.cde_type = 'A')
> and (a.cde_active = 'A')
> ORDER BY a.adr_city, a.nme_Lname, a1.Type DESC

> --chris knight
> tallahassee, FL
> chris.kni...@awi.state.fl.us
> cdni...@electro-net.com

Other Threads