mssql: data type conversion

General Discussion
1

Hi,

I’m not sure if this is a bug, or on purpose. When I create a changelog from a mssql schema one table contains a float field. It shows up as below:

This is in fact a float w/ col size of 15.

When I apply this generated changelog via update to a new empty schema, the float is converted to a real. The message printed is below:

Changed Columns:
    segments.value
          from FLOAT(15) to real(7,0)

This seems like a bug.

Thanks,
Brendan

2

It is probably related to how mssql calls data types compared to how you can pass them in.  FLOAT(15) is what you wrote, and real(7,0) is what it reads from the database with a generateChangeLog, correct?

Nathan

3

It may also be a datatype aliasing mssql does.

Nathan

4

Hi Nathan,

Thanks for all the timely replies. Much appreciated!

To be clear, the FLOAT(15) is what is defined by me in the source db. I then run a generateChangeLog against that db, and the resulting changelog contains the FLOAT(15) defiinition. It gets converted to real(7,0) when I use that changelog to run an update against a different db. So either liquibase update converts it, or mssql converts it when creating column.

Brendan

5

I don’t see anything in the liquibase code that would do the conversion.  It must be mssql.  According to  http://msdn.microsoft.com/en-us/library/ms173773.aspx, float is an alias for real, and float(15) would be comparable to a real with precision of 7.  I don’t know if the (7,0) parameters are correct for mssql or not.

Are you getting errors or unable to enter decimal values with a real(7,0) column?

Nathan

6

I’m not seeing any problems with the new definition. I guess I’m concerned that the translation from an mssql schema to an mssql schema changes the data type. This seems odd to me.

Brendan

7

It won’t actually be changing the type.  Many databases use aliases to support specifying data types in different ways (for cross-database and backwards compatibility reasons), but they actually translate the different aliases into the “real” types at execution time and the real type is what you get back as metadata on the column.  If you use the “real” type directly there is no difference in what the database sees or uses.

If you already have the column definition created in a changeset, why does it matter what the generateChangeLog is producing?  Why are you not using the original changeset against all databases?

Nathan

8

This makes sense Nathan. I’m just starting to use liquibase, and I am starting on a pre-existing schema…so the changeset I have was generated by generateChangeLog, not written to produce the original schema. So this is why I wanted to understand why the alias used to represent a “FLOAT” changed to “real”. I wonder if the reverse is true…if I use a “real” in changeset, will I get a “float” in db :wink:

Thanks for your help!
Brendan