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