Cast a datetime to character in default clause

Hi Y'all. (English REALLY needs a plural "you"!) I have a situation where a commercial software package goes bananas with datetime data types. But it is happy to work with a time stamp if it is character data type. I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today, Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string. Of course the following will not work but it is effectively what I'm trying to do: create_time char(8) default current hour to second, The default value is not compatible with the data type. So I tried to cast it to character: create_time char(8) default (current hour to second)::char(8), # ^ # 201: A syntax error has occurred. A not-unreasonable complaint. BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator. I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column. The documentation on casting does not seem to cover this type of conversion. I would LOVE some ideas here! Thanks much! ------------------------------ Jacob Salomon (Needing a new snarky signature.) ------------------------------

Jacob: I would just bite the bullet and go with an INSERT TRIGGER on that column: CREATE PROCEDURE "art".get_time_str () returning char(8); return (current hour to second)::char(8); end procedure; CREATE TABLE "art".for_jake ( one SERIAL(3) NOT NULL, two CHAR(8) ); CREATE TRIGGER "art".time_for_jake INSERT ON "art".for_jake REFERENCING NEW AS neu FOR EACH ROW WHEN ((neu.two IS NULL ) ) ( EXECUTE function "art".get_time_str() INTO "art".for_jake.two); $ dbaccess testdb -Database selected. > insert into for_jake( one ) values ( 0 );1 row(s) inserted. > insert into for_jake( one ) values ( 0 );1 row(s) inserted. > select * from for_jake;one two 1 14:06:16 2 14:06:21 2 row(s) retrieved. > Database closed. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks much , Art. I did make a couple of changes from your prescription: (If I hadn't, you would wonder if it was really me. :-) WHEN ((neu.two IS NULL ) ) Why the double parentheses? I took off one pair and it still created the trigger just fine. Haven't had the chance to test it out yet, however. You created the procedure but executed function . While experiencing some errors trying to emulate your code I changed to to execute procedure . Since it compiled fine I'll leave it as is. I do note that in your demo code it worked as you coded it. Question: Had you created it as a function, would I have been able to say let neu.creat_time = get_time_str() ? I must also credit Paul Watson for his article on certain errors that my imperfect emulation was generating. He pointed out exactly how I was deviating to cause error: 732: Incorrect use of old or new values correlation name inside trigger.When I get up to the update trigger I'll likely have more to ask. But I'll jump off that bridge when I come to it. (Overconfident fellow, ain't he? ;-) ------------------------------ Jacob Salomon ------------------------------

Jacob: It turns out that a function is just a procedure that returns something. If you look in the sysprocedure table the is-a-function flag is set for that routine even though it was created as a procedure. In some situations it matters how you call it, apparently not from inside c a trigger action. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello Jacob, What about creating the SQL table with a real DATETIME HOUR TO SECOND column, and create a view on top of it, casting the column to CHAR(8) ? create table tab1 ( pkey int not null primary key,tm datetime hour to second default current hour to second ); insert into tab1 (pkey) values ( 101 ); insert into tab1 (pkey) values ( 102 ); insert into tab1 (pkey) values ( 103 ); create view view1 (pkey,tm) as select pkey, cast(tm as char(8)) from tab1; select * from view1; drop view view1; drop table tab1;Seb ------------------------------ Sebastien FLAESCH ------------------------------

Sebastien, That is SUCH a cool idea! I adore the idea of getting around restrictions! However... (You hadda know that was coming. ) I doubt it would pass muster when people want to insert the datetime value in there as a CHAR. And since the underlying table has the datetime type, the commercial software package we are using (confidential), that seems to have a thing against datetime data types, would go bananas, I wouldn't chance it. Grinning at the very idea, Jacob S

Hi Jacob, I think the view would be limited to reads. https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=statement-updating-through-views#ids_sqs_0649 With my suggestion we fall into case - A query that selects calculated or literal values. Still I would not feel comfortable to use a CHAR(8) to store time values. Think about sorting, computing time data with INTERVAL ... Maybe use stored procedures to access the tables from the "commercial app" ? Or blame the provider ;-) Seb ------------------------------ Sebastien FLAESCH ------------------------------

Jacob: I like the VIEW idea. I like it a lot! As for the INSERTS from that software, you can use an INSTEAD OF trigger to map the char input to a DATETIME HOUR TO SECOND in the underlying table! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

This gets wilder and wilder! The INSTEAD trigger! But since I have to use a trigger anyway, I'll just keep it simple: For the sake of my users who requested new columns and the triggers on an existing table. But thanks so much for introducing me to the concept! ------------------------------ Jacob Salomon ------------------------------