SQLQuery.Last

[TRNelson]

Can anyone tell me how this works internally?  The first time I run it on a fairly large table, it seems to take a very long time (as compared to running it local). 

I have a strange feeling that it is actually reading each record in the table until it reaches the end of the table.  I looked at the ZeBedee log and the amount of data that was transmitted was huge.

[sky_khan]

Its not that strange. Yes, it fetches all records and if SQLQuery.UniDirectional is false it keeps/caches all of them in memory too. So its not advisable for really big tables

[TRNelson]

Its not that strange. Yes, it fetches all records and if SQLQuery.UniDirectional is false it keeps/caches all of them in memory too. So its not advisable for really big tables

Well that seems very odd to me.  Hopefully there is a very good reason for that.  It really isn't necessary to transmit EVERY record to the client if all you are doing is moving the current record pointer.

[sky_khan]

Delphi/FreePascal database/dataset classes is designed for editing records on mind in the first place. So if you do not need to show/edit all records of a table then you better use parameterized and/or created on-the-fly queries.
If you explain what are you trying to achieve you might get better help, i think

[rvk]

With SQLQuery.Last all the records DO need to be read. You could have attached a DBGrid where all those records need to be displayed. If you didn't attach a DBGrid you could just retrieve the correct record you want via specialized SQL (like stated before).

(You've just hit the downside of using a database directly over a slow line like the internet/VPN/SSH/Zebedee like stated in the previous topic )

Also note that you should never do a "SELECT * FROM TABLE". It will transmit the whole record (for all records) over the line. If you just need an ID, NAME and PLACE in your DBGrid for example, you should do a "SELECT ID, NAME, PLACE FROM TABLE" and when the users wants to change that record you could do a "SELECT * FROM TABLE WHERE ID=:ID" and display it in a separate form.

[TRNelson]

Interesting.  I was unaware of this.

So if you are not using DBGrid, can you advance the current record pointer by using SQLQuery.MoveBy(n) and avoid the transmission of all the intervening records to the client?

And if you execute SQLQuery.Locate(name,value,[parms]) and it doesn't find a match, does it load all the table records (not employing DBGrid again)?
 

[balazsszekely]

This is why stored procedures was invented in the first place. Do a single request client side, then make one or more complex(or not) calculation/query server side. Return a single result/record. It depends on your database server, but nowadays almost all servers support stored procedures.

[rvk]

So if you are not using DBGrid, can you advance the current record pointer by using SQLQuery.MoveBy(n) and avoid the transmission of all the intervening records to the client?
And if you execute SQLQuery.Locate(name,value,[parms]) and it doesn't find a match, does it load all the table records (not employing DBGrid again)?

Nope. Even if you don't use a DBGrid, TSQLQuery doesn't know that. So it will do multiple Next for MoveBy. Even with locate it needs to search the records so it needs to retrieve them.

Even if you use .Filter it retrieves all records. When you want to use .Filter you should use .ServerFilter and .ServerFiltered. In that case the SQL is internally changed and only the records you want are retrieved from the server.

From that, you can see that you need to do some SQL-fiddling to make sure you don't request too much data over the line.

What example do you have where you would do SQLQuery.Last??

Instead of SQLQuery.Last you could do "SELECT FIRST 1 FIELDS FROM TABLE ORDER BY ID DESC"?
This would retrieve the last record according to ID in your table. (Notice the FIRST 1, only retrieve 1 record and the ORDER BY ID DESC, reverse order on ID)

Also... you might want to build in paging in your remote app so you don't need to retrieve thousands of records. Only when the users scrolls down or goes to the next page you could retrieve a few records each time.

[TRNelson]

Well, this is progressing as it has in the past.  My learning pattern seems to be take a small step forward, take another, one more slightly bigger one, hit a bump and roadblock, fall back a few steps, Ah ha, now I get it!, repeat.  Good thing I like puzzles.  Helps keep the old grey matter alive.

To answer the question as to why I want to use SQLQuery.Last is pretty simple.  I don't really need it and was just following an example on how to replace TDBNavigator and hand code it as a learning exercise.

So if I code a stored procedure to search for a record (like SQLQuery.Locate), will this avoid all the data transmission?  I would be pretty okay with doing that instead of using Locate.  Just need to figure out how to do it then.

[TRNelson]

This is why stored procedures was invented in the first place. Do a single request client side, then make one or more complex(or not) calculation/query server side. Return a single result/record. It depends on your database server, but nowadays almost all servers support stored procedures.

That sounds like a good plan.  One that I have no clue how to do but will work on learning how.  I have never written one server side procedure yet.   I am using Firebird.

[rvk]

One that I have no clue how to do but will work on learning how.  I have never written one server side procedure yet.   I am using Firebird.

Besides using a stored procedure you could also just use a specific SELECT statement.

For example... if you want to edit the record with ID 23 you would do a "SELECT * FROM TABLE WHERE ID=23". There is no need to use a stored procedure for that. And it would be equivalent to doing "SELECT * FROM TABLE" and a SQLQuery.Locate('ID',23,[]);.

When you want to go directly to the last record you could change the SQL to "SELECT * FROM TABLE ORDER BY ID DESC" and reopen the dataset. The first record would be the last ID without retrieving all data and you can do Prior to step through the dataset again (only now are Prev and Next reversed )

So... if in SQL there is "ORDER BY ID DESC" then the first record is the "last" and Next is the same as Prev in the normal dataset. Otherwise the first record is the first and Next is really the next record.

(That way you could simulate all buttons of a DBNavigator)

[balazsszekely]

@rvk is right, in this particular case you don't need a stored procedure, although you can use one just for the sake of exercise.
Guys if it's firebird, what about this query:

Code: Pascal  [Select][+][-]

1. select * from TABLENAME where id in (select max(id) from TABLENAME)

PS: http://www.firebirdsql.org/refdocs/langrefupd21-ddl-procedure.html

[rvk]

Code: Pascal  [Select][+][-]

1. select * from TABLENAME where id in (select max(id) from TABLENAME)

Yeah, you're right. I didn't even think about MAX(ID). But the advantage of SELECT * FROM TABLE ORDER BY ID DESC is that you can still step backwards in the ID-list with Dataset.Next because the dataset is still all the records . So if you're looking to emulate the DBNavigator with the Next/Prev/First and Last buttons you could still do the Last and the dataset is still open so that with the Previous button you can issue a Dataset.Next to get to the previous record. Nice huh... (and confusing  )

[TRNelson]

This is all good and useful information.

My original question was about SQLQuery.Last and this has pretty much brought me right back to where I was with a previous question.  Not a problem.  This is all good stuff.

Ultimately what I want to have most of the functions of DBNavigator and a search function.  I would like my users to be able to search for a particular record and if successful have the current record pointer moved to the first record that matches the criteria without reloading the table.  That way they can continue to navigation from that point.  SQLQuery.Locate does that handily but it causes a lot of data movement.

Is there any way of directly accessing and setting the absolute position of the current record counter in a dataset?

[rvk]

Ultimately what I want to have most of the functions of DBNavigator and a search function.  I would like my users to be able to search for a particular record and if successful have the current record pointer moved to the first record that matches the criteria without reloading the table.  That way they can continue to navigation from that point.  SQLQuery.Locate does that handily but it causes a lot of data movement.

Is there any way of directly accessing and setting the absolute position of the current record counter in a dataset?

Nope, there is not. If you want the complete dataset you can't put a cursor in the "middle" without reading the previous records. There isn't even such a thing as "middle" in a dataset if you haven't read all records yet because the client only know how many records there are if the server gives an EOF on the last record.

I think you need to step off the idea of "moving the pointer" in the dataset. You said you don't have a DBGrid (or do you??) so you could just work with SQL statements to get the correct record.

For instance.
1)
A TEdit-box as filter.
Typing in test you could issue a statement like SELECT * FROM TABLE WHERE NAME like %test%.
The dataset would only hold records with test in the name. The user could step through those with Next en Prior.

2)
A TEdit-box as start-value.
You could do SELECT * FROM TABLE WHERE NAME>=test.
In that case you could get all records with name starting from test and all the rest. The user could step through all Next records but can't do Prior to those records. But maybe that's enough.

So if it is sufficient that the user only sees the records with what (s)he typed in then go for option 1. If (s)he needs to step further through to name=xoro then go for option 2.

In both cases you could also implement a "Last" button and order by NAME DESC in which case the first record is directly the last. The only thing you need to do is remember that you have a reversed list so if the user presses the Previous button you do a Dataset.Next and if (s)he presses Next-button you do a Dataset.Prior.

I think that is the best and fastest way to work when you want direct DB-access and don't have a DBGrid.

If you want I could create an example tomorrow (if somebody doesn't beat me to it) with a dataset, filterbox and a few navigate buttons, if you can't implement this yourself.