Empty Result Set

[agerber85]

I’m getting an error message that I’m trying to pull a value from an Empty result set. The funny thing is, I run the SQL query somewhere else, and the results aren’t empty. (By “somewhere else”, I mean the Query section of Base. I create it by copying and pasting the SQL statement in my macro into a new Query in SQL mode.) Is there a good way to troubleshoot this?

In the macro, I put the SQL Query into a string,

sSQL = "SELECT MEASUREMENTDATA.ID, MEASUREMENTDATA.ACCEPTED FROM MEASUREMENTDATA JOIN FILTERITEMSINWORKORDERS4 ON MEASUREMENTDATA.ITEMINWORKORDER = FILTERITEMSINWORKORDERS4.ITEMINWORKORDER"

and then

oSQLStatement = oDB.createStatement
mQueryResult = oSQLStatement.executeQuery(sSQL)
mQueryResult.first
iDataSet =mQueryResult.getInt(1)

It’s that last line that yields the Empty Result error.

By “somewhere else”, I mean the Query section of Base. I create it by copying and pasting the SQL statement in my macro into a new Query in SQL mode.

Forgot to mention, it’s LO 5.3.6.1 on Win 10, Connecting to a MySQL DB for the backend.

[Ratslinger]

Hello,

Please, when posting, include specific LO version, OS and for Base the database you are using. With errors, the specific error. Also what is the SQL used? Where are you running from and where is this somewhere else? Can’t see what you are looking at from here!

Please edit question and place this data there or in a comment. Do not use an answer.

[agerber85]

Thanks for responding! I’ve edited the question to make it a more complete picture.

[agerber85]

I added a check for whether the results object is empty. But this isNull(oResultSet) funktioniert nicht - OpenOffice.info seems to indicate that sometimes the result set will not be null, but will be an empty data set object. I tried using the methods Toxitom recommended. I’ve found that .isBeforeFirst() and .isAfterLast() are not both true. Should these both be true if the set is empty? What if I’ve already called the result set object’s .first method?

[Ratslinger]

Do not see specific LO version, OS or database used posted but asked for.

[agerber85]

Sorry, forgot to add that. It’s LO 5.3.6.1 on Win 10, Connecting to MySQL.

[Ratslinger]

Hello,

You seem not to understand the importance of detailed information. Just spent time figuring out what DB you are using (MySQL from what I gathered). Then abstract of tables you may have. Then a guess at the connector you may be using. After all that I find no problem. Tested with Native and JDBC (mysql-connector-java-8.0.12) connectors. Result:

Only minor mod to macro to get connection. Also added print statement to display result.

[agerber85]

Sorry, you’re right, I think I have under-appreciated the importance of giving you enough details. Actually, the connector I’m using is 5.1.44.

[Ratslinger]

Hello,

Guessing you are using MySQL v5.something. My test was with v8.0.25. LO is v7.1.4.2 on Ubuntu 20.04

Although I have Win 10, I am not going to load out all the older items you are using. As you can see what you presented in the question does work with more current software. Suggest you upgrade LO, MySQL and connector.

[agerber85]

Thanks, you’re correct. We have MySQL 5.7.19 installed, but we are needing to migrate to a newer server. (The old server’s version of Windows was too outdated to keep using, for security reasons.) I intend to get LO and the connector updated, and I’m working on getting the latest connector downloaded. Unfortunately, for some reason, Oracle finds it necessary to keep the download of the connector behind the login wall, and for some reason, I can’t recall the password. (I’ve had a brain tumor removed since the last time I had to log in.) And now, their server is not sending me a password reset email. I’ve checked my spam folder, but it wasn’t there. Any ideas on how to get the .jar file? Edit: I’ve set up a new account with Oracle using another email address.