ZQuery, DB KEY, SortedFields and Fetchrow problems

[fantablup]

I'm trying to get it all working, but problem after problem.

Three big problems i am struggling with.

1: When using TZQuery, i set SortedFields to Firstname. The DBCResultset will not work. When going 10 records forward from the first record, I get the correct row, and it works on the next rows too. But between record 1 and 10, i only get row 1. The same goes for from the last record and down 10 records.
Why is it only working good after the 10 first records when i use SortedFields, but all ok without using it?

When i do not use SortedFields, it works good.

2: When setting FetchRow to 20, i get 20 records from the query. But when i use SortedFields, i get all records.
I need to get FechRows to work with SortedFields. What solution is there?

3: Is there any solution on getting the DB KEY from the rows from ZQuery? So i can update it or delete it with a SQL statement.
I mean, the DB KEY like the Firebird RDB$DB_KEY.
Is there any solution on this key in MySQL too?
I need to get the DB KEY from the transaction.

Hope you can help.

Best regards

[marsupilami]

Hello Fantablup,

welcome to the forums

I'm trying to get it all working, but problem after problem.

Three big problems i am struggling with.

1: When using TZQuery, i set SortedFields to Firstname. The DBCResultset will not work. When going 10 records forward from the first record, I get the correct row, and it works on the next rows too. But between record 1 and 10, i only get row 1. The same goes for from the last record and down 10 records.
Why is it only working good after the 10 first records when i use SortedFields, but all ok without using it?

When i do not use SortedFields, it works good.

That sounds like a bug. Could you please try to create a small sample application and database script that show the problem? This really helps with debugging.

2: When setting FetchRow to 20, i get 20 records from the query. But when i use SortedFields, i get all records.
I need to get FechRows to work with SortedFields. What solution is there?

Erm - there is none. For sorting the records we need to know all the records. If we don't know the record values, we can not sort them? Your only option for getting FetchRows to work with sortig is to have the database do the sorting because then records will be sorted before the database sends them to Zeos.

3: Is there any solution on getting the DB KEY from the rows from ZQuery? So i can update it or delete it with a SQL statement.
I mean, the DB KEY like the Firebird RDB$DB_KEY.

I think there also is no solution here. As far as I know, Firebird and Interbase don't expose the RDB$DB_KEY if it doesn't get selected by the user. So usually Zeos will not know the RDB$DB_KEY value and so cannot show it.
But usually this will not be necessary. Zeos usually detects the primary key of a selection and uses it to delete rows. You can also instruct Zeos to not only use the primary key but also all the other fields. To switch between these modes, change the WhereMode property of TZQuery.
I can only assume that these questions have to do with with your DB admin app. We had discussions on what to do if the user doesn't have a primary key on its data. We came to the conclusion that there isn't much we can do besides using wmWhereAll in the WhereMode.

Is there any solution on this key in MySQL too?
I need to get the DB KEY from the transaction.

I am not sure, if MySQL has a concept like the RDB$DB_KEY. I assume that usually this would depend on the storage engine that gets used? But my knowledge on MySQL is bad.

Best regards,

Jan

[fantablup]

Thank you Jan

You answered so professional that i became amazed.

Wish it could be the same elsewhere too

Yes, it's for the DB admin app.

I will create a little sample application for you to see the thing you think is a bug.

You helped me allot. I can now continue with some other solution.

When user enter their own SQL, i can not limit rows from the server, so it will depend on the user to limit rows in the SQL. The same goes for procedures.

When users just opens a table or view, i have already implemented added all the fields to the SQL select, and using ROWS from - to.

When Inserting updating or deleting, i have to look in other sources to see how they do it. Because the app will not know the row number, and it can be tables without any indexes. So, updating one row should not update another row that has exactly the same data in it. This can actually be true.

Thanks again Jan. You have been a time saver, and a good help.

Best Regards.

[fantablup]

I think i know how to getting the transaction row number on the SQL executed by the user.
I already have implemented a chack if it is a select statement, and added the RDB$DB_KEY to the end of the first select in the SQL. So, in Firebird it works good.
I can do the same with other engines, with getting the rowid. These numbers does not change by other transactions.

If it has none select in the first statement, it can not be edited, deleted or updated.

This has to be the correct solution.

[fantablup]

I solved all the sorting issues.
Also on the user executed SQLs.

I just wanted to let you know how i did it.
I don't use the IZResultSet or DBCResultset other than getting metadata, but using the ZQuery, and set SortedFields, and then i created a custom function that loads 500 records at once. And when going down the grid, it checks if the last rows is less then 200, and then loads the next 500 results to the grid.

And when clicking a header title, it close the dataset, and re-set the SortedFields, and re-open it. And whola, it works. And it is super fast. Also works good on results from procedures.

Two problems solved. Sorting and max row fetching. I can't use the fetchrows, because it does not work with sorting. But this is working good with sorting.
You can actually implement this in the component i think.

[marsupilami]

I don't use the IZResultSet or DBCResultset other than getting metadata

Regarding Metadata: You might want to think about TZSQLMetadata.

, but using the ZQuery, and set SortedFields, and then i created a custom function that loads 500 records at once. And when going down the grid, it checks if the last rows is less then 200, and then loads the next 500 results to the grid.

And when clicking a header title, it close the dataset, and re-set the SortedFields, and re-open it. And whola, it works. And it is super fast. Also works good on results from procedures.

I am not sure, if you pushed sorting to the server side. If you didn't, then how do you solve the following example?

aab
abc
[... some 600 records thal all sort behind the first ones ...]
aaa

If you push sorting to the server, the server will know about aaa and send it first. If you sort on the client side you can't know about aaa and don't know that there is an important record to fetch because you will not see it within the first 500 records you fetch?

[fantablup]

Thanks for the metadata tip.
Always great to get help and tips.

I did more testing, and you are correct.
It still loading all the records at once.
But the grid is loading what i set, so that s working.

Maybe there is no solution on user executed SQL after all.

Too little testing from my side.

I will create I little SQL parser and change or add ORDER BY in the original SQL text. I guess that is the only solution. But then it should go well.
This should be implemented in Zeos actually. Would be great.
The sortedfields is just not usable on large datasets, when you don't know the SQL text.

[fantablup]

It works.
I realized that this is the same way FlameRobin do it. Because it works on select's but fails on procedures. So, it has to be the same thing.
You can not add order by on procedure calls. You can only have order by inside the procedures.

Finding the last Order By clause that is not in any sub selects and setting new sorting.

Now, it is finally working as i wanted.
And i also not using any rdb$db_key anymore for updating. Not working on everything. I only use the zeos updating with the transaction row numbers. Works on everything.
Every problem is solved.

This took some time to get to. I learn on the way every day it seems.
Now i can finish the SQL editor finally.

Thanks for all help and tips to both. It made me being able to create the solution.

Best Regards.