Focal Point
Maintain -- Sending null, seeing zero in the db
February 29, 2008, 01:51 PM
John_EdwardsMaintain -- Sending null, seeing zero in the db
Ok, so the Oracle database that I am writing to has a trigger on it so that when I send a new record to it with no value assigned to the primary key, it will generate one on insert.
I do just what it wants -- I send a record with the ID explicitly assigned the value MISSING, include a TYPE statement in the update case that verifies its missingness, but when it gets to the Oracle table a record is created with a key value of zero. More to the point, the entire stack of records is generated with a key value of zero, and each overwrites the previous, resulting in just the last record being written.
Is there a way in Maintain to submit a record to Oracle so that the value of the key field presented is indeed a zero?
February 29, 2008, 06:24 PM
John_EdwardsI'm not feeling the love, people. I mean, I come back a few hours later, and only FIVE people have even READ my question. Now, I realize that the question is a hard one and the subject is a bit out of the ordinary, but it gets awfully lonely out here, and if you could do me the courtesy of at least
pretending to read it I'd feel a bit better. Just click and move on. It won't take more than a second. It doesn't take a lot of effort to take a peek inside the envelope.
I appreciate I'm asking you to be a bit disingenuous, and that's not something I generally do. But in this case I think it's safe to look at it as a "little white lie" -- the kind of thing you'd do to spare a blind date's feelings, or reassure your spouse that they still look young and vibrant in spite of their years.
So if you do stumble upon this post (likely because you inadvertantly clicked it while aiming for the stylesheet question one line lower) do me just the smallest favor of spending a moment to let me know you care, even if you have no clue what I'm talking about. It's simply remarkable how much a kind word or two can do to help someone who's having trouble with their code.
Hey, it could be
your Edaserver next time.
love to you all,
John
February 29, 2008, 07:45 PM
Dave AyersJohn,
I read any post I see with 'Maintain' in the Topic description !
Sorry I can't help you with it, but I suspect the the answer lies on the Oracle side of things, and I don't know anything about it.
But, I will be getting into Maintain with SQL Server soon, so I'll keep an eye on how this thread develops...
Dave
March 03, 2008, 09:34 AM
Maintain WizardJohn
Sorry for the delayed response. The Maintain community may be small, but we are fiesty AND growing everyday. Especially overseas. I did 3 demos last week and have 2 more planned for this week. Don't give up hope
Ok. When dealing with Oracle, we have seen that READONLY key field that auto-increments everytime a record is included into the database. When using Maintain against this kind of field, we do not list that field on the include or delete line. We list the other fields, and Maintain and the database seem to know how to include and delete.
Having said that, I have no way of forcing that key value to be anything. It would seem to me, if you only want one record in the database, delete ALL the records before including the new one. That would do it.
Mark
March 03, 2008, 09:52 AM
John_EdwardsCurrently I am using a REVISE command because the records are all mixed in. Rewriting to INCLUDE concerned me as it will be a bit more complicated than I hoped and I was not confident it would provide a different result. If you believe the change will make a difference I will go after this revision today.
Thank you for the response.
J.
March 03, 2008, 10:19 AM
EricHTry adding FIELDTYPE=R into the MFD for the field in question.
EricH
March 03, 2008, 10:44 AM
Maintain WizardJohn
Wait. What do you mean that "Include will make the difference?" If the key field is not readonly, and the fieldtype is numeric, you can absolutely have a value of 0 (zero). But, if the key field is auto increment, then I am not sure there is anyway to reset it, or include it as 0.
Mark
March 03, 2008, 12:08 PM
John_EdwardsYou mentioned include and delete without discussing revise or update, so I was of the opinion that you thought include would work differently. If this is not the case I will not go down that path.
My goal is to
avoid the zero -- I send null hoping for an autoincrement and am instead getting a zero. I just examined my master file and indeed the field is not typed as read-only, so I will flip that switch first and see if it changes the behavior.
Thanks for all the help -- I love you guys (sniff).
J.
March 03, 2008, 08:50 PM
John_EdwardsOh crap. I wrote the wrong thing in my original post. You guys think I'm trying to write a zero ID. I'm trying to get the autoincrement to assign a reasonable value for the ID, and when I send a missing ID I'm getting stuck with a zero in the data table. For some reason the missing ID is getting set to a zero in the data table.
Sorry for the confusion.
March 04, 2008, 08:29 AM
Maintain WizardThat makes more sense.
Maintain will respect the Readonly autoincrement field. Just leave that field off the Include line, and the database should create the value for you.
Mark