Ask Your Question
0

How to display SQL query result

asked 2017-05-10 20:15:33 +0200

BobCrandell gravatar image

updated 2017-05-10 20:26:02 +0200

Ratslinger gravatar image

I have a odb file with 2 records in it with the value of 2000000 in PON and 1 in ID that I'm trying to read with a macro in Calc 5.2.5.1. No matter what advice I follow from Google all I get is: BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: No data is available.

I get the correct result when I use the query in Base: SELECT PON FROM Data WHERE ID = 1;

PON is updated if I run the macro in Calc UPDATE ""Data"" SET PON = 268680 WHERE ID = 1;

I know I'm talking to the odb file.

What do I need to change to make this macro work?

Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
Db = Context.getByName(DATABASE_NAME)
Conn = Db.getConnection("","") 
strSQL = "SELECT PON FROM ""Data"" WHERE ID = 1;"
Stmt = Conn.createStatement()
Result = Stmt.executeQuery(strSQL)
If Not IsNull(Result) Then
    MsgBox Result.getLong(1)
End If

My goal is to read the record, increment it by one and save it.

Thanks and I hope I've included everything you need. Bob

edit retag flag offensive close merge delete

Comments

Only edited question to better format code.

Seems this was brought up before as a bug but right now I can't find it. I can reproduce your problem. In addition, if an incorrect field or table name is used, an error is returned stating so. This means it is communicating but there is no result set being returned when correct statement is used.

Will update if anything further discovered.

Ratslinger gravatar imageRatslinger ( 2017-05-10 21:47:14 +0200 )edit
1

haven't tried this, but maybe it's the quotes.. Try:

strSQL = "SELECT ""PON"" FROM ""Data"" WHERE ""ID"" = 1"

and:

If Not IsNull(Result) Then
    Result.Next()
    MsgBox Result.getLong(1)
End If
librebel gravatar imagelibrebel ( 2017-05-10 22:17:21 +0200 )edit

Using a SELECT statement verified in Base, I went straight in through MRI. executeQuery went through fine but there was no result set created. isFirst = False and isBeforeFirst = True. Got to be a bug.

Ratslinger gravatar imageRatslinger ( 2017-05-10 23:50:30 +0200 )edit

isBeforeFirst=True, just call Next()

librebel gravatar imagelibrebel ( 2017-05-11 00:22:30 +0200 )edit

DUH! @librebel Thanks. I really knew that but...

Until you said Next() I was in a daze. Couldn't see the forest thru the .. Just too much at one time.

Ratslinger gravatar imageRatslinger ( 2017-05-11 06:09:43 +0200 )edit

my pleasure @Ratslinger, i should have pointed that one out more clearly in my first comment ... i know what you mean, me also busy with your cool Charts in Base Module hehe...

librebel gravatar imagelibrebel ( 2017-05-11 06:27:03 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2017-05-11 01:02:05 +0200

Ratslinger gravatar image

updated 2017-05-11 06:08:05 +0200

Anyway trying to figure why my code works & not the posted code. It is the If statement:

If Not IsNull(Result) Then
    MsgBox Result.getLong(1)
End If

Which give me the same error from Calc. However the proper way to check if a result set was returned is:

Sub Connect2
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    DATABASE_NAME = "YOUR_DATABASE_NAME"
    Db = Context.getByName(DATABASE_NAME)
    Conn = Db.getConnection("","") 
    strSQL = "SELECT PON FROM ""Data"" WHERE ID = 1;"
    Stmt = Conn.createStatement()
REM This allows forward & back scrolling & the necessary checking of 'isBeforeFirst'
    Stmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
    Result = Stmt.executeQuery(strSQL)
REM Check if record set returned
    bCursorTest = Result.isBeforeFirst
REM Exit if record set is empty
    If bCursorTest = "False" Then
        MsgBox "No Record Found for Value Entered"
        Exit Sub
     End If
     Result.next
     MsgBox Result.getString(1)
End Sub

And yes I recognize, and agree with all, Result.next added to original question works.

edit flag offensive delete link more

Comments

1

@BobCrandell You may also be interested to know, with this process you can easily move around the result set. For example 'result.last' followed by 'result.getRow' will tell you how many records were returned in the set. For more information and commands see this link and surrounding pages related to it.

Ratslinger gravatar imageRatslinger ( 2017-05-11 17:57:47 +0200 )edit

Thanks. This answer helped me delve deeper into understanding how to work with query results. Weird that .first .previous, and .last aren't enabled in the default mode. Obscure! The row count thing is great, just what I needed.

PhLo gravatar imagePhLo ( 2018-11-11 03:12:48 +0200 )edit
0

answered 2017-05-11 00:16:11 +0200

EasyTrieve gravatar image

updated 2017-05-11 00:16:39 +0200

1) Make sure your database is registered correctly.
2) Make sure to quote your identifiers as I have done below. (There are other ways to do this, but """" gets messy.)
3) Make sure PON is an Integer or numeric field or something that works.
4) First build the SQL code and test run it in SQL... till it works.
5) Then paste this SQL into this below, and then into the Basic editor and hit F5 to run it.
Use MRI to inspect any objects or values you're unsure about.
This code below was tested to work. Oh, you will have to rename the database in getByName(..).

Sub Test
    oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    oDb = oContext.getByName("foobar")

    oConn = oDb.getConnection("","") 

    strSQL = "SELECT [PON] from [Products] WHERE [ID] = 1;"

    Stmt = oConn.createStatement()
    Result = Stmt.executeQuery(strSQL)

    If Not IsNull(Result) Then
        Result.next
        MsgBox Result.getLong(1)
    End If
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-10 20:15:33 +0200

Seen: 1,259 times

Last updated: May 11 '17