1

I have the following:

somequery.SQL.Add('UPDATE `someDBname`.`someTABLEname` SET

`client`='''+someForm.Edit1.Text+''', 
`phone`='''+someForm.Edit2.Text+''', 
`email`='''+someForm.Edit3.Text+''', 
`details`='''+someForm.Edit4.Text+''', 
`specials`='''+someForm.Edit5.Text+''', 
`price`='''+someForm.Edit6.Text+''', 
`address`='''+someForm.Edit7.Text+''',
`deadline`='''+someForm.DateTimePicker1.DateTime+''',
`status`='''+someForm.Edit9.Text+''' 

WHERE `id`=''' + inttostr(someDataSetid.Value) + ''';');

And I'd like to switch over to using parameters, like this:

someQuery.SQL.Clear;
someQuery.SQL.Add( 'UPDATE `someDBname`.`someTABLEname` ( client, phone, email, details, specials, price, address, deadline, status ) values ( :client, :phone, :email, :details, :specials, :price, :address, :deadline, :status ) ' ) ;
someQuery.Params.ParamByName( 'client' ).AsString := someForm.Edit1.Text ;
someQuery.Params.ParamByName( 'telefon' ).AsString := someForm.Edit2.Text ;
someQuery.Params.ParamByName( 'email' ).AsString := someForm.Edit3.Text ;
someQuery.Params.ParamByName( 'detalii' ).AsString := someForm.Edit4.Text ;
someQuery.Params.ParamByName( 'mentiuni' ).AsString := someForm.Edit5.Text ;
someQuery.Params.ParamByName( 'pret' ).AsString := someForm.Edit6.Text ;
someQuery.Params.ParamByName( 'livrare' ).AsString := someForm.Edit7.Text ;
someQuery.Params.ParamByName( 'deadline' ).AsDateTime := someForm.DateTimePicker1.DateTime ;
someQuery.Params.ParamByName( 'status' ).AsString := someForm.Edit9.Text ;
someQuery.ExecSQL(true);

I have no clue how to convert the WHERE clause containing the ID (1st code example) to parameters (2nd code example) Haven't managed to figure it out yet and I can't seem to find an example on google on how to use a WHERE in parameters. I'm fairly new at using parameters.

What should I write after Params.ParamsByName( 'id' ) - to get the id?

Server is MYSQL. Do let me know in the comments If I left anything out and I will edit

Thanks in advance!

t1f
  • 3,021
  • 3
  • 31
  • 61
  • Why would you think you can change from using valid SQL to invalid SQL in order to add parameters? Your second example is not valid at all, with or without parameters. – Ken White Nov 22 '16 at 02:07
  • @KenWhite Probably because I didn't catch what you apparently did, and since I haven't gotten to adding the where yet I haven't tested anything yet. What's not valid with it? – t1f Nov 22 '16 at 02:09
  • The entire syntax of UPDATE is wrong in your second example. Look at the SQL statement in your first, and then compare it (without the parameters) in the second. (Actually, just copy it out of the code, paste it into Notepad, and remove the `SQL.Add('` and the closing `');` and read it. It's totally different, and totally invalid. When switching to parameterized statements, it doesn't change the entire syntax of SQL. You still have to write syntactically valid SQL statements; you don't get to just make up a new language along the way. – Ken White Nov 22 '16 at 02:55
  • @KenWhite - Yes, I copy pasted the same thing from an `insert into` statement I was using in another form to add stuff, didn't notice the difference and assumed it would work, just paid a visit to sql w3 school and that clarified it further for me, thanks for pointing it out. – t1f Nov 22 '16 at 03:06
  • @KenWhite - can you provide some info on how the id param line should look like? – t1f Nov 22 '16 at 03:37
  • You already have an answer below that does so. Why are you ignoring it? The WHERE clause looks exactly like it normally would, except the value to the right of the `=` is a parameter (just like all the other parameters), and you provide the value just exactly like you set the other values (using ParamByName). – Ken White Nov 22 '16 at 03:42
  • @KenWhite - I'm not ignoring it at all, I just don't get what to write after `.Params.ParamByName( 'id ' )` - that's what i was asking. I don't have the ID as a TEdit like I do for the other things, It's only in the DataSet and it changes it's different for every row, so that's where my confusion is. I've tried adding `( 'id' ).AsString := someDataSetid.Value ;` and it doesn't work – t1f Nov 22 '16 at 03:47

1 Answers1

2

it's very good that you have decided to switch from string concatenation to parameter binding but that doesn't mean you can change the UPDATE syntax. You are still bound by the documented syntax for that 

'UPDATE `someDBname`.`someTABLEname` SET client=:client, phone=:phone, email=:email, details=:details, specials=:specials, price=:price, address=:address, deadline=:deadline, status=:status WHERE id=:id';

This is pretty much the same syntax as in your first query but instead of string concatenation you use place holders. And then you bind the parameters one by one 

someQuery.Params.ParamByName( 'client' ).AsString := someForm.Edit1.Text ;
someQuery.Params.ParamByName( 'telefon' ).AsString := someForm.Edit2.Text ;
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Hi and thanks for answering! I'm having a bit of a hard time understanding this exactly. That means I have to ditch the Parameter lines and use what you call, place holders? Also, on the page you linked the example lists several things your examples doesn't (like [ and some , and no spaces). I'm guessing that's the confusing part. Mind writing a complete example on what my particular code should look like? I'd understand it better once I see the whole thing – t1f Nov 22 '16 at 02:30
  • Please see update. What's meant by place holder is :client, :phone etc which you already have in your query. But note that you had made your correct syntactically incorrect which is what I have pointed out in my answer – e4c5 Nov 22 '16 at 02:59
  • Oh, I see. Just checked sql w3 school and noticed the difference also, the following would seem to be correct then, could you confirm please? ``( 'UPDATE `someDBname`.`someTABLEname` SET client=:client, phone=:phone, email=:email, details=:details, specials=:specials, price=:price, address=:address, deadline=:deadline, status=:status WHERE id=:id' ) ;`` If it is, I assume all I need to add is the parameter line for ID, which is part of my original question as how that should look since I can't figure that out at all. (It's also missing from my 2nd code example, for the exact same reason) – t1f Nov 22 '16 at 03:09
  • Yes, that's pretty much it. Not quite sure how parameters are bound in your language (you haven't specified but something along the lines of your original answer or `someQuery.Params.ParamByName( ':pret' )` would work. (note the newly added `:` – e4c5 Nov 22 '16 at 06:28
  • I'm using Delphi - Rad Studio 10 Seattle. Thanks for the edit / reply - I'll try it – t1f Nov 22 '16 at 14:46
  • Nope, `( ':pret' )` not working says parameter not found. Any thoughts? and without the : in params it just says I have an sql syntax error – t1f Nov 22 '16 at 15:20
  • sorry I must have made a copy paste error with that and can't remember what I was trying to paste, but obviously you have to use the names like :email, :phone etc which are in your prepared statement. – e4c5 Nov 22 '16 at 15:22
  • Thank you for the answer, I'll accept it as it resolved my syntax that I was using wrong and will open another question for the ID part and why the parameters aren't working (throwing that error) thanks! – t1f Nov 22 '16 at 17:32