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 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
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
End If                  '-----new line for error test
sQuery.Next()
sLookup = sQuery.GetString(1)
PutToField("MacroResponse",sLookup,"Text")
End Sub

edit retag close merge delete

Sort by » oldest newest most voted

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 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:

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()

more

@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.

( 2020-04-09 01:20:52 +0100 )edit

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 Subin the IF condition -> https://ask.libreoffice.org/en/questi...

( 2020-04-09 01:27:34 +0100 )edit

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

( 2020-04-09 01:29:51 +0100 )edit

True I missed the Exit Sub

( 2020-04-09 01:35:36 +0100 )edit

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.

( 2020-04-09 01:45:03 +0100 )edit

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

( 2020-04-09 01:49:58 +0100 )edit

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.

( 2020-04-09 02:02:35 +0100 )edit

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.

( 2020-04-09 02:23:36 +0100 )edit

Point taken, thank you

( 2020-04-09 13:07:36 +0100 )edit

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 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.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