update table that contains single quote symbol with different online server in informix 4 gl

Question

hi all,I am working on informix-4gl.my programs is about to adds and update user information from one tables to many tables.The tables are also must be update from different online server.The main tables is working on online06 named 'crsell' table and the other tables are on the online03 named 'cmpurc' table.This an example on how i update the tables.

      ## update CMPURC with latest purchaser info ##                       
      LET ins_01 = NULL                                                    
      LET ins_01 = "UPDATE bfs@", link_onln_no CLIPPED, ":cmpurc",         
                   " SET cmp_purc_num = ", "'", p_cm_purc_num,             
                   "'",",",                                                
                   " cmp_purc_nme = ", "'",p_cmp_purc_nme,                 
                   "'",",",                                                
                   " cmp_addr_1 = ", "'",p_cmp_addr_1, "'",",",            
                   " cmp_addr_2 = ", "'",p_cmp_addr_2, "'",",",            
                   " cmp_addr_3 = ", "'",p_cmp_addr_3, "'",                
                   " WHERE cmp_proj_cde = ", "'", p_crsell.crse_proj_cde,  
                   "'",                                                    
                   "   AND cmp_phase_num = ", "'", p_crsell.crse_phase_num,
                   "'",                                                    
                   " AND cmp_lot_num = ", "'", p_crsell.crse_lot_num, "'"  

In case, there were information from user that contains "'" symbol or single quote such as the purchaser name or user address.My problems is when I update the tables,the information that contains single quote symbols will not updated to the 'cmpurc' tables on online03 server. there will show an error message SQL statement error number -201.

I had try to convert symbol "'" to other symbol "^" and update the tables.Then, I update again the 'cmpurc' table with the information that contains "'" symbols.This step are also produce an error.This is way on how i convert the symbol.

  LET rmks_lgth = LENGTH(p_crsell.crse_purc_nme)    
FOR a = 1 TO rmks_lgth                          
  IF p_crsell.crse_purc_nme[a] = "'" THEN       
    LET p_crsell.crse_purc_nme[a] = "^"        
  END IF                                        
END FOR              

convert back to single quotes symbol

LET rmks_lgth = LENGTH(p_cmp_purc_nme)    
FOR a = 1 TO rmks_lgth                    
  IF p_cmp_purc_nme[a] = "^" THEN         
    LET p_cmp_purc_nme[a] = "'"           
  END IF                                  
END FOR                                   

i had test by replacing the "'" symbol with the other values and its produce no error. The error will only occurs when the "'" symbol are being transfer from table 'crsell' to 'cmpurc'.I hope that someone can help me to solve my problems.I am sorry if there is lack of information that i had given to you because i cant post the image because lack of reputation and i am new user .I am very appreciate if you all can help me to solve the problems. thank you very much

Now, I am going to change single quotes to double quotes.I had try change code like this but its reads only single quotes.anyone can give an idea? thank you

      LET rmks_lgth = LENGTH(p_crsell.crse_purc_nme)    
  FOR a = 1 TO rmks_lgth                          
    IF p_crsell.crse_purc_nme[a] = "'" THEN       
     LET p_crsell.crse_purc_nme[a] = "''"        
   END IF                                        
  END FOR              

Answer 1

I believe you need to duplicate the quote or double quote to get just one without syntax error...

This example I run into dbaccess without problem, I don't test into 4gl code...

create temp table tp01( desc char(20), desc2 char(20) ) ;
Temporary table created.

insert into tp01 values ( "tst""1", "tst'");
1 row(s) inserted.

insert into tp01 values ( 'tst''1', 'tst"');
1 row(s) inserted.

select * from tp01;
desc                 desc2
tst"1                tst'
tst'1                tst"

2 row(s) retrieved.