[gnome-db] Redering SQL problems

[Daniel Espinosa <esodan gmail com>]

I have the following SQL statement:

SELECT sum (amount) FROM transactions \
                                    WHERE debit in \
                                     (SELECT id FROM accounts \
                                      WHERE parent in \
                                      (SELECT id FROM accounts WHERE id = ##account::int or parent = ##account::int) \
                                      UNION SELECT id FROM accounts WHERE id = ##account::int)

I'm using a PostgreSQL DB, when translate this command to use directly to psql and it runs correctly.

SELECT sum (amount) FROM transactions WHERE debit in (SELECT id FROM
accounts WHERE parent in (SELECT id FROM accounts WHERE id = 5 or
parent = 5) UNION SELECT id FROM accounts WHERE id = 5)

result:

sum
______

544000
(1 row)

DB definition is attached made by pg_dump

When try to render it and run that statement using the following code:

I get the following messages at runtime using TRUNK Revision 3374 or using GDA version 4.0.2 compiled from sources:

Calculating Balance...
Parsed CREDIT
Getted Parameters...
Setted account ID...

** (libcash_test:6063): CRITICAL **: default_render_select: assertion `GDA_SQL_ANY_PART (stmt)->type == GDA_SQL_ANY_STMT_SELECT' failed
SQL for SUM CREDIT: (null)
** (libcash_test:6063): CRITICAL **: default_render_select: assertion `GDA_SQL_ANY_PART (stmt)->type == GDA_SQL_ANY_STMT_SELECT' failed

** (libcash_test:6063): CRITICAL **: default_render_select: assertion `GDA_SQL_ANY_PART (stmt)->type == GDA_SQL_ANY_STMT_SELECT' failed



As you can see when try to print the SQL statement using gda_connection_statement_to_sql returns NULL.


>>>>>>>>>>CODE>>>>>>>>>>>>>><
gpointer
get_balance (GValue *id, GdaConnection *cnc, GError **error)
{

        GdaSqlParser *parser;
        GdaStatement *debit = NULL;
        GdaStatement *credit = NULL;
        GdaSet *debit_params, *credit_params;
        GdaDataModel *sum_credit, *sum_debit;
        const GValue *val_sum_debit, *val_sum_credit;
        gdouble amount;
        
        static gchar *sql_debit = "SELECT sum (amount) FROM transactions \
                                    WHERE debit in \
                                     (SELECT id FROM accounts \
                                      WHERE parent in \
                                      (SELECT id FROM accounts WHERE id = ##account::int or parent = ##account::int) \
                                      UNION SELECT id FROM accounts WHERE id = ##account::int)";
    
        static gchar *sql_credit = "SELECT sum (amount) FROM transactions \
                                    WHERE credit in \
                                     (SELECT id FROM accounts \
                                      WHERE parent in \
                                      (SELECT id FROM accounts WHERE id = ##account::int or parent = ##account::int) \
                                      UNION SELECT id FROM accounts WHERE id = ##account::int)";
        
        g_print ("Calculating Balance...\n");
        
        if (!G_IS_VALUE (id))
            return NULL;
            
        parser = gda_sql_parser_new ();
        
        /* Get CREDIT SUM */
        credit = gda_sql_parser_parse_string (parser, sql_credit, NULL, error);

        if (!GDA_IS_STATEMENT (credit))
            return NULL;
        g_print ("Parsed CREDIT\n");
        if (!gda_statement_get_parameters (credit, &credit_params, error)) {
            g_object_unref (credit);
            return NULL;
        }
        g_print ("Getted Parameters...\n");
        if (!gda_set_set_holder_value (credit_params, error, "account", id)) {
            g_object_unref (credit);
            g_object_unref (credit_params);
            return NULL;
        }
        g_print ("Setted account ID...\n");
        g_print ("SQL for SUM CREDIT: %s",
                 gda_connection_statement_to_sql (cnc, credit, 
                                                  credit_params,
                                                  GDA_STATEMENT_SQL_PRETTY,
                                                  NULL, NULL));
        sum_credit = gda_connection_statement_execute_select (cnc,
                                                       credit, 
                                                       credit_params,
                                                       error);
        g_object_unref (credit);
        g_object_unref (credit_params);
        
        if (!GDA_IS_DATA_MODEL(sum_credit)) 
            return NULL;        
        
        g_print ("Getted sum of Credits...\n");
        
        // Get DEBIT SUM
        debit = gda_sql_parser_parse_string (parser, sql_debit, NULL, error);
        if (!GDA_IS_STATEMENT (debit)) {
            g_object_unref (sum_credit);
            return NULL;
        }
        
        if (!gda_statement_get_parameters (debit, &debit_params, error)) {
            g_object_unref (debit);
            g_object_unref (sum_credit);
            return NULL;
        }
        
        if (!gda_set_set_holder_value (debit_params, error, "account", id)) {
            g_object_unref (debit);
            g_object_unref (debit_params);
            g_object_unref (sum_credit);
            return NULL;
        }
        
        sum_debit = gda_connection_statement_execute_select (cnc, 
                                                             debit,
                                                             debit_params,
                                                             error);
        g_object_unref (debit);
        g_object_unref (debit_params);
        
        if (!GDA_IS_DATA_MODEL(sum_debit)) {
            g_object_unref (sum_credit);
            return NULL;
        }
        g_print ("Getted sum of Credits...\n");
        
        /* Calculating balance amount */
        val_sum_debit = gda_data_model_get_value_at (sum_debit, 0, 0, error);
        
        if (!G_IS_VALUE (val_sum_debit)) {
            g_object_unref (sum_credit);
            g_object_unref (sum_debit);
            return NULL;
        }
        
        val_sum_credit = gda_data_model_get_value_at (sum_credit, 0, 0, error);
        
        if (! G_IS_VALUE (val_sum_credit)) {
            g_object_unref (sum_credit);
            g_object_unref (sum_debit);
            return NULL;
        }
        
        amount = g_value_get_double (val_sum_debit) - g_value_get_double (val_sum_credit);
        
        g_print ("BALANCE: %f", amount);
        
    return NULL;
}

-- 
Trabajar, la mejor arma para tu superación
"de grano en grano, se hace la arena" (R) (en trámite, pero para los cuates: LIBRE)

Attachment: db.backup
Description: Binary data