# understanding parameter substitution change in base 6.1

I'm getting errors in my base macros when I try to move 5.x working stuff to 6.x, so I'm trying to understand what was changed and why. (I saw this issue some time ago, and was hoping that it would just go away by itself. So I'm still using 5.4 until it is either fixed or I somehow find a workaround.)

From the Base 6.1 release notes:

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.

I think I know what "execute SQL command directly" is, but what is, "a LibreOffice SQL parse tree"?

And how does any of this relate to SQL built and executed by macros? I would have thought they have nothing to do with the GUI interface.

First error I'm getting when I load is:

Then

The code shows that it's gagging when trying to run SQL:

The SQL is as follows (I added some line breaks, to show how it gets built):

sSQL=SELECT *

FROM (

SELECT CONCAT( ifnull( Item, '' ), ifnull( CONCAT( '; ', MC, ' ', ifnull( Category, '' ) ), '' ), if( FALSE <> ifnull( Ok?, FALSE ), ' -OK', '' ) ) AS Choice, items to get.ITEMS TO GET ID AS ID, ifnull( [Item], '' ), items to get categories major.Weight AS CatWtMajor, ifnull( CONCAT( [MC] ), '' ), items to get categories major.MC, items to get categories.Weight AS CatWt, ifnull( CONCAT( [Category] ), '' ), if( FALSE <> ifnull( [Ok?], FALSE ), FALSE, TRUE ) FROM { oj links.items to get categories AS items to get categories LEFT OUTER JOIN links.items to get categories major AS items to get categories major ON items to get categories.Major Category ID = items to get categories major.Major Category ID RIGHT OUTER JOIN links.items to get AS items to get ON items to get categories.ITEMS TO GET CATEGORIES ID = items to get.ITEMS TO GET CATEGORIES ID } ORDER BY ifnull( [Item], '' ) ASC,CatWtMajor ASC,ifnull( CONCAT( [MC] ), '' ) ASC,items to get categories major.MC ASC,CatWt ASC,ifnull( CONCAT( [Category] ), '' ) ASC,if( FALSE <> ifnull( [Ok?], FALSE ), FALSE, TRUE ) ASC

) AS Foo

WHERE Choice = ?


I'm guessing the issue is this final question mark used for it's parameter.

???

(I imagine I can break this down to find the issue, but I thought I'd start just by trying to see if there is some basic thing that has changed that this previously working code now fails from.)

edit retag close merge delete