StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Error Updating a record with a stored procedure

Error Updating a record with a stored procedure

Rob Toyias — Thu, 15 Nov 2007 08:51:21 GMT

Trying to update a record using a stored procedure I recieve the following error:

Trying to update a record I recieve the following error:"Cannot create UPDATE command because a business object with PrimaryKeyIsAutoIncremented = True must have exactly 1 primary key field"

This error makes sense since I have specified two keys for the table I'm updating. Now I'm not here to discuss the merits of combined keys (not my database design) I'd like to know how to best handle this situation. If I don't specify both fields as keys then the delete SP call does not send both fields. If I do set up two keys then I get the above error. And since the database is most definatly handling the field incrementing turning that off seems a bad idea. Is there an easy way around this or do I need to go hog tie our DBA? btw, if it matters this is Oracle (of course).

thanks

RE: Error Updating a record with a stored procedure

StrataFrame Team — Thu, 15 Nov 2007 08:51:21 GMT

I should have it fixed. Give this update a try:

http://forum.strataframe.net/FindPost12716.aspx

RE: Error Updating a record with a stored procedure

Rob Toyias — Tue, 13 Nov 2007 13:59:17 GMT

Ben

I've got a problem with that update release.

What I am seeing now is anytime I call update on a BO with a single primary key I end up with a SP that passes the key as a param but labels it "Parameter1", notice it's without the prepend.

For example my debug file looks like this:

i_SOMEID: '69' [DbType: VarNumeric | Size: 0 | Direction: Input | SourceColumn: SOMEID| SourceVersion: Current]
i_SOMETEXT: 'NA aaa' [DbType: AnsiString | Size: 6 | Direction: Input | SourceColumn: SOMETEXT| SourceVersion: Current]
i_RECORDUPDATEDT: '10/5/2007 10:12:40 AM' [DbType: DateTime | Size: 0 | Direction: Input | SourceColumn: RECORDUPDATEDT | SourceVersion: Current]
i_SYSTEMNM: 'FOO' [DbType: AnsiString | Size: 3 | Direction: Input | SourceColumn: SYSTEMNM | SourceVersion: Current]
i_UPDATEDBY: 'somebody' [DbType: AnsiString | Size: 8 | Direction: Input | SourceColumn: UPDATEDBY | SourceVersion: Current]
Parameter1: '81' [DbType: VarNumeric | Size: 0 | Direction: Input | SourceColumn: SOMEPRIMARYKEY | SourceVersion: Current]
i_ROWREVISION: '0' [DbType: VarNumeric | Size: 0 | Direction: InputOutput | SourceColumn: ROWREVISION | SourceVersion: Current]
i_ROWCOUNT: 'null (Nothing)' [DbType: VarNumeric | Size: 0 | Direction: Output | SourceColumn: | SourceVersion: Current]

This does not occur when I define > 1 PK.

This only occurs when I use the new SF Base class dll, when I swap out and add the old one back in the error goes away.

Any ideas?

RE: Error Updating a record with a stored procedure

StrataFrame Team — Tue, 06 Nov 2007 09:26:39 GMT

Ah, I forgot that further up the line, the primary key is added to the fields to be updated because it's not auto incremented.

[quote]What I'd really like to know is why the SqlDataSourceItem.BuildUpdateCommand_SP includes logic that the Oracle version does not?[/quote]

That's a really good question. I think it's because when we added the functionality to allow users to change the primary key (a big no-no for most people, since it's a PK, but whatever), we made the change to the business layer to pass the pk fields as updatable fields, but the change just didn't get propagated to the OracleDataSourceItem.

Anyways, here's the update:

http://forum.strataframe.net/FindPost12485.aspx

RE: Error Updating a record with a stored procedure

Rob Toyias — Mon, 05 Nov 2007 14:48:18 GMT

The PrimaryKeyFields property containes the two values I added via the BOM.

What I'd really like to know is why the SqlDataSourceItem.BuildUpdateCommand_SP includes logic that the Oracle version does not? Especially since that bit of logic if expressly preventing the duplication of the pk names.

Could it be an issue with the creation of the QueryInfo object? should QueryInfo.Fields normally contain the pk names in it? What about when PrimaryKeyIsAutoIncremented is set to false, would that change the construction of QueryInfo.Fields and add the PK names?

Thanks

RE: Error Updating a record with a stored procedure

StrataFrame Team — Mon, 05 Nov 2007 11:13:43 GMT

Looking through our unit tests, everything is working as it should. Could you post the .designer.cs (.vb?) file for the business object that is causing you the problems?

I'm thinking it has something to do with the PrimaryKeyFields property because the first iteration of the BuildUpdateCommand_SP should not include the PK fields, so they shouldn't be added twice.

RE: Error Updating a record with a stored procedure

StrataFrame Team — Mon, 05 Nov 2007 11:07:29 GMT

Yes, we are working on it. I'll keep you posted as to what we find.

RE: Error Updating a record with a stored procedure

Rob Toyias — Fri, 02 Nov 2007 10:20:19 GMT

Trent or Ben,

I was wondering if anyone has had a chance to look at this yet, and when or if I should expect a fix? I have some other people that are relying on this piece and I need to decide on building a workaround or not.

Thanks for your help.

RE: Error Updating a record with a stored procedure

Rob Toyias — Wed, 31 Oct 2007 14:10:30 GMT

Trent,

I think I've tracked the composite key issue down to BuildUpdateCommand_SP in OracleDataSourceItem.vb

The SqlDataSourceItem.vb version of BuildUpdateCommand_SP has the following logic that I don't see in the Oracle version.

If Not QueryInfo.PrimaryKeyFieldIsAutoInc Then
loParam.ParameterName &= "_ORGPK"
End If

The BuildUpdateCommand_SP is definatly building the SP with two copies of the primary keys when I define more than one in the BOM. One copy each for the values contained in the QueryInfo.Fields collection and one for the values QueryInfo.PrimaryKeyFields collection.

I'll build you an example project if you'd like.

RE: Error Updating a record with a stored procedure

Trent L. Taylor — Thu, 04 Oct 2007 10:19:14 GMT

One more thing, you may want to look at this post as well: http://forum.strataframe.net/FindPost11014.aspx

Do a search of the forum for Oracle and you will find a lot fo threads where other developers have setup SPs for Oracle which may give you some ideas.

RE: Error Updating a record with a stored procedure

Trent L. Taylor — Thu, 04 Oct 2007 09:48:14 GMT

Rob,

I do not beleive this is a bug, but I never like to rule anything out :). It sounds to me that the sproc is not correct. Based on this thread alone there is not enough information to diagnose your problem. The first thing that I would do is turn on debugging for your data source:

[codesnippet]MicroFour.StrataFrame.Data.Databasics.DataSources("").SetDebugOn("c:\MyOutput.html",True)[/codesnippet]

This will show you what is being sent via the DAL to the database. You could then compare this to the actual SPROC to see what is going on. This is the first place I would look.

RE: Error Updating a record with a stored procedure

Rob Toyias — Wed, 03 Oct 2007 12:55:05 GMT

Additionally, I set the PrimaryKeyIsAutoIncremented property to false, try my update and it errors out because it actually sends the two feilds I set as primary keys twice in the parameters list. This seems like a bug.

So to address this I tried including the two fields I set as keys in the FieldsToExcludeFromUpdate list and tried my update again. This time I get an error about keys not existing in the table.

What am I doing wrong here?

RE: Error Updating a record with a stored procedure

Rob Toyias — Tue, 02 Oct 2007 18:23:37 GMT

Yeah, I only wish I could skip the procs for the CRUD. Unfortunately the security bogey man has everyone holding their blankets over their heads. so procs it is.

RE: Error Updating a record with a stored procedure

Peter Jones — Tue, 02 Oct 2007 18:10:57 GMT

Hi Rob,

I presume you know that SF will handle the CRUD without the use of stored procedures. I can imagine this may not be an option for you, i.e. your shop mandates procs, just thought I would mention it.

Cheers, Peter