Chris 9:37 pm on February 23, 2012

SQL to ColdFusion ORMType Reference

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

Darrin 3:11 pm on March 26, 2012

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 4:30 pm on March 26, 2012
  
  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?
Dave 3:37 pm on June 25, 2015

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’);
gregoryalexander77 3:22 pm on November 23, 2019

It was bugging me that I could not find the nvarchar as an option in the Adobe ORM documentation or the ColdFusion ORM book by John Whish. Thanks for documenting this!