DATETIME format again

Hello everybody, I'm trying to use a client with a little bit weird time format. The format I want to use is %Y%m%d %H%M%S (ex. '19991231 235959'). Setting GL_DATETIME in this way leads to the desired result on printing. But trying to insert a value of '235959' into a field in the interval HOUR TO SECOND with exact the same content of this environment variable gives sql error -1261. Inserting colons into the literal ('23:59:59') makes everything fine. But my data source uses this time format above. How can I get rid of these colons ? The informix server that I use is 7.30.U IDS on Linux. Thank you all, Herbert Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

altpeter_rueb@my-deja.com wrote: > > Hello everybody, > I'm trying to use a client with a little bit weird time format. The > format I want to use is %Y%m%d %H%M%S (ex. '19991231 235959'). Setting > GL_DATETIME in this way leads to the desired result on printing. But > trying to insert a value of '235959' into a field in the interval HOUR > TO SECOND with exact the same content of this environment variable > gives sql error -1261. > Inserting colons into the literal ('23:59:59') makes everything fine. > But my data source uses this time format above. How can I get rid of > these colons ? > The informix server that I use is 7.30.U IDS on Linux. If this is an ESQL/C or 4GL program you can use the ESQL/C function dtcvfmtasc() which permits you to specify a conversion format. The function will convert the string into a DATETIME in your code and then you can insert the DATETIME directly asving the engine from having to do it. For a 4GL program just write a 4GL callable ESQL function to wrap the dtcvfmtasc() call. Art S. Kagel

Hi Art, thank you for you answer. The language that I use is Perl (DBD::Informix) so that I have no idea how to implement this wrapping you are proposing. By the way, I encountered the same behaviour of Informix when using dbaccess. H.Altpeter-R'b In article <375D9206.11F16340@bloomberg.net>, kagel@bloomberg.net wrote: > > > altpeter_rueb@my-deja.com wrote: > > > > Hello everybody, > > I'm trying to use a client with a little bit weird time format. The > > format I want to use is %Y%m%d %H%M%S (ex. '19991231 235959'). Setting > > GL_DATETIME in this way leads to the desired result on printing. But > > trying to insert a value of '235959' into a field in the interval HOUR > > TO SECOND with exact the same content of this environment variable > > gives sql error -1261. > > Inserting colons into the literal ('23:59:59') makes everything fine. > > But my data source uses this time format above. How can I get rid of > > these colons ? > > The informix server that I use is 7.30.U IDS on Linux. > > If this is an ESQL/C or 4GL program you can use the ESQL/C function > dtcvfmtasc() which permits you to specify a conversion format. The > function will convert the string into a DATETIME in your code and then > you can insert the DATETIME directly asving the engine from having to > do it. For a 4GL program just write a 4GL callable ESQL function to > wrap the dtcvfmtasc() call. > > Art S. Kagel > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

The function is in the Informix ESQL libraries it should be possible to link that library into your Perl so the function is directly callable. As to the 4GL wrapper function, if you are also using I4GL or R4GL the manuals have a fairly good explanation of how to do this and there are several example functions delivered in the /samples sub-dir as well as many samples in hte IIUG Software Repository. This is not difficult. Art S. Kagel altpeter_rueb@my-deja.com wrote: > > Hi Art, > thank you for you answer. The language that I use is Perl > (DBD::Informix) so that I have no idea how to implement this wrapping > you are proposing. > By the way, I encountered the same behaviour of Informix when using > dbaccess. > > H.Altpeter-Rüb > > In article <375D9206.11F16340@bloomberg.net>, > kagel@bloomberg.net wrote: > > > > > > altpeter_rueb@my-deja.com wrote: > > > > > > Hello everybody, > > > I'm trying to use a client with a little bit weird time format. The > > > format I want to use is %Y%m%d %H%M%S (ex. '19991231 235959'). > Setting > > > GL_DATETIME in this way leads to the desired result on printing. But > > > trying to insert a value of '235959' into a field in the interval > HOUR > > > TO SECOND with exact the same content of this environment variable > > > gives sql error -1261. > > > Inserting colons into the literal ('23:59:59') makes everything > fine. > > > But my data source uses this time format above. How can I get rid of > > > these colons ? > > > The informix server that I use is 7.30.U IDS on Linux. > > > > If this is an ESQL/C or 4GL program you can use the ESQL/C function > > dtcvfmtasc() which permits you to specify a conversion format. The > > function will convert the string into a DATETIME in your code and > then > > you can insert the DATETIME directly asving the engine from having to > > do it. For a 4GL program just write a 4GL callable ESQL function to > > wrap the dtcvfmtasc() call. > > > > Art S. Kagel > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.

altpeter_rueb@my-deja.com wrote: > [...] The language that I use is Perl (DBD::Informix) so that > I have no idea how to implement this wrapping you are proposing. > By the way, I encountered the same behaviour of Informix when using > dbaccess. >kagel@bloomberg.net wrote: > > altpeter_rueb@my-deja.com wrote: > > > I'm trying to use a client with a little bit weird time format. > > > The format I want to use is %Y%m%d %H%M%S (ex '19991231 235959'). > > > Setting GL_DATETIME in this way leads to the desired result on > > > printing. But trying to insert a value of '235959' into a field > > > in the interval HOUR TO SECOND with exact the same content of > > > this environment variable gives sql error -1261. > > > Inserting colons into the literal ('23:59:59') makes everything > > > fine. But my data source uses this time format above. How can I > > > get rid of these colons ? > > > The informix server that I use is 7.30.U IDS on Linux. > > > > If this is an ESQL/C or 4GL program you can use the ESQL/C function > > dtcvfmtasc() which permits you to specify a conversion format. The > > function will convert the string into a DATETIME in your code and > > then you can insert the DATETIME directly asving the engine from > > having to do it. For a 4GL program just write a 4GL callable ESQL > > function to wrap the dtcvfmtasc() call. OK; DBD::Informix is suposed to be discussed primarily in the dbi-users@isc.org mailing list, but this isn't a bad place to discuss things either. Just make it clear what you are talking about. It appears that you have a system where the input and display format for the users are at odds with that used by Informix. However, because you're dealing with Perl, you're also dealing with a premiere string chopping language (pace SNOBOL fans). I'd expect to write two simple Perl subs to convert from Informix to Client format and back again (code unindented to fit on one line): sub ix_client() { my ($in) = @_; $in ~= s/^(\\d\\d\\d\\d)-(\\d\\d)-(\\d\\d) (\\d\\d):(\\d\\d):(\\d\\d)$/$1$2$3 $4$5$6/; return $in; } sub client_ix() { my ($in) = @_; $in ~= s/^(\\d\\d\\d\\d)(\\d\\d)(\\d\\d) (\\d\\d)(\\d\\d)(\\d\\d)$/$1-$2-$3 $4:$5:$6/; return $in; } Untested, but might conceivably work unless I should be using something other than \\d to match digits (I'm working from memory, in case you hadn't guessed). This method does require you to know when you're dealing with a DATETIME YEAR TO SECOND field, of course, and doesn't readily scale if you have many other formats to worry about. As for using GL_DATETIME, I've not got any detailed experience of this. You'd need to look in the dbdimp.ec code to find out where DBD::Informix converts strings into DATETIME values. That code is probably not using the correct conversion routine (given that it doesn't work). Without knowing what the correct conversion routine is (and I'm at home so I don't have the manuals at hand), I'm not sure which routine should be used - I'd expect the code to use dtcvasc() or perhaps dtcvfmtasc(). If it is using dtcvasc(), then that routine should take care of GL_DATETIME. If it is using dtcvfmtasc(), which is more likely, then the format string presumably needs to be manufactured, and maybe it should accommodate GL_DATETIME. However, I have a fundamental conceptual problem with GL_DATETIME and the predecessor DBTIME environment variables; how is a single env var meant to be used with DATETIME YEAR TO SECOND, DATETIME YEAR TO DAY and DATETIME HOUR TO FRACTION(5) all in a single program? I don't know, and there is no documentation to assist. And, as far as I am concerned, I think the answer is "It can't", so my code ignores the variables for lack of anything better to do. So, any changes you make will either be specific to you, or will need to be carefully implemented and properly motivated for inclusion in the main code stream. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>