 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| sql return code | Login/Join |
| Platinum Member |
I'm trying to call a SQL stored proc that does a few inserts and returns an integer code (0 for success, >0 if error) Here's my code: SQL SQLMSS EX db.dbo.spDailyProdInsert; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS MYOUT END The insert works fine, but I have nothing in MYOUT. Any suggestions on how to capture integer return values from a SQL stored proc? Thanks. Thanks. Mark WF 7.6 Windows | ||
|
| Member |
I would need to see your stored procedure first. They can be tricky. Any chance of posting this? Maybe checking security permissions on it as well? WebFOCUS 7.6.1 Win 2003 | |||
|
| Expert |
Mark, The SQLOUT will only hold a result set from SQL calls and not return codes. As you are performing an update (same for deletes, drops etc.) you do not get a result set, so the fact that your MYOUT file is empty is as it should be. To trap the return codes add -? & to the end of your fex to see what internal variables there might be to assist you. Look for &RETCODE and possibly &EXITRC. One of these might be populated with the value you are setting in your stored proc. T
| |||||||||
|
| Master |
Are you doing a select or just an insert? An insert won't return SQLOUT. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Platinum Member |
i'm actually doing several things in the SP (all within a transaction with commit and rollback). the sp contains a "RETURN @retval" where @retval is INT...very simple. i realize there's no SQLOUT (since I'm not returning a result set) but I didn't know any other way to get the return value. I'll check the & and see if anything shows up (thanks Tony) Thanks. Mark WF 7.6 Windows | |||
|
| Platinum Member |
&RETCODE. Actually, since the integer return is not real descriptive, I've decided to do a SELECT at end of the SP that is a 1 col 1 record text of what the SP result is. Thanks. Mark WF 7.6 Windows | |||
|
| Platinum Member |
Now, I've got a related question. In my SP, I'm trapping any errors (such as key violations) and doing the following: SELECT top 1 'Key Violation' as Msg FROM tableA which should return a nice text based msg in SQLOUT. However, the SP doesn't actually get to that part...it just breaks and returns its own error diagnostic info. Anyone know how to avoid this? Thanks. Mark WF 7.6 Windows | |||
|
| Master |
i'm a vb guy, here's my guess as I don't have mssql to test against. IF @err <> 0 GOTO 0 Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Master |
here's a link I used often in a previous life as a developer http://www.sommarskog.se/error-handling-II.html Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Silver Member |
Hi, sorry to resurrect an old thread. i'm using &RETCODE to grab the return code (if not 1, there was some datasource error...) but i cannot figure out how to capture WHAT that error was and display it to the user. i suspect the following snippet holds the key, but i don't know what "SP" refers to. can anyone clarify a bit on this (perhaps provide a quick code sample): Actually, since the integer return is not real descriptive, I've decided to do a SELECT at end of the SP that is a 1 col 1 record text of what the SP result is. --------------------- WebFOCUS 7.6 | |||
|
| Virtuoso |
SP stands for Stored Prodcedure, which is a small program within the sql environemnt that you can fire off from within webfocus. GamP
| |||||||||
| ||||||||||
| Platinum Member |
Jason, thanks for the link - some good info with SQL Server 2005 updates. WebFOCUS 7.6.6/TomCat/Win2003,SQL Server 2005,Oracle | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |