[opencms-dev] SQL Server Install

[eva]

Here is how I got it to work with SQL Server:

(the assumption is that you've got your war deployed, and you can run 
ocsetup from your browser.

I used the the free microsoft JDBC driver (only works with SQL 2000 though).
I put the driver files in the Tomcat's common\lib (msutil.jar, 
mssqlserver.jar, msbase.jar)

I had to change all references to merant 
in  webapps\opencms\WEB-INF\config\opencms.properties
to microsoft.  i.e.

com.microsoft.jdbc.sqlserver.SQLServerDriver
jdbc:microsoft:sqlserver


Then when you run the setup, it still puts merant in the sql server 
connection string during the setup- make sure you change that
to microsoft.  You have to give it the username of the sa db user, and for 
the opencms login, it will
create a database of the name you enter, and a user of the same name (i.e. 
opencms, with user opencms).
Another thing, if your setup fails after the database and or/ user have 
been created, you'll have to go into
SQL server and delete both, or it will give an error when you try to re-run 
it.  So _don't_ create a database or user
in SQL server before you begin!

Once you have the database set up, you can log into CMS with user Admin/admin.
However, there is another bug you have to take care of.

You have to modify the mssql query.properties file in opencms.jar , or 
you'll keep getting an error message about "Invalid parameter binding" when you
try to do things within the content manager.

To do this, you have to delete a line in  the com\opencms\file\mssql 
query.properties file  within the
\webapps\opencms\WEB-INF\lib\opencms.jar.  I'm not sure whether this fix 
has no harmful effects of some kind,
but it's the only way I've gotten it to work.

Here are instructions I found in this mailing list archive for doing it - 
just follow the directions to remove that line - just make sure that you 
get the query.properties back into the
jar with the correct path, i.e.  com\opencms\file\mssql , or it won't work.

 > I traced down the problem to be in the file:
 > com/opencms/file/mssql/query.properties at key
 > C_RESOURCES_GET_FILESINFOLDER
 >=20
 > 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,=20
 > 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=3D?  \
 > AND CMS_RESOURCES.RESOURCE_NAME LIKE
 > RTRIM(CMS_PROJECTRESOURCES.RESOURCE_NAME)+'%' \
 > AND CMS_RESOURCES.RESOURCE_TYPE <> 0 \
 > AND CMS_PROJECTRESOURCES.PROJECT_ID =3D ?  \
 > ORDER BY CMS_RESOURCES.RESOURCE_NAME
 >=20
 > As you can see the prepared statment requires 2 parameters=20
 > but only one is
 > set in the source code, so we removed
 > "AND CMS_PROJECTRESOURCES.PROJECT_ID =3D ?" part.
 > The query found in the other file:
 > com/opencms/file/genericSql/query.properties
 > seems to be correct and has only one parameter.
 >=20
 > After rebuilding the jar file opencms worked without errors.


After following these steps, everything seems to work, except for the weird 
triplicate issue.I bet there's a sql statement error somewhere that's 
causing it...  If anybody has found it,
please share!

-eva


At 8/28/2003 09:47 AM, you wrote:

>Eva,
>
>I'm attempting in install OpenCms 5.0 using the Merant driver and mssql.
>I'm encountering a lot of problems with the install, all of them dealing
>with the mssql server.  I was wondering if you had the same problem and if
>so what you did to get around them? what driver you ended up using? Your
>response would help me out a lot.
>
>Thanks.
>
>Dean Bradley
>Information Systems
>Applications Development
>________________________________________
>
>"Ars Gratia Artis"
>
>
>
> 
>
>                       eva 
>
>                       <kaniasty at yahoo.co        To: 
> opencms-dev at opencms.org
>                       m>                        cc: 
>
>                       Sent by:                  Subject:  [opencms-dev] 
> odd behavior
>                       opencms-dev-admin@ 
>
>                       opencms.org 
>
> 
>
> 
>
>                       08/28/2003 
> 09:35 
>
>                       AM 
>
>                       Please respond 
> to 
>
>                       opencms-dev 
>
> 
>
> 
>
>
>
>
>
>
>I just installed opencms,  and I'm seeing some weird behavior.
>I added an image to an image gallery, and a link to the link gallery.
>When I go to view either of them, the images/links show up in triplicate.
>
>I am using MSSql.
>
>Anybody know if this is a known bug, and how to fix it?
>
>Thanks.
>
>-eva
>
>
>_______________________________________________
>This mail is send to you from the opencms-dev mailing list
>To change your list options, or to unsubscribe from the list, please visit
>http://mail.opencms.org/mailman/listinfo/opencms-dev
>
>
>
>
>
>_______________________________________________
>This mail is send to you from the opencms-dev mailing list
>To change your list options, or to unsubscribe from the list, please visit
>http://mail.opencms.org/mailman/listinfo/opencms-dev