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
Build ID: 1:6.4.6-0ubuntu0.20.04.1
CPU threads: 4;
OS: Linux 5.4;
UI render: default;
Locale: en-AU (en_AU.UTF-8); UI-Language: en-US
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.
I am, obviously, hoping that some clever person will shed some light on this for me.