ODBC related error message with no fault code

[nap]

[MacOS Catalina 10.15.6, Libre Calc 7.2.4.1, mysql-connector-odbc-8.0.27-macos11-x86-64bit]

I have a macro assigned to a button that the user pushes to trigger a series of calculations after they’ve pasted some data into a particular range of cells on the sheet. Other than some mundane calculations, the macro uses the results of the calculations to retrieve information from a MariaDB database using an ODBC driver that I’ve registered with LibreOffice.

The ODBC test works fine, as did my macro, for a while at least. Then, all of a sudden, the macro stopped working and has not worked since. The error, see code below, occurs when the executeQuery() method is called with a validly formatted SQL statement. The frustrating this is that the error message does not seem to tell me anything:

BASIC runtime error
'1'

Type: com.sun.star.sdbc.SQLException Message: MySQL][ODBC 8.0(w) Driver][mysqld-5.7.20]

This is the fragment that performs the DB look-up and the loop construct is pseudo-code.

       DO WHILE TargetCell != ""
		Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
		DATABASE_NAME = "myDb_odbc"
		Db = Context.getByName(DATABASE_NAME)
		Conn = Db.getConnection("user","password") 
		strSQL = "SELECT ""typeID"", ""volume"" FROM ""invTypes"" WHERE ""typeName"" LIKE '" & materialName & "';" 
		Stmt = Conn.createStatement()
		Stmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE 
		Result = Stmt.executeQuery(strSQL)
		If Not IsNull(Result) Then
			Result.Next()
			oSheet.getCellByPosition(3,row).value = Result.getDouble(Result.findColumn("volume"))
			oSheet.getCellByPosition(5,row).value = Result.getLong(Result.findColumn("typeID"))
		End If
		row = row + 1
       LOOP

I’ve tried searching for information about this error, but I’m not getting anywhere.
Does anyone know what might be causing this?

[Ratslinger]

Hello,

With MySQL:

using → Connector/ODBC 8.0.27 without issues.

Edit:

Did you change your SQL string? MySQL does not use quotes around table and field names but rather back ticks or nothing.

[Villeroy]

Dumping a record set to a dynamically linked cell range does not require any macro code. The built-in method does even copy adjacent formula cells (row calculations) and expands/shrinks references as needed (column calculation).
Add the query to your database document
Hit Ctrl+Shift+F4 for the data source window and drag the query into the sheet. This adds a database range.
Call menu:Data>Define… and pick the new import range which is named like “Import1”.
Under additional options check “Keep formatting” (which means the spreadsheet formatting) and check “Insert cells” which adjusts all references and prevents overwriting cells.
Click [Modify] and [OK]
There is also an undocumented refresh timer: [Tutorial] Using registered datasources in Calc (View topic) • Apache OpenOffice Community Forum