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