RE: Informix to Oracle migration

Andrew Hamm wrote: >* their only date is the equivalent of our DATETIME YEAR TO SECOND. So if >you are using plain old DATE, or a DATETIME with a different range, you've >got some problems. INTERVALS are even more distant. Luckily for DATE, their >DATETIME does some moderately helpful shennanigans when the hh:mm:ss is set >to 00:00:00 Oracle 9i does add a TIMESTAMP datatype which does help resolve the DATETIME conversion issues. INTERVALS are still a problem though. David -----Original Message----- From: Andrew Hamm [mailto:ahamm@mail.com] Sent: 08 March 2004 04:00 To: informix-list@iiug.org Subject: Re: Informix to Oracle migration Captain Pedantic wrote: > > Seriously, though, what sort of things should we be asking beyond > these?: FourJs works very well and hasn't let us down. I notice with mild bemusement that Querix are currently asking people to beta-test their "dynamic 4gl" additions - ie compatibility with FourJs BDL, however FourJs have moved a long way further with their Genero product that allows great improvements in the user interface. However, the Genero does have some distinct issues to address since it does make some modest variations on the 4GL "standard" (necessary so as to provide the new nicies), so unless you have sufficient time, it's probably better to convert using FourJs BDL product, and THEN add glamour using Genero in a separate development. * Serials are less of a problem then you might think, since it's not a major problem to use a SEQUENCE and attach it with a trigger. FourJs manage to hide that particular issue very well. ** Oracle has a very small range of datatypes: * their only date is the equivalent of our DATETIME YEAR TO SECOND. So if you are using plain old DATE, or a DATETIME with a different range, you've got some problems. INTERVALS are even more distant. Luckily for DATE, their DATETIME does some moderately helpful shennanigans when the hh:mm:ss is set to 00:00:00 * true "unfixed point" decimals. I hesitate to say floating point because that usually means binary numerics for many people. Oracles DECIMAL always has a fixed point, be it implicit or defined. So if you have any DECIMAL(X) in Informix and you actually rely on the shifting decimal, then you usually need to declare DECIMAL(X*2, X) in Oracle Just To Be Sure. * Don't be tempted by the Optimists Lobby who might try to tell you to convery Informix CHARS to Oracle VARCHARS. Semantically, Informix CHAR = Oracle CHAR, and I-VARCHAR = O-VARCHAR. Neither CHAR = VARCHAR, and only a fool would try to replace one with the other. * true binary numerics - ie FLOAT and DOUBLE PRECISION cannot be found in Oracle. Their numerics are either integral or BCD. Of course, the BCD-based numerics are generally more appropriate for business use, but when people really want a binary representation... * Some Oracle SQL syntax is different, but if you use FourJs their product makes a good effort at mapping most constructs. They also have a conversion document that can be used as a solid guide. If you use rowid's then you need to store them in a char(18), since they are not integral in Oracle. Further, the sqlca member that supplies the last rowid after some statements cannot be used for obvious reasons, but they have supplied the larger stringy Oracle rowid via another mechanism. Best to isolate that in a library routine. Apart from storing rowid's temporarily in variables of a different, there's very little difference. If you ever want to use MS SQL then there are absolutely no rowids; but deal with that only when you have to! NOTE: in all good taste, nobody uses rowids in Informix anyway... sending to informix-list