MS SQL Server 2000 Support

[Migrated From Jforum.net]

I'm trying to use MS SQL Server 2000 with JForum and I am willing to finish the integration to get it to work so that everyone else can use it as well. However, I need to know where to start. I have gotten the schema created with code from Subversion (in the JForum3). I've read all the old posts about hacks and things people have done but it's all very unclear about what is the most current code.

The latest code from the 2.1.8 release does not seem to work with SQL Server 2000 (maybe it does with 2005). I've currently just quicky thrown it up on a Tomcat instance, but long term it will be on IBM WebSphere.

I'm getting a very generic error returned from the app (c3p0 compontent I believe).

So, a few questions to help me get this figured out (after which I will deploy all the fixes for others)

1) Is there any way to get to any kind of advanced logging so I can see what actually is erroring?

2) What is the current most 'up to date' code for SQL Server 2000 that we currently have?

Here is my error:

root cause

net.jforum.exceptions.DatabaseException: Error while trying to start the database: net.jforum.exceptions.DatabaseException: java.sql.SQLException: Connections could not be acquired from the underlying database!
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:470)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:125)
	at net.jforum.C3P0PooledConnection.getConnection(C3P0PooledConnection.java:137)
	at net.jforum.ForumStartup.startDatabase(ForumStartup.java:78)
	at net.jforum.JForum.init(JForum.java:95)
	at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:879)
	at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:612)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:163)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
	at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
	at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
	at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
	at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
	at java.lang.Thread.run(Unknown Source)
Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLException: Connections could not be acquired from the underlying database!
	... 36 more
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
	... 36 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1159)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:405)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:351)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:466)
	... 34 more

[originally posted on jforum.net by SpuTTer]

[Migrated From Jforum.net]

First problem solved, I just needed to add the jtds-1.2.2.jar jar in my lib and I get a better error now. Now I'm just working on the SQL itself.
[originally posted on jforum.net by SpuTTer]

[Migrated From Jforum.net]

Update on my status. I have it up and running with most functions enabled. When I am done I will post a 2.18 database script file so others can use it to avoid some of the steps I've had to do.
[originally posted on jforum.net by SpuTTer]

[Migrated From Jforum.net]

Hello,

Could you post the changes you made to make jforum work with sql2000 ?

By the way, it's a shame that sqlserver support was made using sql2005 specific functions instead of using sql2000 functions that would have worked on every sqlserver.

I read that hibernate usage was mentionned for jforum3. It would solve these kind of problem easily, and core developpers wouldn't have to deal with db access anymore.

Dom
[originally posted on jforum.net by djeanprost]

[Migrated From Jforum.net]

Sorry for the late reply. Here is a MS Sql 2000 script to create all your tables for 2.18. I'm not all the way done testing but it works for me so far.


[originally posted on jforum.net by SpuTTer]

[Migrated From Jforum.net]

Hello,

Thank you for answering.
For what I've seen, the problem is not on the tables creation, but on the statements used by jforum when running. The specific functions that cause the problem are used in sqlserver.sql, not in sqlserver_dbstruct.sql.

Didn't you meet problems using sql2000 when running (not installing) jforum ?
dom
[originally posted on jforum.net by djeanprost]

[Migrated From Jforum.net]

Yes, indeed I did! I have modified the scripts as well. Again, they may not be 100% working and I currently am using some hacks so I dont change the DAO class files but I can post what I have now, or if you wait a bit, I am planning to do it "the right way" and I'll post that stuff too.

One problem is they are using the 2005 specific commands and another is that the generic sql classes use the LIMIT function instead of TOP which messes up MSSQL.
[originally posted on jforum.net by SpuTTer]

[Migrated From Jforum.net]

The reason for that choice is simple I think (though I may be wrong)

Limit allows to specify the "index from" and "number of" values, TOP ... only gives the top 10 or top 20... but always starting from 0.

To work with top statements one would have to have different dao code for mssql access, which I think was tried to be avoided ...

But that's just a guess.

I tried myself with mssql first. I got about almost all to run, but the attachments still gave me some troubles I think. So in the end I gave up on it and decided to go with the fairly well tested mysql statements... way easier to "update" too ;)
[originally posted on jforum.net by Sid]

[Migrated From Jforum.net]

That would be good if all databases supported the LIMIT operator. MS SQL does not, so it will not work with the generic SQL statements that use it.
[originally posted on jforum.net by SpuTTer]