SQL to ColdFusion ORMType Reference

Posted: February 23, 2012 in ColdFusion, SQL

I have not been able to find a good reference chart out there that maps SQL Data Types to ColdFusion ORM Data Types. It’s always really been my best guess. So I’m going to start a reference chart here that as I figure it out I’ll update. If you have any input on this please comment and I will update. Thanks!

ORMType SQL MySQL
big_decimal DECIMAL, MONEY DECIMAL
binary BINARY, VARBINARY TINYBLOB
blob TINYBLOB
Boolean [SMALLINT], BIT BIT
clob LONGTEXT
date DATE DATE
double DOUBLE, MONEY, NUMERIC DOUBLE
character, char CHAR
float REAL, FLOAT FLOAT
integer, int INT INT
long BIGINT BIGINT
serializable TINYBLOB
short SMALLINT SMALLINT
string CHAR, NCHAR, VARCHAR, NVARCHAR VARCHAR
text TEXT, NTEXT LONGTEXT
timestamp DATETIME, SMALLDATETIME, TIMESTAMP DATETIME
true_false CHAR
yes_no CHAR
Comments
  1. Darrin says:

    When I have this code in : property name=”timestamp” column=”timestamp” ormtype=”timestamp” sqltype=”timestamp” getter=”false” setter=”false”;

    My site crashes, I wanted a MS SQL timestamp for data replication, any suggestions?

    • Chris says:

      You may wish to consider renaming the name attribute value. “timestamp” is a reserved word and may not be playing well. Other than that I’m not sure. Are you getting a specific error?

  2. Dave says:

    MYSQL function to translate MySQL data types into Coldfusion ORMtypes, based on your table in the article.

    /* drop and create the function */

    drop function if exists fn_getCfOrmDataType;

    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` FUNCTION fn_getCfOrmDataType(
    mysql_type varchar(50)
    ) RETURNS varchar(50) CHARSET utf8
    begin
    declare orm_type varchar(50);

    CASE (mysql_type)
    when ‘DECIMAL’ then set orm_type = ‘BIG_DECIMAL';
    when ‘TINYBLOB’ then set orm_type = ‘BINARY';
    when ‘TINYBLOB’ then set orm_type = ‘BLOB';
    when ‘BIT’ then set orm_type = ‘BOOLEAN';
    when ‘LONGTEXT’ then set orm_type = ‘CLOB';
    when ‘DATE’ then set orm_type = ‘DATE';
    when ‘DOUBLE’ then set orm_type = ‘DOUBLE';
    when ‘CHAR’ then set orm_type = ‘CHARACTER';
    when ‘FLOAT’ then set orm_type = ‘FLOAT';
    when ‘INTEGER’ then set orm_type = ‘INT';
    when ‘INT’ then set orm_type = ‘INT';
    when ‘BIGINT’ then set orm_type = ‘LONG';
    when ‘TINYBLOB’ then set orm_type = ‘SERIALIZABLE';
    when ‘SMALLINT’ then set orm_type = ‘SHORT';
    when ‘VARCHAR’ then set orm_type = ‘STRING';
    when ‘LONGTEXT’ then set orm_type = ‘TEXT';
    when ‘DATETIME’ then set orm_type = ‘TIMESTAMP';
    when ‘CHAR’ then set orm_type = ‘TRUE_FALSE';
    when ‘CHAR’ then set orm_type = ‘YES_NO';
    else set orm_type = ‘STRING';
    end case;

    return orm_type;
    end$$
    DELIMITER ;

    /* test the function */

    select fn_getCfOrmDataType(‘int’);
    select fn_getCfOrmDataType(‘integer’);
    select fn_getCfOrmDataType(‘char’);
    select fn_getCfOrmDataType(‘jsbfkj’);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s