Run Time Error for Record not found in Macro SQL

[Ardee]

My environment: Windows 10, LibreOffice Version: 6.4.2.2 (x64) HSQLDB Split Database

I found some code in this forum to handle record not found, but it does not work for me.
The lines I added specifically for record-not-found, are commented in the code below

The code below works fine without the commented lines (as long as a record is found).

Since adding the new lines (the commented lines) I get

BASIC runtime error. Object variable not set

on this line

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

How to fix the code for record not found?
Thank you for assistance

Sub LookUpSql (sSeekField,sSource,sWhereItem,sCompareItem,Optional sWhereItemTwo,Optional sCompareItemTwo) As String
    Dim oStatement As Object
    Dim sQuery As Object
    Dim sSqlTask As String
    Dim sLookup As String
		If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) Then
		ThisDatabaseDocument.CurrentController.connect
		End If
		oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
	oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE   '-----new line for error test
	    If IsMissing(sWhereItemTwo) Then	  
	    	If sCompareItem = "" Then
	    	MsgBox("Error: No LookUp value for field " & sWhereItem)
	    	Exit Sub
	    	End If
	    sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """  = '" & sCompareItem & "'"
	    Else
	    	If sCompareItemTwo = "" Then
	    	MsgBox("Error: No LookUp value for field " & sWhereItemTwo)
	    	Exit Sub
	    	End If
	    sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """  = '" & sCompareItem & "' AND """ & sWhereItemTwo & """  = '" & sCompareItemTwo & "'"
	    End If
	    sQuery = oStatement.ExecuteQuery(sSqlTask)
	Dim CursorTest AS Boolean      '-----new line for error test
	CursorTest = sQuery.first        '-----new line for error test
	If CursorTest = "False" Then       '-----new line for error test
	MsgBox("Record not found= " & sSeekfield)      '-----new line for error test
	End If                  '-----new line for error test 
	    sQuery.Next()  
	    sLookup = sQuery.GetString(1)
	    PutToField("MacroResponse",sLookup,"Text")
End Sub

[Ratslinger]

Hello,

What version of HSQLDB are you running?

I run this of your code without error on the stated line (oStatement.ResultSetType =):

Sub LookUpSql
    Dim oStatement As Object
    Dim sQuery As Object
    Dim sSqlTask As String
    Dim sLookup As String
        If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) Then
        ThisDatabaseDocument.CurrentController.connect
        End If
        oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
    oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
End sub

Also see problems in your logic:

Your get the first record

CursorTest = sQuery.first '-----new line for error test

Check if there was result

If CursorTest = “False” Then '-----new line for error test

Here you now have an empty result set

MsgBox("Record not found= " & sSeekfield) '-----new line for error test
End If '-----new line for error test

But even if result set is empty you continue processing

sQuery.Next()

[Ardee]

@Ratslinger thank you for your help.
I ran the your code, and yes no error on my side either, don’t understand now how come.
Regarding your comments on logic, the commented lines were introduced exactly as found elsewhere, since that included referencing a first record, indeed the next() in the original code becomes superfluous, I left everything in place to illustrate exactly what was added.
Will run more tests because I gather it should work.

[Ratslinger]

@Ardee,

Not certain as to where you found this exactly as found elsewhere code since it is not from where you posted the comment to me. There the code was very different and included, for one item, Exit Sub in the IF condition → handle runtime error sql macro - #4 by Ardee

[Ardee]

Where/how can I see what version of HSQLDB I am running?

[Ardee]

True I missed the Exit Sub

[Ratslinger]

You should know what you installed. This is a choice you have made. Here is a macro to display the HSQLDB in use:

Sub hsqlVersion
	Dim databaseURLOrRegisteredName As String
REM adjust this string to your needs. It needs to be the name of a registered database,
REM or a complete URL
	databaseURLOrRegisteredName = "YOUR_REGISTERED_DB"
	Dim databaseContext As Object
	databaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
	Dim databaseDocument As Object
	databaseDocument = databaseContext.getByName( databaseURLOrRegisteredName )
	Dim connection As Object
	connection = databaseDocument.getConnection( "", "" )
	MsgBox "product version: " & connection.getMetaData().getDatabaseProductVersion()
	connection.close
End Sub

You also still missed another logic problem - sQuery.Next() is acually looking for the second record in the result set. This processing was also different in the code presented.

[Ardee]

It is working after all, I’m very sorry to waste your time.
Working on another problem this routine was involved as a sideline and so many versions and changes have disorganised my various test versions including pasting here a wrong test version,
I apologize.
Thank you very much for your reply and effort

[Ardee]

I downloaded LO just over a month ago, there was no particular choice concerning what embedded database version was involved. I later moved to a split database with the most recent suggested way of doing that which involved a download of a hsqldb.jar available on the download site.
Thank you for the code to see version, good to have, version will no doubt become a consideration as I get a better grip on things with LO. Thank you.

[Ratslinger]

The point here is to know what you installed. It matters not if you have a choice or otherwise. If you do not know what database you are using, then how do you know what manual to look at when you have a question?

As you may have seen on this site, which DB or LO version (even OS at times) being used can affect the answer. It is important.

[Ardee]

Point taken, thank you

[Ardee]

The question was a bit messy code with some leftovers when I removed the “Do While” statements since I was seeking just a single record.

Here is a cleaned up, and simplified, working code to look up a single field in a single record, the most frequent requirement within a macro.

Sub LookUpSql (sSeekField,sSource,sWhereItem,sCompareItem,Optional sWhereItemTwo,Optional sCompareItemTwo) As String
    Dim oStatement As Object
    Dim sQuery As Object
    Dim sSqlTask As String
    Dim sLookup As String
   		If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) Then
		ThisDatabaseDocument.CurrentController.connect
		End If
		oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
		If IsMissing(sWhereItemTwo) Then	  
	    	If sCompareItem = "" Then
	    		MsgBox("Error: No LookUp value for field " & sWhereItem)
	    	Exit Sub
	    	End If
	   	sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """  = '" & sCompareItem & "'"
	    Else
	    	If sCompareItemTwo = "" Then
	    	MsgBox("Error: No LookUp value for field " & sWhereItemTwo)
	    	Exit Sub
	    	End If
   	    sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """  = '" & sCompareItem & "' AND """ & sWhereItemTwo & """  = '" & sCompareItemTwo & "'"
	    End If
	    sQuery = oStatement.ExecuteQuery(sSqlTask)
		sQuery.next
		On Error GoTo ErrorSituation
		sLookup = sQuery.GetString(1)
	    GlobalMacroResult = sLookup            '----------store result in a Global variable
	    Exit Sub
	    ErrorSituation:
	    GlobalMacroResult = ""                      '----------store result in a Global variable
	    MsgBox("Record not found= " & sCompareItem)
	    
End Sub