AppFuseWithSQLServer

2006.08.08 - AppFuse 1.9 with Microsoft SQLServer 2000

1] download JTDS jar

jtds jar and copy them to directory "appfuse/lib/jtds-1.2"

2] Add this to "lib/lib.properties"

#
# JTDS SQL Server - http://jtds.sourceforge.net
#
jtds.version=1.2
jtds.dir=${lib.dir}/jtds-${jtds.version}
jtds.jar=${jtds.dir}/jtds-${jtds.version}.jar

3] in "properties.xml" change this lines:

<!-- Defaults for database.properties -->
<property name="database.jar" location="${jtds.jar}"/>
<property name="database.type" value="jtds:sqlserver"/>
<property name="database.name" value="appfuse"/>
<property name="database.host" value="localhost"/>
<property name="database.username" value="sa"/>
<property name="database.password" value="password"/>

<!-- database URL for creating other dbs - used in db-create target -->
<property name="database.admin.url"
value="jdbc:${database.type}://${database.host}/appfuse"/>
<property name="database.admin.username" value="sa"/>
<property name="database.admin.password" value="password"/>
<property name="hibernate.dialect"
value="org.hibernate.dialect.SybaseDialect"/>
<property name="database.driver_class"
value="net.sourceforge.jtds.jdbc.Driver"/>
<property name="database.url"
value="jdbc:${database.type}://${database.host}/${database.name}"/>
<property name="database.show_sql" value="true"/>

4] Edit "build.xml" line 669

<equals arg1="${database.type}" arg2="jtds:sqlserver"/>

5] Solving common problems

Where should be the problem:

1. You need to run "ant setup-tomcat" to set jdbc connector before deploynment
2. check that appfuse database is created (not possible to create db via jdbc)
3. Check permissions (username and password) of MSSQL db and MSSQL is running
4. Check paths
5. Check if host is set properly. In my posted config I am using localhost
6. Check port of your MSSQL server

In my confiuration is after "ant setup-db" displayed net.sourceforge.jtds.jdbc.Driver at URL: jdbc:jtds:sqlserver://localhost:1433/appfuse

2006.08.08 - Microsoft JDBC Driver also available

This wiki page describes the opensource JTDS driver. In addition to that driver, Microsoft also has its own JDBC Driver for SQL Server. http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en

2003.11.21 - Microsoft SQLServer 2000 & AutoCommit

I had problems with MSSQL and automatic transactions, the solution I have found:
Use manual transaction!
Set auto-commit=false and control yourself the commit and rollback, closing all resources that you've used, including the Connection, preventing dead-lock in database and saving pooled connections. If you'll execute a single query, do the same, manual transaction.

2003.11.19 - AppFuse 0.9 with Microsoft SQLServer 2000

I (D33nar) am having trouble running Appfuse 0.9 as well as struts resume. After successfully compiling and building Appfuse, I can start the application on tomcat and get it running, logon with tomcat/tomcat. The problem arises whenever I try modifying data, the JDBC connection performing the update never finishes and all subsequent JDBC connections are blocked by the update connection. I tried using Microsoft and weblogic drivers for JDBC, but face the same problem.. I have spent significant time and am continuing to debug the problem, but would like to hear from anyone out there who has got Appfuse or struts-resume running on SQLServer.

UPDATE: I have traced the problem to JDBC auto-commit. I put a trace on the SQL sent to the server. A transaction is begun, but never committed, sometimes the driver/pool rolls it back with something like

IF @@TRANCOUNT>0 ROLLBACK TRAN BEGIN TRAN

I am not sure if it is

• the driver implementation (unlikely as I tried 3 different ones bea, ms and inet)
• something do the connection pool used
• transaction semantics in the hibernate
• or the appfuse code

I also found that everthing works fine with auto-commit=true, which is not a solution