[SOLVED] Dynamic Text box on Fex and Data Capture in Database

Have you considered using SQL Passthru to do the INSERT. Another option would be to use MODIFY FILE. Do a search for some examples.

  
-DEFAULT &STUDENT_ID = '123456789'
-DEFAULT &STUDENT_NAME = 'PARKER'
-DEFAULT &MARKS = 'C'

ENGINE SQLORA SET DEFAULT_CONNECTION DATA_ADAPTER_NAME
SQL SQLORA
INSERT INTO STUDENT_TABLE (STUDENT_ID, STUDENT_NAME, MARKS)
VALUES ('&STUDENT_ID.EVAL', '&STUDENT_NAME.EVAL', '&MARKS.EVAL')
;
END
-RUN

WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files

Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008

Ignored post by Mighty Max posted

Gold member

posted

Thank you very much.

In your example &STUDENT_ID,@STUDENT_NAME AND @MARKS are static.

When dynamic textboxes are created,then we do not how how many textbox are there in the report.

Say for example textboxes names are like that MARKS1,MARKS2,MARKS3,.........,MARKSN

Then how do I store all the dynamic textboxes value into the database.

Please help me with some sample code.

Thank you.

WebFOCUS 7.6.1
Windows, All Outputs

Posts: 50 | Location: Scun-thorpe,UK | Registered: November 14, 2012

Ignored post by Trilochan posted

Gold member

posted

I have done this.

Dynamic Input page code are given below

DEFINE FILE RPT_TEST_DYNAMIC
MARKS/A255 = '<input maxlength="3" oncopy="return false;" onpaste="return false;" onkeypress="java_script:Checkdecimal[)" type="text" name="marks' || ID || '" >';
END
TABLE FILE RPT_TEST_DYNAMIC
PRINT
'RPT_TEST_DYNAMIC.RPT_TEST_DYNAMIC.STUDENT_ID' AS 'Student ID'
'RPT_TEST_DYNAMIC.RPT_TEST_DYNAMIC.STUDENT_NAME' AS 'Student Name'
'RPT_TEST_DYNAMIC.RPT_TEST_DYNAMIC.MARKS' AS 'Marks'
BY 'RPT_TEST_DYNAMIC.RPT_TEST_DYNAMIC.ID' NOPRINT
END
-RUN

-HTMLFORM BEGIN
<html>
<head>
<script language="javascript" type="text/javascript">
function Checkdecimal()
{
var sDecAllowed = "Y";

if (window.event.keyCode < 48 || window.event.keyCode > 57)
{
//To avoid flashing alert for non impression keys
if (window.event.keyCode < 32)
{
window.event.keyCode = 0 ;
return ;
}
if (window.event.keyCode == 46)
{
if (sDecAllowed == "Y")
{

var sValue = event.srcElement.value ;
if (sValue.indexOf(".") > -1 )
{
alert("Decimal point already exists in this value")
window.event.keyCode = 0 ;
return ;
}
else
{
return ;
}
}
else
{
alert("Decimal place is not allowed")
window.event.keyCode = 0 ;
return ;
}
}

alert("Only Numbers should be entered");
window.event.keyCode = 0 ;
return ;

}
}
</script>

</head>
<form method="POST" target=_new action="/ibi_apps/WFServlet">
<input type="hidden" name="IBIF_ex" value="test_dynamic_result.fex">
<input type="hidden" name="txtRecordCount" value="&LINES">
!IBI.FIL.HLDS;
<input type=submit>
</form>
</html>
-HTMLFORM END

Dynamic output result code are given below

-SET &RecordCount = &txtRecordCount;
-SET &I=1;
-TYPE "Entered markes are given below";
-LOOP
-SET &A=&marks.&I;
-TYPE &A;
-SET &I=&I+1;
-IF &I EQ &RecordCount GOTO ENDLOOP;
-GOTO LOOP
-ENDLOOP

Happy coding.
Trilochan.

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

WebFOCUS 7.6.1
Windows, All Outputs

Posts: 50 | Location: Scun-thorpe,UK | Registered: November 14, 2012

Ignored post by Trilochan posted

Gold member

posted

I have found error in this method.

It works upto a limit of records.

If Record count is more than 500,the system throw a error.

INSUFFICIENT MEMORY IS AVAILABLE FOR PROGRAM/CACHE .

Please help me.

My Webfocus version is 7.6.1.

Thank you.

WebFOCUS 7.6.1
Windows, All Outputs

Posts: 50 | Location: Scun-thorpe,UK | Registered: November 14, 2012

Ignored post by Trilochan posted

Gold member

posted

I have solve this problem by implement paging in the FEX file.

Below is my fex code.

 

-SET &&CONNECTION_NAME='CONNECTION NAME OF ORACLE'
-SET &NO_OF_RECORDS=100;
-DEFAULTS &PG=1;
-DEFAULTS &SUBMITTED=0;
-TYPE &SUBMITTED;
-*---Start-Insert Record
-IF &SUBMITTED EQ 1 THEN GOTO INSERT_DATA ELSE GOTO EXIT_INSERT;
-RUN
-INSERT_DATA
-* Start Insert Into Database
-SET &RecordCount = &txtRecordCount;
-SET &PAGE_COUNT=&PAGE_C;
-SET &PAGE_COUNT1= IF &PAGE_COUNT NE 1 THEN &PAGE_COUNT-1 ELSE &PAGE_COUNT;
-SET &I = IF &PAGE_COUNT EQ 1 THEN 1 ELSE &PAGE_COUNT1.EVAL01;
-SET &RecordCount =IF &RecordCount GT 9 THEN &RecordCount ELSE 0|&RecordCount.EVAL;
-SET &RecordCount2=IF &RecordCount EQ 100 THEN &PAGE_C.EVAL00 ELSE &PAGE_COUNT1.EVAL|&RecordCount.EVAL;
-SET &RecordCount1 =  IF &PAGE_COUNT EQ 1 THEN &RecordCount ELSE IF &TOTAL_PG EQ &PAGE_C THEN &RecordCount2 ELSE &PAGE_COUNT.EVAL00;
-TYPE &RecordCount1;
-LOOP
-SET &REMARK_VALUE=&rem.&I;
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
-TYPE &I;
EX TEST_INSERT_UPDATE_RPT_STUDENT(&I,'&REMARK_VALUE');
END
-SET &I=&I+1;
-IF &I EQ &RecordCount1+1 GOTO ENDLOOP;
-GOTO LOOP
-ENDLOOP
-HTMLFORM BEGIN
<html>
<body >
[b]Record updated successfully[/b]
</body>
</html>
-HTMLFORM END
-*---End-Insert Record
-EXIT_INSERT
-*-Start-Report Data
-*---Start-Total number of records
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
EX TEST_STUDENT_COUNT &NO_OF_RECORDS;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DATA_COUNT
END
-*TABLE FILE DATA_COUNT
-*PRINT
-*T/A40 NO NOPRINT
-*END
-RUN
-READ DATA_COUNT &T.A40.
-SET &A=&T;
-SET &A = TRUNCATE(&A);
-TYPE &A;
-*---End-Total number of records
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
EX TEST_STUDENT_DATA &PG,&NO_OF_RECORDS;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DATAFILE
END
DEFINE FILE DATAFILE
   REMARKS/A255 = '<input maxlength="10" type="text" value="' || REMARKS || '" name="rem' || ID || '" >';
END
TABLE FILE DATAFILE
PRINT
 'DATAFILE.DATAFILE.ID'
 'DATAFILE.DATAFILE.STUDENT_ID' AS 'PIECE ID'
 'DATAFILE.DATAFILE.REMARKS' AS 'REMARKS'
HEADING
"Student Information"
ON TABLE HOLD AS HLDS FORMAT HTMTABLE
END
-SET &SHOWBUTTON = IF &A GT 1 THEN '' ELSE 'none';
-IF &LINES EQ 0 THEN GOTO NO_DATA ELSE PIECE_DATA;
-PIECE_DATA
-HTMLFORM BEGIN
<html>
<head><title>WebFOCUS Report with paging</title>
<script type="text/javascript">
function runrep(direction)
{
 var frm = document.WForm;
 var totalpage=parseInt(&A);
 if (direction == 'prev')
 {
  if(parseInt(frm.PG.value)>1)
  {
   frm.PAGE_C.value=frm.PG.value;
   frm.PG.value--;
   frm.submit();
  }
  else
  {
   alert("You are in the report First Page");
  }
 }
 if (direction == 'next')
 {
  if(parseInt(frm.PG.value)<totalpage)
  {
   frm.PAGE_C.value=frm.PG.value;
   frm.PG.value++;
   frm.submit();
  }
  else
  {
   alert("You are in the report Last Page");
  }
 }
 if (direction == 'submit')
 {
  frm.PAGE_C.value=frm.PG.value;
  frm.submit();
 }
 
}
</script>
</head>
<body>
<form name="WForm" action="/ibi_apps/WFServlet" method="post">
<input type="hidden" name="IBIF_ex" value="test_dynamic_input_p.fex">
<input type="hidden" name="TOTAL_PG" value="&A">
<input type="hidden" name="PG" value="&PG">
<input type="hidden" name="PAGE_C">
<input type="hidden" name="SUBMITTED" value="1">
<input type="hidden" name="txtRecordCount" value="&LINES">
<table>
<tr>
<td><input type="button" style="display:'&SHOWBUTTON'" value="Prev" onClick="runrep('prev')"/></td>
<td><input type="button" style="display:'&SHOWBUTTON'" value="Next" onClick="runrep('next')"/></td>
<td><input type="button" value="Submit" onClick="runrep('submit')"/></td>
<td><input type="reset" value="Reset"/></td>
</tr>
</table>
!IBI.FIL.HLDS;
</form>
</body>
</html>
-HTMLFORM END
-EXIT
-NO_DATA
-HTMLFORM BEGIN
<html>
<body >
<center>
[b]No data exists[/b]
</center>
</body>
</html>
-HTMLFORM END
-*-End-Report Data

TEST_STUDENT_COUNT procedure code is given below

 
CREATE OR REPLACE PROCEDURE "TEST_STUDENT_COUNT"(NOOFRECORDS IN NUMBER DEFAULT NULL,
                                       RC1         OUT SYS_REFCURSOR) AS
  RECCNT     NUMBER(6, 2);
  TOTALPAGES NUMBER(10, 0);

BEGIN
  TOTALPAGES := 0;
  BEGIN
    SELECT COUNT(1)
      INTO RECCNT
      FROM STUDENT;
  
    TOTALPAGES := CEIL(RECCNT / NOOFRECORDS);
  
    OPEN RC1 FOR
      SELECT TO_CHAR(ROUND(TOTALPAGES)) AS T FROM DUAL;
  
  END;
END "TEST_STUDENT_COUNT";

TEST_STUDENT_DATA procedure code is given below

 
CREATE OR REPLACE PROCEDURE "TEST_STUDENT_DATA"(PAGENO      IN NUMBER DEFAULT NULL,
                                        NOOFRECORDS IN NUMBER DEFAULT NULL,
                                        RC1         OUT SYS_REFCURSOR) AS
  RECCNT NUMBER(6, 2);
  FROM_  NUMBER(10, 0);
  TO_    NUMBER(10, 0);

BEGIN

  BEGIN
  
    SELECT COUNT(1)
      INTO RECCNT
      FROM STUDENT;
    FROM_ := ((PAGENO - 1) * NOOFRECORDS) + 1;
    TO_   := FROM_ + (NOOFRECORDS - 1);
  
    OPEN RC1 FOR
      SELECT to_char(ROWNUM) ID, STUDENT_ID,REMARKS
        FROM STUDENT
       WHERE ROWNUM >= FROM_
         AND ROWNUM <= TO_;
  
  END;
END "TEST_STUDENT_DATA";

TEST_INSERT_UPDATE_RPT_STUDENT procedure is given below

CREATE OR REPLACE PROCEDURE TEST_INSERT_UPDATE_RPT_STUDENT(V_R_NO    NUMBER,
                                                    V_REMARKS VARCHAR2) IS

  R_COUNT    NUMBER;
  V_STUDENT_ID NUMBER;
 

BEGIN

  SELECT STUDENT_ID
    INTO V_STUDENT_ID
    FROM STUDENT
   WHERE ROWNUM = V_R_NO;

  SELECT COUNT(1)
    INTO R_COUNT
    FROM RPT_TEST_STUDENT
   WHERE STUDENT_ID = V_STUDENT_ID;

  IF R_COUNT = 1 THEN
    UPDATE RPT_TEST_STUDENT
       SET REMARKS = V_REMARKS
     WHERE STUDENT_ID = V_STUDENT_ID;
  ELSE
    INSERT INTO RPT_TEST_STUDENT
      (STUDENT_ID, REMARKS)
    VALUES
      (V_STUDENT_ID,V_REMARKS);
  END IF;
  COMMIT;
END;

Happly coding.........

WebFOCUS 7.6.1
Windows, All Outputs

Posts: 50 | Location: Scun-thorpe,UK | Registered: November 14, 2012

Ignored post by Trilochan posted