SQL Pass Questions

https://techsupport.informatio...om/sps/80452542.html

When we looked at inserting rows into Oracle, via SQL Passthru, we found several ampervariables that supported us tesing the success or failure of the insert.

In the code below we use &ROWSAFFECTED to present an HTML page, in the event of a bad result.

...
ENGINE SQLORA
INSERT INTO schema.tablename
-*INSERT INTO tablename
VALUES ('&FRED_ID',
        '&WILMA_TEXT',
		TO_DATE('&NOW','MM/DD/YYYY HH24:MI:SS'),
		'&IBIMR_user',
        '&SEQ',
		'&SOURCE')
;
COMMIT;
END
-RUN
-TYPE FocErrNum      = &FOCERRNUM
-TYPE Records        = &RECORDS
-TYPE Oracle Err Num = &RETCODE
-TYPE RowsAffected   = &ROWSAFFECTED
-IF &ROWSAFFECTED LE 0 GOTO ADD_ERROR;

Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster

Posts: 822 | Registered: April 23, 2003

Ignored post by David Briars posted

Francis Mariani

Expert

posted

You can use Dialogue Manager to test the WebFOCUS error code with system variable &FOCERRNUM and the SQL error code with system variable &RETCODE.

If the insert is successful, both system variables are set to 0, if it is not successful, both system variables are set to non-zero error numbers.

Something like:

-IF &FOCERRNUM EQ 0 GOTO INSERT_SUCCESS ELSE GOTO INSERT_FAIL;
...

You can retrieve the WebFOCUS error message text by executing this:

-SET &FOC_ERR_MSG = IF &ERRORNUM EQ 0 THEN '' ELSE FEXERR(&FOCERRNUM, 'A100');

You cannot retrieve the SQL error message text using a system function, but you could code for it - there will be few expected SQL errors...

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Gold member

posted

Thanks you two, this will definitly get us going. Appreciate it!

WF 7703 Outputs all
Windows 7 32
DB2 CLI

Posts: 59 | Registered: August 09, 2012

Ignored post by John C. posted

Guru

posted

&ROWSAFFECTED return values:
>0 - success
0 - mismatched data (no records)
-1 - invalid data/connection

Also checkout this link from David's suggestion and note PASSREC

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

Gold member

posted

Thanks again, great info!

WF 7703 Outputs all
Windows 7 32
DB2 CLI

Posts: 59 | Registered: August 09, 2012

Ignored post by John C. posted

Gold member

posted

Sorry guys new question:

When people are inserting comments into the system we are using something like this with other fields and audit fields:

  
INSERT INTO LIBRARY/TABLE (USER, COMMENT)
        VALUES    ('&IBIMR_user','&COMMENT')

This works great unless someone uses an apostrophe in their comment box. So anything like didn't, or names like D'Andrea, etc gives them our new error message.

Anyway to convert the text box to insert correctly and not have apostrophe effect the SQL?

The text box is in an HTML form that they fill out and submit.

WF 7703 Outputs all
Windows 7 32
DB2 CLI

Posts: 59 | Registered: August 09, 2012

Ignored post by John C. posted

Guru

posted

What is the actual error message?

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

Gold member

posted

  
 (FOC1400) SQLCODE IS -104 (HEX: FFFFFF98)
 : [42601] Token M was not valid. Valid tokens: ) ,.
 (FOC1414) EXECUTE IMMEDIATE ERROR.

  
INSERT INTO LIBRARY/TABLE (USER, COMMENT)
        VALUES    ('&IBIMR_user','&COMMENT')

**TRACE**

INSERT INTO LIBRARY/TABLE (USER, COMMENT)
VALUES ('JCARBE','I'm here to help!')

Im here to help! works perfectly.

WF 7703 Outputs all
Windows 7 32
DB2 CLI

Posts: 59 | Registered: August 09, 2012

Ignored post by John C. posted

Guru

posted

quote:
Im here to help! works perfectly.

If that is the case, I am guessing the quotes around either '&IBIMR_user' or '&COMMENT' (or both) are messing you up.

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

Tom Flynn

Expert

posted

Let Me Google That for You

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Gold member

posted

quote:
Originally posted by Tom Flynn:
Let Me Google That for You

O, yes thank you, very helpful... I know how to do it, just not how to take the variable and do it through WebFOCUS.

How do I take the user input and add the extra quotation? I'm guessing there is a way to do it in the dialogue manager? I was looking at STRIP, but we have never used it outside of a define/compute. Looking through all the character commands possibly GETTOK? Was trying to get input of what others had been successful in the past.

WF 7703 Outputs all
Windows 7 32
DB2 CLI

Posts: 59 | Registered: August 09, 2012

Ignored post by John C. posted

Francis Mariani

Expert

posted

This might help:

Developing Reporting Applications > Managing Flow of Control in an Application > Creating a Standard Quote-Delimited String

&var.QUOTEDSTRING

quote:

Consider the following SQL Translator request:

SET TRACEUSER = ON
SET TRACEON = STMTRACE//CLIENT
SQL
SELECT *
FROM SQLVID WHERE LASTNAME = &1.QUOTEDSTRING;
END

...

In the generated SQL request, the character string used for the comparison is correctly enclosed in single quotation marks, and the embedded single quote is doubled:

SELECT SQLCOR01.CIN , SQLCOR01.LN , SQLCOR01.FN ,
SQLCOR01.EXDAT , SQLCOR01.TEL , SQLCOR01.STR , SQLCOR01.CITY ,
SQLCOR01.PROV , SQLCOR01.POSTAL_CODE , SQLCOR01.OUTDATE ,
SQLCOR01.PCOD , SQLCOR01.TCOD , SQLCOR01.NO , SQLCOR01.TTOT
FROM SQLVID SQLCOR01 WHERE SQLCOR01.LN = 'O''BRIEN';

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

David Briars

Master

posted

quote:
How do I take the user input and add the extra quotation?

Within the same system, that I gave a code excerpt from in an earlier post within this thread, we collect narrative text from the user on a web form.

It made sense for us to add the extra ''' (as well as other narrative cleanup) with a JavaScript regular expression (RegExp) on the client.

For example:

-HTMLFORM BEGIN
<!DOCTYPE html>
<html>
<body>
<script>
 mynarrative = "O'Briars"
 glbNARRATIVE = escape(mynarrative);
 glbNARRATIVE = glbNARRATIVE.replace( new RegExp("%27", "gi"), "''");
 glbNARRATIVE = unescape(glbNARRATIVE);
 document.write(glbNARRATIVE);
</script>
</body>
</html>
-HTMLFORM END
-EXIT

Renders as:

O''Briars

This message has been edited. Last edited by: David Briars,

Posts: 822 | Registered: April 23, 2003

Ignored post by David Briars posted

Tom Flynn

Expert

posted

  
-SET &ECHO = 'ALL';
-SET &COMMENT = 'Dave' || '''' || 's here to help Martha' || '''' | 's children and other' || '''' | 's!';
-*-SET &COMMENT = 'Here to help!';
-TYPE &COMMENT
-SET &COM_LEN = &COMMENT.LENGTH;
-SET &TOK1    = GETTOK(&COMMENT, &COMMENT.LENGTH, 1, '''', &COM_LEN, 'A&COM_LEN.EVAL');
-SET &TOK2    = GETTOK(&COMMENT, &COMMENT.LENGTH, 2, '''', &COM_LEN, 'A&COM_LEN.EVAL');
-SET &TOK3    = GETTOK(&COMMENT, &COMMENT.LENGTH, 3, '''', &COM_LEN, 'A&COM_LEN.EVAL');
-SET &TOK4    = GETTOK(&COMMENT, &COMMENT.LENGTH, 4, '''', &COM_LEN, 'A&COM_LEN.EVAL');
-SET &TOK5    = GETTOK(&COMMENT, &COMMENT.LENGTH, 5, '''', &COM_LEN, 'A&COM_LEN.EVAL');
-SET &TOK6    = GETTOK(&COMMENT, &COMMENT.LENGTH, 6, '''', &COM_LEN, 'A&COM_LEN.EVAL');
-SET &POS1    = POSIT(&COMMENT,  &COMMENT.LENGTH, '''', 1, 'I2');
-TYPE &POS1
-IF &POS1 EQ 0 GOTO DO_INSERT;
-SET &COMMENT = '''' || &TOK1 || '''' || '''' || &TOK2 || '''';
-IF &TOK3 EQ '' GOTO DO_INSERT;
-SET &COMMENT = &COMMENT || '''' || &TOK3 || '''';
-IF &TOK4 EQ '' GOTO DO_INSERT;
-SET &COMMENT = &COMMENT || '''' || &TOK4 || '''';
-IF &TOK5 EQ '' GOTO DO_INSERT;
-SET &COMMENT = &COMMENT || '''' || &TOK5 || '''';
-IF &TOK6 EQ '' GOTO DO_INSERT;
-SET &COMMENT = &COMMENT || '''' || &TOK6 || '''';
-DO_INSERT
-TYPE &COMMENT
-EXIT

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted