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

This is an example of output display that produce error when programs running. ins_01 UPDATE bfs@onlinet03:cmpurc SET cmp_purc_num = '2411', cmp_purc_nme = 'PURCHASER NAME'', cmp_addr_1 = 'A DDRESS 1'', cmp_addr_2 = 'ADDRESS 2' ', cmp_addr_3 = 'ADDRESS 3'' WHERE cmp_proj_cde = 'BE' AND cmp_phase_num = '1 ' AND cmp_lot_num = 'A1001 ' — Rahim Jaafar, Oct 23 '13 at 16:01

score 0 · Accepted Answer · answered Oct 23 '13 at 21:18

0

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.

answered Oct 23 '13 at 21:18

ceinmart

1,799
10
16

i had tried to double the quotes before but i am not sure the way i put correct or not, its still error.that symbol can transfer to other tables , but the only cmpurc tables in different online server will caused error.. – Rahim Jaafar Oct 24 '13 at 03:29
Hi Rahim , ok in this case mark the answer as acceptable only if really work's ok? Did you can test this updates manually using dbaccess? to make sure this is a kind of "restriction" over the server as you said. Testing the situation as you use into 4GL, connect to the main database then try execute the remote update. If occur the syntax error, ask to your DBA check the remote session with `onstat -g ses` and see how the SQL statement appear there. – ceinmart Oct 24 '13 at 09:58
ok thanks.. I had try input user information by using double quotes and there was no error.but now I am thinking on how to make "'" single quotes to becomes "''" doublle quotes. – Rahim Jaafar Oct 24 '13 at 10:49
You will need create a 4GL function to found the quote , then strip the string in two at the point you found it and concat the "new" quote with this two parts of the string. This is the unique idea what become to me now. – ceinmart Oct 24 '13 at 12:49
can you show example on how to strip the string into two string and concate the new quote? – Rahim Jaafar Oct 25 '13 at 03:33
I put the example at the other question you did. – ceinmart Oct 25 '13 at 10:59

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

1 Answers1