Ask Your Question
1

getString, getInt etc returning no data

asked 2020-11-16 10:54:48 +0200

Joffan gravatar image

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

edit retag flag offensive close merge delete

Comments

1

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 gravatar imageJohnSUN ( 2020-11-16 17:04:00 +0200 )edit
1

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

Joffan gravatar imageJoffan ( 2020-11-17 04:09:14 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-11-17 10:35:24 +0200

Joffan gravatar image

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.

edit flag offensive delete link more

Comments

1

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.

JohnSUN gravatar imageJohnSUN ( 2020-11-17 11:06:27 +0200 )edit

Good advice thanks!

Joffan gravatar imageJoffan ( 2020-11-17 14:47:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-16 10:54:48 +0200

Seen: 69 times

Last updated: Nov 17 '20