How to do a FROM_UNIXTIME() in TEIID SQL in a derived table.

0

In Jasperreports Server v5.5.1, Domain Designer, using a Virtual Data Source federating two MySQL Databases, I'm trying to use FROM_UNIXTIME() to convert a unix timestamp.

The Query looks like:

SELECT uid, name, mail, created, access, FROM_UNIXTIME(access) AS access_date FROM JNDI_Community_Replicant.users

The query encountered an error:

com.jaspersoft.commons.semantic.metaapi.MetaDataException: Cannot execute JDBC Query.
org.teiid.jdbc.TeiidSQLException: TEIID30070 The function 'FROM_UNIXTIME(access)' is a valid function form, 
but the arguments do not match a known type signature and cannot be converted using implicit type conversions.

The MySQL Create Table looks like this:

CREATE TABLE `users` (
  `uid` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique user ID.',
  `name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'Unique user name.',
  `pass` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'User’s password (hashed).',
  `mail` VARCHAR(254) DEFAULT '' COMMENT 'User’s e-mail address.',
  `theme` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'User’s default theme.',
  `signature` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'User’s signature.',
  `signature_format` VARCHAR(255) DEFAULT NULL COMMENT 'The filter_format.format of the signature.',
  `created` INT(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for when user was created.',
  `access` INT(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for previous time user accessed the site.',
  `login` INT(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for user’s last login.',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Whether the user is active(1) or blocked(0).',
  `timezone` VARCHAR(32) DEFAULT NULL COMMENT 'User’s time zone.',
  `language` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'User’s default language.',
  `picture` INT(11) NOT NULL DEFAULT '0' COMMENT 'Foreign key: file_managed.fid of user’s picture.',
  `init` VARCHAR(254) DEFAULT '' COMMENT 'E-mail address used for initial account creation.',
  `data` longblob COMMENT 'A serialized array of name value pairs that are related to the user. Any form values posted during user edit are stored and are loaded into the $user object during user_load(). Use of this field is discouraged and it will likely disappear in a future...',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`),
  KEY `picture` (`picture`),
  KEY `login` (`login`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores user data.';

So access is defined as a INT(11). The table is part of the Drupal 7 DB.

What do I have to do to make this work?

djohnson53's picture
103368
Joined: May 25 2012 - 11:10am
Last seen: 4 hours 54 min ago

1 Answer:

0

Although using FROM_UNIXTIME() in this manner works just fine with a JDBC MySQL query, I believe that, because the type relation is typed as INT in the table, and not timestamp, that it is not possible to use the FROM_UNXITIME() TEIID SQL function on it.  Which means that I can't use a virtual data source in this domain.  Refer to the TEIID Reference manual on Type Conversions Explicit and Implicit.

djohnson53's picture
103368
Joined: May 25 2012 - 11:10am
Last seen: 4 hours 54 min ago
Feedback