Inserting values using Application Engine

Question

I am trying to insert the values from my text file to a record using app engine.
I know this could be done using file layout but my requirement suggests not to use file layout but needs to insert into record using app engine people code.

I am writing the following PeopleCode in my appengine (here i am trying to add only one field value to my record in this example)

Local File &MYFILE; 
Local Record &REC_ERN; 


&MYFILE = GetFile("c:\sample.csv", "R", %FilePath_Absolute); 

If &MYFILE.IsOpen Then 

While &MYFILE.ReadLine(&STRING); 






MessageBox(0, "", 0, 0, &STRING); 
&source_type_id = Substring(&STRING, 1, 3); 
&Stmt= "INSERT INTO KGS_TEST (ID) VALUES ( &source_type_id)"; 

SQLExec(&Stmt); 
REM MessageBox(0, "", 0, 0, &source_type_id); 


End-While; 
End-If; 

&MYFILE.Close(); 

The problem I am facing is &source_type_id = Substring(&STRING, 1, 3); the variable &source_type_id has the value but i need to insert this into a record which I have created which has fields (ID,NAME,AGE,department).

Answer 1

The issue you are having is that your variable &source_type_id is inside a string and therefore is interpreted literally as part of the string instead of the value of the variable as you want.

What you need to do is put a bind variable in the string for the value and then pass the value as a parameter to SqlExec.

Local File &MYFILE; 

&MYFILE = GetFile("c:\sample.csv", "R", %FilePath_Absolute); 

If &MYFILE.IsOpen Then 
    While &MYFILE.ReadLine(&STRING); 

        MessageBox(0, "", 0, 0, &STRING); 
        &source_type_id = Substring(&STRING, 1, 3); 
        &Stmt= "INSERT INTO KGS_TEST (ID) VALUES ( :1)"; 

        SQLExec(&Stmt,&source_type_id); 
        REM MessageBox(0, "", 0, 0, &source_type_id); 

    End-While; 
End-If; 

&MYFILE.Close();