I have a requirement wherein i have to write a record into a table. Now the table may have the record or may not. So depending on either condition i would like to do an update or an insert on the table.

Again the table name is dynamic (so i cant use a select).

I have written the following code to achieve this result

db.insert(t<tbl>,db.retry,db.return.error)

on case db.error()
case 0:
break
case 100:
buffer = rcd.<tbl> | Store to be updated values
db.eq(t<tbl>,db.delayed.lock(or db.lock)) | Lock Tbl
rcd.<tbl> = buffer | Restore to be updated values
db.update(t<tbl>,db.retry)
break
default:
<appropriate error messages>
endcase

Now in this case when i do a db.update it always returns an error 206 (record not locked). Instead of assiging the whole rcd if i assign any fields other than the index fields the update works( no error messages).

First : why the error message
Second : How to achieve the update by assigning the whole rcd. I have to do this since the table is dynamic and i dont know at runtime which fields are index fields and have to be excluded.

Anup

I´ve done somthing seemed I did not update but I think that it is very seemed that you want to do

this is the example

function dll0026.archive.table(
string table.name(8),
domain tcncmp comp.org,
domain tcncmp comp.arch,
domain tcncmp comp.arch2,
ref string record.buffer)
{


long pointer.org
long pointer.arch
long pointer.arch2

pointer.org = db.bind("t"&table.name,"",comp.org)
pointer.arch = db.bind("t"&table.name,"",comp.arch)
pointer.arch2 = db.bind("t"&table.name,"",comp.arch2)
if pointer.org > 0 and pointer.arch > 0 and pointer.arch2 > 0 then
db.first(pointer.org)
while e = 0
db.retry.point()

db.record.to.columns(pointer.arch)
db.insert(pointer.arch, db.retry, db.return.dupl)
commit.transaction()

db.next(pointer.org)
endwhile
db.unbind(pointer.org)
db.unbind(pointer.arch)
db.unbind(pointer.arch2)
endif

}

you have a more complete example in the dll tccomdll0026 that is the dll of the archiving process.

Another solution maybe use dinamyc SQL

good luck

Hi dorleta,

thanks for ur inputs. In my case too the db.insert works fine.. I am facing problem with the updates. In insert there is no record to be locked.

Anup

Why use db.* calls? A bit old fashioned, isn't it?

Try this:


select <tbl>.* for update
where <tbl>._index1 = {...}
selectdo
db.update(...)
selectempty
db.insert(...)
endselect


Regards,
Nico

Hi Nico,
i cant use select statements bcoz my table is dynamic. So cant write a select statement.

so have to depend on db.* statements.

Anup

Anup,

As Dorleta, gave you the info, refer to the dll - tccomdll0026-Archive Table Distribution.

It has the code to do both inserts and updates.


pointer.org = db.bind("t"&table.name,"",comp.org)
pointer.arch = db.bind("t"&table.name,"",comp.arch)
if pointer.org > 0 and pointer.arch > 0 then
mess("tcibds5214",0)
|Archiving master data ...
db.first(pointer.org)
while e = 0
copy.mem(record.buffer.sv,record.buffer,int.length)

if db.eq(pointer.arch,db.lock) then
for text.field = 1 to text.counter
store.long(0,
record.buffer(
text.field.position(
text.field)))
endfor
db.record.to.columns(pointer.arch)
empty.ref.fields(table.name)
db.insert(pointer.arch)
commit.transaction()
else
copy.mem(record.buffer,record.buffer.sv,
int.length)
for text.field = 1 to text.counter
store.long(0,
record.buffer(
text.field.position(
text.field)))
endfor
db.record.to.columns(pointer.arch)
empty.ref.fields(table.name)
db.update(pointer.arch)
commit.transaction()
endif
db.next(pointer.org)
endwhile
db.unbind(pointer.org)
db.unbind(pointer.arch)
clean.mess()
endif

I do not have access to source codes.So could not check. Will check this out.

Thanks

Anup

problem solved. the difference between what i was doing and what the standard was doing is that i was using db.update with retry. So db.update without a retry works fine.

Now does this mean i dont have the functionality of a rollback. In this case each update becomes a new transaction in itself. but suppose i want to make a transaction in which i update more than one table , how do i handle the rollback.. or is it automatically handled if the commit.transaction is given after all the tables in the transaction.

bye
Anup