SP and TD Code

Discussions specific to Team Developer 5.1.

SP and TD Code

Postby Kalpana » Tue Sep 27, 2011 5:30 am

Hi Team,

I am having trouble connecting to an SQL 2005 SP from TD Code.
I did search the other posts in the Form.

I have 1 int parameter to be passed into the SP and it returns one int output back to the TD Code.

TD Code looks like

!!CB!! 174
Set CpbReporting.nUnitImbalance=018
Set sgSql=''
Call SqlConnect(hgSql)
If SqlPrepareSP(hgSql,'exec TESTING1 :CpbReporting.nUnitImbalance ' ,' : CpbReporting.nRoundEffect,')
If SqlExecute(hgSql)
While SqlFetchNext(hgSql,nInd)
Call SalMessageBox('','',CpbReporting.nRoundEffect)


And the SP looks looks like


CREATE PROCEDURE TESTING1 @SECTORID INT
AS

DECLARE @COUNT INT

SET @COUNT=(SELECT COUNT (*) FROM SYSADM.SECTOR WHERE SECTOR_NUMBER =@SECTORID)
-- PRINT @COUNT
RETURN @COUNT


But I am getting error '3617-SQL Error 3617 not found in ERROR.SQL.Choose OK to Quit.' when the application tries to execute If SqlExecute(hgSql)
line of code.

Please help me to resolve this ASAP.

Let me know if you need any more information.

Thanks & Regards,
Kalpana
Kalpana
 
Posts: 8
Joined: Wed Jun 01, 2011 2:46 am

Re: SP and TD Code

Postby Jeff Luther » Tue Sep 27, 2011 2:28 pm

Only thing I see is your 'exec <>' statement.

Is ':CpbReporting.nUnitImbalance' supposed to be a bind var? I'd change to concatenating that bind var for '18' right in the statement:
"...TESTING1 " || SalNumberToStrX( <>, 0 )

Last param. has ": Cpb..." space after that colon. Intentional?? Looks wrong.

And SalMessageBox has last param. = MB_* - 1 or more constants. Don't think you want to pass nRoundEffect... See TD Help for details.
Jeff Luther
 

Re: SP and TD Code

Postby Kalpana » Wed Sep 28, 2011 3:42 am

Hi Luther,

Thanks for your reply.
I did make some changes in the TD Code as below

!!CB!! 174
Set sgSql=''
Call SqlConnect(hgSql)
Call SqlPrepareSP(hgSql, ' exec Testing1 || SalNumberToStrX(18,0) || ', ':CpbReporting.nUnitImbalance')
Call SqlExecute(hgSql)
While SqlFetchNext(hgSql,nInd)
Call SalMessageBox ( 'Cannot insert data', 'Database Error' , MB_Ok )

I am passing 18 as input parameter to the SP and I want int variable nUnitImbalance to hold the return value what ever the SP returns.

But still I am getting error (102 select buffer is too small ) while execution of the statement Call SqlExecute(hgSql).

SP Code looks like

CREATE PROCEDURE TESTING1 @SECTORID INT
AS
DECLARE @COUNT INT
SET @COUNT=(SELECT COUNT (*) FROM SYSADM.SECTOR WHERE SECTOR_NUMBER =@SECTORID)
RETURN @COUNT


Please help.

Thanks
Kalpana
Kalpana
 
Posts: 8
Joined: Wed Jun 01, 2011 2:46 am

Re: SP and TD Code

Postby Jeff Luther » Wed Sep 28, 2011 1:42 pm

Jeff is my first name and I did help as I could. You can always help yourself and us if you put together a small test case so it runs and can demo. your problem. I can't 'run' you test msg, though I see another visual gotcha -- did you actually run the code you've pasted into your previous msg.?

Here's what I see see:
'exec TESTING1 :CpbReporting.nUnitImbalance '

is not equivalent to your change:
' exec Testing1 || SalNumberToStrX(18,0) || '

and in fact that won't even compile and run correctly!

It has to be:
' exec Testing1 ' || SalNumberToStrX(18,0)

which tells me you didn't run the code you pasted here :?

I suggest you put a test case together, actually compile and run the code to repro. your issue and attach here. We in tech. support try to get here as we can, but maybe someone can try and run your test.
Jeff Luther
 

Re: SP and TD Code

Postby Kalpana » Mon Oct 03, 2011 6:51 am

Hi Jeff,

Thanks for your reply. Sorry for the delayed reply.
I am now able to call successfully the SP but not able to capture the return value.
Let me try again and update you.

Thanks,
Kalpana
Kalpana
 
Posts: 8
Joined: Wed Jun 01, 2011 2:46 am


Return to Team Developer 5.1

Who is online

Users browsing this forum: No registered users and 1 guest

cron