Hello,

I have a query with the following SQL text:

SELECT Borrowers.*, BorrowerGroups.GroupNr, ReadingLevels.Description As ReadLevelDescr
FROM Borrowers
INNER JOIN BorrowerGroups ON BorrowerGroups.ID = Borrowers.IDGroup
INNER JOIN ReadingLevels ON ReadingLevels.ID = Borrowers.IDReadingLevel
WHERE ID = :ID

I open the query with this code:

qBorrowerItem.Close;
qBorrowerItem.ParamByName('ID').AsInteger := [an ID];
qBorrowerItem.Open;

After the Open the query becomes ReadOnly. I would like to know why. I was planning to edit some of the fields from the main table (Borrowers). Is there a solution for my problem?

Thanks,
Beni.

Beni

Roy Lambert

Beni wrote on Tue, 19 Feb 2013 06:24:58 -0500

>Hello,
>
>I have a query with the following SQL text:
>
>SELECT Borrowers.*, BorrowerGroups.GroupNr, ReadingLevels.Description As ReadLevelDescr
>FROM Borrowers
>INNER JOIN BorrowerGroups ON BorrowerGroups.ID = Borrowers.IDGroup
>INNER JOIN ReadingLevels ON ReadingLevels.ID = Borrowers.IDReadingLevel
>WHERE ID = :ID
>
>I open the query with this code:
>
>qBorrowerItem.Close;
>qBorrowerItem.ParamByName('ID').AsInteger := [an ID];
>qBorrowerItem.Open;
>
>After the Open the query becomes ReadOnly. I would like to know why. I was planning to edit some of the fields from the main table (Borrowers). Is there a solution for my problem?

Its the JOINs. If you replace those with subselects it should work.

SELECT Borrowers.*,
(SELECT BorrowerGroups.GroupNr FROM BorrowerGroups WHEREBorrowerGroups.ID = Borrowers.IDGroup),
(SELECT ReadingLevels.Description FROM ReadingLevels WHERE ReadingLevels.ID = Borrowers.IDReadingLevel)
, ReadingLevels.Description As ReadLevelDescr
FROM Borrowers
WHERE ID = :ID


Untested naturally.

Roy Lambert [Team Elevate]

What I understand from here is that a query can't be edited when there it is build from JOIN’s? This will complicate my life a lot!

On 2/19/2013 1:06 PM, Beni wrote:
> What I understand from here is that a query can't be edited when there it is build from JOIN’s? This will complicate my life a lot!

Behaviour is described here :

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

Raul

Raul wrote:

On 2/19/2013 1:06 PM, Beni wrote:
> What I understand from here is that a query can't be edited when there it is build from JOIN’s? This will complicate my life a lot!

Behaviour is described here :

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

Raul

Thanks for the help.

Beni.

Beni

>What I understand from here is that a query can't be edited when there it is build from JOIN’s?

Raul's given you a link to the rules, but essentially yes.

>This will complicate my life a lot!

That depends on wether or not you want to edit the data directly, or do things as they were in DBISAM where you could edit a canned dataset but you had to post the data back to the tables yourself.

Roy Lambert [Team Elevate]

Beni

You are right that it is complicated!

The problem is that it is hard for a single query to direct results back to multiple datasets. If you think about the logic it is more or less impossible to make updates automatically, particularly when there are calculated fields.

You will almost definitely need to do 1 of 2 things:

Write a CRUD framework or something similar (or use someone elses!) a lot of work ... but once you learn how to use them actually very very cool.

Use multiple queries and link them programmatically in Delphi, so that each dataset and subdataset remains Live/ReadWrite. This is more the "old-school" way of doing things, but it does work as long as your needs are not too complex.

Both ways of working are valid. Other posters may suggest further options.

Roy Lambert wrote:

Beni

>What I understand from here is that a query can't be edited when there it is build from JOIN’s?

Raul's given you a link to the rules, but essentially yes.

>This will complicate my life a lot!

That depends on wether or not you want to edit the data directly, or do things as they were in DBISAM where you could edit a canned dataset but you had to post the data back to the tables yourself.

Roy Lambert [Team Elevate]

Thanks for the answer.
I never used DBISAM. I started with Delphi3 + BDE + Paradox tables and since Borland introduced ADO (don't remember the exact Delphi version – I think it was 5) I used ADO and MS SQL Server. Now I have a customer who requires a database management system like ElevateDB so I have to "relearn" some techniques.
What about the LookUp fields? Can I use lookup fields attached to queries?

Adam Brett wrote:

>Beni
>
>You are right that it is complicated!
>
>The problem is that it is hard for a single query to direct results back to multiple datasets. If you think about the >logic it is more or less impossible to make updates automatically, particularly when there are calculated fields.

Never heard about calculated fields to be editable. In the old days (since you are talking about old school) I use to work with TTable’s against Paradox tables (using TQuery wasn’t a solution for larger tables) and the solution here was to use LookUp fields. Hope that I can do the same with the TEDBQuery.

>You will almost definitely need to do 1 of 2 things:
>
>Write a CRUD framework or something similar (or use someone elses!) a lot of work ... but once you learn how >to use them actually very very cool.
>
>Use multiple queries and link them programmatically in Delphi, so that each dataset and subdataset remains >Live/ReadWrite. This is more the "old-school" way of doing things, but it does work as long as your needs are >not too complex.
>
>Both ways of working are valid. Other posters may suggest further options.

It seems that you always have complicated solutions for simple problems!!!

Beni

>Thanks for the answer.
>I never used DBISAM. I started with Delphi3 + BDE + Paradox tables and since Borland introduced ADO (don't remember the exact Delphi version – I think it was 5) I used ADO and MS SQL Server. Now I have a customer who requires a database management system like ElevateDB so I have to "relearn" some techniques.

OK, but do you want to edit the data directly as though its a table, or edit a canned/insensitive dataset but post the data back to the tables yourself? If its the latter then there's a different solution - TEMPORARY tables or in-memory tables. Also a different syntax but might be easier for you.

>What about the LookUp fields? Can I use lookup fields attached to queries?

Yup. No problem there apart from network traffic.

Roy Lambert [Team Elevate]