DB2 record add problem

StrataFrame Forum

Back

DB2 record add problem

Author	Message
Keith Chisarik	Keith Chisarik posted 18 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 939, Visits: 40K	Keith Chisarik (10/23/2007) Just for anyone that might read this later, DB2 does fully support auto-increment fields, our RPG programmer just doesn't use them so he didn't know they existed. Thanks Peter, I had finally discovered that on my own, just took me a while Keith Chisarik Sigma Data Systems
	Reply Like 0
PeterA	PeterA posted 18 Years Ago Post Details Share Post
Group: Forum Members Posts: 72, Visits: 235	Keith Chisarik (10/18/2007) I know DB2 doesn't have auto increment fields (does it?) so I have to manually manage the primekeys but I don't even get a chance. I get this error as soon as I hit the add button, or programmatically call BO.add(). Yes, DB2 can auto-generate primary keys. Here's a CREATE TABLE statement with an auto-generated key field: CREATE TABLE "SW_TYPES_OWN" ( "ID_OWN_TYP" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE NO CACHE NO ORDER ) , "NM_OWN_TYP" VARCHAR(125) NOT NULL ); I think you can reset the starting number with a table alter, but I've not had any requirement to do that yet, so I'm not sure what the SQL is. Here's the DB2 v9 SQL reference, though. It should (hopefully) have the information in there. http://publibfp.boulder.ibm.com/epubs/pdf/dsnsqk10.pdf Peter
	Reply Like 0
Keith Chisarik	Keith Chisarik posted 18 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 939, Visits: 40K	Just to close this out, I am up and running on DataDirect's DB2 Provider for ADO.NET with the SF framework, just the changes Ben mentioned above and a syntax change to the INSERT SQL statement was required. Thank you. Keith Chisarik Sigma Data Systems
	Reply Like 0
StrataFrame Team	S StrataFrame Team posted 18 Years Ago Post Details Share Post
S Group: StrataFrame Developers Posts: 3K, Visits: 2.5K	Yes, the Db2DataSourceItem can be used with any ADO.NET provider that implements the System.Data.Common classes and provides a DbProviderFactory class. However, when you swap to a new ADO.NET provider, you will need to swap the places where you pass Db2Commands to the FillDataTable() methods of the business object to use the new provider as well.
	Reply Like 0
Keith Chisarik	Keith Chisarik posted 18 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 939, Visits: 40K	My client is not happy with the performance of the IBM DB2 Provider for .NET. As I understand iy SF DB@ support is designed to work with the IBM Provider IBM.DATA.DB2.dll and wont work with other third party providers, is this correct? They want to use this. http://www.hitsw.com/products_services/sql400/dlsql400.html http://www.datadirect.com/products/net/net_for_db2/index.ssp Keith Chisarik Sigma Data Systems
	Reply Like 0
StrataFrame Team	S StrataFrame Team posted 18 Years Ago Post Details Share Post
S Group: StrataFrame Developers Posts: 3K, Visits: 2.5K	Can you send me the create script for the table, the .vb file and the .designer.vb file for the business object/table that is causing the problems? Also send me the stack trace for the error that you're receiving after you got everything back to ground-zero. I think I'm going to have to run it on this end to see what it's doing.
	Reply Like 0
Keith Chisarik	Keith Chisarik posted 18 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 939, Visits: 40K	Keith Chisarik (10/23/2007) So......... I have my DB2 table set to now use auto incrementing keys of type BIGINT. I set the PrimaryKeyIsAutoIncremented property to TRUE on the BO. I get the following error now: ERROR [428C9] [IBM][AS] SQL0798N A value cannot be specified for column "PURCH_PK" which is defined as GENERATED ALWAYS. SQLSTATE=428C9 The field is defined as: PURCH_PK BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) I tried adding the PURCH_PK field to the FieldsToExcludeFromInsert collection to no avail: Cannot create INSERT command because the updating DataTable does not contain columns for all PrimaryKeyFields. Any ideas why this isn't working? The primekey field does auto-increment if I add records manually using a query window. Ben, I did set it back to TRUE, I am 100% sure of it as I thought that was the problem from the start once I got my database auto-incrementing and tested it a few times over before posting. Thanks. Keith Chisarik Sigma Data Systems
	Reply Like 0
StrataFrame Team	S StrataFrame Team posted 18 Years Ago Post Details Share Post
S Group: StrataFrame Developers Posts: 3K, Visits: 2.5K	Looks like it's trying to insert a value into the PK field because you might still have the PrimaryKeyIsAutoIncremented = False set. Set it back to its default of True and you should be able to save it fine. The Db2DataSourceItem is designed to work with auto-incremented columns the same way as SQL Server, it just uses a different server function to retrieve the next assigned value (where SQL Server uses SCOPE_IDENTITY()). So, it should be working. Re-set that field back to True to get back to ground-zero for this post, where you got the InvalidCastException and we'll go from there.
	Reply Like 0
Keith Chisarik	Keith Chisarik posted 18 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 939, Visits: 40K	No problem, thanks. Enjoy poking around with DB2 Keith Chisarik Sigma Data Systems
	Reply Like 0
Trent Taylor	Trent Taylor posted 18 Years Ago Post Details Share Post
Group: StrataFrame Developers Posts: 6.6K, Visits: 7K	DB2 is not something I work in every day so before I can just throw an answer out there like I can for SQL Server or some of the other databases. I will have to get it setup and play with it on my side. I have not dug into the DB2 server to play with this...yet. But we will look into it. Sorry for the delay
	Reply Like 0