PB calling a MS sql Server stored proc...and the raiserror are not bubling up in the correct order.

Running MSsqlserver 2000, PB 8.03 (PFC)

I am doing a RPC call via sqlca like so

ll_rtc = sqlca.usp_claim_verify( istr_name_id.claim_id,
ls_emsg)
SetPointer(arrow!)	
IF sqlca.sqlcode <> 0 then
		MessageBox('Claim Item Processing',&
                'An error occured trying to VERIFY' + &
                ' the claim/items.~r~n' + &
		'sqlcode:' + string(sqlca.sqlcode) + &
                '~r~n'+	'sqlerrtext:'+ sqlca.sqlerrtext)
		return
END IF


If ll_rtc <> 0  THEN
	MessageBox('Claim Item Procesing','Unable to verify' + &
        ' the claim for the following reason:~r~n' + &
	'Procedure Return Code:' + string(ll_rtc) + &
        '~r~n' + 'Procedure Error Message:' + ls_emsg)
END IF		
dw_items_listing.retrieve(claim_id)

Given that the main stored proc calls multiple other stored
procs and in this particular case the inner stored proc upon
error raises an error like so:

exiterror:
SET @retval = -1
set @emsg = '(usp_calculate_product_rate) Some Serious
BooBoo here'
Raiserror(@emsg,1,1)

and if not a true error but a business error does this
Exitnorm:
return @retval


and the outer stored proc(usp_claim_verify) handles this
return value from this particular USP as such:

exec @rc_rate = usp_calculate_product_rate @product_id
if @RC_rate = -1
begin
    SET @retval = -1
    set @emsg = '(usp_claim_verify) Error calculating
product rate'
    goto EXIT_BUSINESSERROR
end
else
begin
    goto EXITERROR
end

with the idea that when usp_claim_verify has a problem I
want to raiserror when the error is internal to
usp_claim_verify, and allow the decendant stored procs to
raiserror when they fail.

And now how PB comes into play and series of events.

based on the PBscript above, my call to usp_claim_verify,
comes back with a sqlca.sqlcode of 0 and a ll_rtc of -1 and
pops up the second messagebox display the error text
:(usp_claim_verify) Error calculating product rate

The next thing the PBscript does is a re-retrieve on the DW
(dw_items_listing) that uses the linkage service and is the
master DW to another DW. The call to the retrieve fires the
DBerror event and displays the error message from the inner
stored proc and displays:
(usp_calculate_product_rate) Some Serious BooBoo here
and for whatever reason completely clears out my DW.


is there something wrong with my raiserror statement?


running the same sequence via SQLanalyzer I get my error
messages in the proper sequence....inner first then outer.

Why am I getting the dberror event firing on the DW when I
retrieve? It appears that sqlca is caching the raisederror
and firing the at the second instance of doing something
over sqlca.

any ideas ?
0
cfauvel
10/17/2003 5:15:11 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

0 Replies
805 Views

Similar Articles

[PageSpeed] 53

Reply:

Similar Artilces:

Calling Stored Proc in MS SQl Server 2005 from PB 10.5.1
ALL: The stored procedure has the OUTPUT parameter defined in it. When it is getting executed the values are not passed back to PB. Please any help is appreciated.Hope a member from Teamsybase will address it. function long sp_Upload_MCCS(int agi_center_id,long agl_campaign_id,ref string messg_text) RPCFUNC ALIAS FOR "dbo.sp_Upload_MCCS" li_ret_fetch = lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id,ls_messg) Is there any parameter need to be setup. I am using OLEDB to connect to DB. tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" + servername+ &...

Call a MS SQL Server 2000 stored procedure in PB 8 via a PB script and Datawindow
I tried to call a stored procedure with owner name "sbs" via PB script and datawindow.Also I tried different way to call it, using ownername.procname, procname, and dbname.ownername.procname. But nothing works. it Always say something is not a parameter of the proc. The error message did not show the owner name as a part of the proc call. so I think that somehow the owner name wasn't sent to SQL 2000 server. if I changed the owner name to dbo, it works! so my question is how we let PB send the whole name(including owner name) of the proc to SQL 2000 server to get it sol...

PB Stored Procs for MS SQL Server?
Are there MS SQL Server versions of the pbsyc.sql and pbsyc2.sql Adaptive Server stored procedures we run as part of installing PowerBuilder (9.0)? If so, where can they be found? I find some for Oracle and DB2 in the server folder of the PB 9 installation CD, but not SQL Server. If there are none, what functionality will be missing in PowerBuilder without them? Thanks in Advance for your help! (BTW, we are very sad to be migrating to MS SQL Server from ASE, but a mandate came down from management to do so. But we are trying to convince them to let us keep our AS...

PB and MS SQL Server stored procs
Hi , We are using MS SQL Server 2000 with PowerBuilder 6.5 . There is a stored procedure that returns four columns from a table ( max 1 row). The procedure has 2 input parameters and it returns 4 columns and 1 row as part of a select statement . We are calling this procedure from PowerBuilder using DECLARE and trying to get the result using EXECUTE and FETCH . But there are no rows returned to PowerBuilder . The same EXECUTE gives the 1 row and 4 columns from with MS SQL SERVER ( using the Query Analyzer) . What do we do ? We also tried using a datawindow with this proc...

Porting Ms Sql Server 6.5 Database to Ms Sql Server 7.0
Hi, We have developed a product using PowerBuilder7.0, Ms Sql Server 6.5 and connecting them using powerbuilder native driver. Now we are thinking of porting Ms Sql Server 6.5 database to Ms Sql Server 7.0.As we feel, we could achive new features of Sql server 7.0.e.g row level locking. Actually i want to know, what things i have to do.e.g what sort of connection i have to use? what new features i will able to use?.will Stored procedures work properly. Is there anyting i have to chang in my application(at front end)e.g datawindows or any embeded sql, external functions. Should i po...

Porting Ms Sql Server 6.5 database to Ms Sql Server 7.0
Hi, We have developed a product using PowerBuilder7.0, Ms Sql Server 6.5 and connecting them using powerbuilder native driver. Now we are thinking of porting Ms Sql Server 6.5 database to Ms Sql Server 7.0.As we feel, we could achive new features of Sql server 7.0.e.g row level locking. Actually i want to know, what things i have to do.e.g what sort of connection i have to use? what new features i will able to use?.will Stored procedures work properly. Is there anyting i have to chang in my application(at front end)e.g datawindows or any embeded sql, external functions. Should i po...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

How to call a MS-SQL Server function from PB
Hello all, I need an advice how to call SQL-Server function from PB which is returning a value - There is a SQL-Server function "string MyFunction( long )" If I'm trying to call this function via a external rpc function in SQLCA this function is executed on the server-side via EXEC ("exec myfunction argument"), so the call does not return a value and I'm getting an SQL-Error within SQLCA. How to call this function (without using an intermediate table). A possible was would be using a pseudo datawindow with a syntax like "select dbo.my...

Calling a SQL Server database function from PB
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C3163D.62FC3DA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a user defined function in sqlserver which is being called from = PB. When I call the function I have to prefix the function name with the = function creators user id.. Such as dbo.myfunction. Is there a way to = call the function without the user id or is this something I'll have to = live with. John ------=_NextPart_000_000A_01C3163D.62FC3DA0 Content-Type: text/html; chars...

Stored proc syntax in MS SQL Server
Using Database Adminstratrion/Design/Procedure Syntax in PB6.5 allows me to retrieve the text of a stored procedure. However, if I do the same thing in PB9 not all of the text from the stored procedure is retrieved or only chunks of it are retireved. I know this is because in MS SQL Server syscomments table the column where the text is stored is 8000 characters and in Sybase it is only 256. The mechanism that is used by PB to retrieve stored proc syntax has not adjusted for the 8000 characters. Is there a patch for this? How can this be fixed? Please use the OLE DB database interfac...

Storing MS Access Database in SQL Server
I have a client who has some specialized info in an Access database. Instead of porting the whole thing over to SQL Server for use in their Custom Module Section in their CSK Site, I thought I would simply allow them to upload their constantly changing Access Database and read straight from it. The CSK already has an upload section (or Download Section, if you prefer) but all the files go straight to the SQL database. Has anyone ever loaded an Access database file straight into a SQL database? I don't mean the data, I mean the actual MDB file. Certainly it can be done, but then how wou...

Problem with stored proc, migrating from MS SQL 2000 to MS SQL 2005
I've been migrating some DB from MS SQL 2000 to MS SQL 2005 all works fine but I have one problem with stored procedures. Even if there is actually no errors I get the message "The transaction ended in the trigger. The batch has been aborted.". The record is succesfully deleted but still I get the message above. I know MS SQL 2005 added the try/catch block did they change something else that cause compatibility issues with 2000? Here's a sample : *** DECLARE @Error bit DECLARE @ErrorMsg varchar (100) DECLARE @ContactIDToDelete int SELECT @ContactIDToDelete ...

Web resources about - PB calling a MS sql Server stored proc...and the raiserror are not bubling up in the correct order. - sybase.powerbuilder.database

Immediate deadlock notifications without changing existing code
In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error. ...

Denali – 2012 - SQL with Manoj
... 25. SQL Server 2012 Certification Path 24. Use new TRY_PARSE() instead of ISNUMERIC() 23. Another reason to use THROW clause instead of RAISERROR ...

Upgrading CRM 4 Attachments to 2011 Using a Linked Server
... ama on ama.ActivityId = apb.ActivityId Where ama.T_Processed = 3 Set @Rows = @@RowCount RAISERROR ('ActivityMimeAttachment Rows Updated...', ...

Resources last updated: 12/28/2015 7:12:38 AM