Issue 38: Support for VALUES IDENTITY_VAL_LOCAL() INTO :hostvar
Status:  NoFix
Owner: ----
Closed:  Mar 2009
Reported by denes1...@yahoo.ca, Mar 3, 2009
This is an enhancement request to add support for the IDENTITY_VAL_LOCAL
function as described in:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004231.htm

There are a couple of C code examples at the end of that page.

In any case, thank you for creating pyodbc.

DenesL
Mar 3, 2009
Project Member #1 mkleehammer
I don't think this is something pyodbc can take advantage -- it's a SQL construct
similar to one SQL Server has (@IDENTITY).  You should be able to use it after an
insert by doing something like:

value = cursor.execute("select IDENTITY_VAL_LOCAL() from SYSIBM.SYSDUMMY1").fetchone()[0]

(I'm not totally sure about the SYSIBM.SYSDUMMY1, you may have to tweak this for
DB/2; it's been a while since I've used DB/2.)

If this works for you, let me know so I can put on the Wiki under a FAQ section.
Status: Investigating
Mar 3, 2009
#2 denes1...@yahoo.ca
Thanks for the tip, it seems to work except for that it returns (example):
Decimal("11")

I thought of asking you because the documentation recommends using:
SELECT VALUES IDENTITY_VAL_LOCAL() INTO :hostvar
and I had no idea how to retrieve hostvar (whichever one) using pyodbc.
Mar 4, 2009
Project Member #3 mkleehammer
It sounds like it is working.  The Decimal data type is chosen because of the data
type of the identity column.  There are two fixes if you don't like decimal:

1) change the data type of your identity column
2) cast the result in the SQL

Mar 4, 2009
#4 denes1...@yahoo.ca
Yes, it works.

It just seems strange to get a decimal value returned when the field is defined as
INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL

Thanks again.
Mar 4, 2009
Project Member #5 mkleehammer
I agree it is weird, so I double-checked the DB/2 documentation you sent.  Here's
something I missed the first time:

"The result is a DECIMAL(31,0), regardless of the actual data type of the
corresponding identity column."

That explains what you are seeing, so you'll have use a cast.  Something like:

value = cursor.execute("select cast(IDENTITY_VAL_LOCAL() as integer) from
SYSIBM.SYSDUMMY1").fetchone()[0]

I've created a new FAQ page for this kind of thing, BTW.  Thanks for the DB/2
documentation.  Sorry I couldn't do more.

Status: Invalid
Mar 5, 2009
#6 denes1...@yahoo.ca
Yes you are right, result is DECIMAL, I missed that.

Further testing showed that instead of SYSIBM.SYSDUMMY1 (which sounds very db2/400
dependant) can be replaced by the table name you are inserting into:

cursor.execute("select IDENTITY_VAL_LOCAL() from %s"%tablename).fetchone()[0]

or an equivalent iterator based:

cursor.execute("select IDENTITY_VAL_LOCAL() from %s"%tablename).next()[0]

What do you mean you couldn't do more?
Your input helped me solve my problem, thanks.
Nov 21, 2010
Project Member #7 mkleehammer
(No comment was entered for this change.)
Status: NoFix