Focal Point
[CLOSED] SQL passthru

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/146105273

February 16, 2010, 04:01 PM
Maria Tan
[CLOSED] SQL passthru
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
February 16, 2010, 04:36 PM
Waz
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!

February 16, 2010, 04:41 PM
Maria Tan
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
February 16, 2010, 04:53 PM
Waz
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!

February 17, 2010, 10:48 AM
Maria Tan
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
February 17, 2010, 04:14 PM
Waz
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!

February 19, 2010, 09:55 AM
BlueZone
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
February 19, 2010, 12:32 PM
Maria Tan
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
February 19, 2010, 12:43 PM
Dan Satchell
The correct syntax for displaying the value of a variable is -TYPE, not -print.

-TYPE &MSGX



WebFOCUS 7.7.05
February 19, 2010, 04:00 PM
BlueZone
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