Informix-SQL "compability"

We are working actually on an migration-project from an AdabasD-application to our IDS Workgroup Server(V10.0) on SuSE (dito V10.0). For all things on the Database-repository we've written successfully sed-Scripts, which migrate the database-schema from Adabas to Informix. The foreign company has unfortunately such SQL-statements inside their application like: "CREATE TABLE copied_struct LIKE template_tab;" The Informix-SQL-Parser dislikes the "LIKE"-keyword, which results in an syntax error (code 201). We're missing such nice feature; is there any possibility to solve this with some built-in function? If not, I assume that we should write an SPL function... The other "big" problem are the insert statements, which sound like that: "INSERT INTO mgp_codeset SET gr_id=99, typ_id=99, gr_text='999';" We've planned to make conditional sourcecode compile instruction inside the integrated development solution. Or is there any other possibility to get such code thru the parser? Much thanks, -Torsten Schmidt- LKV Kiel, Germany

Torsten Schmidt wrote: > > We are working actually on an migration-project from an AdabasD-application > to our IDS Workgroup Server(V10.0) on SuSE (dito V10.0). > > For all things on the Database-repository we've written successfully > sed-Scripts, which migrate the database-schema from Adabas to Informix. > > The foreign company has unfortunately such SQL-statements inside their > application like: > > "CREATE TABLE copied_struct LIKE template_tab;" > The Informix-SQL-Parser dislikes the "LIKE"-keyword, which results in an > syntax error (code 201). We're missing such nice feature; is there any > possibility to solve this with some built-in function? > If not, I assume that we should write an SPL function... IDS can do this using SELECT INTO (use a 1=0 predicate to not copy data as needed) > The other "big" problem are the insert statements, which sound like that: > > "INSERT INTO mgp_codeset SET gr_id=99, typ_id=99, gr_text='999';" Now that's just sick. I don't know of any major RDBMS supporting this syntax. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Serge Rielau wrote: > Torsten Schmidt wrote: >> >> We are working actually on an migration-project from an >> AdabasD-application to our IDS Workgroup Server(V10.0) on SuSE (dito >> V10.0). >> >> For all things on the Database-repository we've written successfully >> sed-Scripts, which migrate the database-schema from Adabas to Informix. >> >> The foreign company has unfortunately such SQL-statements inside their >> application like: >> >> "CREATE TABLE copied_struct LIKE template_tab;" >> The Informix-SQL-Parser dislikes the "LIKE"-keyword, which results in an >> syntax error (code 201). We're missing such nice feature; is there any >> possibility to solve this with some built-in function? >> If not, I assume that we should write an SPL function... > IDS can do this using SELECT INTO (use a 1=0 predicate to not copy data > as needed) Yes, but it seems, that this only goes thru the SQL-Parser, if the target table is temporary. For example: "select * from detail where 1=0 into TEMP detail_neu ;" And under the (sick) Adabas RDBMS the above statement "CREATE TABLE copied_struct LIKE template_tab;" produces permanent tables! > >> The other "big" problem are the insert statements, which sound like that: >> >> "INSERT INTO mgp_codeset SET gr_id=99, typ_id=99, gr_text='999';" > Now that's just sick. I don't know of any major RDBMS supporting this > syntax. I think, you are right! > > Cheers > Serge

You could sort of do this with row types: Define template_tab as a row type then use this to create your tables For Example: CREATE ROW TYPE employee_template ( first_name varchar(30), last_name varchar(35), salary NUMERIC(10,2), bonus NUMERIC(10,2) ); create table employee of type employee_template; create table other_employee of type employee_template;

Torsten Said> >The other "big" problem are the insert statements, which sound like that: > >"INSERT INTO mgp_codeset SET gr_id=99, typ_id=99, gr_text='999';" This isn't standard does Adabas support the standard SQL syntax for the insert statement if it does then convert all of the code to the standard and don't worry about conditional compile.

bozon wrote: > You could sort of do this with row types: > > Define template_tab as a row type then use this to create your tables > > For Example: > > CREATE ROW TYPE employee_template ( > first_name varchar(30), > last_name varchar(35), > salary NUMERIC(10,2), > bonus NUMERIC(10,2) > ); > > create table employee of type employee_template;> > create table other_employee of type employee_template;> I'm curious. Is such a "typed table" just a regular table or does it have special semantics. E.g. in DB2 such a table would have object identifiers etc... Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

I am not famillar with DB2 and I have only played with typed tables in Informix. I thought that typed tables looked like a way to work around Torsten's problem. Instead of initially declaring a template table, he can declare row types and then declare the tables based on these. Informix allows you to create sub-types from rows as : create row type student_template (college_name varchar(64) under employee_template; Looking at the documentation closer typed tables have some "special" properties. You can't alter them to ad columns for one thing. Other limitations and features probably exist.

Serge Rielau said: > Now that's just sick. I don't know of any major RDBMS supporting this > syntax. Oh, the irony... -- Bye now, Obnoxio Information within this post contains forward looking statements within the meaning of Section 27A of the Securities Act of 1933 and Section 21B of the S E C Act of 1934. Statements that involve discussions with respect to projections of future events are not statements of historical fact and may be forward looking statements. Don't rely on them to make a decision. The poster is not a reporting company registered under the Exchange Act of 1934. I have received a life peerage from Her Majesty, who is not an officer, minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The party is going to need financing to continue as a going concern. A failure to finance could cause the party to go out of business. This report shall not be construed as any kind of investment advice or solicitation. You can lose all your money by investing in this party.

Hello Serge, check it out; typed tables are also used in table hierarchies. this is really cool; one can create a hierarchie and select from the basetable. this can produce 'jagged rows' and another nice thing about this is that one can use function overloading with it; so select mymoney(e) from hieracharchie e and have like 3 funcs mymoney which take each a different type and do something different too..called late binding... again check it out See you Superboer afaikr db2 has also table hierarchies; may be the typed stuff is simular to what db2 has in hierarchies. Serge Rielau schreef: > bozon wrote: > > You could sort of do this with row types: > > > > Define template_tab as a row type then use this to create your tables > > > > For Example: > > > > CREATE ROW TYPE employee_template ( > > first_name varchar(30), > > last_name varchar(35), > > salary NUMERIC(10,2), > > bonus NUMERIC(10,2) > > ); > > > > create table employee of type employee_template;> > > > create table other_employee of type employee_template;> > > I'm curious. Is such a "typed table" just a regular table or does it > have special semantics. E.g. in DB2 such a table would have object > identifiers etc... > > Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab

Superboer wrote: > Hello Serge, > > check it out; typed tables are also used in table hierarchies. > this is really cool; one can create a hierarchie > and select from the basetable. this can produce 'jagged rows' > and another nice thing about this is that one can use > function overloading with it; > > so > > select mymoney(e) from hieracharchie e > > and have like 3 funcs mymoney which take each a different type and do > something different too..called late binding... > > > again check it out Yes (I implemented typed views in DB2 :-). My question was not so much about the added capabilities, but rather restrictions. DB2 has similar restrictions on schema evolution on typed tables as IDS it appears. In addition an extra OID column and a hidden type-id column are created and indexed. Because of that I'd not recommend typed tables as a workaround for CREATE TABLE LIKE on DB2... hence by curiosity. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Hello Serge, one can always use the typed stuff; and when it is not needed anymore alter the table and get rid of the type info leaving a normal table. so create the table using the template, load data. then drop the typed stuff, then do alters if needed; the table is a normal table now. > Yes (I implemented typed views in DB2 :-). cool; however in informix it is no view; they are real tables!!! > it appears. In addition an extra OID column and a hidden type-id column > are created and indexed. in informix nope; they are real tables!!! and the info about type is stored in the system catalog; not in the table itself!!! no OID or hidden stuff!!! again check it out!! way cool. See you Superboer.