createUnoService("com.sun.star.sdb.RowSet")

cornsplicer1 · November 30, 2021, 9:26pm

The code below to access a MySQL table used to work when I first created it. Now however it bombs out to my error handler when it attempts to do the execute statement (earlier in the code there is “ON ERROR GOTO Cancelled” statement). The database connection is established earlier in the macro and it works because it executes an SQL statement for me and returns the correct result.

My suspicion is that I am failing to intialise something correctly, but I cannot see what that might be. When I stepped through the code when it was working, it executed exactly as expected, so I’m needing some pointers to debug the problem.

The intended function of the macro is to parse a text file which contains data which is then written into my database table, line by line.

If more information is required I can post the whole of the macro, but at this stage I would like to try and resolve the problem myself, with the forum’s guidance to help me through it. That way I’ll learn better!

The environment is Libre Office 7.1.7.2 running under Windows 11 (doesn’t work with W10 either). The database is MySQL 8.0 CE running as localhost on the pc.

Looking forward to your thoughts as I have not had any joy via Google searches.

Thank you.

oRS=createUnoService(“com.sun.star.sdb.RowSet”)

With
  oRS.DataSourceName=DBName
  oRS.CommandType=0 ' table 
  oRS.Command= InTable	  

  oRS.IgnoreResult=TRUE
  oRS.execute()
End With

Ratslinger · November 30, 2021, 11:28pm

Hello,

Cannot give too much direction as you have provided only sparse information. Code presented works (using my table and source) with Ubuntu 20.4 & LO v7.2.3.2 & JDBC & MySQL v8.x. However your result is always empty. Here are some issues:

Don’t know what connector you are using - JDBC, ODBC, SDBC
What error are you actually getting - with getting a records result my error is Cursor before first or after last record if IgnoreResult is True
On what line of code is the error occurring
Why is oRS.IgnoreResult=TRUE present
You stated code used to work. What changed? Different LO version? Connector?

cornsplicer1 · December 1, 2021, 1:12am

Thank you Ratslinger for a very quick response.

I am using the ODBC connector.
The program jumps to my error location on carrying out the excute() command in the “with” statements.
I confess I haven’t worked out yet how to read the error report - how do I do that?.
The oRS.IgnoreResult= TRUE is present because it was in the code snippet that I copied, and as it seemed to work I left it alone (another bit to learn I think)
Originally I was on V6 of LO, but I haven’t regressed to see if it starts working again, and I hadn’t made that association until you mentioned it.

Leave it with me to ponder for a day or two, and I will report back then.

Many thanks again.

Ratslinger · December 1, 2021, 1:23am

Comment out the On Error code and run. It will stop on the problem line. Can also add line in error location to produce information. See → Error Handling
.
As for IgnoreResult, see → IgnoreResult
.
Will now try test with ODBC.
.
.
OK, tested same as JDBC.

RobertG · December 1, 2021, 7:10am

Where is defined InTable? This should be a string, the table name.

cornsplicer1 · December 1, 2021, 6:00pm

The line:

InTable = “movedb.import_summaryscores” ’ Name of table to import into

occurs earlier on in the macro.

cornsplicer1 · December 1, 2021, 6:45pm

Error 1:
Type: com.sun.star.sdbc.SQLException
Message: Access denied for user ‘Phil Roberts’@‘localhost’ (using password: NO) (line : 285)

Thank you that is very helpful. I inserted MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & “)” into the error routine. Looks like MySQL is refusing the connection, so I have been looking for the problem in the wrong place. Something must have changed in the MySQL set up.

I’ll do some more investigation and will update this thread when I find out what’s wrong…

cornsplicer1 · December 2, 2021, 4:36pm

I have concluded that the problem lies in MySQL and/or its ODBC connector. When I shut down the localhost server and then restarted it, my macro began working properly again. At present, after several restarts of my PC it is working perfectly. Therefore the problem isn’t in Libre Office (unless there is a really weird bug in its ODBC driver).

I need more information on the failure circumstances before knowing exactly what is wrong and how to make it totally reliable. Therefore I need it to fail again to make more progress. I will read up on ODBC connectors in MySQL and see where that leads me.

I think my LO query can now be classified as solved. Many thanks again to Ratslinger for the helpful hints.

Ratslinger · December 2, 2021, 7:33pm

Just an FYI, ODBC driver is from MySQL. LibreOffice does not provide ODBC or JDBC drivers for databases but may provide an SDBC (direct) driver.

May want to see if MySQL has a newer driver than what you are using…

cornsplicer1 · December 8, 2021, 5:45pm

It stopped working again for no observable reason.

In the end after reinstalling the MySQL CE, recreating the user, and carefully checking the start up configuration, I didn’t manage to solve the access denied error.

So I rewrote the macro to avoid using RowSet, instead constructing an SQL insert query to write into the MySQL table. After judicious use of & “’” “,” “(”, “)” it all worked with no bother at all (apart from having to have a ON ERROR RESUME NEXT telling the macro to ignore the SQL warning error about no result set being returned - not a surprise when doing an INSERT).

I did not want to go down the road of granting unrestricted privileges to all users or using the original MySQL native authentication, so I am giving up on that approach. Life’s too short to waste it on the more arcane properties of MySQL!

Thank you for your help with my problem. It drove me to successfully overcome it, and I am grateful for that.