Re: Informix and Lawson Software

I can't see Jean's message, but piggy-backing on Mark's reply... > Jean Sagi wrote: >> >> I have a 100% 4gl system, that tools, 4Js / Querys are able to run >> the same program against other databases? Oracle or SQl-Server for >> instance? Yes, But. The SQL code embedded in the 4GL must "back off" a bit when it comes to some special Informix syntax. Here's a brief and non-comprehensive rundown based on our experience with 4JS and Oracle and MS SQL Server. We have a few Oracle sites running successfully for a year or three, and I think one or two MS sites went live a little while ago. I'm not sure what we'll have a go with next, probably DB2. Prelude: The 4JS ODI (Open Database Interface) does a lot of work behind the scenes, mapping Informix-style SQL to the target engine. It does a very good job of mapping just about everything that is mechanically mappable, but there are a few things for each target engine that defy sensible automation. For these you need to address it in the 4GL code. 1) Datatypes. MS supports an outrageous number of modern and baroque data types, so there is no problem mapping all Informix data types to MS (actually, I can't really comment on BLOBS or datablade types of types) The only problem with MS is SERIAL columns. MS does have an equivalent without locking problems, but unfortunately it has a very nasty obtuse restriction - you cannot insert your own value into the serial. You cannot even use the ZERO place filler that we have all come to know and love. Fortunately, the 4JS ODI can easily map this if you have used a literal 0 in the code, but if you are inserting from a variable (even if you always assign zero to the variable) then you need to muck around. If you really want to insert your own value into a serial, MS does allow you to do that providing you jump through some hoops of flame with C4 strapped to your body. It's not convenient, and you cannot use the same INSERT statement to sometimes insert 0, sometimes a non-zero value. Oracle has a more restricted set of datatypes. All their numerics* are basically DECIMAL(M,N) so that covers INT, SMALLINT, DECIMAL and MONEY, but you cannot get a DECIMAL with a floating point such as DECIMAL(10) where the number can be 123456789.1, 12345678.91, .... 1.234567891. You will probably have to convert to DECIMAL(20,10) and live with the consequences. I'm not sure they even have a genuine binary-based floating point equivalent of FLOAT and SMALLFLOAT - I have a feeling even these are mapped to a BCD-based representation. *I think - i ain't no oracle expert Oracle doesn't have a flexible time-based data type. The only thing on offer is the equivalent of DATETIME YEAR TO SECOND. Even your Informix DATE types need to be converted to this. Fortunately, the 4JS ODI does a lot of clever work so that your code can still pretend it's working with a DATE despite running against Oracle. We had to do a lot of work to correct many instances of DATETIME YEAR to FRACTION(2) which admittedly were in there due to laziness accepting that as the default. Virtually all of our DATETIME's like that were in fact best designed as YEAR TO SECOND so we got lucky. If you do need advanced DATETIME and INTERVAL's (not supported at all), you's probably got a lot more work ahead of you. MS and Oracle CHAR and VARCHAR are virtually identical semantically, so map directly. Don't be conned by MS or Oracle people telling you that you should convert them all to VARCHAR. Performance is not relevant when the code is broken. Converting CHAR to VARCHAR will break things in hidden places. You have been warned. Oracle doesn't support a SERIAL data type, instead it uses thingies called SEQUENCES which are kinda like number-generating cursors that are stored in the database. There is some odd syntax to either allocate the next number in the sequence, or to access the "current" value that has been assigned to you, but once again, 4JS ODI looks after this. 2) Temp tables. Oracle doesn't support temp tables, but once again, the 4JS ODI does an excellent job emulating this. Assuming the program terminates normally, it will remove all the real tables that have been created to emulate temps. If you get programs crashing out violently (ie so bad that even the ODI shut-down code doesn't get a chance) then you will need some simple procedures to clean out stale temp tables. Their names follow a regular predictable pattern so it's quite easy. MS supports temp tables, but it uses a peculiar syntax where the name of the table has to have a leading # or @ or some such character (I forget which). Once again, the 4JS ODI does a great job hiding this crap from you. There is no problem with abandoned temp tables in MS (assuming the engine will clean up properly...) 3) Isolation, locking and wait modes. The engines mostly all cover the same grounds, but of course with different syntax. 4JS looks after the details. Whether you use DIRTY READ, COMMITTED READ or something more hardcore, you shouldn't have many or any problems. The wait mode in Oracle (or is it the NO WAIT mode?) is supported not by saying SET LOCK MODE TO x but by inserting a special keyword in a special place in the select statements. Sigh. Something similar but of course the opposite happens with MS. 4JS looks after this. Actually, not true. We have one library function who's job is to attempt to lock without wait the target record by pointing an UPDATE CURSOR at it. There is sufficient ugly interaction between transactions, update cursors, wait modes and the syntax specific to each engine that we had to wrap a CASE statement around parts of the code that build the update cursor. Fortunately it was well-isolated in the library, and there was hardly any instance of this in the application code itself. You might not be so lucky. 4) syntax of SELECTS Outer joins, the available operators, nested thingies, string subscripting and so on vary to greater or lesser degrees in the different engines. Some things can be mapped in the 4JS ODI, other things you need to accommodate to. EG for outer joins, it can generally be mapped but there is a warning in the ODI manual that IF you do so and so, then it can't work so blah blah blah. 5) ROWID's. Oracle does support ROWID, but they are CHAR(18). MS does not support ROWIDs. At all. In any form. Deal with it. Primary Key's are a good idea... Summary: There is some work to do depending on the target engine so don't expect an immediate plug-in solution unless the data types you use are limited and your SQL instructions are very conservative. However it is definitely achievable and there will be a far lower cost than the effort of completely re-writing a system in Java and JDBC for example. The 4JS documentation is generally quite good about explaining what you need to look for and how to deal with it, and there's an active user group that will be more than happy to help with any ideas or advice. As for Querix, I don't know it, so sorry, I can't talk it up :-) -- I have a simple philosophy that gets me through life: Never buy cheap toilet paper. Learn to know what is important, and what is not important. - Replies directly to this m