getsql not returning parameters from foundset

Classic Servoy
1

Hi

I have a form, on which i am limiting the records via controller.search

From another form, i am calling

databaseManager.getSQL(forms.fabman_frm_detail.foundset)

Upon application output of this i can see that the sql string is missing the parameters specified within the search

select distinct fabriccondition3186.fc_id from fabric_condition fabriccondition3186 left outer join component_detail componentdetail6564 on fabriccondition3186.cd_id=componentdetail6564.cd_id where componentdetail6564.cd_plant = ? and componentdetail6564.cd_complex = ?

Upon application output of

databaseManager.getSQLParameters(forms.fabman_frm_detail.foundset

I get my params

Has anyone seen this before, if so how do I get the sql for the foundset with params included?

Regards

2

Hi McCourt,

This is how Servoy works. It sends prepared statements to the back-end database. So the SQL is exactly that.
You could make a method that takes the SQL and the params and places them in the SQL string. If I remember correctly someone already did this, should be somewhere on this forum.

3

Hi,
I understand that this is how servoy works.

Here is what we are trying to acheive

var $sql = databaseManager.getSQL(forms.fabman_frm_detail.foundset);

var $year_zero_query = "SELECT a.fc_fabric_key " +
        				   "FROM budget_schedules a, fabric_condition b, component_detail c "+
        				   "WHERE a.fc_fabric_key = b.fc_id and b.cd_id = c.cd_id " +
        				   "and b.fc_program_year_basic IS NOT NULL "+
                           "AND b.fc_program_year_basic !=0 "+
                           "AND b.fc_id IN (" + $sql + ")";
    controller.loadRecords($year_zero_query);

This however errors upon execution with the following when we are using search within fabman_frm_detail.
It works fine when no search is called

Error loading primary key data, No value specified for parameter 1

Regards

4

Hi McCourt,

You could do something like this:

var $sql    = databaseManager.getSQL(forms.fabman_frm_detail.foundset),
    $param  = databaseManager.getSQLParameters(forms.fabman_frm_detail.foundset);

var $year_zero_query = "SELECT a.fc_fabric_key " +
        "FROM budget_schedules a, fabric_condition b, component_detail c "+
        "WHERE a.fc_fabric_key = b.fc_id and b.cd_id = c.cd_id " +
        "and b.fc_program_year_basic IS NOT NULL "+
        "AND b.fc_program_year_basic !=0 "+
        "AND b.fc_id IN (" + $sql + ")";
if ( $param != [] ) {
    controller.loadRecords($year_zero_query, $param);
} else {
    controller.loadRecords($year_zero_query);
}
5

Hi

I am still receiving the same error with the inclusion of the $param check.

Error loading primary key data, No value specified for parameter 1
 > com.servoy.j2db.dataprocessing.DataException: No value specified for parameter 1
[DFA]
select distinct fabriccondition1490.fc_id from fabric_condition fabriccondition1490 left outer join component_detail componentdetail1565 on fabriccondition1490.cd_id=componentdetail1565.cd_id where componentdetail1565.cd_plant = ?

Is it the case that we always have to manually concatenate params into getsql result? this seems very longwinded.
From previous suggestions this is not how getsql is supposed to function, why does servoy not recognise the paramaters?
Maybe Servoy could jump in here and clarify.

Regards

6

McCourt,

You are creating an sql string and when you pass this into controller.loadRecords() you need to pass in an array of args that is the same size as the number of question marks in the string.

So if you get part of the string you create from databaseManager.getSQL() you need to add the matching parameters as weel.

Rob