My personal and professional life

2018-08-22

LibreOffice 6.1 and named parameters substitution

I've recently upgraded to LibreOffice 6.1 eager to try to new features and improvements. However, I soon noticed that all my sub forms in Base are broken. Those were sub forms executing SQL statements like:

SELECT id, name FROM persons

So I could get additional information from related tables by mapping master to slave fields. Apparently in earlier versions Base was rewriting the query, but this is no more the case. You'll get errors like:
  • The data content could not be loaded.
  • SQL Status: 07009 Invalid descriptor index.
  • You tried to set a parameter at position '1' but there is/are only '0' parameter(s) allowed. One reason may be that the property "ParameterNameSubstitution" is not set to TRUE in the data source.
I quickly opened the release notes and found what I've omitted previously:

Base
  • The ODBC, JDBC and Firebird SDBC drivers/bridges previously applied named parameter substitution (for ODBC and JDBC: when enabled) to all SQL commands, including those that were tagged in the GUI as "execute SQL command directly", thereby not preserving the SQL command exactly as typed. Named parameter substitution now is applied only to SQL queries that are generated from a LibreOffice SQL parse tree, leaving "execute SQL command directly" commands completely untouched.
So the solution is to rewrite the sub form queries like this (which is the unnamed syntax used by MySQL):

SELECT name FROM persons WHERE id = ?

Or in other words write the named parameter explicitly in the query.