#96 Private Message baseListing sqlserver.sql code bug

[Emir]

Hi, i think there is a bug in the sqlserver.sql code:

PrivateMessageModel.baseListing with #FILTER# that gets replaced during runtime.

When i try to access my private messages trough the forum frontend, the sql code that gets executed is:

  SELECT * 
    FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY pm.privmsgs_date DESC) - 1 AS rownumber, 
    pm.privmsgs_type, pm.privmsgs_id, pm.privmsgs_date, pm.privmsgs_subject, u.user_id, u.username 
    FROM jforum_privmsgs pm, jforum_users u 

    WHERE privmsgs_to_userid = 8 
    AND u.user_id = pm.privmsgs_from_userid  
    AND ( pm.privmsgs_type = 1 
    OR pm.privmsgs_type = 0 
    OR privmsgs_type = 5)



    WHERE rownumber >= 0 and rownumber < 15

and i get the sql exception:
see attachment err.PNG

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.

The correct statement should be:

   SELECT * 
    FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY pm.privmsgs_date DESC) - 1 AS rownumber, 
    pm.privmsgs_type, pm.privmsgs_id, pm.privmsgs_date, pm.privmsgs_subject, u.user_id, u.username 
    FROM jforum_privmsgs pm, jforum_users u 

    WHERE privmsgs_to_userid = 8 
    AND u.user_id = pm.privmsgs_from_userid  
    AND ( pm.privmsgs_type = 1 
    OR pm.privmsgs_type = 0 
    OR privmsgs_type = 5) **res**



    WHERE **res**.rownumber >= 0 and **res**.rownumber < 15

[Emir]

• edit: a closed bracket needs to be added to the statement 2 as well, before res

Regards

[Ulf Dittmer]

Thanks for reporting that. Unfortunately, we currently do not have access to SQLServer, so things may slip by unnoticed :-(

Does it work if you use the following in sqlserver.spl?

PrivateMessageModel.baseListing = SELECT * \
    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY pm.privmsgs_date DESC) - 1 AS rownumber, \
    pm.privmsgs_type, pm.privmsgs_id, pm.privmsgs_date, pm.privmsgs_subject, u.user_id, u.username \
    FROM jforum_privmsgs pm, jforum_users u \
    #FILTER# ) res \
    WHERE res.rownumber >= ? and res.rownumber < ?

[Emir]

Yes, thats the code that worked for me :)