How do you run an Sql query in a macro using an HSQLDB embedded database?

[DBNoob]

Hi,
I’ve been trying to run a simple query using a macro and the database I am using in the default embedded HSQLDB. I’m not sure what the issue is but every time I execute the sql I get a “no data” error.

The customers table has the following fields: Customer_ID and Customer_Name. I’d like to have a query run when the Customer Name list box is changed and make the Customer_ID field populate with the matching ID. The query works fine outside of the macro.

Here is my macro code:

Sub CustNameFilterNewWO

oForm = ThisComponent.Drawpage.Forms.getByName("Filters") 'Get Form
oSubForm1 = oForm.getByName("Customer")
oField = oForm.getByName("Cust Name box")
sSelectedValue = oField.getCurrentValue()
oConn = ThisDatabaseDocument.DataSource.getConnection("","") 
SQL = "SELECT ""Customer_ID"" FROM ""Customers"" WHERE ""Customer_Name"" = '" & sSelectedValue &"'"
oQuery = oConn.createStatement()
oResult = oQuery.executeQuery(SQL)
oField = oForm.getByName("Cust ID box")
oField.text = oResult.getString(1)
oField.refresh()

End Sub

[Ratslinger]

Hello,

There are a couple of items. This is based upon you using stated HSQLDB as other databases may operate differently (such as Firebird).

After the executeQuery statement you need to get a record using oResult.next

However, you should first verify if there were any records actually returned in the record set. You can do this by setting ResultSetType and checking the return set. For a sample see my answer in this post → How to display SQL query result

[DBNoob]

Thank you so much! Adding oResult.next fixed the issue!