understanding parameter substitution change in base 6.1

asked 2019-05-28 03:32:40 +0200

EasyTrieve gravatar image

updated 2019-05-28 03:34:30 +0200

Connecting to MariaDB only.

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:

image description


image description

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

image description

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



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 flag offensive close merge delete