TDolphin UPDATE,INSERT error

[metro]

Daniel

Is there an working example for update or insert into MySQL database with TDolphin

Afield: = {"id", "sound", "maried", "total", "description", "date_maried"}
Avalue: = {"12345", "c: \ ring.wav". t., 1000, "Kellogg's", "2015-04-10"}

types in SQL are {"CHAR", "TEXT","TINYINT","DECIMAL", "TEXT", "DATETIME"}

The problem would be to put the description eg. "Kellogg's" (special character ' ) broken or text in two rows (MEMO get)


I used two ways to UPDATE.
Method 1: OSRV: Update (Arg1, aColumns, aValues, cWhere) and I get an error
2. Way: OSRV: Execute (cQuery)





Question. How to solve this problem and whether it is possible to make an example for UPDATE; INSERT with these data types: numeric, date, logic, blob, text type variable in the MySQL database.

Best regards
Metro

[joseluisysturiz]

Revisa en la clase esto...ClipValue2SQL, saludos...

[nageswaragunupudi]

Using the method
oSrv:Update ( cTableName, aColumns, aValues, cWhere)
should not give error.

Can you reproduce exactly your code for oSrv:Update( ... ) and what is the error?

[Biel EA6DD]

Code: Select all  Expand view

StrTran("Kellogg's","'","''")

Replace single quote ' with 2 single quote, single quote will be stored in the field.

[metro]

Thanks for the quick reply

This is the code that saves:

Code: Select all  Expand view

**********************************************************************
function Q_UpdateRow(Arg1,aColumns,aValues,cWhere)      
   local cQuery:= ""
   cQuery = "UPDATE " + Arg1 + " SET "
   //---
   for i = 1 to len(aColumns)
      if i = 1  
          //cQuery += " " + aColumns[i] + " ='" + aValues[i] + "' "    
          cQuery += " " + aColumns[i] + " =" + ClipValue2SQL( aValues[i] ) + " "    
      else
         
         //cQuery += ", " + aColumns[i] + " ='" + aValues[i] + "' "
         cQuery += ", " + aColumns[i] + " =" + ClipValue2SQL( aValues[i]  ) + " "    
      endif
   next
     
   cQuery += " " + cWhere                  //cWhere  := " WHERE id= '" + aTmp[1] + "'  LIMIT 1"

  // 1
  oSrv:Execute(cQuery)  
   
   // 2
   //oSrv:Update( Arg1, aColumns, aValues, cWhere )

Return(.t.)      

I tried in two ways:
1- oSrv:Execute(cQuery) .

If (get MEMO) description as in Picture5, everything is fine, but if you put the description as shown in Picture6 or "aaa' bbb " reports an error as shown in Picture 4


2.If you put another way:
oSrv:Update( Arg1, aColumns, aValues, cWhere )
(cQuery as in Figure 7)
I get an error as shown in Figure 2


Biel, does not need to restrict users to enter text

Thanks
Best regards
Metro

Picture 2


Picture 4


Picture 5


Picture 6


Picture 7

[Lailton]

Metro,

cSQL := "UPDATE ca3_sysuser SET user_pass='pero\'s', user_comp='pero\'s', login_3ca=1, first_name='pero\'s', last_name='pero', is_admin=1,
description='pero', date_entered='2015-04-16', date_modified='2015-04-16', modified_user_id='admin', created_by='admin',
title='title', department='pero', phone_home='pero', phone_mobile='pero', phone_work='pero', status='pero', p1='pero', p2='a',
p3='b', p4='c', p5='d' WHERE id='pero' LIMIT 1"

You need use a REPLACE, character ' to \' it will solve your problem.
Sample:

m_var := "pero's "
m_var := strtran(m_var,"'","\'")

cSQL := "INSERT INTO ca3_sysuser (user_pass) VALUES ('"+m_var+"')"

Regards,
Lailton

[joseluisysturiz]

Nose si esto le sirva, Val2Escape( cString ), saludos...

viewtopic.php?f=6&t=27408&p=152577&hilit=Val2Escape#p152577

[metro]

Lailton, joseluisysturiz thanks for the help


I think that the function Val2Escape() solve the problem ...

Code: Select all  Expand view

**********************************************************************
function Q_UpdateRow(Arg1,aColumns,aValues,cWhere)      
   local cQuery:= ""
 
   cQuery = "UPDATE " + Arg1 + " SET "

   for i = 1 to len(aColumns)

      if i = 1  
         if valtype( aValues[i] ) = "C"        
               cQuery += " " + aColumns[i] + " ='" + aValues[i] + "' "     // zadnja bez zareza
         elseif valtype( aValues[i] ) = "L"        
               cQuery += " " + aColumns[i] + " ='" + LToCharSQL(aValues[i]) + "' "
         elseif valtype( aValues[i] ) = "D"
               cQuery += " " + aColumns[i] + " ='" + dtoc(aValues[i]) + "' "
         elseif valtype( aValues[i] ) = "N"
               cQuery += " " + aColumns[i] + " ='" + str(aValues[i]) + "' "      
         endif            
      Else
         if valtype( aValues[i] ) = "C"
               cQuery += ", " + aColumns[i] + " ='" +Val2Escape( aValues[i]) + "' "
         elseif valtype( aValues[i] ) = "L"        
               cQuery += ", " + aColumns[i] + " ='" + LToCharSQL(aValues[i]) + "' "
         elseif valtype( aValues[i] ) = "D"
               cQuery += ", " + aColumns[i] + " ='" + dtoc(aValues[i]) + "' "
         elseif valtype( aValues[i] ) = "N"
               cQuery += ", " + aColumns[i] + " ='" + str(aValues[i]) + "' "                    
         endif
      endif
   next
   
   cQuery += " " + cWhere
   oSrv:Execute(cQuery)  

Return(nil)      

I tested with all special characters ( ' !"#$%&/()=?* ) and doing well.
Also, works fine text in multiple rows (Get-> MEMO)

Thanks all for your help
Best regards
Dubravko

[joseluisysturiz]

Combinando ClipValue2SQL( Val2Escape( var ) ), evitar tener que hacer tantas validaciones, con tdolphin si es fecha o numero uso ClipValue2SQL, si es con string como tu caso uso ClipValue2SQL( Val2Escape( var ) ) y para solo string uso comillas simples o puedes usar igual el ClipValue2SQL, pero ten cuidado con los campo nulos, define en tu tabla valores por defecto, ya que si guardas sin ningun valor es la VAR entonces se guardara valor NULL, saludos...

[metro]

hi joseluisysturiz

Thank you for your excellent advice. I'll be sure to pay attention to the default values in sql table.

Best regards
Dubravko