Oracle/SQL Server and Informix Equivalent Command

I am very familiar with both SQL Server and Oracle and have been using them for years. But, now I am on a job where I have to do some work with Informix (the accounting system is Prelude, which runs on UniData, which apparently runs Informix). I am having trouble getting the equivalent commands to work or to find them. What I need to do is export some tables and then pull them into SQL Server. Here is the problem. The prelude system does not store the tables as SQL compatible so they created this hokey ODBC dictionary and you have to convert tables 1 at a time to an ODBC view. The views do not resemble tables at all really, what happens is a table that gets converted to a view, you end up with like 20 seperate views that are like 1 view per column in the underlying table (I am using Visual Schema Generator). I can do a SELECT * FROM [table] TO '/home/datadump/tablename.dmp' but, I am confused. I thought I could do a LISTDICT [tablename] but the format is not very clear. I cannot get the format to just give me a dump with no linebreaks so I can pipe it to a text file (the reason being, I have to create an equivalement SQL Server table). I tried CLEAR COLUMN; but that did not correct it. So, my question is this. If I was in Oracle I would issue the following commands SPOOL ON SPOOL '/home/datadump/tablename.dmp' SET HEADING OFF LINESIZE 256 SELECT RTRIM(cField1) || '~' || RTRIM(cField2) (...) FROM sourcetable SPOOL OFF In SQL Server I would create a view then bulk copy the view out. In either case I could do a DESC tablename or an SP_HELP tablename and get the structure. Can someone please help me out and give me the equivalent commands? Thahnks....

Ray M wrote: > I am very familiar with both SQL Server and Oracle and have been using them > for years. But, now I am on a job where I have to do some work with Informix > (the accounting system is Prelude, which runs on UniData, which apparently > runs Informix). I am having trouble getting the equivalent commands to work > or to find them. What I need to do is export some tables and then pull them > into SQL Server. Here is the problem. The prelude system does not store the > tables as SQL compatible so they created this hokey ODBC dictionary and you > have to convert tables 1 at a time to an ODBC view. The views do not > resemble tables at all really, what happens is a table that gets converted > to a view, you end up with like 20 seperate views that are like 1 view per > column in the underlying table (I am using Visual Schema Generator). I can > do a SELECT * FROM [table] TO '/home/datadump/tablename.dmp' but, I am > confused. I thought I could do a LISTDICT [tablename] but the format is not > very clear. I cannot get the format to just give me a dump with no > linebreaks so I can pipe it to a text file (the reason being, I have to > create an equivalement SQL Server table). I tried CLEAR COLUMN; but that did > not correct it. So, my question is this. If I was in Oracle I would issue > the following commands > > SPOOL ON > SPOOL '/home/datadump/tablename.dmp' > SET HEADING OFF LINESIZE 256 > SELECT RTRIM(cField1) || '~' || RTRIM(cField2) (...) > FROM sourcetable > SPOOL OFF > > In SQL Server I would create a view then bulk copy the view out. In either > case I could do a DESC tablename or an SP_HELP tablename and get the > structure. Can someone please help me out and give me the equivalent > commands? Thahnks.... The UNLOAD command in DB-Access is a moderate appoximation to what you seem to need - as others suggested. An alternative that can work (on Unix more easily than WinNT/2K/XP) is SQLCMD from the IIUG Software Archive - http://www.iiug.org/software. output '/home/datadump/tablename.dmp'; -- SPOOL ON; SPOOL ... -- SET HEADING OFF LINESIZE SELECT ...output '/dev/stdout'; The redirection to /dev/stdout works regardless of whether such a device actually exists on your machine - it gets faked when necessary. You might also find the CSV and/or QUOTE formats that SQLCMD supports of use: format csv; format quote; The CSV format uses commas as the delimiter and (double) quotes around the strings and other non-numeric types (DATE, etc). For better or worse, it uses backslash double quote to deal with double quotes within a field value. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

You are running UniData, and although at one time it was owned by Informix (now IBM), that is a separate product which is *not* a relational database - it is a "pick" based database, also called "post-relational" or "multi-value" database. This newsgroup is for Informix, the relational database. You may want to try comp.databases.pick. I have seen some of the answers about unload, dbaccess, etc. but unless I misunderstood your post, I don't think they will work. Hal Maner M Systems International, Inc. www.msystemsintl.com "Ray M" <messier@nichewareinc.com> wrote in message news:ccedda51.0307162011.1c107e7@posting.google.com... > I am very familiar with both SQL Server and Oracle and have been using them > for years. But, now I am on a job where I have to do some work with Informix > (the accounting system is Prelude, which runs on UniData, which apparently > runs Informix). I am having trouble getting the equivalent commands to work > or to find them. What I need to do is export some tables and then pull them > into SQL Server. Here is the problem. The prelude system does not store the > tables as SQL compatible so they created this hokey ODBC dictionary and you > have to convert tables 1 at a time to an ODBC view. The views do not > resemble tables at all really, what happens is a table that gets converted > to a view, you end up with like 20 seperate views that are like 1 view per > column in the underlying table (I am using Visual Schema Generator). I can > do a SELECT * FROM [table] TO '/home/datadump/tablename.dmp' but, I am > confused. I thought I could do a LISTDICT [tablename] but the format is not > very clear. I cannot get the format to just give me a dump with no > linebreaks so I can pipe it to a text file (the reason being, I have to > create an equivalement SQL Server table). I tried CLEAR COLUMN; but that did > not correct it. So, my question is this. If I was in Oracle I would issue > the following commands > > SPOOL ON > SPOOL '/home/datadump/tablename.dmp' > SET HEADING OFF LINESIZE 256 > SELECT RTRIM(cField1) || '~' || RTRIM(cField2) (...) > FROM sourcetable > SPOOL OFF > > In SQL Server I would create a view then bulk copy the view out. In either > case I could do a DESC tablename or an SP_HELP tablename and get the > structure. Can someone please help me out and give me the equivalent > commands? Thahnks.... >