Hi,
I just moved a torque app (v 3.0) from MySQL to SQL Server 2005 this
week. (I'd previously also run this with SQL Server 2000, so I had a
little experience). Some notes...
I downloaded the latest version of the Microsoft JDBC driver. After
some gnashing of teeth, I realized that the driver class and JDBC URL
format have changed from the earlier JDBC. My setup...
torque.dsfactory.broadcast.connection.user = user
torque.dsfactory.broadcast.connection.password = test
torque.database.broadcast.adapter=mssql
torque.dsfactory.broadcast.connection.url =
jdbc:sqlserver://localhost:3249;DatabaseName=broadcast_test
torque.dsfactory.broadcast.pool.validationQuery=SELECT 1
torque.dsfactory.broadcast.connection.driver =
com.microsoft.sqlserver.jdbc.SQLServerDriver
A couple of changes from earlier versions of the JDBC driver:
* In the past the URL has needed ";SelectMethod=cursor" but I left it
off and it seemed to work.
* In the past you need to set SQL server to work with Windows or
user/password authentication. Both are now supported (there's a DLL
to install if you want to use Windows authentication)
* Apparently the port has changed from 1433 to 3249. Maybe it's
better to not even specify this.
And some potential bugs/issues
* When generating SQL with idmethod set to native, the identity column
was not correctly generated. I had to set the autoIncrement="true" on
each individual primary key column.
* Then, in order to insert data for tables with identity columns, I
had to tweak the SQL template as follows. (required in order to
insert a row with an primary key). I'm using an older version of
Torque, not sure if this is exactly applicable
Index: C:/Documents and Settings/wglass/My
Documents/Workspace/internal/broadcast2/build/templates/sql/load/mssql/row.vm
===================================================================
--- C:/Documents and Settings/wglass/My
Documents/Workspace/internal/broadcast2/build/templates/sql/load/mssql/row.vm
(revision
443)
+++ C:/Documents and Settings/wglass/My
Documents/Workspace/internal/broadcast2/build/templates/sql/load/mssql/row.vm
(revision
468)
@@ -1,4 +1,12 @@
+
+#set($hasIdentity = false)
+#foreach($col in $row.ColumnValues)#set($hasIdentity = $hasIdentity
|| $col.Column.autoIncrement)#end
+#if($hasIdentity)
+SET IDENTITY_INSERT $row.Table.Name ON;
+#end
INSERT INTO $row.Table.Name (##
#set ($comma="")#foreach($col in
$row.ColumnValues)$comma${col.Column.Name}#set($comma=",")#end)
VALUES (#set ($comma="")#foreach($col in
$row.ColumnValues)$comma${generator.parse("sql/load/mssql/val.vm", "",
"column", $col)}#set ($comma=",")#end);
-
+#if($hasIdentity)
+SET IDENTITY_INSERT $row.Table.Name OFF;
+#end
WILL
On 1/19/07, Bill Tomlinson <[EMAIL PROTECTED]> wrote:
I've read the faqs and docs, but I can't find a page where the 2005
version of SQL server is explicitly mentioned.
Short question: is SQL Server 2005 supported, and if so, what driver do I use?
Long details:
We have an older application using an older version of Torque and the
2000 version of SQL Server and using an older version of the SQL
Server JDBC driver from Microsoft. For various reasons we want to
switch to 2005.
I changed the connection url to the new database server (with 2005)
and I got this error which, to be honest, doesn't mean much to me:
FATAL ERROR: Problem connecting with database.([Microsoft][SQLServer JDBC Driver
][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) p
rotocol stream is incorrect. Parameter 1 (""): Data type 0x38 is unknown.)
So it's either our application, torque, or the quite old jdbc driver.
On the assumption that it is the jdbc driver, I downloaded the latest
SQL server jdbc driver from Microsoft and tried to run that. But I got
this error:
FATAL ERROR: Problem connecting with database.(Unknown JDBC driver: com.microsof
t.sqlserver.jdbc.SQLServerDriver: Check your configuration file)
Which I understand to mean that the driver isn't one that Torque
supports (Microsoft changed the package for the driver from the older
driver).
I checked the source code for DBFactory, and the trunk HEAD version
doesn't have this driver either. So am I correct that even the latest
version of Torque doesn't support the latest MS jdbc drivers?
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
Forio Business Simulations
Will Glass-Husain
[EMAIL PROTECTED]
www.forio.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
