Database error - cursor not returned from query

Question

Had an issue with "cursor not returned from query" and found a solution that basicaly reopens the query once more:

procedure TForm2.AdvGlowButton1Click(Sender: TObject);
begin
with ClientdataSet1 do
begin
Close;
CommandText :='';
CommandText :='INSERT INTO TLOG (LOKACIJA_ID,OPOMBA) VALUES (:a1,:a2)';
Params.ParamByName('a1').Value :=  Form3.ClientDataSet4.FieldByName('LOKACIJA_ID').AsString;
Params.ParamByName('a2').Value := cxMemo1.Text;
Execute;
CommandText :='';
CommandText :='SELECT * FROM TLOG WHERE LOKACIJA_ID =:a1';
Params.ParamByName('a1').Value := Form3.ClientDataSet4.FieldByName('LOKACIJA_ID').AsString;
Open;
end;

Now,I am wondering if this is the right approach or is there another way around this error? If I try and open the dataset after first execute (delete the rest)I get the mentioned error. Is this the way this is supposed to function? This is a datasnap client-server app with sqlite as backend DB.

edit: The form with the dataset1 that runs this query uses another form (form3) i.e it's Form3.DSProviderConnection1 to connect to server.On the server side in ServerMethodsUnit1 I have a DatasetProvider8 which is linked to SQLQuery7 (in the SQL of the query I have: select * from TLOG). I suppose I could replace this query on the server with a table.Now,what I am doing is this on the FormShow :

procedure TForm2.FormShow(Sender: TObject);
begin
with ClientdataSet1 do
begin
ClientdataSet1.Close;
ClientdataSet1.CommandText :='';
ClientdataSet1.CommandText :='SELECT * FROM TLOG WHERE LOKACIJA_ID =:a1';
ClientDataSet1.Params.ParamByName('a1').Value := Form3.ClientDataSet4.FieldByName('LOKACIJA_ID').AsString;
ClientDataSet1.Open;
end;

I am fetching records based on the location. So now the user sees only the records from his location in the grid.So if I am not mistaken if user adds or changes a record the data first must be inserted and then displayed again in the same manner it was fetched. Or no ? Or perhaps it would be better to replace that server query with a table and display the table itself with a filter (location_id) so I could just run insert query and just call Table refresh?

The reason you get the error is because you have turned your ClientDataset.CommandText into an INSERT statement, which does indeed return no cursor. Why not try creating a method on your remote data module to add a record instead ? You would then just refresh or open the cds on the client side. — Hugh Jones
– Hugh Jones, Commented Jan 28, 2014 at 9:23
I suggest you ditch the with statement altogether. It seems it's being ignored here in any case because all your references are explicit. with has a simple alternative, and simply isn't worth the potential is has to waste your time, as the following recent example will attest: stackoverflow.com/a/21371312/224704 — Disillusioned
– Disillusioned, Commented Jan 28, 2014 at 9:30
@user3181689 Using with is still a bad idea. Follow the link I provided above to observe how a tiny error resulted in a huge and unnecessary waste of time. Considering the fact that with has a trivial and much safer alternative, there really is no point in using it. — Disillusioned
– Disillusioned, Commented Jan 28, 2014 at 13:24

Community · Accepted Answer · 2017-05-23 12:21:00Z

1

You have a number of options. The most appropriate will depend on the particulars of your environment and requirements. I'm not sure what you've got because you refer to ClientDataSet, which implies mulit-tier architecture. However, you don't seem to be using it in a multi-tier fashion.

Option 1

Based on this comment I've updated option 1 to demonstrate using a query.

//Your dataset first needs to be linked to an appropriate underlying structure
//E.g. a Table, or a Query that selects from a single table.
//This example uses a query, and assumes FCurrentLocation is the "pertinent" one.
DataSet1.CommandText := 'SELECT * FROM TLOG WHERE LOKACIJA_ID = :a1';
DataSet1.ParamByName('a1').Value := FCurrentLocation;
DataSet1.Open;

//... Later / another method
DataSet1.Insert;
DataSet1.FieldByName('LOKACIJA_ID').AsString := FCurrentLocation;
DataSet1['OPOMBA'] := y; //Alternative to FieldByName
DataSet1.Post;

Opion 2

Implement INSERT and SELECT in a stored procedure, then:

StoredProc.ProcName := 'MyProc';
StoredProc.Params.ParamByName('a1').Value := x;
...
StoredProc.Open;

Option 3

Depending on your connection provider, you may even be able to simply put both queries in a single statement.

DataSet.CommandText := 'INSERT INTO TLOG (LOKACIJA_ID, OPOMBA) VALUES (:a1, :a2); ' +;
                       'SELECT * FROM TLOG WHERE LOKACIJA_ID = :a1';

Option 4

Some RDBMS's provide syntax for returning inserted/updated rows as part of the same SQL statemement. Unfortunately I'm unsure about SQL Lite.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jan 28, 2014 at 9:26

Disillusioned

14.9k3 gold badges47 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jan Doggen Over a year ago

@User3181689 It depends on your app but I suggest Craig's first approach. Just do whatever you want in the ClientDataSet (edit, delete, insert), then when you want to write back changes to the database, do ApplyUpdates(). TClientDataSet is very powerful, you should read up on it.

user3181689 Over a year ago

To open the table I use a query so only selected records are picked up from the table.This is important because LOKACIJA_ID is pertinent to a location so the records displayed are ONLY from a particular location.So when a user adds a record he is adding it for a particular location and it is immediately visible.I am wondering if this is a sound idea to first insert and then open in the same query?

Disillusioned Over a year ago

@user3181689 I've updated option 1 to demonstrate using a query.

user3181689 Over a year ago

based on your first example was basically my first problem. I run the select query to fetch required data. Once I had it open,I posted but query wouldnt refresh the data so I had to open it again.Problem is that I always work with the data provided by the sqlquery. Or am I missing something?

Collectives™ on Stack Overflow

Database error - cursor not returned from query

1 Answer 1

Option 1

Opion 2

Option 3

Option 4

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1

Opion 2

Option 3

Option 4

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related