Recent

Author Topic: SQL insert issue: Inactive dataset ?  (Read 14131 times)

IPguy

  • Sr. Member
  • ****
  • Posts: 385
SQL insert issue: Inactive dataset ?
« on: January 02, 2012, 04:56:22 am »
Just getting started with d/b's and have read most of the tutorials on the wiki, but still having issues.

I'm getting a exception with the comment: "Operation cannot be performed on an inactive dataset".

I am able to pull data from the table via my test program (and via FlameRobin).
When I attempt to insert a row, I get the above message.  Oddly, the row is inserted into the virtualDBGrid, but not, it seems, into the DB.

Suggestions?  Feedback?

Code: [Select]
procedure TfRCMMain.TestInsertClick(Sender: TObject);
begin
 SQLQuery1.Close;
 SQLQuery1.sql.text := 'insert into Groups (GRPNAME,ENDIS) values (:GRPNAME, :ENDIS)';

 SQLQuery1.params.parambyname('GRPNAME').asstring := Edit1.text;
 // Have to use integer until Boolean is released in FB v3
 SQLQuery1.params.paramByName('ENDIS').asinteger := StrToInt(Edit2.text);

 IBConnection1.Connected := True;

 SQLQuery1.ExecSQL;
//  Error points to the below line ....
 SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
 SQLTransaction1.Commit; //... and commit them using the transaction.

end;       
         

0.9.31-34510,2.5.1, win32-Vista
FireBird 2.5.1 embedded

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQL insert issue: Inactive dataset ?
« Reply #1 on: January 02, 2012, 07:31:56 am »
Quick guess: shouldn't you set your transaction to active/use .starttransaction...?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQL insert issue: Inactive dataset ?
« Reply #2 on: January 02, 2012, 09:20:32 am »
You're mixing up 2 things:
-running queries against the database with TSQLQuery.ExecSQL. A query in SQL is provided and executed. No dataset is involved.
-modifying a dataset and applying the updates with TSQLQuery.ApplyUpdates. A dataset is opened based on the query in SQL, modified with a DBControl or explicitly with Edit, Append, etc and, when all finished, the database is updated accordingly with ApplyUpdates.
You can't use both at the same time. SQLQuery1.Close will close the dataset and throw away all your changes.

I don't know what the rest of you code looks like but since you can change the dataset in the dbcontrol, your dataset is opened elsewhere. If that is the case, call ApplyUpdates before the call to SQLQuery1.Close.

IPguy

  • Sr. Member
  • ****
  • Posts: 385
Re: SQL insert issue: Inactive dataset ?
« Reply #3 on: January 02, 2012, 03:58:09 pm »
Thanks for the suggestions.  The insert is now working, but I have a few other issues.  Time to find more examples to study.

Here are both the corrected insert (I moved the ApplyUpdates to the top) as well as the initial query.  So far, these are the only two d/b related code chunks.

Code: [Select]
procedure TfRCMMain.GetTableGroupClick(Sender: TObject);
// Show the contents of the Group table
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'select * from Groups';
  IBConnection1.Connected := True;

  SQLQuery1.Open;
end;                 

Code: [Select]
procedure TfRCMMain.TestInsertClick(Sender: TObject);
begin
 SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
 SQLQuery1.Close;
 SQLQuery1.sql.text := 'insert into Groups (GRPNAME,ENDIS) values (:GRPNAME, :ENDIS)';
 SQLQuery1.params.parambyname('GRPNAME').asstring := Edit1.text;
 SQLQuery1.params.paramByName('ENDIS').asinteger := StrToInt(Edit2.text);
 IBConnection1.Connected := True;
 SQLQuery1.ExecSQL;
 SQLTransaction1.Commit; //... and commit them using the transaction.
end;     



IPguy

  • Sr. Member
  • ****
  • Posts: 385
Re: SQL insert issue: Inactive dataset ?
« Reply #4 on: January 02, 2012, 05:14:52 pm »
ludob,
re: datasets & SQLquery's:
You are correct - I am confused about where to use dataset and where to use SQLquery's.
The below implies that I do not need to issue sql statements to display, filter, insert / edit / delete data in my DB tables.  Is that correct?

When would I need to use sql statements and when should I not use sql statements?

Pulling quotes from one of the Lazarus Database Tutorials, where it talks about Datasets:
Quote
# Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
# Automatic database update: no need to update the database manually with SQL statements, a single method take cares of it

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQL insert issue: Inactive dataset ?
« Reply #5 on: January 02, 2012, 05:41:24 pm »
Quote
The below implies that I do not need to issue sql statements to display, filter, insert / edit / delete data in my DB tables.  Is that correct?
Yes. When using datasets you don't have to use ExecSQL to apply your updates. You still need to specify the dataset you are working with in TSQLQuery.SQL though.
Quote
When would I need to use sql statements and when should I not use sql statements?
ExecSQL is used for about everything else that cannot be done with a dataset ;)
Examples:
- creating,altering or deleting a table or view
- bulk replacements (update xyz set fielda=123 where fieldb=abc) or deletes
- bulk inserts (insert into xyz select ...)
- related updates on different tables such as decreasing a stock quantity when adding an item in an order (assuming you don't have a stored procedure in the database that does this)
- ...


BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQL insert issue: Inactive dataset ?
« Reply #6 on: January 03, 2012, 09:12:38 am »
Quote
The below implies that I do not need to issue sql statements to display, filter, insert / edit / delete data in my DB tables.  Is that correct?
Yes. When using datasets you don't have to use ExecSQL to apply your updates. You still need to specify the dataset you are working with in TSQLQuery.SQL though.

What Ludo said....

Of course (pedantic mode on), the controls (e.g. dbgrid) get their data from the datasource control/object.
The datasource gets its data from the query object/control.
The query object SQL property has an SQL select statement that tells it what data to retrieve. If possible, DeleteSQL, InsertSQL and UpdateSQL properties are filled in for you. These statements will be executed when the GUI controls have deletions/insertions or edits/updates and send their changes to the database.

If the query statement in the SQL property is complicated, you might need to specify DeleteSQL, InsertSQL, UpdateSQL yourself.

Finally, the SQL query object is linked to the transaction object/control, which is linked to the db connection object.
In Firebird and a lot of other databases, you need to explicitly commit transactions, this is especially important for queries that change data (insert/delete/update). You might circumvent this by using commitretaining, which keeps open the transaction after committing. Haven't used this myself

Hope this makes sense & is correct  :D

Finally, to let your program insert/edit/update data outside of GUI controls, it might be easier to set up a second set of transaction/query objects. You can then push your changes to the db using those. If you want to refresh the GUI, you can simply requery the query object that is linked to the GUI...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

IPguy

  • Sr. Member
  • ****
  • Posts: 385
Re: SQL insert issue: Inactive dataset ?
« Reply #7 on: January 03, 2012, 02:06:20 pm »
(Sigh.  My head hurts.)
Thanks Guys.  It is starting to make a bit more sense.

The issue for those of us who are just starting to work with database is that there are many solution sets and no clear decision tree to guide us.

I would be interested in an example app that shows how to do several basic operations via SQL and via dataset operations.  (ie: button1 does a SQL append & button2 does a dataset append, etc...) 

edit: just to be clear and not to offend those who have put a lot of work into documentation, a lot of the wiki articles have been very helpful in getting me this far. 
« Last Edit: January 03, 2012, 02:09:56 pm by IPguy »

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQL insert issue: Inactive dataset ?
« Reply #8 on: January 03, 2012, 03:27:29 pm »
Quote
The issue for those of us who are just starting to work with database is that there are many solution sets and no clear decision tree to guide us.
Bigchimp will say I oversimplify but basically when you use DBControls, you use datasources and thus datasets and you have to use ApplyUpdates to apply the changes. The "magic" behind TSqlQuery will handle the sql for you, keep track of your records (cursor, changes),etc. And yes, when the select SQL is complicated (fe. using multiple tables, no primary index,...) you'll need to give the magic a little hand and specify the Delete/Update/InsertSQL. When using ExecSQL however, you are totally on your own. No "magic". What You Do Is What You Get.

So you can start the decision tree pretty much from the user interface you want to put in place. DBControls? Use datasets, unless you want to re-invent the wheel. A data migration tool? Data aren't displayed nor modified individually by the user and exact data transfer is probably a must, so execsql is your solution. And yes, in the end your application will probably end up with a mix of the 2, but important is to keep the functionality completely separated. In my applications, I typically have 1 or more TSqlQuery components, sometimes using a different connection and/or transaction, that are exclusively used with ExecSQL. No datasource ever attached. I call them Qry1, Qry2, ... and I make sure their use is kept local inside a single procedure so that they can be-reused from almost everywhere. On the other hand, the TSqlQuery components that do have a datasource attached will only be used as datasets and never be used for anything else. Give them a name that doesn't include the word "Query" to make it clear. Call them TableABC if you like.
Another piece of advise, don't re-use TSqlQuery (used as dataset) and datasource components. You have 5 different dbgrids spread over different forms displaying 5 different sets of data: give them each their own TSqlQuery and Tdatasource. Close the bigger datasets when not used to keep memory usage in limits but don't try to save a few kilobytes in trying to re-use components. It's a wast of time and a source of errors.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQL insert issue: Inactive dataset ?
« Reply #9 on: January 03, 2012, 03:42:29 pm »
... hmmm... what Ludo said ;) (Usually I'm the one that oversimplifies stuff).
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQL insert issue: Inactive dataset ?
« Reply #10 on: January 03, 2012, 04:22:18 pm »
... hmmm... what Ludo said ;) (Usually I'm the one that oversimplifies stuff).
I'm still in new years mood: plenty of good intentions  :D Won't last though.

 

TinyPortal © 2005-2018