This HowTo contains configuration help on using MS SQL Server with Torque. Contributions towards updating the information below can be submitted to the Torque Dev mailing list.
Use the SQL Server Enterprise Manager to create the database. When using the TDK, it helps to name the database the same name as your project. If the name is not the same, you will need to manually change the database name in your properties files. After the database is created, you will need to create a new user, or give an existing user permissions to the new database. Create the new user with SQL Server Authentication, and assign the users default database to the newly created database. Make sure the database user has the db_owner role. Please refer to SQL Server documentation for further details.
There is an excellent open source driver for MS SQL called jTDS. This driver is being used in production mode with Torque and many other JDBC applications. The information in this HowTo has been verified with this driver, other drivers should work the same, but your milage may vary.
In addition, Microsoft has a JDBC driver for MS SQL Server available from their SQL Downloads page. Please not that this driver is limited to MS SQL 2000 and above and will not work with 7.0.
Alternatively, there are a wide variety of third party commercial drivers available. See JDBC(TM) Technology - Drivers for a comprehensive list.
Finally, for completeness, there is the JDBC-ODBC bridge driver from Sun that is included with the Java 2 SDK Standard Edition as package sun.jdbc.odbc. Sun's statement on this driver is, "Note that the bridge driver included in the SDK is appropriate only for experimental use or when no other driver is available." So use at your own risk. Also, if you are using a non-windows box to run Torque, you will need to find ODBC drivers for SQL server for that platform. For UNIX environments, you can use unixODBC. Please follow the ODBC driver instructions and setup a DSN for the new database. You must uncheck the box that says, "Use ANSI nulls, paddings and warnings.".
In the build.properties file make sure that:
torque.database = mssql torque.database.type = mssql
And set the JDBC connection information:
torque.database.createUrl = [Follow JDBC Driver Docs] torque.database.buildUrl = [Follow JDBC Driver Docs] torque.database.url = [Follow JDBC Driver Docs] torque.database.driver = [JDBC Driver class] torque.database.user = [db username created above] torque.database.password = [db user password] torque.database.host = [hostname] torque.database.name = [name of db created above] torque.database.schema = [db username created above]
If you are using the jTDS driver, these settings would look like the entries below ( (replace [hostname] and [db name]!) Note that if you are using MS SQL 7.0, you should add ;TDS=7.0 after the [db name]:
torque.database.createUrl = jdbc:jtds:sqlserver://[hostname]:1433/template1 torque.database.buildUrl = jdbc:jtds:sqlserver://[hostname]:1433/[db name] torque.database.url = jdbc:jtds:sqlserver://[hostname]:1433/[db name] torque.database.driver = net.sourceforge.jtds.jdbc.Driver
If you are using the Microsoft driver
(i.e. com.microsoft.jdbc.sqlserver.SQLServerDriver),
you'll need to use the SelectMethod=cursor parameter in
the JDBC connection string (as outlined by this
JDC thread). Your databaseUrl would look something
like the following:
jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb;SelectMethod=cursor
If you are using the JDBC-ODBC bridge driver, see the Sun documents related to this for the values to use.
SQL Scripts generated by the Ant sql target do not contain the fully qualified table names, e.g. user.table. When manually running these scripts, do this as the user you want to own the tables. I.e., if you run it as 'SA', your tables will be created as dbo.table rather than user.table. This can lead to problems in determining which table gets used. General rule of thumb is that the tables should be owned by the user you connect with.
SQL Scripts generated by the datasql Ant targetData may include autoincrement fields that MS SQL will not allow to be set via SQL unless surrounded the following statements:
SET IDENTITY_INSERT <table> ON SET IDENTITY_INSERT <table> OFF
In addition, the order does not take foreign keys into consideration. The order in which tables are populated may need to be manually changed to insure the "key" tables get created first.
Beware the use of MS SQL keywords in table and column names. This will cause problems in the generated SQL.
MS SQL supports defaultIdMethod="native" attribute of the database tag for autogenerated fields.
The Ant jdbc target will generate an almost correct schema for all tables owned by the SQL user specified in the torque.database.schema property. If this property is not set, no tables will be processed.
The schema.xml file generated by the jdbc target will be missing various important items, like autogenerated field tags, unique field specifications, and index definitions. In addition, the order of the tables may need to be redone to create the tables with key fields first. But in general, the jdbc task is a good way to use GUI tools to create your table structure and then extract the basics to Torque XML.
There are various ways to set up your runtime torque.properties file. The easiest is to use the build.xml process in the master directory of runtime package. Just make sure that the generation build.properties are in your user.home directory and run ant in the master directory. A properly configured Torque.properties should be created in the directory above this one.
If you are configuring the properties manually, the folling settings are MSSQL specific. Note that there are several generic settings you will need to look at as well.
torque.database.default=[data base ref] (e.g. default, or the project name) torque.database.wacs.adapter=mssql torque.dsfactory.[data base ref].connection.driver = [JDBC Driver class] torque.dsfactory.[data base ref].connection.url = [Follow JDBC Driver Docs] torque.dsfactory.[data base ref].connection.user = [db username created above] torque.dsfactory.[data base ref].connection.password = [db user password]