SqlException in query with join and parameters.

[jettero]

Hi all.

In my JSF page I've a table and two dropDown components.
Table is binded to tableDataProvider, DropDown1 to table1DataProvider and DropDown2 to table2DataProvider.
RowSets and DataProviders are declared within the page bean, not in the session bean.
Each query does a join between two or three tables (MySQL) and uses a parameter.
This parameter is "fixed" (the parameter does not depend from the dropdown selected value) and is retrived from session bean.
In the prerender method I set the parameter for each rowset with rowset.setObject() and dataprovider.refresh() methods.
The page loads successfully but when I submit the page, a "SqlException: Parameter #1 has not been set" is generated.
What's wrong? I've other pages with dataProviders that use parameters but do not use join and these work. Is this the problem?

Tnx for your attention.

[HandyGeek]

Hi jettero,
There should be no problems with a join.  It sounds like a syntax
problem in the parameter binding.  Could you show the SQL code?

Best, David

jettero wrote:

> Hi all.
>
> In my JSF page I've a table and two dropDown components.
> Table is binded to tableDataProvider, DropDown1 to table1DataProvider and
> DropDown2 to table2DataProvider.
> RowSets and DataProviders are declared within the page bean, not in the
> session bean.
> Each query does a join between two or three tables (MySQL) and uses a
> parameter.
> This parameter is "fixed" (the parameter does not depend from the dropdown
> selected value) and is retrived from session bean.
> In the prerender method I set the parameter for each rowset with
> rowset.setObject() and dataprovider.refresh() methods.
> The page loads successfully but when I submit the page, a "SqlException:
> Parameter #1 has not been set" is generated.
> What's wrong? I've other pages with dataProviders that use parameters but do
> not use join and these work. Is this the problem?
>
> Tnx for your attention.
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[jettero]

These are the queries from the page bean

employmentRowSet.setCommand("SELECT ALL employment.id,\n
        employment.organization_id, \n        
        employment.teacher_id, \n
        teacher.id, \n                    
        teacher.taxcode, \n                                      
        concat(teacher.firstname,\', \',teacher.lastname) AS fullname\n
        FROM employment\n          
        INNER JOIN teacher ON employment.teacher_id = teacher.id\n
        WHERE employment.organization_id = ?");


classRowSet.setCommand("SELECT ALL class.id, \n                    
        class.organization_id, \n                    
        concat(class.section,cast(class.class as CHAR),\' \',class.studies) as fullclass\n
        FROM class\n
        WHERE class.organization_id = ?");

teachingRowSet.setCommand("SELECT ALL teaching.id, \n                    
        teaching.teacher_id, \n                    
        teaching.class_id, \n                    
        teaching.nh, \n                    
        teaching.nrh, \n                    
        class.id, \n                    
        class.organization_id, \n                    
        class.class, \n                    
        class.section, \n                    
        class.studies, \n                  
        teacher.id, \n                    
        teacher.taxcode, \n                    
        teacher.firstname, \n                    
        teacher.lastname \n
        FROM teaching\n          
        INNER JOIN class ON teaching.class_id = class.id, teacher\n
        WHERE teaching.teacher_id = teacher.id\n          
        AND class.organization_id = ?");

I use the concat function to get the string to display within the dropdown.
class.class is int and I use cast function to get the char value.

this is the code I use to set parameters in the prerender()

employmentRowSet.setObject(1, getSessionBean1().getId());
classRowSet.setObject(1, getSessionBean1().getId());
teachingRowSet.setObject(1, getSessionBean1().getId());
employmentDataProvider.refresh();
classDataProvider.refresh();
teachingDataProvider.refresh();


I do not understand why the exception is generated only after submit the page :(

HandyGeek wrote:

Hi jettero,
There should be no problems with a join.  It sounds like a syntax
problem in the parameter binding.  Could you show the SQL code?

Best, David

jettero wrote:
> Hi all.
>
> In my JSF page I've a table and two dropDown components.
> Table is binded to tableDataProvider, DropDown1 to table1DataProvider and
> DropDown2 to table2DataProvider.
> RowSets and DataProviders are declared within the page bean, not in the
> session bean.
> Each query does a join between two or three tables (MySQL) and uses a
> parameter.
> This parameter is "fixed" (the parameter does not depend from the dropdown
> selected value) and is retrived from session bean.
> In the prerender method I set the parameter for each rowset with
> rowset.setObject() and dataprovider.refresh() methods.
> The page loads successfully but when I submit the page, a "SqlException:
> Parameter #1 has not been set" is generated.
> What's wrong? I've other pages with dataProviders that use parameters but do
> not use join and these work. Is this the problem?
>
> Tnx for your attention.
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Rick Fincher]

Hi jettero ,

I've had problems sometimes using  dataprovider.refresh() and have had
to usr rowset.execute() instead.

I have not figured out why that is yet.  It is no always, but sometime
only using rowset.execute() works.

Rick

jettero wrote:

> Hi all.
>
> In my JSF page I've a table and two dropDown components.
> Table is binded to tableDataProvider, DropDown1 to table1DataProvider and
> DropDown2 to table2DataProvider.
> RowSets and DataProviders are declared within the page bean, not in the
> session bean.
> Each query does a join between two or three tables (MySQL) and uses a
> parameter.
> This parameter is "fixed" (the parameter does not depend from the dropdown
> selected value) and is retrived from session bean.
> In the prerender method I set the parameter for each rowset with
> rowset.setObject() and dataprovider.refresh() methods.
> The page loads successfully but when I submit the page, a "SqlException:
> Parameter #1 has not been set" is generated.
> What's wrong? I've other pages with dataProviders that use parameters but do
> not use join and these work. Is this the problem?
>
> Tnx for your attention.
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[jettero]

I've tried to update mysql connector from 5.1.5 to 5.1.6 but i've always the same error.
Do you thing there is a problem with the query syntax or with the jsf lifecycle??

tnx


These are the queries from the page bean

employmentRowSet.setCommand("SELECT ALL employment.id,\n
        employment.organization_id, \n        
        employment.teacher_id, \n
        teacher.id, \n                    
        teacher.taxcode, \n                                      
        concat(teacher.firstname,\', \',teacher.lastname) AS fullname\n
        FROM employment\n          
        INNER JOIN teacher ON employment.teacher_id = teacher.id\n
        WHERE employment.organization_id = ?");


classRowSet.setCommand("SELECT ALL class.id, \n                    
        class.organization_id, \n                    
        concat(class.section,cast(class.class as CHAR),\' \',class.studies) as fullclass\n
        FROM class\n
        WHERE class.organization_id = ?");

teachingRowSet.setCommand("SELECT ALL teaching.id, \n                    
        teaching.teacher_id, \n                    
        teaching.class_id, \n                    
        teaching.nh, \n                    
        teaching.nrh, \n                    
        class.id, \n                    
        class.organization_id, \n                    
        class.class, \n                    
        class.section, \n                    
        class.studies, \n                  
        teacher.id, \n                    
        teacher.taxcode, \n                    
        teacher.firstname, \n                    
        teacher.lastname \n
        FROM teaching\n          
        INNER JOIN class ON teaching.class_id = class.id, teacher\n
        WHERE teaching.teacher_id = teacher.id\n          
        AND class.organization_id = ?");

I use the concat function to get the string to display within the dropdown.
class.class is int and I use cast function to get the char value.

this is the code I use to set parameters in the prerender()

employmentRowSet.setObject(1, getSessionBean1().getId());
classRowSet.setObject(1, getSessionBean1().getId());
teachingRowSet.setObject(1, getSessionBean1().getId());
employmentDataProvider.refresh();
classDataProvider.refresh();
teachingDataProvider.refresh();


I do not understand why the exception is generated only after submit the page :(

HandyGeek wrote:

Hi jettero,
There should be no problems with a join.  It sounds like a syntax
problem in the parameter binding.  Could you show the SQL code?

Best, David

jettero wrote:
> Hi all.
>
> In my JSF page I've a table and two dropDown components.
> Table is binded to tableDataProvider, DropDown1 to table1DataProvider and
> DropDown2 to table2DataProvider.
> RowSets and DataProviders are declared within the page bean, not in the
> session bean.
> Each query does a join between two or three tables (MySQL) and uses a
> parameter.
> This parameter is "fixed" (the parameter does not depend from the dropdown
> selected value) and is retrived from session bean.
> In the prerender method I set the parameter for each rowset with
> rowset.setObject() and dataprovider.refresh() methods.
> The page loads successfully but when I submit the page, a "SqlException:
> Parameter #1 has not been set" is generated.
> What's wrong? I've other pages with dataProviders that use parameters but do
> not use join and these work. Is this the problem?
>
> Tnx for your attention.
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[jettero]

rowset.execute() does not work for me

Any other ideas? I have read and re-read the documentation, but I was not able to understand where the problem is :(

Rick Fincher wrote:

Hi jettero ,

I've had problems sometimes using  dataprovider.refresh() and have had
to usr rowset.execute() instead.

I have not figured out why that is yet.  It is no always, but sometime
only using rowset.execute() works.

Rick

jettero wrote:
> Hi all.
>
> In my JSF page I've a table and two dropDown components.
> Table is binded to tableDataProvider, DropDown1 to table1DataProvider and
> DropDown2 to table2DataProvider.
> RowSets and DataProviders are declared within the page bean, not in the
> session bean.
> Each query does a join between two or three tables (MySQL) and uses a
> parameter.
> This parameter is "fixed" (the parameter does not depend from the dropdown
> selected value) and is retrived from session bean.
> In the prerender method I set the parameter for each rowset with
> rowset.setObject() and dataprovider.refresh() methods.
> The page loads successfully but when I submit the page, a "SqlException:
> Parameter #1 has not been set" is generated.
> What's wrong? I've other pages with dataProviders that use parameters but do
> not use join and these work. Is this the problem?
>
> Tnx for your attention.
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Futaleufu_John]

Take a look at the button action method that handles the submit.

Does that method do anything with the row set or data provider?

Anytime you execute the row set or, I think, refresh the data provider, you have to set the underlying query parameters.

Get a stack trace to see where in your code the exception is thrown.

jettero wrote:

rowset.execute() does not work for me

Any other ideas? I have read and re-read the documentation, but I was not able to understand where the problem is :(

[jettero]

I did debug and I found two problems.
I used a test page with a table and a button which deletes the selected row in the table
http://blogs.sun.com/winston/entry/single_selectable_row_table_component

1) The guide that I have followed is not working in this case because the setSelected method is not executed while it works when the query uses only one table. In this case the exception is not thrown but the action method "fails".

2) The exception (java.sql.SQLException: No value specified for parameter 1) is thrown if I use virtual forms.
In my true page there are other input components then use the virtual form.
The virtual form used in the test page includes the submit button and the radiobutton.
I used this virtual form on other pages where the query uses a single table and it works.
The exception is thrown (after the preprocess method and before the button action method) by

public PhaseId getPhaseId() {
        return PhaseId.RENDER_RESPONSE;
    }

in TableSelectPhaseListener.java

I cannot find an alternative way to select rows that do not use TableSelectPhaseListener

Tnx for your attention


Take a look at the button action method that handles the submit.

Does that method do anything with the row set or data provider?

Anytime you execute the row set or, I think, refresh the data provider, you have to set the underlying query parameters.

Get a stack trace to see where in your code the exception is thrown.

jettero wrote:

rowset.execute() does not work for me

Any other ideas? I have read and re-read the documentation, but I was not able to understand where the problem is :(

[Futaleufu_John]

I have experienced problems with virtual forms and try to avoid them.

First, try getting rid of the virtual form and see if that helps.

Second, double-check to make sure you followed all the instructions in the blog. When I first started using radio buttons in tables and table select phase listeners, I invariably left something out.

If it still doesn't work, let's fix whatever's wrong and then reintroduce the virtual form.

I did debug and I found two problems.
I used a test page with a table and a button which deletes the selected row in the table
http://blogs.sun.com/winston/entry/single_selectable_row_table_component

1) The guide that I have followed is not working in this case because the setSelected method is not executed while it works when the query uses only one table. In this case the exception is not thrown but the action method "fails".

2) The exception (java.sql.SQLException: No value specified for parameter 1) is thrown if I use virtual forms.
In my true page there are other input components then use the virtual form.
The virtual form used in the test page includes the submit button and the radiobutton.
I used this virtual form on other pages where the query uses a single table and it works.
The exception is thrown (after the preprocess method and before the button action method) by

public PhaseId getPhaseId() {
        return PhaseId.RENDER_RESPONSE;
    }

in TableSelectPhaseListener.java

I cannot find an alternative way to select rows that do not use TableSelectPhaseListener

Tnx for your attention

Futaleufu_John wrote:

Take a look at the button action method that handles the submit.

Does that method do anything with the row set or data provider?

Anytime you execute the row set or, I think, refresh the data provider, you have to set the underlying query parameters.

Get a stack trace to see where in your code the exception is thrown.

jettero wrote:

rowset.execute() does not work for me

Any other ideas? I have read and re-read the documentation, but I was not able to understand where the problem is :(