Base: test if record exists

[TheAnalyst]

Hi, I want to take a datum entered on a LibreBase form and see if it exists as the key in a different table - same database.

I have been playing with the suggestions given at How to display SQL query result

I think that I have copied the code correctly as follows:

DIM Context
DIM Conn
DIM Db

    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    Db = Context.getByName("SpreadSheetTest")
    Conn = Db.getConnection("","") 

DIM oStatement
DIM bExists
DIM xResult
DIM sqlString

sqlString = "SELECT [Tournament_ID] FROM [Tournament_Info] WHERE Tournament_ID = 123"

oStatement = Conn.createStatement()
oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
bExists = oStatement.executeQuery(sqlString)

xResult = bExists.isBeforeFirst
msgbox(xResult)

Ignore the name of the database. This is nothing to do with spreadsheets

The Tournament_ID “123” is an integer in the other table and a long on this form. It will be 7 digits when I stop messing around so that should work. The table that the form is based on does not have a relationship with the second table. I have just been a bit lazy and reused the identifiers.

The SQL seems to work just fine in the SQL editor: it correctly returns no results as typed above and returns a single record when I use 567 which, of course, I know does exist.

In the code, bExists.isBeforeFirst always returns TRUE and bExists.isFirst always returns FALSE.

EDIT TO ADD

Version: 6.4.6.2

Build ID: 1:6.4.6-0ubuntu0.20.04.1

CPU threads: 4;

OS: Linux 5.4;

UI render: default;

VCL: gtk3;

Locale: en-AU (en_AU.UTF-8); UI-Language: en-US

Calc: threaded

I am using the Firebird engine (default for installation); I don’t know how to obtain the version number.

The use of “[” and “]” came from the suggested code by EasyTrieve in the above link. Apparently it makes the code look nicer

It certainly didn’t upset the SQL engine.

END EDIT

I am, obviously, hoping that some clever person will shed some light on this for me.

Regards,
TA

[Ratslinger]

Hello,

Please specify what database you are using. Am confused by use of [ and ] in SQL. Also what specific version of LO & OS do you have?

[Ratslinger]

Hello,

A few preliminaries. It is important to note DB, OS and LO versions as it can affect the answer. This is a good example of that. Firebird DB was the default only for a short time. Currently it is back to HSQLDB. Nothing wrong with Firebird, just still not easily used without some work around. Also, because you are using Firebird, the items applicable to HSQLDB will not always be the same. This is true of moving through a result set. See my answer in this post → firebird equivalent for resultset.last. On more issue is the code you chose to ‘copy’. The link you copied from used code to access a DB from Calc. Since you are in Base already and connected to the DB you need, there are easier methods to run SQL.

Now here is code which should work for you:

sub recordExist
    dim sql as string, result
    REM make sure your connected to the database
    if IsNull(ThisComponent.CurrentController.ActiveConnection) then
        ThisComponent.CurrentController.connect
    endif
    Dim oStatement As Object
    oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
    sql = "SELECT COUNT(""Tournament_ID"") FROM ""Tournament_Info"" WHERE ""Tournament_ID"" = 123"
    result = oStatement.executeQuery(sql)
    result.next
    If result.getInt(1) = 1 Then
        Print "Record Found"
    Else
        Print "Record does not exist"
    End If
end sub

Have personally not used square brackets in SQL. Double quotes are needed (opposed to single normally) in a macro when used in a string. Would not be needed normally if it were not mixed case.

Process now simply gets a count of records found and checks result of count.

[TheAnalyst]

Many thanks ratslinger! Worked (almost) perfectly

Because this routine is being called from a form (I assume!), I had to use ThisComponent.Parent etc.

In my defense, I didn’t just blindly copy the code. I originally did use ThisComponent.Parent and when my routine failed, I assumed that I didn’t understand the connection properly and so copied the Calc connection code.

My several hours of reading and searching were not wasted though - I have certainly increased my depth of knowledge in this particular area and came across quite few interesting solutions to problems that I don’t yet have

How do I close this questoin and mark this answer as “Perfect”?

Fantastic work ratslinger and thanks, again, very much!

Regards
TA

[Ratslinger]

@TheAnalyst,

Actually, thought I did this from form but now recall it was straight from the IDE. This can be a cause of concern when dealing with ThisComponent. Sorry for not being clear on that point.

Glad all is working for you.

This, as well as all my answers, are far from “Perfect”. However, you can if wanted mark as accepted (and/or up-vote ^)

Please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.

Actual closing of the question is not needed and left open may offer others to respond with a different method.