getString, getInt etc returning no data

[Joffan]

Trying to create a basic database with limited, ancient knowledge, and I want to use BASIC macros and SQL commands to carry out data manipulations. So far, I’ve managed to INSERT data rows into tables, but can’t get any sensible responses for basic SELECT statements. I’ve proved there’s a connection to the database, as I can insert rows, but every time I try to run a SELECT query, and use getString() or getInt() etc. to see the returned values, I get
“BASIC runtime error. An exception occurred. Type: com.sun.star.sdbc.SQLException. Message: No data is available.”

The SELECT query is returning the Text field “TEST_TEXT” from table “TABLE_TEST”, where column “TEST_ID” is 40. The result should be “Dog”. It makes no difference if I run the Macro in the debugger, or invoke it from a form. Inspecting the contents of the return result set from the executeQuery instruction doesn’t reveal anything to me either. How come INSERT works, but SELECT doesn’t?!?! I’ve run the SQL against the DB through the Tools > SQL editor, and it all works fine. What am I missing???

Sub SQLTEST_INSERT
if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
Thisdatabasedocument.CurrentController.connect
endif
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()

'Create new row
sSQL =“INSERT INTO ““TABLE_TEST”” ( ““TEST_ID””,”“TEST_TEXT”" ) VALUES (40,‘Dog’) "
result = oStatement.executeQuery(sSQL)

'Select TEST_TEXT from the recently created row
sSQL ="SELECT ““TEST_TEXT”” from ““TABLE_TEST”” where ““TEST_ID””=40 "

result = oStatement.executeQuery(sSQL)

answer = result.getString(1)

End Sub

[JohnSUN]

Immediately after executing oStatement.executeQuery(sSQL), the pointer is set before the dataset, before the first record (beforeFirst). Just do result.next() before trying to read anything from the dataset.

[Joffan]

Ahhhhhhhhhhhhhhhhhhhh. Nice once, that explains a lot. I guess this was one of those questions that makes experts thing “read the @@#@ manual…”.

[Joffan]

JohnSun’s answer here worked perfectly:

Immediately after executing oStatement.executeQuery(sSQL), the pointer is set before the dataset, before the first record (beforeFirst). Just do result.next() before trying to read anything from the dataset.

[JohnSUN]

To be absolutely precise, you need to formulate a little differently: “if result.Next () returns TRUE (managed to move to the next record), then try to get values ​​from the fields of the record.” This will avoid errors if SELECT returned an empty dataset.

[Joffan]

Good advice thanks!