Topic
  • No replies
sugnangus
sugnangus
1 Post

Pinned topic Informix Create Table Issue with DEFAULT DATETIME

‏2014-03-19T17:56:22Z |

I am working with a data model to be deployed on an  Informix 11.9 Server.  I am having problems with Data Studio DDL formatting a create table statement that will execute properly during the build., I am not sure if I am doing something wrong, or this is just a bug as I have only been using the application for about two weeks.  I have posted the complete create table statement that Data Studio created below.  

Data Studio created this statement, "crte_on_tms DATETIME YEAR TO SECOND DEFAULT DATETIME (CURRENT)  YEAR TO SECOND NOT NULL," which is incorrect syntax for Informix.  In order for the statement to execute properly, it must look like this,  "crte_on_tms DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,".

This is the only real problem that I have encountered using Data Studio so far, although it prevents me from using the Generate DDL directly to run on the server as it errors out on each of the DEFAULT DATETIME statements, and fails to build the table.  This requires me to produce the DDL in text format, edit the text and then build the database the old school way using dbaccess.  The default user statement works correctly as well as several other defaults that I set for integer and text in the schema.

Thanks in advance for any help that you can give me.

Best Regards,

Steve Johnson

CREATE TABLE data_type (
        data_type_cde VARCHAR(10) NOT NULL,
        crte_by_txt VARCHAR(25) DEFAULT USER NOT NULL,
        crte_on_tms DATETIME YEAR TO SECOND DEFAULT DATETIME (CURRENT)  YEAR TO SECOND NOT NULL,
        mod_by_txt VARCHAR(25) DEFAULT USER NOT NULL,
        mod_on_tms DATETIME YEAR TO SECOND DEFAULT DATETIME (CURRENT)  YEAR TO SECOND NOT NULL
    )
IN dataspace2 LOCK MODE ROW;