Re: sql server -> informix Answered: green (solid confidence) — Core question -- can a SERIAL column be omitted from the INSERT column list like a SQL Server identity column -- is answered definitively ('No'); asker's own follow-up shows they'd already reached the same conclusion (only a view-based workaround exists, itself then correctly shown to break SELECTs).

Advisory only.

Posted in 2003

Summary

Moving from SQL Server, rkusenet asked if a serial column can be omitted from an INSERT without code change. Arthur Groen suggested inserting 0 and reading SQLCA.SQLERRD[2]. Obnoxio The Clown suggested renaming the table and creating a view with the original name; rkusenet replied that SELECTs would then fail because the serial column would be hidden. Unresolved.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

Arthur Groen — Wed, 11 Jun 2003 17:14:31 +0200 — source: Usenet: comp.databases.informix

Insert a 0 (zero) for the serial value and informix will asign the next serail the it. insert into table_a values(0, field_2,field_3). You can read the value back with Let F_serail = SQLCA.SQLERRD[2] /Arthur ----- Original Message ----- From: rkusenet <rkusenet@sympatico.ca> To: <informix-list@iiug.org> Sent: Wednesday, June 11, 2003 3:41 PM Subject: sql server -> informix > We are about to port an application from SQL Server to Informix. > In SQL Server, identity column (equivalent of serial) is always > the first column. This gives the developers an advantage of skipping > the first column (if it is identity) and SQL server will automatically > assign the next higher number during an insert. > > So we have inserts like > insert into table_a values(field_2,field_3).> > In this case field_1 is an identity field and gets value automatically. > > Can this code work in Informix. Is there a way around, though I don't think > so. > > > > >

↪ replying to Arthur Groen

rkusenet — Wed, 11 Jun 2003 12:05:19 -0400 — source: Usenet: comp.databases.informix

"Arthur Groen" <arthur@hiljo.nl> wrote in message news:bc7iin$4u0$1@terabinaries.xmission.com... > > Insert a 0 (zero) for the serial value and informix will asign the next > serail the it. > > insert into table_a values(0, field_2,field_3).> > You can read the value back with > Let F_serail = SQLCA.SQLERRD[2] This is not what I asked. I wanted to know whether it is possible to entirely omit the serial field in the values section and still get serial inserted. This way I don't have to change code. Well the answer is it is possible using views. But even that would mean a code change.

↪ replying to rkusenet

Obnoxio The Clown — Wed, 11 Jun 2003 19:08:36 +0100 — source: Usenet: comp.databases.informix

rkusenet wrote: > > "Arthur Groen" <arthur@hiljo.nl> wrote in message > news:bc7iin$4u0$1@terabinaries.xmission.com... >> >> Insert a 0 (zero) for the serial value and informix will asign the next >> serail the it. >> >> insert into table_a values(0, field_2,field_3).>> >> You can read the value back with >> Let F_serail = SQLCA.SQLERRD[2] > > This is not what I asked. > I wanted to know whether it is possible to entirely omit the serial field > in the values section and still get serial inserted. This way I don't > have to change code. No. > Well the answer is it is possible using views. But even that would mean a > code change. Why? Rename the table and create a view with the original table name...

↪ replying to Obnoxio The Clown

rkusenet — Wed, 11 Jun 2003 14:10:03 -0400 — source: Usenet: comp.databases.informix

"Obnoxio The Clown" <obnoxio@hotmail.com> wrote > No. > > > Well the answer is it is possible using views. But even that would mean a > > code change. > > Why? Rename the table and create a view with the original table name... And then what would happen to all SELECTS. They will fail as the serial field will no longer be visible in the view.