0
CREATE DEFINER=`username`@`servername` PROCEDURE `sp_name_of_procedure`(
    IN NAMEOFTHETABLE VARCHAR(40),
    IN RECORDSPERPAGE INT
)
BEGIN
    
    set @q1 := concat("SELECT *  FROM DATABASE.",NAMEOFTHETABLE );
    set @q2 := concat("SELECT count(*)  FROM DATABASE.",NAMEOFTHETABLE ,"AS TOTALRECORDS");
    prepare s2 from @q2;
    execute s2;   
    
    set @q1 := concat(@q1,' LIMIT ',TOTALRECORDS,',',RECORDSPERPAGE );
    prepare s1 from @q1;
    execute s1;
    deallocate prepare s1;
    deallocate prepare s2;
END

I am needing to view some logs in a table, and it needs to be dynamic script so i can use this to view any of the logs without having to make static stored procedures for each table. Do not worry, validation is being taken care of server and client side. I can pull the Recordset, and I can get it to limit how many to pull, but I need it to offset at the END of the Recordset, so for example, if I wanted to just view the last 100 logs of a table.

It seems I might have to actually make 2 different recordsets to do this, because by setting the cursor type to go backwards, I can not get q2 to run to caluclate how many records I have to set that as the offset. I can't use MAX because the keys are all different in each table, so a COUNT(*) is easiest method I would think. But I get this error:

ODBC driver does not support the requested properties.

EDIT - ( TLDR : The recordset needed to be flipped by MYSQL instead of letting ASP Classic do it )

But I guess my question after explaining everything is, is there a way to tell the LIMIT command to just go to the last record without calculating that number beforehand? And if not, is my assumption that I should make 2 stored procedures and have one cursor type run first that allows for the count, then run the second stored procedure to plug in that count to OFFSET the records towards the end of the list? Maybe I answered my own question on how to do this, but perhaps someone has some information on LIMIT and OFFSET that will throw me to the end of the recordset without having to count the amount of records first? Thanks in advance! (side note - I know COUNT isn't efficient, and * isn't, but I need dynamic use and at the moment I am literally only using 2% of the CPU load.

UPDATE - This question is different than the offered suggestions as they were using pagination, and this particular script is designed to dump an exact amount of records to the screen per user form request, and won't exactly need to go forward or backwards.

easleyfixed
  • 219
  • 1
  • 13
  • Hmm this might be useful .. trying this out. https://stackoverflow.com/questions/13925732/how-to-get-the-last-record-before-the-last-one-in-mysql/13925779 Not exactly what I need but gets me thinking, the brevity of this is great. I prefer selects in selects to multi line sql statements to generate the data. – easleyfixed Jan 27 '22 at 18:50
  • It might be ORDER BY is a way to do this very simply. – easleyfixed Jan 27 '22 at 18:53
  • https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem – Mihai Jan 27 '22 at 18:54
  • Cursor type issues probably. Can't do a count when you allow cursor to go backwards. I think i found my solution by doing an ORDER BY DESC by Key .. that should work . Will update shortly. – easleyfixed Jan 27 '22 at 18:56
  • Shooot .. this would work but the keys are all custom per table . .so i can't just be like order like "key name" because it is unique... still scratching head for now .. Unless .. there is a way to do ORDER BY first_column_dynamic_name .. I seem to recall there is a way to do this but can't remember off top of head. – easleyfixed Jan 27 '22 at 18:57
  • Ahhh easier than I thought .. here is how .. just tell it the column number .. derp, lol. https://stackoverflow.com/questions/11368871/mysql-can-we-order-by-column-position-instead-of-name/11368893 – easleyfixed Jan 27 '22 at 19:00
  • Does this answer your question? [MySQL Data - Best way to implement paging?](https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging) – user692942 Jan 27 '22 at 19:19
  • Does this answer your question? [Pagination on with mysql queries](https://stackoverflow.com/a/25547633/692942) – user692942 Jan 27 '22 at 19:22
  • Thank you but unfortunately I am not using paginating or paging on this particular script. However, I will save this link for future use as I have some things in the help desk add on that this would be very useful. In the end it was thinking about it backwards, instead of getting the recordset and flipping it in asp classic, i should of flipped the recordset in mysql first , but thanks agian! – easleyfixed Jan 27 '22 at 20:17
  • 1
    Okay, so you are not doing pagination but you literally have a value being passed called `RECORDSPERPAGE`…whether you navigate the paged data or not it’s still paged data (your taking a slice based on a set page size). Honestly struggling to understand what the point of this question is?? – user692942 Jan 27 '22 at 20:24
  • Yes, that value of RECORDSPERPAGE is how many records I want on the page. However, there will only be one page for this script. The problem was with trying to use LIMIT (INT,INT) , I was trying to put the COUNT of how many records into the first INT and the use the following INT to go backwards RECORDSPERPAGE number of records, but in reality I was thinking about it wrong, and just needed to flip the data and use an ORDER DESC instead of using ASP Classic cursor to move backwards. Long story short, I was just thinking about it incorrectly. – easleyfixed Jan 27 '22 at 20:38
  • However you look at it, it’s still pagination you're just only interested in the first page. How you maintain any filters or sort order still feeds into it, my point is both those duplicates are relevant whether you think so or not. – user692942 Jan 27 '22 at 20:56
  • Relevant and noted, but my point is yes its helpful, but is more a correlation in this instance, but I do appreciate the input, the creation of this question basically puts that link into my inbox, so I appreciate it. – easleyfixed Jan 27 '22 at 21:08

1 Answers1

0
set @q1 := concat("SELECT *  FROM DATABASE.",NAMEOFTHETABLE );
set @q1 := concat(@q1,' ORDER BY 1 DESC');
set @q1 := concat(@q1,' LIMIT ',RECORDSPERPAGE);

This basically did it for me I still have some tweaking to do, but I think I was over thinking it. I wanted to OFFSET by the max record count, but instead, I just needed to flip the order of the records. So the stored procedure accepts an input of a tablename and then an integer to see how many records. It is now confirmed to be working as intended, and I was even able to remove the MoveLast and use a MoveFirst since the recordset was already presorted backwards if that makes sense. Always let the DB do the heavy lifting for you when possible.

easleyfixed
  • 219
  • 1
  • 13