Re: Informix vs SQL Server questions

I'll try to answer some of your questions: > jsulliva@eag.mhs.compuserve.com (Jay D. Sullivan) writes: > Hello, > > We currently have a large Visual Basic application which uses a > Microsoft SQL Server back end (4.2 or 6.0) via ODBC. We are > considering porting the app to use an Informix back end for a certain > client and I'm trying to get a feel for how difficult this might be. > > We would probably want to use Informix Workgroup Server for NT version > 7.1 for development but the client may end up by using something > different, possibly OnLine Dynamic Server. I assume that this > shouldn't be a problem. It shouldn't, except for some connectivity problems you may have to sort out, and version differences. Informix has added many new facilities even at the sql-level lately. > Also, I assume that we should have no problem with establishing an > ODBC connection from a Windows operating system to either of these > Informix databases over TCP/IP. Not if you use OpenLink or some other high quality ODBC driver. There still seems to be many problems with Informix's own drivers (Informix CLI). > Here are some questions/concerns that we've had when considering > porting the app from SQL Server to other RDBMS's. Remember, we'd need > to do this stuff through ODBC: > > 1) Does Informix support outer joins? How about outer joins to > multiple tables? (DB2 didn't do this, at least not easily). Can we > use ODBC syntax for this? (SQL Server had a problem with using the > ODBC syntax when outer joining multiple tables). Informix does support outer joins and can do sofisticated outer joins between multiple tables. I am not sure about the ODBC syntax, but it should work with any good ODBC driver. That's the point of such a driver to convert from the ODBC syntax to whatever the database engine requires. Informix has it's own syntax for this different from SQL-server. > 2) I see that Informix provides stored procedures and I'm sure that > these can pass back return values. However, can stored procedures > return sql result sets? For example, can we call a stored procedure > and get back results just like as if we had issued a sql select > statement? Yes to all here. > Is there a problem with creating temporary tables in the > stored procedures (just for the life of the procedure)? I haven't tried, but there shouldn't be any such problems. > 3) Does Informix provide a data type similar to SQL Server's TEXT type > which can store a large text string (32K or so would be fine)? Yes, they have a blob type called text for this. The limit is 2 Gb. > Can > this field be searched on with LIKE searches? Unfortunately not. You will have to wait for the Universeal Server due out towards the end of this year to be able to do that. > Are there any special > restrictions with including these fields in a select statement or a > where clause? (We had some problems with Oracle on this issue). They generally can't be in a where clause, but you can of course select from them. Some special handling of the data you get back is needed though. As we haven't started using them yet I don't know how this is done with ODBC. It should be the same with every database, but may not be. > 4) I see that Informix supports insert, update and delete triggers. > We make extensive use of these in SQL Server. Do you know of any > differences between SQL Server's triggers and Informix triggers that > we should be concerned with? Different syntax as far as I know. > 5) We make extensive use of views in SQL Server. We create views > which bring in multiple tables and then we assign access rights to the > view. Are there any big differences between how SQL Server and > Informix handle views? Shouldn't be. I don't know about the updatability of views in SQL Server. In Informix views can only be updated if they are plain selects (no functions or aggregates) on a single table only. > 6) From what I can tell from what I've read, Informix can assign > access rights for a user at the table level (and select rights at a > column level). I didn't see anything mentioned about groups. In SQL > Server we have assigned table access rights to particular *groups*. > Then we assign users to that group to gain access to the tables which > that group has rights to. Does Informix support this scenario? In newer versions (7.x something) there are roles that seem to be about the same. A problem is that you can't assign a user to a role inside the database. You have to do it in your application. Whether that can be done via ODBC I don't know. An alternative is utility programs available from third parties that can assign group level rigths in the database. In the database they are still assigned at the individual user level, but the utility keeps full track of the groups for you, so you don't need to be conserned with the individual users except which group(s) he belongs to. Nils.Myklebust@ccmail.telemax.no NM Data AS, Postbox 9090, Gronland, 0133 Oslo, Norway My opinions are those of my company