[opencms-dev] opencms and ms-sql

[Andras Balogh]

Hi,

I have had problems with the new opencms and MS-SQL 2000.
After installing it and logging in, when we switch to the offline project
and the
Administration we got this error:
com.opencms.core.CmsException: 4 Sql exception. Detailed error:
[com.opencms.file.mssql.CmsDbAccess] parameter #2 has not been set.
root cause was java.sql.SQLException: parameter #2 has not been set
 at
com.opencms.file.genericSql.CmsDbAccess.getFilesInFolder(CmsDbAccess.java:33
83)
 at
com.opencms.file.genericSql.CmsResourceBroker.helperGetFilesInFolder(CmsReso
urceBroker.java:4047)
 at
com.opencms.file.genericSql.CmsResourceBroker.getFilesInFolder(CmsResourceBr
oker.java:3331)
 at com.opencms.file.CmsObject.getFilesInFolder(CmsObject.java:1696)
....

I have tried it with 3 different jdbc drivers (not including the merant
driver becuase we don't know from where to get it)
I traced down the problem to be in the file:
com/opencms/file/mssql/query.properties at key
C_RESOURCES_GET_FILESINFOLDER

SELECT CMS_RESOURCES.RESOURCE_ID, CMS_RESOURCES.PARENT_ID, \
CMS_RESOURCES.RESOURCE_NAME, CMS_RESOURCES.RESOURCE_TYPE, \
CMS_RESOURCES.RESOURCE_FLAGS, CMS_RESOURCES.USER_ID, \
CMS_RESOURCES.GROUP_ID, CMS_PROJECTRESOURCES.PROJECT_ID, \
CMS_RESOURCES.FILE_ID, CMS_RESOURCES.ACCESS_FLAGS, CMS_RESOURCES.STATE, \
CMS_RESOURCES.LOCKED_BY, CMS_RESOURCES.LAUNCHER_TYPE, \
CMS_RESOURCES.LAUNCHER_CLASSNAME, CMS_RESOURCES.DATE_CREATED, \
CMS_RESOURCES.DATE_LASTMODIFIED, CMS_RESOURCES.RESOURCE_SIZE, \
CMS_RESOURCES.RESOURCE_LASTMODIFIED_BY, \
CMS_RESOURCES.PROJECT_ID LOCKED_IN_PROJECT \
FROM CMS_RESOURCES, CMS_PROJECTRESOURCES \
WHERE CMS_RESOURCES.PARENT_ID=?  \
AND CMS_RESOURCES.RESOURCE_NAME LIKE
RTRIM(CMS_PROJECTRESOURCES.RESOURCE_NAME)+'%' \
AND CMS_RESOURCES.RESOURCE_TYPE <> 0 \
AND CMS_PROJECTRESOURCES.PROJECT_ID = ?  \
ORDER BY CMS_RESOURCES.RESOURCE_NAME

As you can see the prepared statment requires 2 parameters but only one is
set in the source code, so we removed
"AND CMS_PROJECTRESOURCES.PROJECT_ID = ?" part.
The query found in the other file:
com/opencms/file/genericSql/query.properties
seems to be correct and has only one parameter.

After rebuilding the jar file opencms worked without errors.
Can an opencms developer confirm that the second part of the condition is
not neeeded
or what other workaorunds, solutions exist?

On the list only a few people complained about this error, so i wonder if
for other developers the new opencms
works with ms-sql without any problem?

Best wishes,

Andras.