I too, am a bit cautious of using addSql() (SQL injection comes to mind). :P
McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty close to each other with regards to the window functions (OVER...). http://www.troels.arvin.dk/db/rdbms/#select-limit-offset I just had a look at the hibernate dialect for Oracle and it seems that they are implementing the LIMIT and OFFSET using the Window Function method. http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/Oracle9Dialect.java?view=markup Look at the getLimitString() method. MSSQL uses the TOP syntax: http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/SQLServerDialect.java?view=markup PostgreSQL/MySQL uses the LIMIT OFFSET method: http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java?view=markup Wondering how difficult it would be to "port" that functionality? ---Jaco -----Original Message----- From: McKinley [mailto:[email protected]] Sent: Tuesday, January 19, 2010 5:06 PM To: [email protected] Subject: Re: Rowlimits: OFFSET and LIMIT I am afraid that an addSql method would quickly turn into an addSqlInjection method if you catch my meaning. All the pieces are there already to support ROW_NUMBER OVER (ORDER BY ...). They just need to be abstracted from their current use as necessary and integrated into the function support. In fact, I have to make use of ROW_NUMBER OVER (ORDER BY ...) in SQL Server 2005 for an upcoming project. I'll implement it for Oracle too if someone can show me the necessary Oracle documentation. I'll create it as a subclass of DBFuncExpr with an internal ArrayList<DBOrderByInfo> with all the usual orderBy and clearOrderBy methods. The orderBy will be housed in some sort of "over" object. "over" will also house the partitionBy and clearPartitionBy methods. I will probably *not* throw an error upon SQL generation if the partitionBy contains a column not in the FROM clause unless anyone can tell me that it is not safe. Your DBMS will catch the error. Also, I will not do any checks on orderBy for the same if you combine this with a ranking function. Again, the DBMS will catch the error and it is within your control to conditionally modify the ROW_NUMBER function partionBy and orderBy and the FROM to make sure your query will actually run. I'll submit the patch to the issue track on Saturday and post a link to this thread. Thanks, McKinley On Tue, Jan 19, 2010 at 2:36 PM, Vladimir Tsanev <[email protected]> wrote: > Another approach to 2 might be to extend DBExpr - and implement addSql > method. > But I do not know what should be the implementation of addReferencedColumns > (the other abstract method). ##################################################################################### Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Thank You. ##################################################################################### ##################################################################################### This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal #####################################################################################
