OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

SQL SELECT using LIKE statement

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
mattnotfat
Power User
Power User


Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK

PostPosted: Mon Oct 30, 2006 9:51 am    Post subject: SQL SELECT using LIKE statement Reply with quote

Hello Braniacs,

Can anyone help me out with a valid SQL statement using LIKE. In the code below 'DeptCode' is a six character code, 'strCode' is the first four charcters - but I don't get any results. The data source is a spreadsheet.
Code:
      oStatement = oConnectionEstablishment.createStatement()   
      strStatement = "SELECT * FROM " & CHR$(34) & "Sheet1" & CHR$(34) & " WHERE " & CHR$(34) & "DeptCode" & CHR$(34) & " LIKE " & CHR$(39) & strCode & CHR$(39)
       oRSEstablishment = oStatement.executeQuery(strStatement)      


Many thanks
_________________
The early bird gets the worm, but the second mouse gets the cheese!
Back to top
View user's profile Send private message Visit poster's website
DiGro
Super User
Super User


Joined: 02 Jun 2004
Posts: 1415
Location: Hoorn NH, The Netherlands

PostPosted: Mon Oct 30, 2006 9:56 am    Post subject: Re: SQL SELECT using LIKE statement Reply with quote

mattnotfat wrote:
strStatement = "SELECT * FROM " & CHR$(34)


Well, I'm not the best in SQL but don't I miss something behind FROM ?

There is only one quotation
_________________
DiGro

Windows 7 Home Premium and AOO 4.0.1 NL (Dutch)
Back to top
View user's profile Send private message
mattnotfat
Power User
Power User


Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK

PostPosted: Mon Oct 30, 2006 10:16 am    Post subject: Reply with quote

Well, I think my statement is valid - it executes no problem.

I reckon there must be something wrong with the LIKE part as it's giving me no results?
_________________
The early bird gets the worm, but the second mouse gets the cheese!
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Oct 30, 2006 10:38 am    Post subject: Reply with quote

mattnofat:

With HSQLDB, your LIKE statement ( what you are looking for ) needs to be 'surrounded' with % on each side.

To quote from HSQL documentation http://www.hsqldb.org/doc/guide/ch09.html :
HSQL documentation wrote:
The LIKE keyword uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' or '_' itself an escape character must also be specified using the ESCAPE clause. For example, if the backslash is the escaping character, '\%' and '\_' can be used to find the '%' and '_' characters themselves. For example, SELECT .... LIKE '\_%' ESCAPE '\' will find the strings beginning with an underscore.


This is an example of an SQL statement, using LIKE to find any e in the LastName:
Code:
SELECT
   "FirstName",
   "LastName"
FROM "Test" AS "Test"
WHERE "LastName" LIKE '%e%'


Please also note, CHR$(37) will provide the % you need to add.

I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
mattnotfat
Power User
Power User


Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK

PostPosted: Wed Nov 01, 2006 4:36 am    Post subject: Ah, so that's what it was! Reply with quote

Thanks Sliderule

The problem wasn't so much the syntax for the like statement, it was the way I was checking for the number of results.

After getting the results I did this
Code:
      oRSEstablishment.first()
      if not oRSEstablishment.isLast() then   
         do
            oRSEstablishment.next()
            count = count + 1
         loop until oRSEstablishment.isLast()
      End if
      
      msgBox "There are : " & count & " results"

And got no results, wheras if I do this:
Code:
      oRSEstablishment.last()
      msgBox "Establishment row is: " & oRSEstablishment.Row()   
      
I get the right number of results. I wrote the code the first way because that's how I plan to iterate through the results - so it remains to be seen if I'm going to have problems making use of the result set.

Will report back soon...
_________________
The early bird gets the worm, but the second mouse gets the cheese!
Back to top
View user's profile Send private message Visit poster's website
davelegge
OOo Enthusiast
OOo Enthusiast


Joined: 30 Nov 2005
Posts: 112

PostPosted: Wed Nov 01, 2006 1:57 pm    Post subject: Re: Ah, so that's what it was! Reply with quote

mattnotfat wrote:
....<snip>....wheras if I do this:I get the right number of results. I wrote the code the first way because that's how I plan to iterate through the results - so it remains to be seen if I'm going to have problems making use of the result set.

Will report back soon...


So why not use
Code:
      oRSEstablishment.last()
      RSEcount =  oRSEstablishment.Row()   
      For i = 1 to RSEcount . . . .


In your first code clip
Code:
      oRSEstablishment.first()
      if not oRSEstablishment.isLast() then   
         do
            oRSEstablishment.next()
            count = count + 1
         loop until oRSEstablishment.isLast()
      End if

the do loop wil never get executed if there is only one row,
since the first row is also the last row!
if you do not like the for loop try something like
Code:
oRSEstablishment.beforefirst()
do while not oRSEstablishment.isLast()
      count = count + 1
     oRSEstablishment.next()
loop

_________________
Dave Legge
==========
Computers allow me to make mistakes
far easier and quicker
than I would otherwise make them
Back to top
View user's profile Send private message
mattnotfat
Power User
Power User


Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK

PostPosted: Mon Nov 06, 2006 4:51 am    Post subject: Reply with quote

Thanks Dave,

I did use a For loop in the end. I don't have anything against For loops - and maybe it's just lack of sleep fogging my brain, but don't get why my first version wasn't working at all. I had 178 results in the ResultSet yet doing[code]ResultSet.first() then followed it with [code]ResultSet.next()[/code] and the call to [code]the call to ResultSet.last()[/code] returned True.

Is is my logic or the ResultSet object that is a bit strange?
_________________
The early bird gets the worm, but the second mouse gets the cheese!
Back to top
View user's profile Send private message Visit poster's website
davelegge
OOo Enthusiast
OOo Enthusiast


Joined: 30 Nov 2005
Posts: 112

PostPosted: Mon Nov 06, 2006 9:13 am    Post subject: Reply with quote

mattnotfat wrote:
Thanks Dave,

....

Is is my logic or the ResultSet object that is a bit strange?


Probably Wink
_________________
Dave Legge
==========
Computers allow me to make mistakes
far easier and quicker
than I would otherwise make them
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group