Ask Your Question
0

BASE Macro SQL error when record not found

asked 2018-11-27 23:26:58 +0200

Carrbigdog gravatar image

I am attempting to write a macro that uses SQL to query a table and need to determine if there is a "Not found" result. I copied the routine at https://ask.libreoffice.org/en/questi... but am getting a run time error.

oContext=CreateUnoService("com.sun.star.sdb.DatabaseContext")   'One service for all SQL processes
oDB1=oContext.getByName("DogDBTest1")   'get the database
oConnection1=oDB1.getConnection("","")      'establish connection to database
oConnection1.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE   'added to allow for NRF

oResult1 = oStatement1.executeQuery(sReadReg & sRegistryID)

CursorTest = oResult1.first
If CursorTest = "False" Then
    MsgBox (sRegistryID & " Not Found"
Else    
    While oResult1.next()
       MsgBox (oResult1.getString(1) & "=String(1) " & oResult1.getString(2) & "=String(2) " _
       & oResult1.getString(5) & "=String(5)"
    wEnd

yada....... Run time error says "Property or Method not found: ResultSetType

Can anyone see errors in code or point me to alternate solution to determine if no record was found I also attempted to use ifNull solution but that didn't work either

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-11-28 00:03:14 +0200

Ratslinger gravatar image

updated 2018-11-28 01:10:27 +0200

Hello,

You don't mention at which line of code the error occurred but there are a couple of items with problems.

You create a connection but not the necessary statement for SQL:

oStatement1 = oConnection1.createStatement()

Then oStatement1 here is valid (error prior to above):

oResult1 = oStatement1.executeQuery(sReadReg & sRegistryID)

This is incorrect:

oConnection1.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE

ResultSetType is not in oConnection1. It is in oStatement1

Don't see how sReadReg is defined so may potentially be a problem.

MsgBox (sRegistryID & " Not Found"

has a begin parenthesis - should not be there.

With:

While oResult1.next()

you have skipped the first record. Logic problem.

There may be more but you need to look closer at what code you have and what is being copied. You should know what the individual statements do and not just insert a routine which may work elsewhere. Your "copied" code is quite different from the code posted in the link. I use similar code to what was copied in many routines and it works.

edit flag offensive delete link more

Comments

@Ratslinger, it has been my experience that result sets start with the cursor isBeforeFirst by default, akin to an index of -1, so .next() will not skip the first record, but rather it will start reading from the first record, as long as the cursor position hasn't been "messed with" before the first .next() in the loop is called.

PhLo gravatar imagePhLo ( 2018-11-28 03:19:05 +0200 )edit

CursorTest = oResult1.first gets the first record. Nothing is done with it. While oResult1.next() starts processing at record two which can actually be yet another problem if there is only one record in the result set.

Ratslinger gravatar imageRatslinger ( 2018-11-28 03:30:44 +0200 )edit

Gotcha. Yeah, when I do a similar test, I test like this: bCursorTest = oResult.isBeforeFirst : If bCursorTest = False Then... which doesn't advance the cursor. So then .next() simply starts off at the first record.

PhLo gravatar imagePhLo ( 2018-11-28 07:48:36 +0200 )edit

Yes, it is understood how it should have been done but that is not what is in the code in the OP therefore just another problem with the code posted.

Ratslinger gravatar imageRatslinger ( 2018-11-28 18:44:01 +0200 )edit

Thanks for all the replies. Maybe someday I will be knowledgeable enough to offer some Newbie help on Base.

Carrbigdog gravatar imageCarrbigdog ( 2018-11-28 22:11:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-27 23:26:58 +0200

Seen: 32 times

Last updated: Nov 28 '18