*
The moose likes JForum and the fly likes SQL Server support and migrating from HSQLDB to MS SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » JForum
Bookmark "SQL Server support and migrating from HSQLDB to MS SQL" Watch "SQL Server support and migrating from HSQLDB to MS SQL" New topic
Author

SQL Server support and migrating from HSQLDB to MS SQL

Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
This is more of an FYI - it seems that there isn't official support for MS SQL Server, but we have to use that, so I thought I'd post the changes we had to make to the SQL Scripts to get things working (the SQLServer scripts that ship have a few syntax errors in them, and a missing column and one missing table).

We also just finished migrating from an HSQLDB based test bench to a live SQL Server, and I thought I'd share the lessons learned from that experience in case anyone else is crazy enough to try this :-)


Migration:

To begin with, the sqlserver scripts on our system were dated july and august of 2007 (JForum 2.1.8 is the install). Your mileage may vary.


Edited sqlserver_db_struct.sql with the following changes:

Remove the following from the definition from the CREATE TABLE for jforum_forums_watch:

,
PRIMARY KEY CLUSTERED
(
[forum_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]


Add a 'topic_moved_id' column to the CREATE TABLE for jforum_topics (shown in color here):

CREATE TABLE [dbo].[jforum_topics](
[topic_id] [bigint] IDENTITY(1,1) NOT NULL,
[forum_id] [int] NOT NULL DEFAULT ((0)),
[topic_title] [varchar](100) NOT NULL,
[user_id] [bigint] NOT NULL DEFAULT ((0)),
[topic_time] [datetime] NULL,
[topic_views] [bigint] NULL DEFAULT ((1)),
[topic_replies] [bigint] NULL DEFAULT ((0)),
[topic_status] [tinyint] NULL DEFAULT ((0)),
[topic_vote_id] [tinyint] NULL DEFAULT ((0)),
[topic_type] [tinyint] NULL DEFAULT ((0)),
[topic_first_post_id] [bigint] NULL DEFAULT ((0)),
[topic_last_post_id] [bigint] NOT NULL DEFAULT ((0)),
[topic_moved_id] INT DEFAULT 0,
[b] [moderated] [int] NULL DEFAULT ((0)),


add the following between the CREATE NONCLUSTERED INDEX blocks for topic_last_post_id on jforum_topics and user_id on jforum_topics:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[jforum_topics]') AND name = N'topic_moved_id')
CREATE NONCLUSTERED INDEX [topic_moved_id] ON [dbo].[jforum_topics]
(
[topic_moved_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO


Change the CREATE TABLE for jforum_moderation_log so it reads as follows (there were a number of typeos and an incorrect data type):

CREATE TABLE [dbo].[jforum_moderation_log] (
[log_id] [bigint] IDENTITY(1,1) NOT NULL,
[user_id] [bigint] NOT NULL,
[log_description] TEXT NOT NULL,
[log_original_message] TEXT,
[log_date] DATETIME NOT NULL,
[log_type] [tinyint] DEFAULT ((0)),
[post_id] [bigint],
[topic_id] [bigint],
[post_user_Id] [bigint],
PRIMARY KEY CLUSTERED
(
[log_id] ASC
) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]



Once those changes are made to the sql script, you should be able to execute it against the MS SQL Server database, and get a schema constructed without errors.


I then edited WEB-INF\config\jforum-custom.conf, removing all database.* settings except for the following (and changed their values as shown):

database.driver.name=sqlserver
database.connection.implementation=net.jforum.PooledConnection


And edit the WEB-INF\Config\database\sqlserver\sqlserver.properties to provide correct servername, etc...


I also made sure that the jtds jar file (this is a good JDBC driver for MS Sql databases) was available in the classpath (we use this driver for multiple tomcat instances, so we have it in the global Tomcat lib folder).


Now we are ready to migrate the data from HSQLDB...


Create a data script using SQuirrelL SQL Client (or a tool of your choosing - that was just handy)
Remove \n strings from the script, replacing them with empty strings (HSQLDB uses these as newline delimeters for multi-line text blobs. SQL Server doesn't).
Add 'SET IDENTITY_INSERT <tablename> ON' and 'SET IDENTITY_INSERT <tablename> OFF' commands around tables that have identity columns (google 'Set identity_insert' for more info on this)
Execute the data insertion script in SQL Server Management Studio (or Query Analyzer, or whatever)
Do a text search for 'msg' in the results to make sure there were no errors


Finally, bring up the jforum instance, and test it out. For us, that was all that was required...
[originally posted on jforum.net by trumpetinc]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Good work. This should be checked into CVS.

Here is the complete file after his changes are done.
[originally posted on jforum.net by SpuTTer]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
I think u should use a third party help to resolve this problem, i use dbload to solve it when i was migrated my data, it can migrate almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. i found it on google search.


Download Free : http://www.dbload.com
[originally posted on jforum.net by wonn1377]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Server support and migrating from HSQLDB to MS SQL