Problem SQL command -> INSERT INTO
lazarus / 16-Databases

----------------------------------------------------------------------
timmermanj · 2010-01-05 10:04

Hi,

I hope someone can help me with this.
I think this is very easy, i'm just a beginner.

I've succeed to make a connection with a MS-Access DB in Lazarus.
(ODBC)
When I give this SQL-command:

     SELECT * FROM Telefoonlijst

The "Telefoonlijst" table becomes visible. So the relation between my
components are working fine.

Then i close my SQL-Query & enter following SQL-command

     INSERT INTO Telefoonlijst VALUES ("Crosspony", "Tony",
"000/00.00.00")

If I open this Query then I get following error:

     Project dbTest.exe raised exception class 'EDatabaseError' with
message:
                    SQLQuery1: Cannot open a non-select statement

Could someone please advice?

Is my SQL-command wrong?
Am i doing something wrong when opening & closing my SQL-query?

I hope someone can help!

Thanks!

----------------------------------------------------------------------
theo · 2010-01-05 10:12

Quote from: timmermanj on January 05, 2010, 11:04:36 am     Project
dbTest.exe raised exception class 'EDatabaseError' with message:
                    SQLQuery1: Cannot open a non-select statement

It's written in the error message. Use ExecSQL
http://wiki.lazarus.freepascal.org/MySQLDatabases#TSQLQuery

----------------------------------------------------------------------
timmermanj · 2010-01-05 10:58

Perfect! I knew it was not that hard. 

Thanks a lot!

----------------------------------------------------------------------
jonas shinaniganz · 2011-09-12 09:59

Just incase you are still alive after more then 1 year, this is
exactly my problem but i dont get what timmermanj did with ExecSQL ?
Whats that? How do i use it?

And now please dont kill me for using forum search & gravedigging =)

----------------------------------------------------------------------
Arbee · 2011-09-12 10:24

An SQL SELECT statement returns data (hopefully) and for that you need
to open the query via the "open", after which you can traverse the
result set.

SQL statements that update the table (INSERT, UPDATE and DELETE
mainly) do not return a result.  If you use such an SQL statement you
execute it via "execSQL".

the explanation is almost in the commands themselves, actually.

----------------------------------------------------------------------
jonas shinaniganz · 2011-09-12 11:02

Does that mean the query has 2 States?

Open / ExecSQL

----------------------------------------------------------------------
Arbee · 2011-09-12 12:43

Not sure what you mean with two states .... It has two functions to
process SQL statements.  "open" for SELECTs and "execSQL" for the
rest.

----------------------------------------------------------------------
jonas shinaniganz · 2011-09-12 13:38

First of all i would like to thank you for your time...
The Stuff i want to do is working now.

But i havent jet understood the concept of the SQLQuery.
After I called :

SQLQuery1.Exec;

The SQL Query is in some kind of Execution state?
Because i never called SQLQuery1.Open; but the data returning works
fine tho. greetings

----------------------------------------------------------------------
SteveF · 2011-09-12 16:12

Quote from: jonas shinaniganz on September 12, 2011, 03:38:54 pmFirst
of all i would like to thank you for your time...
The Stuff i want to do is working now.

But i havent jet understood the concept of the SQLQuery.
After I called :

SQLQuery1.Exec;

The SQL Query is in some kind of Execution state?
Because i never called SQLQuery1.Open; but the data returning works
fine tho. greetings

The difference is in the intention of the SQL statement.

The statement "SELECT.... " returns a result set which you can then
display in some fashion.  In that case, SQLQuery1.Open creates a
conduit that makes it possible to display the result set within
Lazarus.

Other SQL statements are used to manipulate or modify either the data
in the tables, or the tables themselves.  Because the modification is
being done within the database by the database engine, you need to
tell the DB engine to make the changes.  The Exec command tells the DB
engine to make the changes contained within the SQL statement.

Make more sense now?

Steve

----------------------------------------------------------------------
jonas shinaniganz · 2011-09-13 09:24

Great!

With your help i could get an another problem solved and im really
happy right now. Time to move on!

Thanks a lot.

Jonas

----------------------------------------------------------------------
tguns · 2011-11-28 15:14

Hey guys, I got a similar problem.

I got a function parsing a text file, with a complex record as a
result.

Once the parsing is done, I want to insert those values into a SQLite3
Database.

Code: [Select]with PData do
      for I := Low(Players) to High(Players) do begin
        Query.SQL.Text := Format('INSERT INTO players VALUES
(''%s'',%s,%d)',
                           
 [Players[I].PID,Game.GID,Players[I].SStack]);
        Query.ExecSQL;
      end;
This seems to work fine, as when I do a SELECT query, I got all the
new data listed.

Problems appear, when I try to save the data to the actual database
file.

Code: [Select]Query.Upda]"]>Blockedde := upWhereAll;
Query.ApplyUpdates;
Transaction.Commit;
This returns an error "Cannot open a non-select statement"

These are the inital settings:
Code: [Select]Connection: TSQLite3Connection;
Query: TSQLQuery;
Transaction: TSQLTransaction;

...

path := ExtractFilePath(ParamStr(0));
Memo1.Append(path + 'test.db');
Connection.DatabaseName := path + 'test.db';
Connection.Transaction := Transaction;
Connection.Connected := True;
Query.DataBase := Connection;
Query.Transaction := Transaction;
Thanks for the help.

----------------------------------------------------------------------
Lacak2 · 2011-11-29 10:58

Quote from: tguns on November 28, 2011, 04:14:04 pmProblems appear,
when I try to save the data to the actual database file.

Code: [Select]Query.Upda]"]>Blockedde := upWhereAll;
Query.ApplyUpdates;
Transaction.Commit;
This returns an error "Cannot open a non-select statement"

I do not understand 

If you use "insert" + ExecSQL then only Transaction.Commit is required
to commit changes and store data permanently.

ApplyUpdates is another story. It applies changes made in local
dataset (using Edit/Appens ... Post methods) and post them into
underlaying database.

----------------------------------------------------------------------
M[a]nny · 2011-11-29 15:43

Correct me if i am wrong, i use it like this:

... if i want to make INSERT, UPDATE or DELETE:
Code: [Select]var
  Q: TZQuery;
begin
  // i count Connection (TZConnection) is already active and connected
  Q := TZQuery.Create(nil);
  Q.Connection := Connection;

  Q.SQL.Text := 'DELETE FROM table';
  Q.ExecSQL;

  Q.Free;

... and if i want to SELECT:
Code: [Select]var
  Q: TZQuery;
begin
  // i count Connection (TZConnection) is already active and connected
  Q := TZQuery.Create(nil);
  Q.Connection := Connection;

  Q.SQL.Text := 'SELECT * FROM table';
  Q.Open;
  while not Q.EOF do begin
    ShowMessage(Q.FieldByName('column').AsString);
    Q.Next;
  end;
  Q.Close;

  Q.Free;