Re: jdbcr4 and identity columns

I just noticed a followup indicating that the column was not an identity column. I am posting my reply anyhow... for the archives.

From the first of those links from Nick, the following /overriding/ clause is apparently desirable in the described scenario, if matched columns and values are provided, including the identity column, but for which the DEFAULT keyword is not coded as the value:
OVERRIDING USER VALUE

From the syntax diagram for the DB2 SQL INSERT statement, a revision of the statement, adding that clause after the /include-columns/ list:

stmt = JDBC_PrepStmt(conn : 'Insert Into PREMISETABLE'
+ '( OBJECTID'
+ ', PREMISE'
+ ', PREMISEADDRESS'
+ ') OVERRIDING USER VALUE'
+ ' values ( ?, ?, ? )');

Whatever is specified for the first parameter marker should be ignored by the database, and a value instead generated by the database for the identity column.

However I am not so sure that OVERRIDING clause is helpful for the scenario described in the OP, whereby the target of the statement apparently is not DB2. More likely, the DEFAULT keyword is preferable; I believe that should be acceptable outside of the DB2:

stmt = JDBC_PrepStmt(conn : 'Insert Into PREMISETABLE'
+ '( OBJECTID'
+ ', PREMISE'
+ ', PREMISEADDRESS'
+ ')'
+ ' values ( DEFAULT, ?, ? )');

The above is described here [must read entire message thread]:
http://bytes.com/topic/db2/answers/181662-jdbc-preparedstatement-identity-column

Regards, Chuck

On 04 Sep 2013 08:20, Nicholas T Lawrence wrote:

1) You should specify the same number of values and columns on the
insert, and make sure that they "match up".

You can use the DEFAULT keyword if you want to explicitly use a
default value.

2) Identity columns can be created so that they are GENERATED BY
DEFAULT or GENERATED ALWAYS

If you generate by default, the insert can specify a value for
the column, or not specify the value and allow the system to generate
a value.

3) See the following two links:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyinsertidentity.htm

_i Inserting values into an identity column i_

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafysqlpidentity.htm

_i Creating and altering an identity column i_

On Wed, 4 Sep 2013 13:55:52 +0000 "Smith, Mike" wrote:

I'm sure this is really easy to do, but I'm not having any luck.

I have a table on SQL SERVER that that has an identity column.

So a shortened version of the file might be

PREMISETABLE in SQL SERVER
OBJECTID
PREMISE
ADDRESS

stmt = JDBC_PrepStmt(conn : 'Insert Into PREMISETABLE'
+ '(PREMISE,'
+ 'PREMISEADDRESS)'
+ ' values (?,?,? )');

JDBC_setString (stmt: 2: UPPRM);
JDBC_setString (stmt: 3: SERVADDR);

rc = JDBC_ExecPrepUpd( stmt );

When I try this, I get an error stating that Parameter #1 has not
been set.

It seems I can't set Parameter #1 either, because it is an
identity column.

How do I insert records into this table ignoring the identity
column?