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

= user
= test

=
;DatabaseName=broadcast_test
1
=

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

C:/Documents and Settings/wglass/My
(revision
443)
C:/Documents and Settings/wglass/My
(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 N;
+#end
INSERT INT $row.Table.Name (##
#set ($comma="")#foreach($col in
$row.ColumnValues)$comma${col.Column.Name}#set($co mma=",")#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 FF;
+#end

WILL

1/19/07, Bill Tomlinson <wstomlin (AT) gmail (DOT) comwrote:
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 ERRR: 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.

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 ERRR: Problem connecting with database.(Unknown JDBC driver: com.microsof
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: torque-user-unsubscribe (AT) db (DOT) apache.org
For additional commands, e-mail: torque-user-help (AT) db (DOT) apache.org
--

Bill,

Actually, despite my long email, it wasn't really difficult. I just
needed to use the right JDBC driver, JDBC url, and driver class name.
The other issues had to do with auto-generating the SQL schema.

I did not patch DBFactory. Didn't occur to me. I set the adapter
property to "mssql" and it just worked.

I'm a little surprised the JDBC driver class is hard coded in torque.
(I'm not really a Torque maven). Can some one else comment? Is
setting the adapter a substitute for Torque auto-recognizing the
driver class?

WILL

1/19/07, Bill Tomlinson <wstomlin (AT) gmail (DOT) comwrote:
1/19/07, Will Glass-Husain <wglass (AT) forio (DOT) comwrote:
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

Thanks a lot for your reply. So I guess it can be done with some work.

But it does sound like SQL server 2005 is not an officially supported
DB for the project.

And I'm still confused about the driver issue. The new MS jdbc driver
is not listed in DBFactory, right? see:

The old driver is in there
()

But the new driver isn't
()

Did you have to patch DBFactory? am I misreading the code.

To unsubscribe, e-mail: torque-user-unsubscribe (AT) db (DOT) apache.org
For additional commands, e-mail: torque-user-help (AT) db (DOT) apache.org
--