[Solved] Limiting a SQL Search

Discuss the database features

[Solved] Limiting a SQL Search

Postby Nocton » Tue Oct 04, 2011 4:34 pm

I wish to do have an SQL statement (to go in a form) which limits the number of records shown to 1 to 10, 11 to 20 and so on. None of the LIMIT and TOP commands that I have used in other applications work, even though one post I found http://www.oooforum.org/forum/viewtopic.phtml?t=64460 seemed to suggest that they do work.

Does anyone have a solution?

Regards, Nocton
Last edited by Nocton on Wed Oct 05, 2011 6:35 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 497
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Limiting a SQL Search

Postby probe1 » Tue Oct 04, 2011 6:16 pm

This WFM (OOo 3.2.1/openSuse, embedded HSQLDB, SQL direct enabled)

For first 10 records, saved as qry_Limit_step_1
Code: Select all   Expand viewCollapse view
SELECT "Name" FROM "tab_kunden" ORDER BY "id" LIMIT 10


record 11-20 qry_Limit_step_2
Code: Select all   Expand viewCollapse view
SELECT "Name" FROM "tab_kunden" ORDER BY "id" LIMIT 10 OFFSET 10


record 21-30 qry_Limit_step_3
Code: Select all   Expand viewCollapse view
SELECT "Name" FROM "tab_kunden" ORDER BY "id" LIMIT 10 OFFSET 20


The ORDER BY clause is essential


To switch the queries I use a control element (Pushbutton) and change the form command

Code: Select all   Expand viewCollapse view
Sub frm_limit_step( oEvent )
oEvent.Source.Model.Parent.Command = "qry_Limit_step_2"
oEvent.Source.Model.Parent.reload()
End Sub


You can remember the query name in a global variable, or use a counter

Code: Select all   Expand viewCollapse view
limit_step = limit_step + 1
oEvent.Source.Model.Parent.Command = "qry_Limit_step_" & limit_step


(both initialize on form open), or use a numeric field (spin button) to change the interval value...

Or use
Code: Select all   Expand viewCollapse view
oEvent.Source.Model.Parent.CommandType = 3
and create the query SQL on the fly (putting the SQL in .COMMAND again)


Does this help?
Cheers
Winfried

DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
Volunteer
 
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: Limiting a SQL Search

Postby Nocton » Tue Oct 04, 2011 7:20 pm

Thank you Winfried. However, I always get an error when I run the SQL command placing it in the Content section of my subform.

My syntax is:
Code: Select all   Expand viewCollapse view
SELECT DISTINCT "EmailAddress" FROM "MemberDetails" AS "MemberDetails" WHERE "EmailAddress" IS NOT NULL ORDER BY "EmailAddress" ASC LIMIT 10


If I run it in Tools/SQL to execute it directly, it says it executes successfully. But it will not run in the form, nor can I save it in a query, because it always says there is a syntax error.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 497
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Limiting a SQL Search

Postby probe1 » Tue Oct 04, 2011 7:56 pm

Which database you use (see statusbar of ODB)

I can save your query here... (HSQLDB).
Once saved you can use it for a form...

You checked/ activated the Run SQL command directly icon in the toolbar?
Cheers
Winfried

DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
Volunteer
 
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: Limiting a SQL Search

Postby Nocton » Wed Oct 05, 2011 12:15 pm

Maybe the problem is because I am using 'file server' mode (see the Quick Fix http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523.
As I could not wait for a resolution of this problem, which appears to depend on which version of SQL one is using, I have got round the problem by using Basic code to limit the number of records. But it would be nice to know how it can be done in the SQL that I am using.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 497
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Limiting a SQL Search

Postby Sliderule » Wed Oct 05, 2011 5:59 pm

Norton:

The problem you are describing is NOT an HSQL ( database engine ) issue, but rather, an OpenOffice Base Parser issue.

You see, the OpenOffice Base Parser will first check the SQL before sending it to the database engine. However, the OpenOffice Base parser does NOT recognise ( accept ) use of LIMIT or TOP in an SQL statement. :cry:

Therefore, if you write the Query, and, save it to Run Directly ( so the OpenOffice Base Parser is NOT first exeucted ) . . . it will work. The job of for the OpenOffice Base Parser, is to 'convert' dates, to ISO standard ( YYYY-MM-DD ) if entered in local format ( same for time values ), AND, to allow a Parameter Query, to allow a user to pass values to the SQL ( accomplished with a colon ), for example:

Where "MyField" = :Enter_Search_Text

Bottom line, the valid SQL will work, but, you have to implement it so it is passed directly to the database engine, rather than allowing the OpenOffice Base Parser to first check it.

I hope this helps, and, is as clear as mud. Please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1167
Joined: Thu Nov 29, 2007 9:46 am

Re: Limiting a SQL Search

Postby probe1 » Wed Oct 05, 2011 6:09 pm

probe1 wrote:[Tue Oct 04, 2011 11:16 pm]SQL direct enabled


probe1 wrote:[Wed Oct 05, 2011 12:56 am]You checked/ activated the Run SQL command directly icon in the toolbar?


That's what I wrote two times before
Cheers
Winfried

DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
Volunteer
 
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: Limiting a SQL Search

Postby Nocton » Wed Oct 05, 2011 6:35 pm

Thank you sliderule for explaining the situation very clearly. I have tried it out and it works as you say. Winfried, you also said what to do, and I thought I had run the SQL directly. However, I had started with my SQL statement directly embedded in the form, without a saved query and clearly got confused with the 'Analyze SQL command' which does the same job I think.

So I've got there in the end!

Regards

Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 497
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Limiting a SQL Search

Postby DatFrame » Tue Mar 28, 2017 7:03 pm

I know this post is very old, but I just wanted to post an alternative solution I found:

I wanted to limit the query results as well, however I wanted to use other base functions that required "Run SQL command directly" to be OFF (for example, you cannot use form filters when running SQL directly). I found a property of the row set called "MaxRows" that allows you to limit the number of rows returned without using SQL, see http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/RowSet.html#MaxRows.

Here is a little code sample for how to limit the records returned by a Base form:
Code: Select all   Expand viewCollapse view
Dim oForm as Object
Set oForm = thisComponent.DrawPage.Forms.getByName("MyForm")

REM The max rows property will limit the number of rows you can scroll to
oForm.setPropertyValue("MaxRows",30)

REM If you want all of these rows to be fetched at once, change the FetchSize as well:
oForm.setPropertyValue("FetchSize",30)

REM You will need to reload the form in order for these properties to take effect
oForm.reload()

Set oForm = Nothing


I did have some issues with the properties being set, as sometimes oForm.reload() did the job, and sometimes it seemed that the form did not recognize it's new MaxRows limitation.

Hope this helps anyone out there like me!
LibreOffice 5.2 on Windows 10
DatFrame
 
Posts: 10
Joined: Mon Feb 20, 2017 10:39 pm


Return to Base

Who is online

Users browsing this forum: Google [Bot] and 4 guests