SQLDB parameters conceptual problem. lazarus / 16-Databases ---------------------------------------------------------------------- iru · 2015-11-27 20:59 Gentlefolk, I have a problem with how to use parameters with SQLDB parameters and FireBird. I have a TSQLQuery with the "SQL"property set to 'select * from res- table where A = :A and B=:B;' This defines parameters A and B and allows me to read records from the res-table. If I wish to update/insert records into res-table I have something like the following in TSQLQuery "InsertSQL" "Insert into res-table (A, B, C) values (:A, :B, :C);" I then get an error that parameter "C" is not defined because "C" is not specified in the "select". So how can I use 2 parameters to select required records but then  insert/update records other  fields? Any help appreciated, Ian ---------------------------------------------------------------------- Jurassic Pork · 2015-11-28 07:07 hello, can you show us your code to do that ? do you use INSERTSQL Property ? for example, this code works for me : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  sqlquery1.sql.text := 'insert into users (NAME,EVALUATION) values (:NAME,:EVALUATION)'; sqlquery1.Params.ParamByName('NAME').AsString := 'NewName'; sqlquery1.Params.ParamByName('EVALUATION').ASFloat := 1000;  sqlquery1.ExecSQL; sqltransaction1.CommitRetaining; sqlquery1.sql.Cle ar; sqlquery1.sql.Text := 'select * from users'; datasource1.DataSet.Refresh;   Not sure that it is the best solution    Friendly, J.P ---------------------------------------------------------------------- LacaK · 2015-11-29 08:52 Quote from: iru on November 27, 2015, 09:59:49 pmI have a TSQLQuery with the "SQL"property set to 'select * from res-table where A = :A and B=:B;' This defines parameters A and B and allows me to read records from the res-table. If I wish to update/insert records into res-table I have something like the following in TSQLQuery "InsertSQL" "Insert into res-table (A, B, C) values (:A, :B, :C);" I then get an error that parameter "C" is not defined because "C" is not specified in the "select". It should work as far as "res-table" has column "C". Because :A, :B, :C in InsertSQL are not related to Params in SQL, but are related to columns returned by select defined in SQL ---------------------------------------------------------------------- iru · 2015-11-29 11:08 JP, Thank you for your response. I note that you have placed the "insert.... in the sqlquery1.sql rather than sqlquery1.insertSQL. So perhaps parameters must be defined in sqlquery1.sql rather than in a "select" in sqlquery1.sql. I have modified my code to  use one "select" to extract data from the database and another (with additional fields) when the InsertSQL is executed. Why do you use the "select" and "insert" via sqlquery1.sql rather than use the sqlquery1.insertsql??? Once again thanks for the response, Ian ---------------------------------------------------------------------- Jurassic Pork · 2015-11-29 11:42 hello, ask to Lacak for that . He has said :  QuoteBecause :A, :B, :C in InsertSQL are not related to Params in SQL, but are related to columns returned by select defined in SQL I don't know exactly how InsertSQL works  and it seems not very clear for me. ---------------------------------------------------------------------- fpcoder · 2015-11-30 00:59 If I understand right, ExecSQL works only with the command in the SQL property, it doesn't use InsertSQL. As JP showed, if parameters are used in the SQL property and ParamCheck is true, the TSQLQuery senses this and creates these parameters automatically. Parameters works only for the SQL property. InsertSQL, UpdateSQL and DeleteSQL doesn't use parameters at all, even if they exist. To verify that param values are not used by InsertSQL, create a param, assign a value and perform insert: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Query.Close;Query.Params.CreateParam(ftString, 'item_name', ptInput);Query.ParamByName('item_name').AsString:= 'Test';Query.InsertSQL.Text := 'INSERT INTO items(item_name) VALUES(:i tem_name)';Query.Open;Query.Insert;Query.Post;Query.ApplyUpdates;TX.Co mmit; No value will be inserted. Only when the command contains the real value it will be inserted: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Query.Close;Query.InsertSQL.Text:=Format('INSERT INTO items(item_name) VALUES(%s)',[QuotedStr('Test')]);Query.Open;  //Requires a SELECT statement.Query.Insert;Query.Post;Query.ApplyUpdates;TX.Commit;  For trivial queries TSQLQuery auto-generates the statements for InsertSQL, UpdateSQL and DeleteSQL. So often its not necessary to add anything manually except the select statement in the SQL property. ---------------------------------------------------------------------- LacaK · 2015-11-30 08:57 Quote from: kapibara on November 30, 2015, 01:59:39 amIf I understand right, ExecSQL works only with the command in the SQL property, it doesn't use InsertSQL. Yes Quote from: kapibara on November 30, 2015, 01:59:39 amParameters works only for the SQL property. InsertSQL, UpdateSQL and DeleteSQL doesn't use parameters at all, even if they exist. Yes Quote from: kapibara on November 30, 2015, 01:59:39 amFor trivial queries TSQLQuery auto-generates the statements for InsertSQL, UpdateSQL and DeleteSQL. So often its not necessary to add anything manually except the select statement in the SQL property. Yes ---------------------------------------------------------------------- iru · 2015-11-30 09:30 Gentlefolk, Thank you all for the responses and the information in them. Late last night I had come to the conclusion that data in parameters was not saved but was struggling to reconcile this with information (or my interpretation of that information) that suggested that parameter data was saved. Thanks for the example/s. The example of     "Query.InsertSQL.Text:=Format('INSERT INTO items(item_name) VALUES(%s)',[QuotedStr('Test')]);" shows a simple case of one field, with a number of fields constructing the SQL could be ugly.. I noted that if I had the following: "insert into items(item_name) values (:item_Name)" and then had in the InsertSQL "Query.fieldByName('item_name').AsString := 'FRED'; and totally ignored the parameter, 'FRED' was saved in 'item_name' correctly. The parameter :item_name was simply a place filler in the SQL insert statement, no building of SQL required. Thanks once again, Ian ---------------------------------------------------------------------- magu · 2015-11-30 11:46 I also find using the InsertSQL a handful. My solution was to use two separate TSQLQueries: One to fetch the data and keep it displayed, and another to update. This is especially useful when the data displayed is the result of a complex query and you only want to allow updates to one or two fields.