How to display SQL query result

English
, , ,
#1

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

How do you run an Sql query in a macro using an HSQLDB embedded database?
How to scroll through a table in base
Base: test if record exists
Experiences migrating from HSQL to Firebird in Libreoffice 6.2.5
#2

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.

#3

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
#4

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.

#5

isBeforeFirst=True, just call Next()

#6

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.

#7

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…

#12

or this other:
DatabaseContext = createUnoService(“com.sun.star.sdb.DatabaseContext”) ==
Context = CreateUnoService(“com.sun.star.sdb.DatabaseContext”)
DataSource = DatabaseContext.getByName(“GestACRA”) ’ Com ODBC porque persiste o problema do acesso directo via LibreOfice 7.5.3:
PopUp Dialog "User: ??? " / “Pwd: *******”

is the same, as far as i realise (well i’m new in Libreoffice/basic macros!)
My problem is the access is locking while connecting via Loibre+ODBC simultaneously Delphi+Ibx+firbird3.0

Connection = DataSource.ConnectWithCompletion(InteractionHandler) ’
By the way is there some InteractionHandler (!?) for popingUp the Parameter dialog for Queries with
parameter like Select *All from DBTable where Id=:wID and zipcode=:wZipcode?!

Any ideias? Can you help? Best regards

#13

@dosreis57
Not clear what you are getting at with:

Should be:

CreateUnoService("com.sun.star.sdb.DatabaseContext")

Has no bearing here. This is a connection using the registered name of the Base file. It could be connected in any manner.

Don’t know what you are asking here. Possibly Connection to odb from macro in standalone form - #5 by Ratslinger
.
There is no handler for parameters nor :myParam type parameters in a macro. Instead use an input box to get the users input and use that in the SQL string.
.
Edit:
Here are some examples using a macro to connect directly to a database using ODBC (not a registered Base file):
.
Is it possible to connect to mysql database from writer without using base? - #3 by Ratslinger
.
Connect to mssql (ODBC) database via Macro - #2 by Ratslinger
.
Also, if you have a registered Base file, you can access it in a Calc document through the Data Sources. From menu select View-Data Sources. Lots of possibilities that way. However you state nothing about what you are attempting to do so cannot give any further direction. Do not even know the LibreOffice version, OS or database you are using.

#9

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.

1 Like
#10

@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.

#11

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.

#8

  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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.