On Friday April 06 2007 10:18 pm, Andrew Douglas Pitonyak wrote:
> 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 ...
All of this may be very accurate, but how can any of this be
applied when a table is created in the Design View of a database in
OOo?
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
