Hi all! I hope somebody can help me. I have very little hair left, and it is decreasing more rapidly the longer I have this problem :) I am connecting to a MS SQL Server 9.0.2047 from Fedora 7 linux, using freetds, unixodbc and rubyodbc. Most things work fine, just not the following: I have a schema/migration like this: create_table "users", :force => true do |t| t.column "username", :string, :limit => 50, :null => false t.column "password", :string, :limit => 50, :null => false t.column "enabled", :boolean, :null => false t.column "firstName", :string, :limit => 50 t.column "lastName", :string, :limit => 50 t.column "email", :string, :limit => 50 t.column "userService", :integer t.column "accessProfile_id", :integer t.column "salted_password", :string, :limit => 40 t.column "salt", :string, :limit => 40 t.column "security_token", :string, :limit => 40 t.column "token_expiry", :datetime t.column "deleted", :boolean, :default => false, :null => false t.column "verified", :boolean, :default => false end When I run db:schema:load using the sqlserver adapter I get the following DDL (just added newlines for readability): DROP TABLE users CREATE TABLE users ( [id] int NOT NULL IDENTITY(1, 1) PRIMARY KEY, [username] varchar(50) NOT NULL, [password] varchar(50) NOT NULL, [enabled] bit NOT NULL, [firstName] varchar(50) DEFAULT NULL, [lastName] varchar(50) DEFAULT NULL, [email] varchar(50) DEFAULT NULL, [userService] integer DEFAULT NULL, [accessProfile_id] integer DEFAULT NULL, [salted_password] varchar(40) DEFAULT NULL, [salt] varchar(40) DEFAULT NULL, [security_token] varchar(40) DEFAULT NULL, [token_expiry] datetime DEFAULT NULL, [deleted] bit DEFAULT 0 NOT NULL, [verified] bit DEFAULT 0 ) Running this using the SQL Server Management Studio gives the wanted result. Running the same DDL using a migration script either using the migration statements above or using an "execute" call, gives the wrong result. A dump of the table after running using ActiveRecord looks like this: /****** Object: Table [dbo].[users] Script Date: 10/22/2007 13:03:30 ******/ CREATE TABLE [dbo].[users]( [id] [int] IDENTITY(1,1) NOT NULL, [username] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [enabled] [bit] NOT NULL, [firstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL), [lastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL), [email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL), [userService] [int] NOT NULL DEFAULT (NULL), [accessProfile_id] [int] NOT NULL DEFAULT (NULL), [salted_password] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL), [salt] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL), [security_token] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL), [token_expiry] [datetime] NOT NULL DEFAULT (NULL), [deleted] [bit] NOT NULL DEFAULT ((0)), [verified] [bit] NOT NULL DEFAULT ((0)), PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] ************************************* Notice the change of NULL to NOT NULL. ************************************* What am I doing wrong? I dump the SQL in the file /usr/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi all!
I hope somebody can help me. I have very little hair left, and it is
decreasing more rapidly the longer I have this problem :)
I am connecting to a MS SQL Server 9.0.2047 from Fedora 7 linux, using
freetds, unixodbc and rubyodbc. Most things work fine, just not the
following:
I have a schema/migration like this:
create_table "users", :force => true do |t|
t.column "username", :string, :limit => 50,
:null => false
t.column "password", :string, :limit => 50,
:null => false
t.column "enabled", :boolean,
:null => false
t.column "firstName", :string, :limit => 50
t.column "lastName", :string, :limit => 50
t.column "email", :string, :limit => 50
t.column "userService", :integer
t.column "accessProfile_id", :integer
t.column "salted_password", :string, :limit => 40
t.column "salt", :string, :limit => 40
t.column "security_token", :string, :limit => 40
t.column "token_expiry", :datetime
t.column "deleted", :boolean, :default =>
false, :null => false
t.column "verified", :boolean, :default =>
false
end
When I run db:schema:load using the sqlserver adapter I get the
following DDL (just added newlines for readability):
DROP TABLE users
CREATE TABLE users (
[id] int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[username] varchar(50) NOT NULL,
[password] varchar(50) NOT NULL,
[enabled] bit NOT NULL,
[firstName] varchar(50) DEFAULT NULL,
[lastName] varchar(50) DEFAULT NULL,
[email] varchar(50) DEFAULT NULL,
[userService] integer DEFAULT NULL,
[accessProfile_id] integer DEFAULT NULL,
[salted_password] varchar(40) DEFAULT NULL,
[salt] varchar(40) DEFAULT NULL,
[security_token] varchar(40) DEFAULT NULL,
[token_expiry] datetime DEFAULT NULL,
[deleted] bit DEFAULT 0 NOT NULL,
[verified] bit DEFAULT 0
)
Running this using the SQL Server Management Studio gives the wanted
result. Running the same DDL using a migration script either using the
migration statements above or using an "execute" call, gives the wrong
result. A dump of the table after running using ActiveRecord looks like
this:
/****** Object: Table [dbo].[users] Script Date: 10/22/2007 13:03:30 ******/
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[enabled] [bit] NOT NULL,
[firstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[lastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[userService] [int] NOT NULL DEFAULT (NULL),
[accessProfile_id] [int] NOT NULL DEFAULT (NULL),
[salted_password] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL DEFAULT (NULL),
[salt] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[security_token] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL DEFAULT (NULL),
[token_expiry] [datetime] NOT NULL DEFAULT (NULL),
[deleted] [bit] NOT NULL DEFAULT ((0)),
[verified] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
*************************************
Notice the change of NULL to NOT NULL.
*************************************
What am I doing wrong? I dump the SQL in the
file /usr/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb
Any help is greatly appreciated.
Uwe Kubosch
Datek Wireless A/S
Norway
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Uwe Kubosch
2007-Oct-23 11:55 UTC
Re: SQL Server migration result differs depending on client
Hi again! My deepest apologies for answering my own post. However, I would like to share the solution to my problem: There is an option in MS SQL Server 2007 (9.0.2047), "ANSI NULL default", that sets the default nullable state for new columns. This option is default set to "false" which sets new columns to "NOT NULL" if not specified otherwise. When using "Microsoft SQL Server Management Studio", or the RadRails Query view in the Data perspective (uses JDBC) to run DDL, this option is ignored. Running the same exact DDL using ActiveRecord::Base.connection.execute the option is honored, and the resulting table columns are set to "NOT NULL" when not specified. Setting the MS SQL Server option to "true" solved the problem. The problem can also be solved by specifying "NULL" in the DDL. This however must be done in the sqlserver adapter. Sorry for the noise. Uwe Kubosch Datek Wireless AS Norway --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Rick DeNatale
2007-Oct-23 12:24 UTC
Re: SQL Server migration result differs depending on client
On 10/23/07, Uwe Kubosch <uwe-179169uk07tuMpJDpNschA@public.gmane.org> wrote:> > Hi again! > > My deepest apologies for answering my own post. However, I would like > to share the solution to my problem:No apologies necessary. When someone solves their own problem and keeps it to himself, only he learns from the experience. When you answer your own question on a mailing list. We all learn. Thanks! -- Rick DeNatale My blog on Ruby http://talklikeaduck.denhaven2.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
I- Rubyist
2007-Oct-30 10:57 UTC
Re: SQL Server migration result differs depending on client
Rick Denatale wrote:> On 10/23/07, Uwe Kubosch <uwe-179169uk07tuMpJDpNschA@public.gmane.org> wrote: > No apologies necessary. When someone solves their own problem and > keeps it to himself, only he learns from the experience. > > When you answer your own question on a mailing list. We all learn.I completely agree with Rick, I''ve been looking at get an application setup using SQL Server and was encountering the same issue but couldn''t find much information on the solution, until I found your post. So thanks for saving me a lot of time and effort Uwe!! Regards, iRubyist -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---