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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL passthru

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL passthru
 Login/Join
 
Gold member
posted
I want to update one column in the Oralce database

SQL SQLORA SET SERVER MYSERVER
UPDATE PERSON
SET LAST_UPDATED_DATE = SYSDATE
WHERE EMPLOYEE_ID='000001'
END

However, i got the following error:

(FOC1517) UNRECOGNIZED COMMAND UPDATE PERSON
0 ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC324) THE PARAMETER TO BE SET IS NOT RECOGNIZED: LAST_UPDATED
(FOC1517) UNRECOGNIZED COMMAND WHERE EMPLOYEE_ID='N067569'

Thanks in advance for your help

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


====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4
 
Posts: 52 | Registered: June 28, 2005Report This Post
Expert
posted Hide Post
Add in SQL or SQL SQLORA before UPDATE PERSON

You should check out the syntax of SQL passthru


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
Yes. It works.

However, how can i customize output message?


--------------------------------------------------------------------------------

No HTML Output!

--------------------------------------------------------------------------------


(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/UPDATE


====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4
 
Posts: 52 | Registered: June 28, 2005Report This Post
Expert
posted Hide Post
You need to capture the results of the SQL command, and check it.

Then you can show an appropriate message.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
Thanks.

Can you give me an example as how to capture the results of the SQL command and check it and show the message?

Regards


====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4
 
Posts: 52 | Registered: June 28, 2005Report This Post
Expert
posted Hide Post
Maria, here is an example, there are others in the forum as well.
-*******************************************************************************
-* Set the trace file

-SET &TEMPPATH = TEMPPATH(80,'A80');
-SET &TRACEFL  = &TEMPPATH || ('cmd_op.trc');

-*******************************************************************************
-* Set Commands To Set Tracing

SET TRACEOFF  = ALL
SET TRACEON=R1H
SET TRACEUSER = ON
SET TRACEUSER = &TRACEFL

-RUN
SQL SQLORA
SELECT *
FROM MISSING_TABLE;
END
-RUN
-RUN
-* The previous two -RUN's must be there for this to work.
SET TRACEOFF  = ALL
SET TRACEUSER = OFF
-RUN

-*******************************************************************************
-* Allocate The Trace File
FILEDEF TRACEFIL DISK &TRACEFL

-RUN

-*******************************************************************************
-* Write out a master to read the TRACEFIL list
EX -LINES 4 EDAPUT MASTER,TRACEFIL,CV,FILE
FILENAME=TRACEFIL, SUFFIX=FIX,$
SEGNAME=TRACEFIL, $
  FIELD=LINE ,ALIAS=  ,A500 ,A500 ,$

-RUN

-*******************************************************************************
-* Process the trace file to get the results of the command.

DEFINE  FILE TRACEFIL
 Item3/A20     = GETTOK(LINE,500,3,' ',20,'A20') ;
 MSG_Item/A493 = SUBSTR(500,GETTOK(LINE,500,2,'"',500,'A500'),8,500,493,'A493') ;
 CMD_Flag/A1   = IF EDIT(MSG_Item,'9') EQ ':' THEN 'Y' ELSE 'N' ;
END

TABLE   FILE TRACEFIL
 PRINT  
        MSG_Item

 WHERE        Item3    EQ 'NGtrEvnt:'

-* ON     TABLE HOLD AS TMP_CHCK

END

-RUN



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Maria - Here is piece of SQL-RC check code that we use. The -803 is a duplicate insert failure. It is an acceptable condition in our situation, you may take it out if you do not want it.

-*
SQL SQLORA
....
....
;
TABLE
END
-RUN
-*
-SET &SQL_ERROR1 = &RETCODE;                                            
-IF (&SQL_ERROR1.EVAL EQ 0 OR &SQL_ERROR1.EVAL EQ -803) GOTO LBL_NXT1;
-SET &MSGX = 'Submission Failed (Error# &SQL_ERROR1.EVAL)';             
-SET &MSG1 = 'Please notify your WebFOCUS support.';                      
-INCLUDE ERROR_MSG.FEX                                                    
-EXIT                                                                   
-*                                                                         
-LBL_NXT1               
...continue processing.......                                                


The ERROR_MSG.FEX is a standard module we use inhouse for error trap and display.

Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
Sandeep and Waz;

Thanks for your help.

I got the following error message:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/UPDATE
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC303) CONTROL LINE NOT RECOGNIZED IN FOCEXEC: -print &MSGX


My fex is:

SET SQLENGINE=SQLMSS

SQL SQLORA SET SERVER MYSERVER
SQL SQLORA
UPDATE table
SET
WHERE
END

-RUN
-SET &SQL_ERROR1 = &RETCODE;
-SET &MSGX = 'Update Successfully';
-SET &MSG1 = 'Please notify your WebFOCUS support.';
-IF (&SQL_ERROR1.EVAL EQ 1364) GOTO LBL_NXT1;
-print &MSGX
-LBL_NXT1
print &MSG1


====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4
 
Posts: 52 | Registered: June 28, 2005Report This Post
Virtuoso
posted Hide Post
The correct syntax for displaying the value of a variable is -TYPE, not -print.

-TYPE &MSGX


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
As Dan says, it should be -TYPE to just print back the return code on screen.

Confused a bit with your code however.
You start with SQLENGINE=SQLMSS, but have an Oracle table referenced with SQLORA ???

Are these not mutually exclusive?


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report 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     [CLOSED] SQL passthru

Copyright © 1996-2020 Information Builders