Next_RowID

Definition

Next_RowID takes a table that is registered with the geodatabase as an input parameter and returns the next value for the ObjectID (RowID) field.

You can use this value when you are inserting a row to the table using SQL.

An error is returned if the table you specify is not registered with the geodatabase.

Syntax

<geodatabase administrator schema>.next_rowid (<table owner>, <table name>)

In most geodatabases, the geodatabase administrator schema is sde. However, it is dbo in dbo-schema geodatabases in SQL Server, and in user-schema geodatabases in Oracle, it is the name of the user's schema.

Return type

Integer

Examples

The following are examples of using the Next_RowID function in each supported database type.

The first example inserts a record into the applicants table owned by webman, and calls the Next_RowID procedure to insert a value to the RowID field (ObjectID). In DB2 and SQL Server, you must call the Next_RowID procedure first to get a value for the ObjectID field, then use that value in the insert statement.

The second example attempts to insert a record into the logins table, calling the Next_RowID procedure to insert a value to a field named ID. However, the logins table is not registered with the geodatabase; therefore, an error is returned.

DB2

In DB2, stored procedures cannot be used directly in an insert statement. Therefore, you must call the Next_RowID procedure to get the value for the next RowID, then use that number in the insert statement.

CALL sde.next_rowid('WEBMAN', 'APPLICANTS', ?, ?, ?);

Value of output parameters
----------------------------
Parameter Name :   O_ROWID
Parameter Value :  30

Parameter Name :   O_MSGCODE
Parameter Value :  0

Parameter Name :   O_MESSAGE
Parameter Value :  Procedure successfully completed.

Return Status = 1


INSERT INTO webman.applicants (objectid, app_name, status)
VALUES
(30, 'Roy Bean', 'active')

The SQL command completed successfully

In this example, the table is not registered with the geodatabase, so no ID value is returned:

CALL sde.next_rowid('WEBMAN', 'LOGINS', ?, ?, ?);

Value of output parameters
----------------------------
Parameter Name :   O_ROWID
Parameter Value :  -

Parameter Name :   O_MSGCODE
Parameter Value :  -20169

Parameter Name :   O_MESSAGE
Parameter Value :  No SDE ROWID column.

Return Status = 1

Oracle

INSERT INTO webman.applicants (objectid, app_name, status)
VALUES
(sde.gdb_util.next_rowid('WEBMAN', 'APPLICANTS'), 'Roy Bean', 'active');

1 row created

COMMIT;
INSERT INTO webman.logins (id, l_name)
VALUES 
(sde.gdb_util.next_rowid('WEBMAN', 'LOGINS'), 'maplebutter');


(sde.gdb_util.next_rowid('WEBMAN', 'LOGINS'), 'maplebutter');
 *
ERROR at line 3:
ORA-20220: Class WEBMAN.LOGINS not registered to the Geodatabase.

PostgreSQL

INSERT INTO webman.applicants (objectid, app_name, status)
VALUES
(sde.next_rowid('webman', 'applicants'), 'Roy Bean', 'active')

Query returned successfully: 1 row affected, 109 ms execution time.
INSERT INTO webman.logins (id, l_name)
VALUES 
(sde.next_rowid('WEBMAN', 'LOGINS'), 'maplebutter')

Notice: Class webman.logins not registered to the Geodatabase.

SQL Server

When using the Next_RowID stored procedure in SQL Server, you must obtain the next row ID value, then insert that value when you insert a record to the table.

DECLARE @myval int
EXEC sde.next_rowid 'mgr', 'customers', @myval OUTPUT
SELECT @myval "Next RowID";

Next RowID
3188
INSERT INTO webman.applicants (objectid, app_name, status)
VALUES
(3188, 'Roy Bean', 'active');

Query returned successfully: 1 row affected, 109 ms execution time.

If you try to run the Next_RowID procedure for a table that is not registered with the geodatabase, you receive an error message, as shown in the following example:

DECLARE @myval int;
EXEC sde.next_rowid 'mgr', 'inventory', @myval OUTPUT;
SELECT @myval "Next RowID"

Msg 50000, Level 16, State 1, Procedure next_rowid, Line 16
Class inventory not registered to the Geodatabase.

Related Topics

6/19/2015