Solveig L Haugland wrote:
Hi,
I've got a bunch of Base questions so here goes. ;>
In table design view, you can specify Integer as the type and then
specify Auto-increment in the bottom area of the window and an
auto-increment statement. The default auto-increment default statement
is IDENTITY. However, I don't know how to control the starting value
or increment amount. Is there a way to specify that the value should
start at, say, 7000, and increment by 5?
Thanks,
Solveig
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[EMAIL PROTECTED]
I started to Write a Base book, but the publisher dropped the project,
so I packed most of what I had and placed it in AndrewBase.odt.
I looked at Identity at one point. This is what I wrote:
*********************
HSQLDB, included with OOo, implements an Identity statement used to
generate a sequence of integers. An auto-value integer field can be
implicitly set with an automatically generated number—insert a null
value into the auto-value column to implicitly insert the next sequence
number. You can explicitly set the value stored in an auto-value field.
If the explicitly entered value is greater than the next available
sequence number, then the sequence is reset to the explicit value.
Listing 27 demonstrates implicitly and explicitly setting an auto-value
field while inserting new rows in a database.
Listing 27. Implicitly and explicitly set an auto-value field.
CALL IDENTITY() ** Assume returns 50
INSERT INTO `Table1` VALUES (NULL, 'x0') ** Insert (50, 'x1')
INSERT INTO `Table1` VALUES (137, 'x1') ** Insert (137, 'x2')
INSERT INTO `Table1` VALUES (130, 'x2') ** Insert (130, 'x3')
INSERT INTO `Table1` VALUES (130, 'x3') ** Failure, 130 exists.
INSERT INTO `Table1` VALUES (NULL, 'x4') ** Insert (138, 'x0')
INSERT INTO `Table1` VALUES (NULL, IDENTITY()) ** Insert (139, 138)
*********************
Note that this does not set the initial value at definition time, but it
does set the initial value. This example assumes that you are using the
built-in database. Anything that you mention in this regard is likely to
be "backend" specific. The backend for a normal Base document is HSQLDB,
so if you see what it will do, then you know what you can do with Base.
Also, there is a DB specific mailing list that may have better information.
Now, back to HSQLDB:
http://hsqldb.org/doc/guide/ch02.html#N104AE
Identity Auto-Increment Columns
Each table can contain one auto-increment column, known as the IDENTITY
column. An IDENTITY column is always treated as the primary key for the
table (as a result, multi-column primary keys are not possible with an
IDENTITY column present). Support has been added for CREATE TABLE
<tablename>(<colname> IDENTITY, ...) as a shortcut.
Since 1.7.2, the SQL standard syntax is used by default, which allows
the initial value to be specified. The supported form is(<colname>
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY
m])PRIMARY KEY, ...). Support has also been added for BIGINT identity
columns. As a result, an IDENTITY column is simply an INTEGER or BIGINT
column with its default value generated by a sequence generator.
When you add a new row to such a table using an INSERT INTO <tablename>
...; statement, you can use the NULL value for the IDENTITY column,
which results in an auto-generated value for the column. The IDENTITY()
function returns the last value inserted into any IDENTITY column by
this connection. Use CALL IDENTITY(); as an SQL statement to retrieve
this value. If you want to use the value for a field in a child table,
you can use INSERT INTO <childtable> VALUES (...,IDENTITY(),...);. Both
types of call to IDENTITY() must be made before any additional update or
insert statements are issued on the database.
The next IDENTITY value to be used can be set with the ...
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
