Ask Your Question
0

Run Time Error for Record not found in Macro SQL

asked 2020-04-08 23:56:00 +0100

Ardee gravatar image

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
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2020-04-09 00:50:28 +0100

Ratslinger gravatar image

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

edit flag offensive delete link more

Comments

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

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

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

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

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

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

True I missed the Exit Sub

Ardee gravatar imageArdee ( 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.

Ratslinger gravatar imageRatslinger ( 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

Ardee gravatar imageArdee ( 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.

Ardee gravatar imageArdee ( 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.

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

Point taken, thank you

Ardee gravatar imageArdee ( 2020-04-09 13:07:36 +0100 )edit
0

answered 2020-04-10 12:01:01 +0100

Ardee gravatar image

updated 2020-04-10 16:09:51 +0100

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-08 23:56:00 +0100

Seen: 87 times

Last updated: Apr 10 '20