Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Update a column in a SQL table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Update a column in a SQL table
 Login/Join
 
ELockett
Member
posted
I am being tasked with creating a report - based off of a SQL database, that contains static columns and then 1 comment column (that the user needs to be able to update) from within the report. This update would need to then be written into our underlying SQL table. We do not have a license for MAINTAIN, so that's not an option.

This message has been edited. Last edited by: ELockett,


App Studio 8.2.03 Windows all formats
 
Posts: 24 | Registered: September 13, 2010Report This Post
BabakNYC
Virtuoso
posted Hide Post
One alternative is to use MODIFY language but I'm not sure how you'd prompt for them to type stuff in. You could write this into a file and run a batch job in MODIFY I guess.

Alternatively, (and I haven't done this before and I'm thinking high level) you could create a stored procedure in your RDBMS that accepts a parameter and updates a row in the table. Run a standard report to let users select the row they want to update with a drill down. Then prompt them for the text they have to type and pass the value of the &VARIABLE in the prompt to the stored procedure to update based on the key.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
David Briars
Master
posted Hide Post
John Johnson at Lender Processing Services gave a great presentation at Summit 2012 on this topic.

The presentation was called 'Database Updates Made Easy in WebFOCUS'.

I was able to easily reproduce what he shows in the .pptx and then adapted it to my requirements.

In a nutshell the presentation showed how to make your reports interactive and allow real time database updates.

Perhaps someone at your local IB office can forward the .pptx to you?
 
Posts: 822 | Registered: April 23, 2003Report This Post
ELockett
Member
posted Hide Post
Yes, I've been trying to find info on the MODIFY language... haven't found the information yet in the technical library.

BabakNYC,
The idea about the drill down is interesting... I never thought about looking at it like that.

David,
Thanks for the tip. I will see if someone can forward me the .pptx. That sounds interesting and useful for future development also.


App Studio 8.2.03 Windows all formats
 
Posts: 24 | Registered: September 13, 2010Report This Post
BabakNYC
Virtuoso
posted Hide Post
MODIFY syntax isn't fully documented anymore but the language still supports it. If you have a very simple use case, I'd say this'll do the job. But if you're going to do hard core transaction processing, I'd go the MAINTAIN way because that's what's fully supported and documented. Otherwise, use Stored Procs.

Open this Doc and search for MODIFY FILE in it. You'll see examples of it. http://documentation.informati...p77/mf_develop77.pdf

the basic syntax is
MODIFY FILE FILENAME
MATCH key field
ON MATCH UPDATE
ON NOMATCH REJECT
DATA
END

You'll have to define where the data is coming from. In your case most likely it's a FIXFORM FROM HOLD. It's been a very long time since I wrote MODIFY code so I can't really help you with it much, but the examples they have should give you a basic understanding of the syntax for a very simplistic update example.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
ELockett
Member
posted Hide Post
I was able to solve this by creating a stored procedure to update my field and creating a report based on that with nothing but a Define field confirmation message. I created a html page with an input box for the user to input the comment and when they click the link on the main report, it brings up the HTML. From there, the user enters the comment and clicks submit (which is actually running the stored procedure report) and then the report runs a confirmation in the same window. I also added a refresh to just the panel of the main report so that the user can see the updated comments field.


App Studio 8.2.03 Windows all formats
 
Posts: 24 | Registered: September 13, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Update a column in a SQL table

Copyright © 1996-2020 Information Builders