[help] Problem when updating data on database
lazarus / 16-Databases

----------------------------------------------------------------------
rayhanhd · 2015-06-26 06:34

goof afternoon everyone.

i got serious problem here.

i cannot use update syntax on mysql. it always get aborted clients.
when i use another syntax like insert or delete, theres no problem.
i really confuse.

i use lazarus for ubuntu. and using mysql as database server.

I'm sorry for my language, cause English wasn't my main language.

----------------------------------------------------------------------
exdatis · 2015-06-26 06:52

We need much more information to help you. Which lib you use? SqlDb?
See:
http://wiki.freepascal.org/SQLdb_Tutorial1
http://wiki.freepascal.org/SQLdb_Package

p.s. do not forget applyUpdates, commit etc. Let we see your
sql_update code

----------------------------------------------------------------------
rayhanhd · 2015-06-27 14:33

Quote from: exdatis on June 26, 2015, 08:52:12 amWe need much more
information to help you. Which lib you use? SqlDb?
See:
http://wiki.freepascal.org/SQLdb_Tutorial1
http://wiki.freepascal.org/SQLdb_Package

p.s. do not forget applyUpdates, commit etc. Let we see your
sql_update code

i'm using zeos component.
its akward, caus it happen suddenly.
mysql status said that my connection was aborter.

here's my update sql.

zquerry1.sql.clear;
zquerry1.sql.text:='update tablename set value1 = '+edit1.text+' where
primarykey = '+editpk.text+';';
zquerry1.execsql

----------------------------------------------------------------------
rvk · 2015-06-27 14:58

I think you're not quoting your variables correctly. You need to put
quotes around the string-variables.
Code: [Select]zquerry1.sql.Clear;
zquerry1.sql.Text := 'update tablename' +
  ' set value1 = ' + QuotedStr(edit1.Text) +
  ' where primarykey = ' + QuotedStr(editpk.Text) + ';';
zquerry1.execsql;

But I would suggest you use parameters:
Code: [Select]zquerry1.sql.Clear;
zquerry1.sql.Text := 'update tablename' +
  ' set value1 = :new_value' +
  ' where primarykey = :key_value';
zquery1.ParamByName('new_value').asString := edit1.Text;
zquery1.ParamByName('key_value').asString := editpk.Text;
zquerry1.execsql;

----------------------------------------------------------------------
taazz · 2015-06-27 15:25

Quote from: rvk on June 27, 2015, 04:58:32 pmI think you're not
quoting your variables correctly. You need to put quotes around the
string-variables.
you assume that the fields are string typed. Might not be

----------------------------------------------------------------------
rvk · 2015-06-27 15:44

Quote from: taazz on June 27, 2015, 05:25:00 pmQuote from: rvk on June
27, 2015, 04:58:32 pmI think you're not quoting your variables
correctly. You need to put quotes around the string-variables.
you assume that the fields are string typed. Might not be
Yes, I did. But you're correct. If primarykey is an integer (which it
probably is) that one shouldn't be quoted. Of value1 it's not
specified. So only the string-variables should be quoted.

Quote from: rayhanhd on June 27, 2015, 04:33:01 pmmysql status said
that my connection was aborter.
It would be useful if you gave the exact error-message.
Code: [Select]  try
    zquerry1.sql.Clear;
    zquerry1.sql.Text := 'update tablename' +
      ' set value1 = ' + QuotedStr(edit1.Text) + // <-- is this a
string or integer??
      ' where primarykey = ' + editpk.Text + ';';
    zquerry1.execsql;
  except
    on E: Exception do
      Showmessage('Error = ' + E.Message);
  end;

Aborted connections could have lots of other causes too. In that case
we would need to know more about the rest of the program.