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     [SOLVED]Inserting records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Inserting records
 Login/Join
 
Platinum Member
posted
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
 
Posts: 106 | Registered: September 20, 2010Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 106 | Registered: September 20, 2010Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Virtuoso
posted Hide Post
Good One


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 106 | Registered: September 20, 2010Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 106 | Registered: September 20, 2010Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Platinum Member
posted Hide Post
Thank you dbeagan,

This worked...


WebFocus-8/Windows/HTML, PDF, EXCEL
 
Posts: 106 | Registered: September 20, 2010Report 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     [SOLVED]Inserting records

Copyright © 1996-2020 Information Builders