handle runtime error sql macro

jvglynnjr · August 22, 2016, 7:16pm

problem: In a macro that executes an SQL query, when the user-inputted key value is not found, there is a run-time error. How can I have the macro exit the subroutine when this occurs rather than trigger a run-time error? (which opens a debugging screen, confusing the user?) i.e. I want there to be a messagebox to warn the user to retry input whenever a value that is not already in the table is entered.

Ratslinger · August 22, 2016, 8:50pm

You must set SCROLL_SENSITIVE:

    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
     Thisdatabasedocument.CurrentController.connect
  endif
     oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
  'Setting is here'
  oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE         
  sSQL ="SELECT * FROM ""TestTable1"" WHERE ""Cust"" IS NULL"
  result = oStatement.executeQuery(sSQL)
  'CHEK IF ANY RECORDS RETURNED'
  CursorTest = result.first
  If CursorTest = "False" Then
  	MsgBox "No Records"
  	Exit Sub
  End If
do UNTIL result.isAfterLast = TRUE
	sMyData = result.getInt(1) 
	result.next
	CustomerNumber = CustomerNumber + 1
 loop
result.last
MsgBox "Number of Records = " & result.getRow()

Edit 08/23/16

The above shows a full routine to get active connection, setting the result scroll type, checking to see if there are any results and then processing results.

Here are only the statements for what you wanted:

 'Setting is here'
 YOURCONNECTION.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE         

 'CHEK IF ANY RECORDS RETURNED -  "result" is return from your query'
 Dim CursorTest AS Boolean
 CursorTest = result.first
'Test for zero records returned.
If CursorTest = "False" Then
     MsgBox "No Records"
     Exit Sub
 End If

jvglynnjr · August 23, 2016, 12:21pm

Not clear at all what this is doing. Why would I need that loop? Also, not looking for a null value in the table, just a select criterion that doesn’t find a record.

Ardee · April 7, 2020, 11:28pm

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

I tried using above code but get error BASIC runtime error. Object variable not set on the line:

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

@Ratslinger, Is it because of a different environment?
Thank you

Ratslinger · April 8, 2020, 6:41pm

@Ardee,

Environment should not be a factor.

Just entered the same code in an HSQLSB split on Ubuntu 18.04 & LO v6.4.2.2 and it worked without a problem. If you continue to have a problem, ask as a new question with full code used.

Ardee · April 8, 2020, 9:39pm

@Ratslinger thanks for your reply, will post as new question