Microsoft SQL Server version

[Migrated From Jforum.net]

Any estimate of when a working Microsoft SQL Server-compatible version of JForum might be ready? I have no flexibility with my choice of database and I really like your product so far.

Thanks.
[originally posted on jforum.net by bcampeau]

[Migrated From Jforum.net]

You can get the latest patches from the CVS and, if you find any issues, fix them and send the patch for us

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Sorry, I need this for a high traffic website. I'd much rather stick with phpBB for now and wait until this has been out and tested a bit more.

Thanks.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

That's fine, it's up to you!

Keep in touch for news about it.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Any progress been made on the MS SQL Version since the last post in April/May?
Thanks Susan
[originally posted on jforum.net by susan grandy]

[Migrated From Jforum.net]

Here's one working and tested option for MS SQL server and jForum 2.1.6.

https://coderanch.com/t/576609 #13551

It's being migrated to 2.1.7 as well (but not finished yet).

In addition, there are various other MS SQL DAO and script files that have been posted. The 2.1.7 Beta/CVS versions also have MS SQL info in it that may be more stable than the postings.

Finally, FWIW, Jforum 3 will be using Hibernate which will support MS SQL. But that version still has a lot of work needed.
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

I setup the Torque setup for mssql and I was able to create the dababase and run the scripts to initialize.

I am getting the following error when I go to the site after running install and manually making the correct edits.

javax.servlet.ServletException: Servlet.init() for servlet jforum threw exception
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754)
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684)
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
java.lang.Thread.run(Unknown Source)


root cause

net.jforum.exceptions.DatabaseException: Error while trying to start the database: java.lang.NumberFormatException: null
java.lang.Integer.parseInt(Unknown Source)
java.lang.Integer.parseInt(Unknown Source)
net.jforum.util.preferences.SystemGlobals.getIntValue(SystemGlobals.java:237)
net.jforum.dao.torque.TorquePooledConnection.makeConfig(TorquePooledConnection.java:198)
net.jforum.dao.torque.TorquePooledConnection.init(TorquePooledConnection.java:99)
net.jforum.ForumStartup.startDatabase(ForumStartup.java:75)
net.jforum.JForum.init(JForum.java:93)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754)
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684)
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
java.lang.Thread.run(Unknown Source)

I have tried two different logins that have admin rigths.



[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

New information

I did a fresh install.

This time I did not try the manual install but did the automatic install
Selected JDBC and torque
setup all configs and I saw the message to edit the jforum-custom.conf file to: database.connnection.implementation = net.jforum.dao.torque.TorquePooledConnection

I made no other edits

Here is the Tomcat error message.




javax.servlet.ServletException: Servlet.init() for servlet jforum threw exception
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754)
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684)
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
java.lang.Thread.run(Unknown Source)


root cause

net.jforum.exceptions.DatabaseException: Error while trying to start the database: org.apache.torque.TorqueException: org.apache.torque.TorqueException: org.apache.torque.TorqueRuntimeException: Runtime error setting property minIdle
org.apache.torque.TorqueInstance.initDataSourceFactories(TorqueInstance.java:374)
org.apache.torque.TorqueInstance.initialize(TorqueInstance.java:158)
org.apache.torque.TorqueInstance.init(TorqueInstance.java:464)
org.apache.torque.Torque.init(Torque.java:151)
net.jforum.dao.torque.TorquePooledConnection.init(TorquePooledConnection.java:110)
net.jforum.ForumStartup.startDatabase(ForumStartup.java:75)
net.jforum.JForum.init(JForum.java:93)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754)
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684)
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
java.lang.Thread.run(Unknown Source)


note The full stack trace of the root cause is available in the Apache


Any help would be appreciated.


[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

Both errors seem to be related to the value being used for the database.connection.pool.min config parameter. The first one is because the jforum SystemGlobal.getIntValue is documented to return a NullPointerException if the value can't be found. So I wrote my code just to trap for this error and use a default value. Obviously, it returns a different exception.

Anyway, make sure this is set to an integer number in your jforum-custom.conf file. Just to be on the safe side, don't include any spaces before or after the = sign. (Shouldn't matter but...)

On my system, I do see log message about minIdle not being supported but it does not die. Some drivers support it and some don't so I left it being set in the TorquePooledConnection.makeConfig method.

Another thing is to make sure you are using the correct common-pool jar. Version 1.2 or greater (1.2 is in the distro). You may have conflicting jars in your shared/lib or common/lib tomcat directories. If you find an older version, I suggest replacing it with the newer version. Shouldn't break any existing code and will let Torque find the latest.

You should check that there are no older version of the other jars as well (especially the common-configuration jar since it's used in initializing Torque).

Also, if you are using SQL 2000 or greater, try taking the ;TDS\=7.0 part of the connection.string. This lets you connect to Version 7.0 and generally doesn't bother 2000 or >. Removing it will make the jdbc driver use the newer version of MS TDS protocol.

FWIW, I've got about a half a dozen one line changes that have been done to fix things between since I released RC1. (None a related to this.) I'll try to get out an official 1.0 with these fixes this weekend.

If none of this works, can you post a sanitized version of your db setting and some details about JVM, TC version, MS SQL version and the like.



[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

Well I tried you suggetions and I am still getting the same error.

Java: jre1.5.0_09
Tomcat: 5.5.17
MS SQL: 2000
Database: jforum_qik
net.sourceforge.jtds.jdbc.Driver: jtds-1.2.jar
Used RC1 of the torque code: all jars exanded correctly.

I tried removing ;TDS\=7.0 fromt he end if the database.connection.string in both files.
Changed database.connection.pool.min to 0,1, and 2
Tried adding database.connection.pool.max=10 to the torque.properties

Here is the sanitized version of jforum-custom.conf and torque.properties

Please note the extra spaces at the end of each file are not in the files on the server. Just showing up in this community.

jforum-custom.conf

#Installation specific configuration options
#Wed Mar 28 13:57:18 GMT-06:00 2007
i18n.board.default=en_US
forum.link=http\://xxxxxx
database.support.autokeys=true
database.driver.name=torque
database.connection.pool.timeout=5000
database.connection.dbname=jforum_qik
installed=true
database.support.subqueries=false
dao.driver=net.jforum.dao.torque.TorqueDataAccessDriver
homepage.link=http\://xxxxxx
mysql.unicode=
database.connection.pool.min=10
database.connection.password=xxxxxx
database.connection.pool.max=10
user.hash.sequence=xxxxxx
database.connection.username=xxxxxx
dbencoding=utf-8
login.authenticator=net.jforum.dao.torque.security.TorqueLoginAuthenticator
database.connection.port=1433
dbunicode=true
database.connection.implementation=net.jforum.dao.torque.TorquePooledConnection
database.connection.host=1xxxxxx
torque.database.adaptor=mssql
database.connection.driver=net.sourceforge.jtds.jdbc.Driver
database.connection.string=jdbc\:jtds\:sqlserver\://${database.connection.host}\:${database.connection.port}/${database.connection.dbname};user\=${database.connection.username};password\=${database.connection.password};TDS\=7.0
mysql.encoding=

torque.properties

#Wed Mar 28 13:57:17 GMT-06:00 2007
database.driver.name=torque
database.connection.password=xxxxxx
torque.database.adaptor=mssql
database.connection.dbname=jforum_qik
database.support.autokeys=true
database.connection.host=xxxxxx
database.connection.pool.min=10
database.connection.implementation=net.jforum.dao.torque.TorquePooledConnection
login.authenticator=net.jforum.dao.torque.security.TorqueLoginAuthenticator
database.connection.username=xxxxxx
database.connection.port=1433
dbencoding=utf-8
database.connection.string=jdbc\:jtds\:sqlserver\://${database.connection.host}\:${database.connection.port}/${database.connection.dbname};user\=${database.connection.username};password\=${database.connection.password};TDS\=7.0
dao.driver=net.jforum.dao.torque.TorqueDataAccessDriver
database.connection.driver=net.sourceforge.jtds.jdbc.Driver



[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

Thank you for the advice.

One more question.

.../config/jforum-custom.conf and .../config/database/torque/torque.properties files to both have the database connection information duplicated?
[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

Think I've got it figured out. There are some condition in which the Torque initialization code will throw a hard error if an initialization property is not supported. The fact that I passed on the minIdle in my initialization can trigger this... I worked with my config combination but failed in yours.

I'll get out a patched version ASAP to fix this. (Hopefully late today).


[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

There is a new version of the Torque DAO available. Hopefully this will fix your problem (and it fixes a few other problems found since the first release, as well).

You can get the full binary and source versions from:

http://http://people.apache.org/~gmonroe/jforum/

To just update from 1.0 RC1 to 1.0, you only need this file:

http://people.apache.org/~gmonroe/jforum/update-1.0RC1-to-1.0/jForum-TorqueDAO.jar

Replace, the RC1 jar of the same name (in your WEB-INF/lib directory) with this one.
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

Great New!

Replaced the jar file and it worked perfectly.

I really appreciate your quick response to this issue and getting it fixed.

Our business needs requires that we have to user MS SQL server in a load balanced web environment.

Thank you

John Parham

[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

New issues

registered one user and when adding a second getting error.

java.sql.SQLException: Violation of PRIMARY KEY constraint 'jforum_users_PK'. Cannot insert duplicate key in object 'jforum_users'.

Now on the forums index page at the top between Forum Index XML and the Forum banner


Method public static net.jforum.entities.LastPostInfo net.jforum.repository.ForumRepository.getLastPostInfo(int) throws java.lang.Exception threw an exception The problematic instruction: ---------- ==> assignment: lpi=fir.getLastPostInfo(forum.id) [on line 41, column 41 in default/forum_list.htm] ---------- Java backtrace for programmers: ---------- freemarker.template.TemplateModelException: Method public static net.jforum.entities.LastPostInfo net.jforum.repository.ForumRepository.getLastPostInfo(int) throws java.lang.Exception threw an exception at freemarker.ext.beans.OverloadedMethodModel.exec(OverloadedMethodModel.java:129) at freemarker.core.MethodCall._getAsTemplateModel(MethodCall.java:93) at freemarker.core.Expression.getAsTemplateModel(Expression.java:89) at freemarker.core.Assignment.accept(Assignment.java:90) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.MixedContent.accept(MixedContent.java:92) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.IteratorBlock$Context.runLoop(IteratorBlock.java:160) at freemarker.core.Environment.visit(Environment.java:351) at freemarker.core.IteratorBlock.accept(IteratorBlock.java:95) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.MixedContent.accept(MixedContent.java:92) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.IteratorBlock$Context.runLoop(IteratorBlock.java:160) at freemarker.core.Environment.visit(Environment.java:351) at freemarker.core.IteratorBlock.accept(IteratorBlock.java:95) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.MixedContent.accept(MixedContent.java:92) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.Environment.process(Environment.java:176) at freemarker.template.Template.process(Template.java:231) at net.jforum.JForum.service(JForum.java:198) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754) at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684) at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.NullPointerException at net.jforum.dao.torque.TorqueForumDAO.getLastPostInfo(TorqueForumDAO.java:385) at net.jforum.dao.torque.TorqueForumDAO.getLastPostInfo(TorqueForumDAO.java:463) at net.jforum.repository.ForumRepository.getLastPostInfo(ForumRepository.java:566) at net.jforum.repository.ForumRepository.getLastPostInfo(ForumRepository.java:582) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at freemarker.ext.beans.BeansWrapper.invokeMethod(BeansWrapper.java:583) at freemarker.ext.beans.OverloadedMethodModel.exec(OverloadedMethodModel.java:111) ... 40 more

If you try to view a topic you get

An error has occurred.

For detailed error information, please see the HTML source code, and contact the forum Administrator.

java.lang.reflect.InvocationTargetException

Using latest version of Torgqu and SQL server 8








[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

OK, found the underlying cause... some of my changes to support my app's SSO component slipped into the general code. My SSO implimentation supplied the user_id and so the table was not defined as an identity field (auto increment's the key as records are added).

First thing to do is to alter the user_id column in the jforum_users table so that it is an identity field. It's easy to do via Enterprise Manager's table design screen (just go to tables, right click on the table and select "Design Table", select the user_id column and set Identity to yes, Identity Seed to 1, Identity Increment to 1.

Next, get an update jar from:

http://people.apache.org/~gmonroe/jforum/update-1.0RC1-to-1.0/jForum-TorqueDAO.jar

Restart everthing. This will at least get you to the proper code. (Sorry!).

There may still be some "artifacts" in the DB that will need to be cleaned up (e.g., you may have a user with an id of 0 and related group membership, posting stuff that will need to be cleaned up). I can walk you thru this if that's the case.




[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

This fix worked.

I appreciate you help.

Will future SQL scripts fix the use_id identity issue?

John

[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

Yes, the public version will have this in the schema creation sql scripts. But I still need to do a full distro build and push it out. Not a big deal but haven't had time to do this and did a quick jar rebuild to get you working.

Thanks for "beta testing" and putting up with the problems.

FWIW, As I mentioned this issue was because I've got two variations of the this DAO. My open-source contributed version and one that has been slightly modified to support tight SSO/Groups/management integration with our main application.

So the "private" DAO version has been extensively tested and is in production use on our design, staging, and external servers. But a some of the bugs that got chased out of the "private" version didn't get transferred to the "public" version.

With the public V1.0, I did a full source comparison between the two versions and reconciled the fixes. This problem was a single attribute in an XML file that slipped thru. I've gone back and double checked, so (touch wood) there should be no more private specific items. So, the public version should be as stable as our private version.

The only place that I'm only 95% sure of is that there may be a few foreign key constraint issues on some of the jForum functions we don't use a lot. When I modeled the DB in Torque's XML Schema format, I followed good DB design practice of identifying all FK relationships.

However, in testing I found a several places that jForum would delete records who's id was a FK to another record or would do an insert with a null/0 FK (and then fix it later). The solution for these was generally to remove the DB constraints from the DB schema and assume that jForum would "do the right thing" (but in some cases leave "orphaned" records behind).

There may be a few of these sort of issues left. Let me know if you run across any.


[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

The content updater just sent me this message.

Many messages that I?ve posted are not actually being displayed by the tool. For example, the ?Announcements? forum is showing only 1 post, although the number of topics is 2. A similar thing happens with ?Reading and Posting Messages?, where 7 messages are being displayed and 9 were posted. ?User Maintenance? has 2 where 4 were posted.

Any ideas?

John

[originally posted on jforum.net by obiweb]

[Migrated From Jforum.net]

Sorry for the delay... was on vacation last week with limited computer access...

I think this may be related to a bug in Version 2.1.6.

In ForumRepository the top of getTotalMessages() should be changed from:

	public static int getTotalMessages(boolean fromDb) throws Exception
	{
		int total = ((Integer)cache.get(FQN, TOTAL_MESSAGES)).intValue();
		
		if (fromDb || total == 0) {

to this:

	public static int getTotalMessages(boolean fromDb) throws Exception
	{
        Integer i = (Integer)cache.get(FQN, TOTAL_MESSAGES);
        
        int total = i != null ? i.intValue() : 0;
        
        if (fromDb || total == 0) {

[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

hi guys, would you mind give me an idea about how soon will the MS SQL version be finished?
[originally posted on jforum.net by cocreate2008]

[Migrated From Jforum.net]

[originally posted on jforum.net by cocreate2008]

[Migrated From Jforum.net]

cocreate2008 wrote:hi guys, would you mind give me an idea about how soon will the MS SQL version be finished?

Sorry for the delay... last week was busy and I was fighting a bad cold too

It depends on what you mean by "done". I'm running my TorqueDAO with MS SQL and V2.1.6 + some bug fixes from CVS in production. So, AFAIK, the TorqueDAO is pretty stable, especially since obiweb helped debug some of the install / code changes. (Not to say that a few don't exists).

However, the "gotcha" here, is that V2.1.6 had some bugs in it if you ran the distribution version. My install has the fixes applied that were in CVS. If you want to go this route, I can probably do a quick compare of the code and generate a bug fix "patch" (e.g., a set of complied classes that update the distribution).

The alternative is to use V2.1.7, which has a pretty well debugged set of native MS SQL DAO queries included. For various reasons, there are not a choice in the installation wizard, but you can follow the manual install and use these. This is not too complicated, just running some SQL scripts, editing the config files, and the like.

FWIW, since I don't use this so I can't assess the possible problems... but then there haven't been many posts about this not working either.
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

Hi,

I am using MS SQL Server as the database, and since 2.1.7 doesn't work with that, I have used 2.1.6 + Torque DAO, as you suggested here.

Could you pls explain how to get Bug Fixes from CVS, beacuse 2.1.6 contains a whole lotta even basic issues like file upload et.al.

Any help would be appriciated.

Thanks,
Aseem
[originally posted on jforum.net by aseem]

[Migrated From Jforum.net]

"If you want to go this route, I can probably do a quick compare of the code and generate a bug fix "patch" (e.g., a set of complied classes that update the distribution)"

I think this would be a real help!!!


Thanks,
Aseem
[originally posted on jforum.net by aseem]

[Migrated From Jforum.net]

This seemed like a good quick lunch time project. So, I did some compares of my code with the 2.1.6 source in CVS. I thought I had a lot of changes but most of them turned out to be specific to what I did to tightly integrate jForum with our application.

Attached is a patch file that fixes the major bugs I found in testing my DAO (plus some minor enhancements, better error reporting).
[originally posted on jforum.net by monroe]