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,

Search for MODIFY

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.

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,

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

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
 

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

Hi Martin,

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

Thanks,

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    

Thank you dbeagan,

This worked...