Re: jdbcr4 and identity columns

[CRPence <CRPbottle@xxxxxxxxx>]

  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?