Servoy 3.1.6 freeze/crash when using getDataSetByQuery

Classic Servoy
1

Hi all,

I have the following query:

(SELECT DISTINCT ON(document_date,transaction_document_id,transaction_id) transaction_id FROM acc_transactions,acc_documents WHERE  
((transaction_document_id = document_id AND (date_trunc('day',document_date) BETWEEN '2007-01-01' AND '2007-08-15') AND 
document_type NOT IN ('Delivery Note','Pro-Forma Invoice','Quote') AND document_customer_id = 4413 AND (document_outstanding <> 0 OR document_type = 'Payment on Account'))  
OR (transaction_document_consolidated_id = document_id AND transaction_document_consolidated_id NOTNULL AND (date_trunc('day',document_date) BETWEEN '2007-01-01' AND '2007-08-15') 
AND document_customer_id = 4413 AND document_outstanding <> 0))   ORDER BY document_date,transaction_document_id) UNION  SELECT transaction_id FROM acc_transactions WHERE transaction_type_id = 62 
AND date_trunc('day',transaction_date) BETWEEN '2007-01-01' AND '2007-08-15' AND transaction_customer_id = 4413

It executes in 120 ms from pgAdmin.

In Servoy,

(SELECT DISTINCT ON(document_date,transaction_document_id,transaction_id) transaction_id FROM acc_transactions,acc_documents WHERE  
((transaction_document_id = document_id AND (date_trunc('day',document_date) BETWEEN ? AND ?) AND 
document_type NOT IN ('Delivery Note','Pro-Forma Invoice','Quote') AND document_customer_id = ? AND (document_outstanding <> 0 OR document_type = 'Payment on Account'))  
OR (transaction_document_consolidated_id = document_id AND transaction_document_consolidated_id NOTNULL AND (date_trunc('day',document_date) BETWEEN ? AND ?) 
AND document_customer_id = ? AND document_outstanding <> 0))   ORDER BY document_date,transaction_document_id) UNION  SELECT transaction_id FROM acc_transactions WHERE transaction_type_id = 62 
AND date_trunc('day',transaction_date) BETWEEN ? AND ? AND transaction_customer_id = ?

with params

[Mon Jan 01 2007 00:00:00 GMT-0000 (GMT),Wed Aug 15 00:00:00 BST 2007,4413,
Mon Jan 01 2007 00:00:00 GMT-0000 (GMT),Wed Aug 15 00:00:00 BST 2007,4413,
Mon Jan 01 2007 00:00:00 GMT-0000 (GMT),Wed Aug 15 00:00:00 BST 2007,4413]

Freezes Servoy Developer (Mac OS X) and crashes Servoy client (Win XP)

I’m executing with

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, 10000);

The only thing I can think of is that Servoy is unhappy with the dates, which are coming from two DATETIME globals in Servoy.

Any ideas?

2

I wouldn’t be surprised to see the postgres parse engine fail with this param. Maybe try altering the global type and passing down a string with the date portion only in it. The run time binding of the “?” should bind properly with this string and subsequently feed the pg SQL parser correctly. I have run into a similar problem with feeding Jasper Reports: we use pg here as well.

Best, Michael

3

So this is your SQL:

(
    SELECT DISTINCT ON(document_date,transaction_document_id,transaction_id) transaction_id 
    FROM acc_transactions,acc_documents 
    WHERE 
    (
        (
            transaction_document_id = document_id 
            AND (date_trunc('day',document_date) BETWEEN ? AND ?) 
            AND document_type NOT IN ('Delivery Note','Pro-Forma Invoice','Quote') 
            AND document_customer_id = ? 
            AND (document_outstanding <> 0 OR document_type = 'Payment on Account')
        )
        OR 
        (
            transaction_document_consolidated_id = document_id 
            AND transaction_document_consolidated_id NOTNULL 
            AND (date_trunc('day',document_date) BETWEEN ? AND ?) 
            AND document_customer_id = ? 
            AND document_outstanding <> 0
        )
    )
    ORDER BY document_date,transaction_document_id
) 

UNION 

SELECT transaction_id 
FROM acc_transactions 
WHERE transaction_type_id = 62 
AND date_trunc('day',transaction_date) BETWEEN ? AND ? 
AND transaction_customer_id = ?

Apart from the missing parenthesis around ‘date_trunc(‘day’,transaction_date) BETWEEN ? AND ?’ in the second last row it looks fine (well I would add them to be safe, you did so yourself already in the first part of the query).
I would pass the dates in yyyy-mm-dd format by the way. So use the Utils.DateFormat() function

Hope this helps.

4

ROCLASI:
I would pass the dates in yyyy-mm-dd format by the way. So use the Utils.DateFormat() function

This sounds like a good idea - the only thing is that I don’t understand why I haven’t needed to do this before…