AppFuseWithSQLServer

This is version 14. It is not the current version, and thus it cannot be edited.
[Back to current version]   [Restore this version]

2005.07.27 - AppFuse 1.8 with Microsoft SQLServer 2000

1] download JDTS jar

jdts jar and copy them to directory "appfuselib/jdts"

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

#
# MS SQL SERVER
# http://jasigch.princetonedu:9000/display/UPC/MSSQL+with+the+JTDS+JDBC+Driver
#
mssql.version=1.1
mssql.dir=${lib.dir}/jtds
mssql.jar=${mssql.dir}/jtds-${mssql.version}.jar

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

<!-- Defaults for database.properties -->
<property name="database.jar" location="${mssql.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 -->
<!--pro MSSQL nelze vytvorit databazi -->
<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}:1433/appfuse"/>
<property name="database.show_sql" value="true"/>

4] in build.xml change

<property name="operation" value="CLEAN_INSERT"/>

to

<property name="operation" value="MSSQL_CLEAN_INSERT"/> 

5] in database.properties change hibernate dialect

hibernate.dialect=org.hibernate.dialect.SybaseDialect

6] 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

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