Page 1 of 1
TDolphin UPDATE,INSERT error
Posted: Wed Apr 15, 2015 11:02 pm
by 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
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 2:58 am
by joseluisysturiz
Revisa en la clase esto...ClipValue2SQL, saludos...
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 4:37 am
by 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?
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 6:31 am
by Biel EA6DD
Replace single quote ' with 2 single quote, single quote will be stored in the field.
Re: TDolphin UPDATE,INSERT error
Posted: Thu Apr 16, 2015 9:58 am
by 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
Re: TDolphin UPDATE,INSERT error
Posted: Fri Apr 17, 2015 12:29 pm
by 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
Re: TDolphin UPDATE,INSERT error
Posted: Fri Apr 17, 2015 5:01 pm
by joseluisysturiz
Nose si esto le sirva, Val2Escape( cString ), saludos...
http://forums.fivetechsupport.com/viewt ... pe#p152577
Re: TDolphin UPDATE,INSERT error - solved
Posted: Fri Apr 17, 2015 8:48 pm
by 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
Re: TDolphin UPDATE,INSERT error
Posted: Fri Apr 17, 2015 8:58 pm
by 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...
Re: TDolphin UPDATE,INSERT error
Posted: Sat Apr 18, 2015 11:17 am
by 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