| Issue 38: | Support for VALUES IDENTITY_VAL_LOCAL() INTO :hostvar | |
| 1 person starred this issue and may be notified of changes. | Back to list |
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
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
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
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
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
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
(No comment was entered for this change.)
Status:
NoFix
|
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.