I am a newbie at programming, no formal training so I apologize up front if this has been addressed or makes no sense. Please excuse the crudeness of my code, it was pieced together from research and is trial code. Was not able to find this exact answer on BB. Is it possible to write a script that will do a db.update based on information stored in a db.columns.to.record or is it only possible to do a db.insert?

I saw a similar post (http://www.baanboard.com/baanboard/showthread.php?t=15228) which suggests deleting the record for the new item, use db.record.to.columns and then db.insert. This would probably work but I am trying to execute this for tcibd001 and references exist on the item number so it can not be deleted.

End goal, update an item's fields based on the values of another item in the same table.

The code below will run without error but no update occurs on the record in tcibd001 (copy.item). It remains untouched.


db.row.length(ttcibd001,buff.size)
alloc.mem(rec.buff,buff.size)
select tcibd001.*
from tcibd001
where tcibd001._index1 = {:i.item} |i.item is the item with information to be copied
selectdo
tcibd001.item = copy.item |copy.item is the item to be updated
tcibd001.seab = copy.item(10;37)
tcibd001.dfit = copy.item
tcibd001.txta = 0
table.id = db.bind("ttcibd001",rec.buff)
db.columns.to.record(table.id)
endselect

db.retry.point()

select tcibd001.*
from tcibd001 for update
where tcibd001._index1 = {:copy.item}
selectdo
db.record.to.columns(table.id)
db.update(ttcibd001,db.retry)
commit.transaction()
endselect
db.unbind(table.id)
free.mem(rec.buff)



Any help is greatly appreciated. I am trying to avoid writing a script that does an update at the field for field level but if that is the only way, then so be it.

Yes it is possible. Since I am familiar with 4c4 let me try an example with it. And no I am not sure it will work in LN.

functions:
|orig.item - the item you want to copy data from
|copy.item - item to have the data updated from orig item
function find.the.item()
{
select tiitm001.*
from tiitm001
where tiitm001.={:orig.item}
as set with 1 rows
selectdo
endselect
store.item.record.buffer()
| now find the item you want to update
db.retry.point()
select tiitm001.*
from tiitm001 for update
where tiitm001.={:orig.item}
as set with 1 rows
selectdo
endselect
rcd.ttiitm001 = record.buff | set it to the record buffer of the orig.item
tiitm001.item = copy.item | Make sure key index is reset to the correct item
db.update(ttiitm001,db.retry)
commit.transaction()
}

function store.item.record.buffer()
{
if not record.len then
db.row.length(ttiitm001, record.len)
alloc.mem(record.buf, record.len)
endif
record.buf = rcd.ttiitm001
}


This should update every field of copy.item with the fields in orig.item. Or if you wanted you could find copy.item and delete it. Then find the orig.item, set the item number to copy.item and do an insert. This code does not include any error checking or anything of the sort - so make sure the original item and copy item are valid items.

Thank you for the reply and suggestion mark_h. I was able to modify your code a bit for LN but still receive a 206 Record not locked error.


function find.the.item()
{
orig.item = " 223.022.02"
copy.item = " 0223.925.000"

select tcibd001.*
from tcibd001
where tcibd001.item ={:orig.item}
as set with 1 rows
selectdo
endselect
store.item.record.buffer()

| now find the item you want to update
db.retry.point()
select tcibd001.*
from tcibd001 for update
where tcibd001._index1 ={:orig.item}
as set with 1 rows
selectdo
endselect
rcd.ttcibd001 = record.buf | set it to the record buffer of the orig.item
tcibd001.item = copy.item | Make sure key index is reset to the correct item
db.update(ttcibd001,db.retry)
commit.transaction()
}

function store.item.record.buffer()
{
if not record.len then
db.row.length(ttcibd001, record.len)
alloc.mem(record.buf, record.len)
endif
record.buf = rcd.ttcibd001
}


I also tried:

function find.the.item()
{
orig.item = " 223.022.02"
copy.item = " 0223.925.000"

select tcibd001.*
from tcibd001
where tcibd001.item ={:orig.item}
as set with 1 rows
selectdo
endselect
store.item.record.buffer()

| now find the item you want to update
db.retry.point()
select tcibd001.*
from tcibd001 for update
where tcibd001._index1 ={:copy.item}
as set with 1 rows
selectdo
endselect
rcd.ttcibd001 = record.buf | set it to the record buffer of the orig.item
tcibd001.item = copy.item | Make sure key index is reset to the correct item
db.update(ttcibd001,db.retry)
commit.transaction()
}

function store.item.record.buffer()
{
if not record.len then
db.row.length(ttcibd001, record.len)
alloc.mem(record.buf, record.len)
endif
record.buf = rcd.ttcibd001
}


And got the same error. Any thoughts or assistance is greatly appreciated.

Do you have a db.retry set anywhere?

Okay - I think I know what it is. When you use rcd.ttXXXXX type structure there are some variables in the background like tcibd001._dlock. I bet the first copy to buff sets it to 0 since it is not locked. Then when you set the second buffer to the first - it makes that variable 0 or not locked. So for grins and giggles - no promises:


long hold.lock
select tcibd001.*
from tcibd001
where tcibd001.item ={:orig.item}
as set with 1 rows
selectdo
endselect
store.item.record.buffer()

| now find the item you want to update
db.retry.point()
select tcibd001.*
from tcibd001 for update
where tcibd001._index1 ={:copy.item}
as set with 1 rows
selectdo
endselect
hold.lock = tcibd001._dlock
rcd.ttcibd001 = record.buf | set it to the record buffer of the orig.item
tcibd001.item = copy.item | Make sure key index is reset to the correct item
tcibd001._dlock = hold.lock
db.update(ttcibd001,db.retry)
commit.transaction()
}

function store.item.record.buffer()
{
if not record.len then
db.row.length(ttcibd001, record.len)
alloc.mem(record.buf, record.len)
endif
record.buf = rcd.ttcibd001
}


I found this in the help on dlock -
When placing a delayed lock on a record, the program fills the field <table>._dlock with an identification number linked to the record. This field identifies the original record. This guarantees a fast search procedure. This field will be overwritten when changing the record buffer.

Notice the last statement. I do not promise this will work and have never tried it myself.

Hi Vince! I actually did in one of the previous itterations but looks like I forgot it here. I had it in front of the first select query. I actually tried a field for field version like we discussed and was finally able to get that to work but it just seems so drawn out (one of the tables I am working on has 100+ fields) so as you can see I am still determined to get this. It is soooo close.

@mark_h, thanks i will give it a look and and other try.

Thanks to you both for the replies.

If above suggestion by Mark doesnt work, also try this:-

1. Try removing/commenting db.set.retry.point from your code
2. add elocked to your db.update i.e. db.update(ttcibd001,db.retry,elocked)

I have faced this and solved by doing the above things.

Also, you can try writing your code in after.update.db.commit section.

@vince, I just got what you were suggesting, not that it was missing but perhaps it was causing the issue. Sorry about that.

@mark_h, no worries, I know this is experimental and I am in a development environment. I am just thankful for the help.

So both methods suggested allowed for the process/update to complete but when reveiwing the records of both items (orig and copy) they do not match. This is the same error I was having originally. Does this process only copy certain fields or an entire records (except the field I change, .item), or is there some sort of setting in the declaration of the buffer that tells it what to allow?

For example, I am looking at the tcibd001.cmnf field. On the orig item it is " " and on the copy item it has a value. After running the session I was expecting for the copy.item.cmnf to be " " but its value remains. I did see that the item group changed properly though.

Well on 4c4 I would expect it to be all the fields. I am not sure about LN and things like segmented domains, etc. What you can do is run it in debug mode and see what each of the fields are at the various points of the code. You might have to post the whole script for the LN experts. Since I am 4c4 what I do, or even suggest, might not be relevant.

Shouldn't be fundamentally different from Baan IV, just different tables. Segmented domains shouldn't matter since we're addressing the whole field.

V

*Solved. I thought the "elocked" version was working but when checking 'e' in the debugger, it was throwing a 206 error so nothing was updating. So I must have had a typo or something in the other approach. I went in and scrapped everything and re-wrote from scratch. This time it worked and updated all fields. Just wanted to close the loop on this one and thank all that replied, for the help and the patience. Final code was :


declaration:
table ttcibd001
domain tcitem copy.item,orig.item
string record.buf based
long record.len
long table.id
long hold.lock

#include <bic_dam>
#include <bic_tt>

functions:
function extern copy.rec()
{
orig.item = " xxx.xxx.xx"
copy.item = " xxxx.xxx.xxx"

select tcibd001.*
from tcibd001
where tcibd001.item ={:orig.item}
as set with 1 rows
selectdo
endselect
store.item.record.buffer()

| now find the item you want to update
select tcibd001.*
from tcibd001 for update
where tcibd001._index1 ={:copy.item}
as set with 1 rows
selectdo
endselect
hold.lock = tcibd001._dlock
rcd.ttcibd001 = record.buf
tcibd001.item = copy.item
tcibd001._dlock = hold.lock
db.update(ttcibd001,db.retry)
commit.transaction()
}

function store.item.record.buffer()
{
if not record.len then
db.row.length(ttcibd001, record.len)
alloc.mem(record.buf, record.len)
endif
record.buf = rcd.ttcibd001
}


Hope this helps someone else but use at your own risk. Caution, you have to be careful to make sure you correct important fields based on the table you use this with. Example: on items general table, the Search Key II for us is the same as the part number. Also we don't want to overwrite the Item Signal. Have to make sure and correct that (not reflected in the script above).

-JP

So, it looks like you have removed the db.retry.point.

Good! :)

Congrats. :)