How can I insert an actual 0 in serial column?
A DBA asked how to store an actual 0 in a SERIAL column (IDS 11.50.FC5); inserting 0 just triggers the next sequence value, and the docs state SERIAL cannot hold values below 1. Workaround given and used: alter the column to INTEGER, insert the 0 row, then alter back to SERIAL (subsequent inserts continued correctly). Others suggested instead keeping the column as INTEGER and using a CREATE SEQUENCE object (which can start at 0), and warned the 0 row may break non-binary reloads/unload-load. Jonathan Leffler noted negative values can still be inserted directly into a serial column.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC5 IBM 5.3 I have a developer who for some reason wants to insert an actual 0 in a serial column.I tried single quoting a o in the values clause but it still generated the next number in sequence. How can I acomplish this? thanx, dan
Dan, you should be able to insert a zero into that table. "insert into dans_table (serial_column_name_here) values (0);" As long as the value is not already there, it will insert. Can you confirm that it is not already there in the table? If you are inserting it by using an unload file and "seeded" the row with a zero, then that is the default action of the engine to convert the zero to the next serial value. Are you using a "load from file insert into table" statement here with an unload file that has a seeded zero? Example file: 0|Dan|Mueller|ccccc|cccc|ccccc| Joe -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: Thursday, March 24, 2011 8:38 AM To: ids@iiug.org Subject: How can I insert an actual 0 in serial column? [23193] IDS 11.50.FC5 IBM 5.3 I have a developer who for some reason wants to insert an actual 0 in a serial column.I tried single quoting a o in the values clause but it still generated the next number in sequence. How can I acomplish this? thanx, dan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi. According to the manual, Information Center says "Columns of serial data types cannot store values less than 1". Check it out: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_1410.htm?resultof=%22%73%65%72%69%61%6c%22%20%22%64%61%74%61%22%20 Best regards. Em 24/03/2011 09:38, DAN MUELLER escreveu: > IDS 11.50.FC5 > IBM 5.3 > > I have a developer who for some reason wants to insert an actual 0 in a serial > column.I tried single quoting a o in the values clause but it still generated > the next number in sequence. How can I acomplish this? > > thanx, > dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 <http://www.iiug.org/conf/2011/iiug/>;
Dan, http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sq lr.doc/sqlrmst131.htm The default serial starting number is 1, but you can assign a non-default initial value, n, when you create or alter the table. Any number greater than 0 can be your starting number. The maximum SERIAL is 2,147,483,647. If you assign a number greater than 2,147,483,647, you receive a syntax error. (Use the SERIAL8 data type, rather than SERIAL, if you need a larger range.) That being said... Try the following; Start the Column off as an Integer which allows the 0. Insert Row with value of 0. Alter the table and change the column to Serial (if more then one row is loaded then start the serial above that value). Hope this helps, Eric B. Rowell On Thu, Mar 24, 2011 at 9:38 AM, DAN MUELLER <dan.mueller@trnswrks.com> wrote: > IDS 11.50.FC5 > IBM 5.3 > > I have a developer who for some reason wants to insert an actual 0 in a serial > column.I tried single quoting a o in the values clause but it still generated > the next number in sequence. How can I acomplish this? > > thanx, > dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell
I stand corrected, you are correct Alexandre, it uses the zero to seed .... and does not actually insert the zero. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Alexandre Marini Sent: Thursday, March 24, 2011 8:54 AM To: ids@iiug.org Subject: Re: How can I insert an actual 0 in serial column? [23195] Hi. According to the manual, Information Center says "Columns of serial data types cannot store values less than 1". Check it out: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_1410.htm?resultof=%22%73%65%72%69%61%6c%22%20%22%64%61%74%61%22%20 Best regards. Em 24/03/2011 09:38, DAN MUELLER escreveu: > IDS 11.50.FC5 > IBM 5.3 > > I have a developer who for some reason wants to insert an actual 0 in > a serial > column.I tried single quoting a o in the values clause but it still generated > the next number in sequence. How can I acomplish this? > > thanx, > dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 <http://www.iiug.org/conf/2011/iiug/>; ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I was able to alter it to an int and insert my "0" row then alter again back to serial. After all that the next row picked up where it was supposed to. Is there any ill effects of having that "0" row in there now?
On 24/03/11 13:38, DAN MUELLER wrote: > IDS 11.50.FC5 > IBM 5.3 > > I have a developer who for some reason wants to insert an actual 0 in a serial > column.I tried single quoting a o in the values clause but it still generated > the next number in sequence. How can I acomplish this? > > thanx, > dan > Alter the column to an integer and use a sequence when you don't want to insert the 0. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Why is it so important to have a zero, why does it matter ..? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: Thursday, March 24, 2011 9:03 AM To: ids@iiug.org Subject: Re: How can I insert an actual 0 in serial column? [23198] I was able to alter it to an int and insert my "0" row then alter again back to serial. After all that the next row picked up where it was supposed to. Is there any ill effects of having that "0" row in there now? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Inserting 0 will just allocate the next serial number, not 0. Graeme Norton | Software Product Development Lead | Technology and Platforms | ITV plc 104 Kirkstall Road | Leeds | LS3 1HD | Tel: 0113 222 7157 | Mob: 07764 256655 | Graeme.Norton@ITV.COM ITV plc Head Office Tel +44 (0) 20 7157 3000 http://www.itv.com/ Please consider the environment before printing this email -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Alexandre Marini Sent: 24 March 2011 13:54 To: ids@iiug.org Subject: Re: How can I insert an actual 0 in serial column? [23195] Hi. According to the manual, Information Center says "Columns of serial data types cannot store values less than 1". Check it out: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_1410.htm?resultof=%22%73%65%72%69%61%6c%22%20%22%64%61%74%61%22%20 Best regards. Em 24/03/2011 09:38, DAN MUELLER escreveu: > IDS 11.50.FC5 > IBM 5.3 > > I have a developer who for some reason wants to insert an actual 0 in a serial > column.I tried single quoting a o in the values clause but it still generated > the next number in sequence. How can I acomplish this? > > thanx, > dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 <http://www.iiug.org/conf/2011/iiug/>; ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. -------------------------------------------------------------------------- ITV Broadcasting Limited (Registration No. 955957) (ITV) is incorporated in England and Wales with its registered office at The London Television Centre, Upper Ground, London SE1 9LT. Please visit the official ITV website at http://www.itv.com/ for the latest company news. The contents of this email and any attachments are confidential, may be privileged, may be subject to copyright and are intended solely for the use of the individual to whom they are addressed. If you have received this email and you are not the intended recipient please notify mailto:postmaster@itv.com and delete this email and you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this email are strictly prohibited. Although ITV routinely screens for viruses, recipients should scan this email and any attachments for viruses. ITV makes no representation or warranty that this email or any of its attachments is free of viruses or defects and does not accept any responsibility for any damage caused by any virus or defect transmitted by this email. ITV reserves the right to monitor all e mails and the systems upon which such e mails are stored or circulated. Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of ITV. Thank You. --------------------------------------------------------------------------
Hi Dan,
The starting number for a serial is always greater than 0, see IBM's
documentation !
There is another Informix solution for your problem.
You can use the SEQUENCE Informix.
The CREATE SEQUENCE statement allow you to create a sequence database
object from which multiple users can generate unique integers.
But, your serial column in your table must become an integer and your
developer will have to use the created sequence in the code.
By example :
create sequence mysequence increment by 1start 0;
create table foo(
myseq integer,
tabname char(20)
);
insert into foo
select mysequence.nextval, tabname from systables;
select * from foo;
The last query will display :
myseq tabname
0 systables
1 syscolumns
2 sysindices
3 systabauth
4 syscolauth
5 sysviews
6 sysusers
7 sysdepend
8 syssynonyms
9 syssyntable
10 sysconstraints
11 sysreferences
12 syschecks
13 sysdefaults
and so on
Regards,
Le 24/03/2011 14:38, DAN MUELLER a écrit :
> IDS 11.50.FC5
> IBM 5.3
>
> I have a developer who for some reason wants to insert an actual 0 in a
serial
> column.I tried single quoting a o in the values clause but it still generated
> the next number in sequence. How can I acomplish this?
>
> thanx,
> dan
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Franck Thomas
ConsultiX
franck.thomas@consult-ix.fr
Téléphone : 33 (0) 1 39 12 18 00
Mobile : 33 (0) 6 78 81 09 33
Fax : 33 (0) 1 39 12 18 18
Hi,
I would not go through the trouble to insert an actual 0 into a serial
column. It may work the way you have done it but some time later you may
wish for whatever reason to reload that table. Any method that is not a
binary reload ( table restore, onload ) will cause the zero to become a
value and will likely abort the reload at that point as it may take a value
that is loaded later ( I assume that you would have a unique index on the
serial column at some point ).
I am a fan of serials and not a fan of sequence, ( because serial is table
level, not DB level, and there are no extra work required to get the next
value or find the value inserted ) but if the value 0 is truly important
to application then I would consider this a good use for sequence and not a
good use for serial.
I would need to know why, from the developer, a zero is required ?
George.
From: "DAN MUELLER" <dan.mueller@trnswrks.com>
To: ids@iiug.org
Date: 03/24/2011 09:05 AM
Subject: Re: How can I insert an actual 0 in serial column? [23198]
Sent by: ids-bounces@iiug.org
I was able to alter it to an int and insert my "0" row then alter again
back
to serial. After all that the next row picked up where it was supposed to.
Is
there any ill effects of having that "0" row in there now?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You can create sequence that always returns zero:
create sequence mysequence start 0 maxvalue 0 cycle nocache;
insert into foo select mysequence.nextval, tabname from systables;
select * from foo;0 systables
0 syscolumns
0 sysindices
0 systabauth
0 syscolauth
0 sysviews
0 sysusers
0 sysdepend
0 syssynonyms
0 syssyntable
...
but in any case You cannot insert value less than 1 into serial column
On Fri, Mar 25, 2011 at 02:49, VICTOR VAT <victor16@inbox.ru> wrote:
> but in any case You cannot insert value less than 1 into serial column
>
INSERT INTO TableWithSerial(SerialCol) VALUES(-1);
INSERT INTO TableWithSerial(SerialCol) VALUES(-2);
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--00151744823263a94c049f50eebf
On 24/03/2011 14:52, George_Palmer@aotx.uscourts.gov wrote:
> Hi,
>
> I would not go through the trouble to insert an actual 0 into a serial
> column. It may work the way you have done it but some time later you may
> wish for whatever reason to reload that table. Any method that is not a
> binary reload ( table restore, onload ) will cause the zero to become a
> value and will likely abort the reload at that point as it may take a value
> that is loaded later ( I assume that you would have a unique index on the
> serial column at some point ).
>
> I am a fan of serials and not a fan of sequence, ( because serial is table
> level, not DB level, and there are no extra work required to get the next
> value or find the value inserted ) but if the value 0 is truly important
> to application then I would consider this a good use for sequence and not a
> good use for serial.
>
> I would need to know why, from the developer, a zero is required ?
I would guess "special value for a special purpose", which means the
developer is a cunt.
> From: "DAN MUELLER"<dan.mueller@trnswrks.com>
> To: ids@iiug.org
> Date: 03/24/2011 09:05 AM
> Subject: Re: How can I insert an actual 0 in serial column? [23198]
> Sent by: ids-bounces@iiug.org
>
> I was able to alter it to an int and insert my "0" row then alter again
> back
> to serial. After all that the next row picked up where it was supposed to.
> Is
> there any ill effects of having that "0" row in there now?
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.