StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » DB2 record add problem

DB2 record add problem

Keith Chisarik — Thu, 01 Nov 2007 12:00:08 GMT

So..... I got my DB2 project working to display and edit existing records.

When I try to add a record I always get a conversion error on the primekey field.

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().

I have two projects referencing two different libraries/tables with primekeys of different data types exhibiting the same behavior.

Any ideas? Please see the attached error image, I cant seem to add images to posts anymore.

RE: DB2 record add problem

Keith Chisarik — Thu, 01 Nov 2007 12:00:08 GMT

[quote][b]Keith Chisarik (10/23/2007)[/b][hr]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.[/quote]

Thanks Peter, I had finally discovered that on my own, just took me a while :)

RE: DB2 record add problem

PeterA — Thu, 01 Nov 2007 08:00:38 GMT

[quote][b]Keith Chisarik (10/18/2007)[/b][hr]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().[/quote]

Yes, DB2 can auto-generate primary keys. Here's a CREATE TABLE statement with an auto-generated key field:

[quote]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 );[/quote]

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.

[url=http://publibfp.boulder.ibm.com/epubs/pdf/dsnsqk10.pdf]http://publibfp.boulder.ibm.com/epubs/pdf/dsnsqk10.pdf[/url]

Peter

RE: DB2 record add problem

Keith Chisarik — Wed, 31 Oct 2007 12:39:59 GMT

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.

RE: DB2 record add problem

StrataFrame Team — Wed, 31 Oct 2007 11:10:47 GMT

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.

RE: DB2 record add problem

Keith Chisarik — Tue, 30 Oct 2007 10:50:46 GMT

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

RE: DB2 record add problem

StrataFrame Team — Thu, 25 Oct 2007 10:49:17 GMT

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.

RE: DB2 record add problem

Keith Chisarik — Thu, 25 Oct 2007 09:31:36 GMT

[quote][b]Keith Chisarik (10/23/2007)[/b][hr]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:
[b]ERROR [428C9] [IBM][AS] SQL0798N A value cannot be specified for column "PURCH_PK" which is defined as GENERATED ALWAYS. SQLSTATE=428C9[/b]

The field is defined as:
[b] PURCH_PK BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)[/b]

I tried adding the PURCH_PK field to the FieldsToExcludeFromInsert collection to no avail:
[b]Cannot create INSERT command because the updating DataTable does not contain columns for all PrimaryKeyFields.[/b]

Any ideas why this isn't working? The primekey field does auto-increment if I add records manually using a query window.[/quote]

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.

RE: DB2 record add problem

StrataFrame Team — Thu, 25 Oct 2007 09:10:41 GMT

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.

RE: DB2 record add problem

Keith Chisarik — Thu, 25 Oct 2007 08:01:42 GMT

No problem, thanks.

Enjoy poking around with DB2 :)

RE: DB2 record add problem

Trent L. Taylor — Thu, 25 Oct 2007 07:58:18 GMT

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 :)

RE: DB2 record add problem

Keith Chisarik — Thu, 25 Oct 2007 07:35:23 GMT

OK I will look into that, any thoughts on my bigger problem, the use of auto-incrementing keys with a DB2 datasource?

Thanks.

RE: DB2 record add problem

Trent L. Taylor — Wed, 24 Oct 2007 21:59:06 GMT

[quote]also, why would BO.save commit my inserts/updates to the server, yet return FALSE?[/quote]

You might expand on this one since the Save returns an enum value rather than a boolean (i.e. Successful, Failed, etc.)

RE: DB2 record add problem

Keith Chisarik — Wed, 24 Oct 2007 09:30:01 GMT

I went back to using non-incrementing keys so I could get some stuff done until you tell me what is wrong when using DB2 auto-incrementing keys.

Everything is working the way I want by manually updating a new primekey value in the SetDefaultValue method but I have some questions. we used to do this all the time in VFP but I don't see how it can work in a disconnected data model? If I am creating 5 records on Client A and 5 records on Client B since the data isn't committed to the server until the call to BO.save(), my primekey assignment will fall down since Client B will get the same "next primekey" value from the server until Client A commits their inserts. Hopefully this is a moot point since I really want to use good old server assigned keys but i thought it a vlaid question to ask.

Also, why would BO.save commit my inserts/updates to the server, yet return FALSE? That is happening to me now.

RE: DB2 record add problem

Keith Chisarik — Tue, 23 Oct 2007 17:20:59 GMT

Also, I did remove the code from bo.SetDefaultValues.

I miss my SQL Server right now.

Thanks.

RE: DB2 record add problem

Keith Chisarik — Tue, 23 Oct 2007 17:15:26 GMT

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:
[b]ERROR [428C9] [IBM][AS] SQL0798N A value cannot be specified for column "PURCH_PK" which is defined as GENERATED ALWAYS. SQLSTATE=428C9[/b]

The field is defined as:
[b] PURCH_PK BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)[/b]

I tried adding the PURCH_PK field to the FieldsToExcludeFromInsert collection to no avail:
[b]Cannot create INSERT command because the updating DataTable does not contain columns for all PrimaryKeyFields.[/b]

Any ideas why this isn't working? The primekey field does auto-increment if I add records manually using a query window.

RE: DB2 record add problem

Keith Chisarik — Tue, 23 Oct 2007 12:48:29 GMT

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.

RE: DB2 record add problem

Keith Chisarik — Mon, 22 Oct 2007 09:20:25 GMT

gotcha

RE: DB2 record add problem

Trent L. Taylor — Mon, 22 Oct 2007 09:17:33 GMT

[quote]So then would the process be to always set the primekey field to a default, and then make sure to update it with the "real" primekey before calling save?
[/quote]

Well, this is kindof a trick question. You must have the initialization of the properties turned off for the BO, otherwise this would have at least had a "0" in it when created. So in this case, you will have to assign a value or manage the NULL value. In this particular case, I think that I would go ahead and use a NextID table, like you did in VFP, and assign the "real" PK value in the SetDefaultValues of the BO.

RE: DB2 record add problem

Keith Chisarik — Fri, 19 Oct 2007 10:26:12 GMT

That was it, I didn't set the default value for the primekey field. Your the man.

So then would the process be to always set the primekey field to a default, and then make sure to update it with the "real" primekey before calling save?

I was going to use my old VFP way of tracking primekeys that we used before VFP supported auto increment fields, have a table that has a record for each table in the database that contains a field for the next valid primekey.

I cant believe DB2 doesn't have auto increment fields in 2007.....

Thanks again for your help. So simple once you know......

RE: DB2 record add problem

Trent L. Taylor — Fri, 19 Oct 2007 09:34:37 GMT

Well, this is happening in the Get of the property so this indicates that something is trying to pull a value. So two things, look at and/or post the call stack so that we can see where it is coming from. Also, have you initialized the field in the SetDefaultValues event? You mentioned that this is happening when you call the Add(), so at this point, the server has never been queried. So more than likely you are not allowing the fields to be initialized and have not set a value in the SetDefaultValues before something is trying to query the property.

RE: DB2 record add problem

Keith Chisarik — Fri, 19 Oct 2007 09:26:11 GMT

Yes changing that property to false is what got me past my problem updating existing records, which I can do fine. I just cant add records.

RE: DB2 record add problem

Trent L. Taylor — Fri, 19 Oct 2007 08:28:42 GMT

Keith,

Did you set the PrimaryKeyIsAutoIncrementing to False on the BO? This is probably what is causing your problem.

RE: DB2 record add problem

Keith Chisarik — Fri, 19 Oct 2007 08:18:01 GMT

shameless post bump, I'm stuck at this point