Recent

Author Topic: Error - Cannot open a non-select statement  (Read 1010 times)

nikel

  • Jr. Member
  • **
  • Posts: 75
Error - Cannot open a non-select statement
« on: October 10, 2018, 09:21:50 pm »
I'm getting non-select statement error with following code. I have a TEdit on my form, and a dblookup combobox. I want to update via tedit:



Code: Pascal  [Select]
  1. procedure TForm1.List_DllbxMouseDown(Sender: TObject; Button: TMouseButton;
  2.   Shift: TShiftState; X, Y: Integer);
  3. begin
  4.   DbUpdate;
  5. end;  
  6.  
  7. procedure TForm1.DbUpdate();
  8. var
  9.   SQLQuery: TSQLQuery; // Veritabanı
  10. begin
  11.   if (Main_Conn.Connected = false) then // Bağlantı kapalıysa aç
  12.   begin
  13.     Main_Conn.Open;
  14.   end;
  15.  
  16.   Main_Tscn.Active:=True;
  17.  
  18.   Main_Qry.Active:=False;
  19.  
  20.   SQLQuery := TSQLQuery.Create(nil);
  21.   SQLQuery.Database := Main_Conn;
  22.   SQLQuery.Transaction := Main_Tscn;
  23.   SQLQuery.Close;
  24.   SQLQuery.Clear;
  25.  
  26.  
  27.   SQLQuery.SQL.Add('INSERT OR REPLACE INTO "tbl_list" ( ');
  28.   SQLQuery.SQL.Add('  id, txt_option, txt_placeholder, int_data_type_id, txt_value, txt_default_value, bln_use_default, txt_operator, txt_additional_options, txt_description) ');
  29.   SQLQuery.SQL.Add('SELECT L.id, L.txt_option, L.txt_placeholder, D.id, :VALUE, L.txt_default_value, :USE_DEFAULT, L.txt_operator, L.txt_additional_options, L.txt_description ');
  30.   SQLQuery.SQL.Add('FROM "tbl_list" L ');
  31.   SQLQuery.SQL.Add('LEFT JOIN "tbl_data_type" D ');
  32.   SQLQuery.SQL.Add('  ON D.id = L.int_data_type_id ');
  33.   SQLQuery.SQL.Add('WHERE L.id = :ID ');
  34.  
  35.   SQLQuery.Params.ParamByName('ID').AsInteger:=1;
  36.   SQLQuery.Params.ParamByName('VALUE').AsString:=Value_Edt.Text;
  37.  
  38.   SQLQuery.Active:=True;
  39.   SQLQuery.Open;
  40.   SQLQuery.ApplyUpdates();
  41.   Main_Tscn.CommitRetaining;
  42.  
  43.   Main_Qry.Active:=True;
  44. end;

Can anyone help me?

Either if I set ID like this:
Code: Pascal  [Select]
  1. SQLQuery.Params.ParamByName('ID').AsInteger:=StrToInt(List_Dllbx.KeyValue);
I get error: Could not convert variant of type (Null) into type (String)


GetMem

  • Hero Member
  • *****
  • Posts: 3257
Re: Error - Cannot open a non-select statement
« Reply #1 on: October 10, 2018, 09:41:02 pm »
For insert, update you need:
Code: Pascal  [Select]
  1. SQLQuery.ExecSQL
for select:
Code: Pascal  [Select]
  1. SQLQuery.Open;
You should split the operation in two steps:
1. Insert sql, ExecSQL, ApplyUpdates, Commit
2. Select sql, Open

nikel

  • Jr. Member
  • **
  • Posts: 75
Re: Error - Cannot open a non-select statement
« Reply #2 on: October 10, 2018, 09:57:35 pm »
Hello, I'm still getting non-select statement error. I define the function in DBLookupCombobox's MouseDown event, am I doing this right? I guess MouseDown event is triggered before changing an item.

I'm trying to save TEdit's content before a DBLookupCombobox item is changed. Can I do this without using TDBEdit?

Thanks for the help.

dpremus

  • New member
  • *
  • Posts: 6
Re: Error - Cannot open a non-select statement
« Reply #3 on: October 10, 2018, 10:34:52 pm »
It is difficult to figure what you try to achieve but I see lots of problems in your code.

1) First you have a memory leak in line 20
Code: Pascal  [Select]
  1. SQLQuery := TSQLQuery.Create(nil);

This code create new instance of TSQLQuery every time when you call DbUpdate procedure.
Object pascal doesn't have garbage collector so you must free every object that you create.

for example:

Code: Pascal  [Select]
  1.  SQLQuery := TSQLQuery.Create(nil);
  2. try
  3. ...
  4. finally
  5.   TSQLQuery.free;
  6. end;

you can see try ... finally block too, to be sure that your object will free even if something goes wrong.

2) there is no need to create and destroy TSqlQuery component for every sql execution. In your case
you can create  TSQLQuery as form field and create TSQLQuery only once in FormCreate event.

for example:
Code: Pascal  [Select]
  1.  Self.FSQLQuery := TSQLQuery.Create(self);
you can see that this code call "TSQLQuery.Create" with self parameter for owner in this case
TSQLQuery will be automatically released together with form.

3)
Code: Pascal  [Select]
  1.  
  2.   SQLQuery.Active:=True;
  3.   SQLQuery.Open;  //<- this is unnecessary this command is same as: "SQLQuery.Active:=True;"
  4.  

If you look in TDataSet source code you can see this:

Code: Pascal  [Select]
  1. procedure TDataSet.Open;
  2. begin
  3.   Active:=True;
  4. end;      
  5.  

4) Your SQL doesn't return any records so you need to use   TSQLQuery.ExecSQL method instead open.

5) regarding error  "Could not convert variant of type (Null) into type (String)"
problem is here: StrToInt(List_Dllbx.KeyValue);

KeyValue property is variant type and current value is "null" but function StrToInt requires string;

6) "calling DbUpdate from mouse down."
Executing SQL every time when somebody make mouse down does not make sense, this make
unnecessary network trafic and database server load. If you have large database tables your application will be frozen during sql execution.


You need to change design of your application to fix this.




nikel

  • Jr. Member
  • **
  • Posts: 75
Re: Error - Cannot open a non-select statement
« Reply #4 on: October 11, 2018, 12:23:46 pm »
Hi, thanks for the detailed reply. But this doesn't solve my issue. I need to update record each time the DBLookupCombobox item changes because it's something like detail/master form. Here's my code so far:

Code: Pascal  [Select]
  1. procedure TForm1.DbUpdate();
  2. var
  3.   SQLQuery: TSQLQuery; // Veritabanı
  4. begin
  5.   if (Main_Conn.Connected = false) then // Bağlantı kapalıysa aç
  6.   begin
  7.     Main_Conn.Open;
  8.   end;
  9.  
  10.   Main_Tscn.Active:=True;
  11.  
  12.   Main_Qry.Active:=False;
  13.  
  14.   SQLQuery := TSQLQuery.Create(self);
  15.   SQLQuery.Database := Main_Conn;
  16.   SQLQuery.Transaction := Main_Tscn;
  17.   SQLQuery.Close;
  18.   SQLQuery.Clear;
  19.  
  20.  
  21.   SQLQuery.SQL.Add('INSERT OR REPLACE INTO "tbl_list" ( ');
  22.   SQLQuery.SQL.Add('  id, txt_option, txt_placeholder, int_data_type_id, txt_value, txt_default_value, bln_use_default, txt_operator, txt_additional_options, txt_description) ');
  23.   SQLQuery.SQL.Add('SELECT L.id, L.txt_option, L.txt_placeholder, D.id, :VALUE, L.txt_default_value, :USE_DEFAULT, L.txt_operator, L.txt_additional_options, L.txt_description ');
  24.   SQLQuery.SQL.Add('FROM "tbl_list" L ');
  25.   SQLQuery.SQL.Add('LEFT JOIN "tbl_data_type" D ');
  26.   SQLQuery.SQL.Add('  ON D.id = L.int_data_type_id ');
  27.   SQLQuery.SQL.Add('WHERE L.id = :ID ');
  28.  
  29.   SQLQuery.Params.ParamByName('ID').AsInteger:=List_Dllbx.KeyValue;
  30.   SQLQuery.Params.ParamByName('VALUE').AsString:=Value_Edt.Text;
  31.  
  32.   // SQLQuery.Active:=True;
  33.   SQLQuery.ExecSQL;
  34.   SQLQuery.ApplyUpdates();
  35.   Main_Tscn.Commit;
  36.  
  37.   Main_Qry.Active:=True;
  38. end;  

I attached a screenshot.

GetMem

  • Hero Member
  • *****
  • Posts: 3257
Re: Error - Cannot open a non-select statement
« Reply #5 on: October 11, 2018, 01:19:13 pm »
As I mentioned in my previous post you cannot mix insert/update with a select statement. For insert/update use SQLQuery.ExecSQL for select SQLQuery.Open. Do the operation in two step, like this:
Code: Pascal  [Select]
  1. procedure TForm1.DbUpdate();
  2. var
  3.   SQLQuery: TSQLQuery;
  4. begin
  5.   if (Main_Conn.Connected = false) then
  6.   begin
  7.     Main_Conn.Open;
  8.   end;
  9.  
  10.   Main_Tscn.Active:=True;
  11.   Main_Qry.Active:=False;
  12.  
  13.   SQLQuery := TSQLQuery.Create(self);
  14.   try
  15.     SQLQuery.Database := Main_Conn;
  16.     SQLQuery.Transaction := Main_Tscn;
  17.  
  18.     //insert
  19.     SQLQuery.SQL.Add('INSERT OR REPLACE INTO "tbl_list" ( ');
  20.     SQLQuery.SQL.Add(':ID, txt_option, txt_placeholder, int_data_type_id, txt_value, txt_default_value, bln_use_default, txt_operator, txt_additional_options, txt_description) ');
  21.     SQLQuery.Params.ParamByName('ID').AsInteger:=List_Dllbx.KeyValue;
  22.     SQLQuery.ExecSQL;
  23.     SQLQuery.ApplyUpdates();
  24.     Main_Tscn.Commit;
  25.  
  26.     //select
  27.     SQLQuery.SQL.Clear;
  28.     SQLQuery.SQL.Add('SELECT L.id, L.txt_option, L.txt_placeholder, D.id, :VALUE, L.txt_default_value, :USE_DEFAULT, L.txt_operator, L.txt_additional_options, L.txt_description ');
  29.     SQLQuery.SQL.Add('FROM "tbl_list" L ');
  30.     SQLQuery.SQL.Add('LEFT JOIN "tbl_data_type" D ');
  31.     SQLQuery.SQL.Add('  ON D.id = L.int_data_type_id ');
  32.     SQLQuery.SQL.Add('WHERE L.id = :ID ');
  33.     SQLQuery.Params.ParamByName('ID').AsInteger :=List_Dllbx.KeyValue;
  34.     SQLQuery.Params.ParamByName('VALUE').AsString:=Value_Edt.Text;
  35.     SQLQuery.Open;
  36.   finally
  37.     SQLQuery.Free;
  38.   end;
  39. end;

By the way SQLQuery has a property(see Object Inspector) named InsertSQL, where you can define the insert statement, the main sql query then can be a simple select. Either way it should work just fine.

nikel

  • Jr. Member
  • **
  • Posts: 75
Syntax error near "("
« Reply #6 on: October 11, 2018, 04:29:11 pm »
Hi thanks for the helps. Before fixing error: Could not convert (Null) into type int64 at line
Code: Pascal  [Select]
  1. SQLQuery.Params.ParamByName('ID').AsInteger:=List_Dllbx.KeyValue

I want to learn how can I update a record corectly, rather than "insert or replace" command. The code below is giving me syntax error near (

Code: Pascal  [Select]
  1. SQLQuery.SQL.Add('UPDATE "tbl_list" ( id, txt_option, txt_placeholder, int_data_type_id, txt_value, txt_default_value, bln_use_default,  txt_operator, txt_additional_options, txt_description ) ');
  2. SQLQuery.SQL.Add('SELECT L.id, L.txt_option, L.txt_placeholder, D.id, L.txt_value, L.txt_default_value, L.bln_use_default, L.txt_operator, L.txt_additional_options, L.txt_description ');
  3. SQLQuery.SQL.Add('FROM "tbl_list" L ');
  4. SQLQuery.SQL.Add('LEFT JOIN "tbl_data_type" D ON D.id = L.int_data_type_id ');
  5. SQLQuery.SQL.Add('WHERE L.id = :ID AND L.txt_value = :VALUE ');
  6. SQLQuery.Params.ParamByName('ID').AsInteger:=List_Dllbx.KeyValue;
  7. SQLQuery.Params.ParamByName('VALUE').AsString:=Value_Edt.Text;
  8. SQLQuery.ExecSQL;
  9. // SQLQuery.ApplyUpdates();
  10. // Main_Tscn.Commit;

Thanks for all helps again.

ASerge

  • Hero Member
  • *****
  • Posts: 998
Re: Syntax error near "("
« Reply #7 on: October 11, 2018, 06:26:45 pm »
Sometimes the query text contains :USE_DEFAULT, but nothing is assigned to this parameter.
INSERT OR REPLACE INTO used for bulk operation. From your examples, the goal is not very clear, so it's hard to say how best.
You can use UPDATE to update several fields, but you need to write SET Field=Value.
And I'm a little annoyed by that expression: "if (Main_Conn.Connected = false) then". "if not Main_Conn.Connected then" very easy? Lets write "if (((Main_Conn.Connected = false) = True) <> False) then"  :)

nikel

  • Jr. Member
  • **
  • Posts: 75
Re: Error - Cannot open a non-select statement
« Reply #8 on: October 11, 2018, 11:32:58 pm »
Woww I saw this in a C tutorial.

Code: Pascal  [Select]
  1. ((Main_Conn.Connected = false) = True)

Does this mean true && false

or

true || false?

lucamar

  • Hero Member
  • *****
  • Posts: 504
Re: Error - Cannot open a non-select statement
« Reply #9 on: October 12, 2018, 12:57:37 am »
Woww I saw this in a C tutorial.
Code: Pascal  [Select]
  1. ((Main_Conn.Connected = false) = True)
Does this mean true && false
or
true || false?
Build yourself a truth table. If and only if Main_Conn.Connected is false the inner parenthesis resolves to (false = false) which is, obviously true, so the outer parens resolve to (true = true).

Which is exactly the same (in C) as !(Main_Conn.Connected) or, in Pascal, not Main_Conn.Connected

Only, in C there's no built-in True or False, so there may be some reason for the construction---though I seriously doubt it.

ETA: I'm curious, where can on see that "tutorial"?
« Last Edit: October 12, 2018, 01:09:30 am by lucamar »
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 1.8.4/FPC 3.0.4 on:
(K)Ubuntu 11..16, Windows XP SP3 (Home/Prof.) and various DOS incarnations.

nikel

  • Jr. Member
  • **
  • Posts: 75
Re: Error - Cannot open a non-select statement
« Reply #10 on: October 12, 2018, 04:53:17 am »
Hi there! I couldn't remember it clearly sorry. It's C++ tutorial at this page:

More interesting conditions using boolean operators
« Last Edit: October 12, 2018, 04:57:51 am by nikel »