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

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

**********************************************************************
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...

http://forums.fivetechsupport.com/viewt ... pe#p152577

[metro]

Lailton, joseluisysturiz thanks for the help


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

Code: Select all

**********************************************************************
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 _ 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