FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour TDolphin UPDATE,INSERT error
metro
Posts: 18
Joined: Tue Sep 25, 2012 06:01 PM
TDolphin UPDATE,INSERT error
Posted: Wed Apr 15, 2015 11:02 PM

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
Posts: 2064
Joined: Fri Jan 06, 2006 09:28 PM
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 02:58 AM

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

Dios no está muerto...



Gracias a mi Dios ante todo!
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 04:37 AM

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?

Regards



G. N. Rao.

Hyderabad, India
Biel EA6DD
Posts: 682
Joined: Tue Feb 14, 2006 09:48 AM
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 06:31 AM
Code (fw): Select all Collapse
StrTran("Kellogg's","'","''")

Replace single quote ' with 2 single quote, single quote will be stored in the field.
Saludos desde Mallorca
Biel Maimó
http://bielsys.blogspot.com/
metro
Posts: 18
Joined: Tue Sep 25, 2012 06:01 PM
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 09:58 AM
Thanks for the quick reply

This is the code that saves:
Code (fw): Select all Collapse
**********************************************************************
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
Posts: 231
Joined: Fri Jul 20, 2012 01:49 AM
Re: TDolphin UPDATE,INSERT error
Posted: Fri Apr 17, 2015 12:29 PM

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

Regards,

Lailton Fernando Mariano
joseluisysturiz
Posts: 2064
Joined: Fri Jan 06, 2006 09:28 PM
Re: TDolphin UPDATE,INSERT error
Posted: Fri Apr 17, 2015 05:01 PM

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

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

&&&

Dios no está muerto...



Gracias a mi Dios ante todo!
metro
Posts: 18
Joined: Tue Sep 25, 2012 06:01 PM
Re: TDolphin UPDATE,INSERT error - solved
Posted: Fri Apr 17, 2015 08:48 PM
Lailton, joseluisysturiz thanks for the help


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

Code (fw): Select all Collapse
**********************************************************************
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
Posts: 2064
Joined: Fri Jan 06, 2006 09:28 PM
Re: TDolphin UPDATE,INSERT error
Posted: Fri Apr 17, 2015 08:58 PM

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... :shock:

Dios no está muerto...



Gracias a mi Dios ante todo!
metro
Posts: 18
Joined: Tue Sep 25, 2012 06:01 PM
Re: TDolphin UPDATE,INSERT error
Posted: Sat Apr 18, 2015 11:17 AM

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

Continue the discussion

Static archive · New replies & topics via GitHub Discussions