Re: OnLine Workgroup Server questions

}From: "Jay D. Sullivan" <jsulliva@eag.mhs.compuserve.com> }Date: Tue, 09 Jul 1996 12:29:53 -0700 }X-Informix-List-Id: <news.25917> } }Hello, we have Informix OnLine Workgroup Server version 7.12 for Windows }NT and I've got a few questions which I'm hoping someone can help me }with. } }I'm working on converting a large number of SQL scripts from MS SQL }Server syntax to Informix syntax and there are a few things I'm having }problems with. I should let you know that I'm running these scripts in }the SQL Editor which came with Workgroup Server (SQLEDITOR.EXE). } }1) Is there an "If Then" syntax which I can use in the SQL Editor? I }know that this is available in stored procedures but the SQL Editor }chokes on it. In SQL Server we had a clause similar to the following at }the top of every table creation script: } }if exists (select * from systables where tabname = 'customer' and tabtype }= 'T') }begin } drop table customer; }end; } }Is there something similar I can do in SQL Editor? If not, is there a }way that I can issue a "drop table" command and not have the script }terminate if the table doesn't exist? There isn't a way to do this in standard SQL -- Transact-SQL in Sybase and MS-Access does support this. With Informix, if it was something you might want to do semi-frequently, you could write a stored procedure to do the job. On the other hand, you might simply decide to try dropping the table anyway. DB-Access (which is probably the Unix equivalent of SQLEDITOR) does not stop on errors, so if the table didn't exist, the script would continue anyway. Or maybe you can get (or write) a better command interpreter which can be made to selectively ignore errors. One such program is SQLCMD from the c.d.i archives: continue push; # Preserve current continue mode continue on; # Force it to continue on error drop table customer; # Try an operation prone to failurecontinue pop; # Reinstate prior continue mode (Yes, it's a shameless plug for code I wrote, but it does the job!) }2) In SQL Server, a VARCHAR(10) will let you store all 10 characters. In }Informix, it looks like I can only store 9 characters since Informix uses }one byte to store the length. Do I have to go through the hundreds of }scripts that I have and increase all the VARCHARs by 1 or is there a }simpler way that I'm not thinking of? Informix's VARCHAR(10) will store up to 10 characters, and will occupy up to 11 characters on disk -- Informix does the +1 for you. }3) It's a long story as to why we need to do this but, is there any way }to have a foreign key point to a view instead of a table? If the straight-forward attempt doesn't work, the answer is almost certainly 'No'. What's the long story? Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>