Problem with databaseManager.getSQL

Classic Servoy
1

Have an export method that works perfectly in 3.1.

The first line is

var query = databaseManager.getSQL(forms.rprt_rct_donor_totals.foundset)

in 3.1 it is returning the correct SQL

select receipts.pk_receipts from receipts inner join etc.

in 3.5 it is adding a number to all the table names so of course the method returns no records.

Here is the “var query” result:

query select receipts234.pk_receipt from receipts receipts234 inner join commitments commitments321 on receipts234.fk_commitment=commitments321.pk_commitment inner join contacts contacts322 on receipts234.fk_contact=contacts322.pk_contact where receipts234.fk_client = ? and commitments321.cmt_campaign = ? and receipts234.rct_amount between ? and ? and receipts234.rct_date between ? and ? order by contacts322.con_name_last asc

Has something changed in 3.5?

Best. K

2

Kurt,

In 3.5 the sql engine has been rewritten and is much more flexible.
Things like outer joins and self joins are possible in Servoy since 3.5.

The sql you are showing is not incorrect, the names introduced are table aliases we generate when the query contains joins.

In the example you are sorting on contacts.con_name, maybe there are missing commitments or contacts records for your receipts records.
Try changing the relation join types from ‘inner join’ to ‘left outer join’ in the relation definitions.

Rob