Recent

Author Topic: SQLDB parameters conceptual problem.  (Read 3718 times)

iru

  • Sr. Member
  • ****
  • Posts: 321
SQLDB parameters conceptual problem.
« on: November 27, 2015, 09:59:49 pm »
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

  • Hero Member
  • *****
  • Posts: 1228
Re: SQLDB parameters conceptual problem.
« Reply #1 on: November 28, 2015, 08:07:48 am »
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][+][-]
  1.  sqlquery1.sql.text := 'insert into users (NAME,EVALUATION) values (:NAME,:EVALUATION)';
  2.  sqlquery1.Params.ParamByName('NAME').AsString := 'NewName';
  3.  sqlquery1.Params.ParamByName('EVALUATION').ASFloat := 1000;
  4.  sqlquery1.ExecSQL;
  5.  sqltransaction1.CommitRetaining;
  6.  sqlquery1.sql.Clear;
  7.  sqlquery1.sql.Text := 'select * from users';
  8.  datasource1.DataSet.Refresh;    

Not sure that it is the best solution  :-[ 

Friendly, J.P
« Last Edit: November 28, 2015, 08:16:01 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLDB parameters conceptual problem.
« Reply #2 on: November 29, 2015, 09:52:56 am »
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".

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

  • Sr. Member
  • ****
  • Posts: 321
Re: SQLDB parameters conceptual problem.
« Reply #3 on: November 29, 2015, 12:08:39 pm »
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

  • Hero Member
  • *****
  • Posts: 1228
Re: SQLDB parameters conceptual problem.
« Reply #4 on: November 29, 2015, 12:42:27 pm »
hello,
ask to Lacak for that . He has said : 
Quote
Because :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.    :-\
« Last Edit: November 29, 2015, 12:49:33 pm by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: SQLDB parameters conceptual problem.
« Reply #5 on: November 30, 2015, 01:59:39 am »
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][+][-]
  1. Query.Close;
  2. Query.Params.CreateParam(ftString, 'item_name', ptInput);
  3. Query.ParamByName('item_name').AsString:= 'Test';
  4. Query.InsertSQL.Text := 'INSERT INTO items(item_name) VALUES(:item_name)';
  5. Query.Open;
  6. Query.Insert;
  7. Query.Post;
  8. Query.ApplyUpdates;
  9. TX.Commit;

No value will be inserted.


Only when the command contains the real value it will be inserted:

Code: Pascal  [Select][+][-]
  1. Query.Close;
  2. Query.InsertSQL.Text:=Format('INSERT INTO items(item_name) VALUES(%s)',[QuotedStr('Test')]);
  3. Query.Open;  //Requires a SELECT statement.
  4. Query.Insert;
  5. Query.Post;
  6. Query.ApplyUpdates;
  7. TX.Commit;
  8.  

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.
« Last Edit: November 30, 2015, 02:19:41 am by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLDB parameters conceptual problem.
« Reply #6 on: November 30, 2015, 09:57:44 am »
If I understand right, ExecSQL works only with the command in the SQL property, it doesn't use InsertSQL.
Yes

Parameters works only for the SQL property. InsertSQL, UpdateSQL and DeleteSQL doesn't use parameters at all, even if they exist.
Yes

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.
Yes

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: SQLDB parameters conceptual problem.
« Reply #7 on: November 30, 2015, 10:30:01 am »
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

  • New Member
  • *
  • Posts: 36
Re: SQLDB parameters conceptual problem.
« Reply #8 on: November 30, 2015, 12:46:40 pm »
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.


 

TinyPortal © 2005-2018