• JDBC-Query in View sortable attribute

    By Karsten Kropp 1 decade ago

    When using view control with index values to reference to the columns the sorting is wrong.

    Example: I have a JDBC query containing 3 columns ("select a, b, c from t"). The columns are referenced by using 0,1,2 as columnName and are sortable. When clicking the second column the view is sorted by the first column. It looks like the system creates "order by 1" for the second column (correct is "order by 2" in SQL).

    • Can you please elaborate?

      By Philippe Riand 1 decade ago

      When using the JDBC demo database, it works well with the view panel. Please send us an example working with this demo DB (a sample .xsp page) showing the issue.

      • Example

        By Karsten Kropp 1 decade ago

        I try to sort by the second column, but the system seems to sort by the first column (see screenshot). The page crashs when you try to sort by the first column.

         

        Here is the XPage code:








        id="viewColumnHeader28">



        id="viewColumnHeader1">



         

        • Could you provide that crash log?

          By Andrejus Chaliapinas 1 decade ago

          If you say your page crashes - could you attach your crash log? Probably you'll find it in your \IBM_TECHNICAL_SUPPORT folder in some recent xpages_exc_...log file.

           

          Also could you attach your table's DDL (both structure definition and sample data insert statements)?

        • Looks like there is a issue with the index

          By Philippe Riand 1 decade ago

          Can you reproduce it with the demo derby DB? Or how can we reproduce it ourselves?

          • Example with Derby in XPagesJDBC Database

            By Markus W 1 decade ago

            Example with Derby in XPagesJDBC Database.

            for the JDBC_StaticViewPanelQuery - XPage:

            Sorting for wrong clounm when using index. ( just try to sort by lastname )


                            
                                                         layout="Previous Group Next" xp:key="headerPager" id="pager2">
                                

                            

                            
                                                         sqlQuery="select id, firstname, lastname, city, State from users"
                                    var="jdbcData1" defaultOrderBy="id">
                                

                            

                            
                                
                                                                 id="viewColumnHeader6" value="ID" sortable="true">
                                    

                                

                            

                                                 columnName="firstname">
                                
                                                                 id="viewColumnHeader7" value="First Name" sortable="true">
                                    

                                

                            

                            
                                
                                                                 id="viewColumnHeader8" value="Last Name" sortable="true">
                                    

                                

                            

                            
                                
                                                                 id="viewColumnHeader9" value="City" sortable="true">
                                    

                                

                            

                            
                                
                                                                 id="viewColumnHeader10" value="State">
                                    

                                

                            

                        

            • Need to understand better your requirement

              By Andrejus Chaliapinas 1 decade ago

              Hi Markus,

              Could you explain why do you need to access columns by index and not by their names/aliases?

              • No longer a problem

                By Markus W 1 decade ago

                Hi,

                with the fix of the problem with aliases in DB2 I do not longer need this (thanks for that ;)). 

                But this is obviously a bug. The wrong column get sorted. Maybe someone else will need this.

                • Glad to see that you have a workaround

                  By Philippe Riand 1 decade ago

                  But thank you for the sample code, it was easy to reproduce. I put a fix in and it will be in the next, even though you don't need it :-)