Focal Point
[SOLVED]Inserting records

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

October 21, 2020, 01:05 PM
Krishna.edara
[SOLVED]Inserting records
Hi

i`m trying to insert records in a table as users input them from HTML page (i used text box). example user enters as bellow
1234
3456
7891
2468

i need them to insert as rows not as a single string.

any help is appreciated,
Thanks.

This message has been edited. Last edited by: Krishna.edara,


WebFocus-8/Windows/HTML, PDF, EXCEL
October 21, 2020, 03:04 PM
MartinY
Search for MODIFY


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
October 22, 2020, 11:34 AM
dbeagan
I have found that the text area created in HTML Canvas does not pass any carriage return or line feed characters to the variable in the called fex. I resolved this by adding some JavaScript to the page that replaces the CR or LF characters with commas.

  document.getElementById('textarea1').value = document.getElementById('textarea1').value.replace(/\r\n/g,',');
  document.getElementById('textarea1').value = document.getElementById('textarea1').value.replace(/\r/g,',');
  document.getElementById('textarea1').value = document.getElementById('textarea1').value.replace(/\n/g,',');  

I've seen some variation in what carriage return or line feed character(s) occur in the text area when you hit the Enter key, so the JavaScript code allows for a few variations.

Once you have your values in a variable in the fex like this, using your example:
1234,3456,7891,2468
then you can work on using MODIFY (as suggested by MartinY) or SQL to insert the values. If each four-digit number represents a different row that is to be inserted, then you could do some kind of loop (e.g. -REPEAT) to get each individual four-digit value for insertion.


WebFOCUS 8.2.06
October 26, 2020, 08:41 AM
Krishna.edara
Thank you for your reply Martin and dbeagan,

without using java script i can achieve this by using STRREP function, so my current output is look like bellow

1111|2222|3333|4444 all in single row,

i can use MODIFY function to load the data, but before how do i convert them to Multiple Rows from a single column as shown bellow?

1111
2222
3333
4444

Thank you for your help

This message has been edited. Last edited by: Krishna.edara,


WebFocus-8/Windows/HTML, PDF, EXCEL
October 27, 2020, 06:46 PM
dbeagan
I'm not quite clear on what you are saying, maybe this is helpful:

-* Make up a table and sample &Data values.
 SQL CREATE TABLE foccache/sometable (KEY INTEGER );
 END
-SET &Data = '1111|2222|3333|4444';

-* Enter each &Data item as a separate row.
-SET &i = 1; 
-SET &DataRow = TOKEN(&Data, '|', &i) |',$';

 MODIFY FILE sometable
 MATCH KEY
 ON MATCH REJECT
 ON NOMATCH INCLUDE
 DATA
-REPEAT ENDREPEAT WHILE &DataRow NE ',$' ;
&DataRow
-SET &i = &i + 1;
-SET &DataRow = TOKEN(&Data, '|', &i) |',$';
-ENDREPEAT
 END  

Then you can see the result:

 TABLE FILE sometable
 PRINT *
 END



WebFOCUS 8.2.06
October 27, 2020, 08:52 PM
FP Mod Chuck
Good One


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
November 02, 2020, 07:52 PM
Krishna.edara
Thank you dbeagan,

it worked for one column, i missed in my previous post that i need to insert 3 columns, other two columns are Variables (ibi user and timestamp), I`m using following code.
How do i insert &USER_ID in COL2 and &DT in COL3
 
ENGINE SQLORA SET DEFAULT_CONNECTION ADAPTER

-SET &COL1 = '88888888|99999999|77777777|66666666';

-SET &LEN = &IBIMR_user.LENGTH;
-SET &IBIMR_user = UPCASE(&LEN, &IBIMR_user, 'A&LEN.EVAL');
-TYPE &IBIMR_user;

-DEFAULTH &USERID = &IBIMR_user;

-SET &USER_ID = &IBIMR_user;
-SET &DT = '&DATE';
-TYPE &USER_ID;
-SET &i = 1;
-SET &COL1Row = TOKEN(&COL1, '|', &i) |',$';

 MODIFY FILE TABLE_NAME
 MATCH COL1 COL2 COL3
 ON MATCH REJECT
 ON NOMATCH INCLUDE
 DATA
-REPEAT ENDREPEAT WHILE &COL1Row NE ',$' ;
&COL1Row
-SET &i = &i + 1;
-SET &COL1Row = TOKEN(&COL1, '|', &i) |',$';
-ENDREPEAT
 END

ENGINE SQLORA SET DEFAULT_CONNECTION ADAPTER
SQL SQLORA
COMMIT;
END
-RUN
 



WebFocus-8/Windows/HTML, PDF, EXCEL
November 03, 2020, 07:59 AM
MartinY
Should be something looking as below
You need to define as many &COLx and other related variables, as you need column
Also, I assume that I can drive everything using &COL1Row only as the stop reference in the loop
ENGINE SQLORA SET DEFAULT_CONNECTION ADAPTER

-SET &COL1 = '88888888|99999999|77777777|66666666';
-SET &COL2 = '88888888|99999999|77777777|66666666';
-SET &COL3 = '88888888|99999999|77777777|66666666';

-SET &LEN = &IBIMR_user.LENGTH;
-SET &IBIMR_user = UPCASE(&LEN, &IBIMR_user, 'A&LEN.EVAL');
-TYPE &IBIMR_user;

-DEFAULTH &USERID = &IBIMR_user;

-SET &USER_ID = &IBIMR_user;
-SET &DT = '&DATE';
-TYPE &USER_ID;
-SET &i = 1;
-SET &COL1Row = TOKEN(&COL1, '|', &i) |',$';
-SET &COL2Row = TOKEN(&COL2, '|', &i) |',$';
-SET &COL3Row = TOKEN(&COL3, '|', &i) |',$';

 MODIFY FILE TABLE_NAME
 MATCH COL1 COL2 COL3
 ON MATCH REJECT
 ON NOMATCH INCLUDE
 DATA
-REPEAT ENDREPEAT WHILE &COL1Row NE ',$';
&COL1Row,
&COL2Row,
&COL3Row
-SET &i = &i + 1;
-SET &COL1Row = TOKEN(&COL1, '|', &i) |',$';
-SET &COL2Row = TOKEN(&COL2, '|', &i) |',$';
-SET &COL3Row = TOKEN(&COL3, '|', &i) |',$';
-ENDREPEAT
 END

ENGINE SQLORA SET DEFAULT_CONNECTION ADAPTER
SQL SQLORA
COMMIT;
END
-RUN



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
November 03, 2020, 11:16 AM
Krishna.edara
Hi Martin,

using this code it is inserting 12 rows instead of 4rown, all 12 records inserted in first column only.

Thanks,


WebFocus-8/Windows/HTML, PDF, EXCEL
November 03, 2020, 02:12 PM
dbeagan
Try this:
-* Make up a table and sample &Data values.
 SQL CREATE TABLE sometable (COL1 INTEGER, COL2 CHAR(12), COL3 DATETIME);
 END
-SET &Data = '88888888|99999999|77777777|66666666';

-* Enter each &Data item as a separate row.
-SET &i = 1; 
-SET &DataRow = TOKEN(&Data, '|', &i) | ',''&FOCSECUSER.EVAL'',''&DATEHYYMDS.EVAL'',$';
-SET &ECHO=ON;
 MODIFY FILE sometable
 MATCH COL1 COL2 COL3
 ON MATCH REJECT
 ON NOMATCH INCLUDE
 DATA
-REPEAT ENDREPEAT WHILE TOKEN(&Data, '|', &i) NE ' ' ;
&DataRow
-SET &i = &i + 1;
-SET &DataRow = TOKEN(&Data, '|', &i) | ',''&FOCSECUSER.EVAL'',''&DATEHYYMDS.EVAL'',$';
-IF &i GT 9 THEN DONE;
-ENDREPEAT
 END    



WebFOCUS 8.2.06
November 09, 2020, 12:24 PM
Krishna.edara
Thank you dbeagan,

This worked...


WebFocus-8/Windows/HTML, PDF, EXCEL