Hi,

I am facing problem using db.drop.table in my script.
the session is giving Error:203 "Action not allowed winthin transaction" at runtime.

Can anyone tell me in which section in my script I have to use db.drop.table so that I will be able to drop a perticular table.

Thanks

Is the table the main table of the session? Could be that that table is opened, transaction is started, automaticly as soon as the session is started.

Han

Hi,
you can use this in your function;
db.retry.point()
db.drop.table(yourtable) |drops your table
db.create.table(yourtable) |creates your table
commit.transaction()

be careful you will lost all the data in the table

Hi

It was a main table in the session. After changing the main table also its giving the same error. My code is as follows

cont.choice.process:
on.choice:
if delete.table = tcyesno.yes
table.id=db.bind("tdpur999")
ret=db.drop.tabel(table.id)
endif

and the table is not declared in the declaration also.
what could be the prolem?

I am using db.drop.table(), but getting the same error as mentioned by Arun.
Any solutions!?

Arun,

Refer to - db.bind() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_db_operations_db_bind)
Refer to the example -

long tcmcs001_id

if (switch.to.company(200) > 0) then
tcmcs001_id = db.bind("ttcmcs001")
db.first(ttcmcs001_id)
endif
...
db.first(ttcmcs001)

Correct your code for the db.bind() table name parameter it should be t<table-name>

cont.choice.process:
on.choice:
if delete.table = tcyesno.yes
table.id=db.bind("ttdpur999")
ret=db.drop.tabel(table.id)
endif

Hi all,

try the db.clear.table() function.
This will clear all rows from the table but the table would not dropped from the datadictionary.

Martin

Dear NP,
I am using db.bind() as per the syntax, but still getting the error.
I infact used db.create.table(), and encountered the same error, then I shifted this statement in before.program section, and it worked fine.
But, for db.drop.table() I am still getting the same error!!:mad: I am using it in the functions section.

db.drop.table is a table level operation . So I think table locking by db.lock.table is necessary.

As the error description says not allowed within a transaction. So db.retry.point /commit.transaction may not be necessary .

Hi morpheus, NP

I tried both db.drop.table & db.create.table in before.program section & are working fine. but when I try this in other section it give the same error that morpheus mentioned. So I think this works only in before.program section. Further comments from NP will be my pleasure.

-Mohan

Mohan,

Here is more information -

DDL statements are dangerous (http://rhea.redhat.com/bboard-archive/acs_design/0008k6.html)
I just discovered something I didn't know (and that other developers I've mentioned it to didn't know). Not only do DDL statements take effect immediately, they commit any uncommitted DML statements. So, DDL statements in transactions are doubleplus-dangerous.
Oracle and Transaction Management (http://www.csse.monash.edu.au/courseware/cse3000/notes/trans.html)

transaction ends when any of the following occurs:
You issue a COMMIT or ROLLBACK (without a SAVEPOINT clause) statement. You execute a DDL statement (such as CREATE, DROP, RENAME, ALTER). If the current transaction contains any DML statements, Oracle first commits the transaction, and then executes and commits the DDL statement as a new, single statement transaction. A user disconnects from Oracle. (The current transaction is committed.) A user process terminates abnormally. (The current transaction is rolled back.) After one transaction ends, the next executable SQL statement automatically starts the following transaction.
Note: Applications should always explicitly commit or roll back transactions before program termination.

DDL (http://www.db.cs.ucdavis.edu/public/oracle/faq/gloss_d.html)
Data Definition Language
Statements beginning with CREATE or DROP and ALTER statements that affect data, not privileges. Note that DDL statements perform an implicit commit and compare to DML.


So if you issue a commit.transaction before you invoke the db.create.table() the call will work.

Arun,
Can anyone tell me in which section in my script I have to use db.drop.table so that I will be able to drop a perticular table.
Can you please let us know why are you trying to drop the table whats the actual requirement ? :confused:
I have seen most programmers using - db.clear.table() and not db.drop.table(). If you like to delete table then you can also use - ttaad4231m000.

db.drop.table() will not give error in before.program. In other sections we get error since the standard program may have started the transaction. I am not sure about this.

As my requirement was to drop and create a table based on the form input, I could not use this in field section. db.clear.table worked fine but it used to take a lot of time to clear data. Here is what I did.

I created a simple 3GL script that has below functions...Ofcourse with all error handling.
db.bind()
db.drop.table()
db.create.table()

I will call the above 3GL to drop and create desired table based on my form input. Hope this helps.

Arun,

Alternatively - you can consider to write a AFS/API to the tools session - ttadv4231m000 - Delete Tables and let that handle all this transaction management.
Also it depends on what flag you are using in the db.clear.table() and that function has some issues with the porting sets you are using. Contact your local BaaN Support for more info.
I had a case for that issue and it was fixed with the 7.3.a.02 porting set.

Change the db.clear.table function, from "db.clear.table(<table>, NO.ROLLBACK)" to "db.clear.table(<table>, WITH.ROLLBACK)". However, this change will cause trouble with the new portingset and the fix for this problem. So if you change these functions, you will need to change them back before upgrading the porting set.

The problem occurs when doing a db.clear.table with NO.ROLLBACK.
So, probably the other sessions do also such db.clear.table.
For session whinp1200m000:

in the source code pwhinp12000 the following function causes the error:

function clear.inp100.B()
{
commit.transaction() |* Leave it this way.
db.retry.point() |* It's the only way in which
db.clear.table(twhinp100, NO.ROLLBACK) |* it is working correctly.
commit.transaction() |* Else error 203.
}

when changing this to:

function clear.inp100.B()
{
commit.transaction() |* Leave it this way.
db.retry.point() |* It's the only way in which
db.clear.table(twhinp100, WITH.ROLLBACK) |* it is working correctly.
commit.transaction() |* Else error 203.
}
problem does not occur, so this would be a workaround.


I used the following code, in a function invoked from a form command it works fine to me.

function extern fill.values()
{
double fincr, dincr
long i, tableid
dincr = 0.01
fincr = 0.01
string buff(256)
tableid = db.bind("tzmadm100", buff, 100)
if tableid then
if db.clear.table(tableid, 0, 100 ) then
exit(0)
endif
else
exit(1)
endif
}

db.clear.table() will work fine. But, in terms of performance which is better db.clear.table or drop/create a table considering the volume of data is high?

AFS/API to the tools session - ttadv4231m000 - Delete Tables is a good alternative. I would like to give a try.

Suppose both of u mean ttaad4231m000 and not ttadv4231m000.

Inside ttaad4231m000 , the program invoke db.drop.table itself according to me . Further when the transactions are on ttaad4231m000 will also give errors like 113.

So according to me db.drop.table() (directly if it works ) will be the fastest.It plainly deletes the table for the company without bothering for the number of records in the table.

i think a commit.transaction() is necesary before db.drop.table() instruction.

Hi All,

I also have similar problem. When I wrote db.drop.table() in function, it gives me error `Action not allowed within transaction' but when I wrote in before.program, it works fine.

My problem is that when I run my session manually, it works fine but when I run the session through JOB, Job ended with `run time error'.

Can anybody help me, thanks in advance

Do you have to use drop table?

commit.transaction()
db.retry.point()
db.clear.table(ttipgc900,1)
commit.transaction()

Of course I use this in a choice.continue section.

Hi Mark,

I want to delete few customised table in the begining of my session and these tables are having more the 2500000 records. When I use db.clear.table, then system goes hang. So I use db.drop.table & db.create.table, these command works fine when I write in before.program.

But when I run the same session through Job, it gives me 'run time error'. Can you please suggest me what to do.

Is there anything in any of the logfiles that might give us a better idea of what the error is?

I want to delete few customised table in the begining of my session and these tables are having more the 2500000 records. When I use db.clear.table, then system goes hang.
Manoj,

Refer to the Tools Programmer's manual for the function usage -

1 NO.ROLLBACK The table is cleared in one or more transactions, depending on the number of records in the table. Each transaction is automatically committed after 20 records have been deleted. The number of records deleted in the final transaction can be 20 or less; this depends on how many records remain to be deleted.

When you use this option, transactions are small and records are not saved in rollback segments.

When you use this option, you must place db.clear.table() at the start of the transaction.

The table is cleared in a single transaction. All records are saved in rollback segments. This is the default option.

The table is cleared in one or more transactions, depending on the number of records in the table. Each transaction is automatically committed after 20 records have been deleted. The number of records deleted in the final transaction can be 20 or less; this depends on how many records remain to be deleted.

When you use this option, transactions are small and records are not saved in rollback segments.

When you use this option, you must place db.clear.table() at the start of the transaction.
Depending on your processor's speed, memory, db configuration, db resource settings etc, 2.5 million record transaction can take a while.
So I use db.drop.table & db.create.table, these command works fine when I write in before.program.
If you drop the table then there are no records to be purged so it runs fast.

Note - Without looking at your code, it is difficult for others to suggest changes/ideas to improve.

NP - the programmers manual isn't really accurate there. Probably a Dinglish translation ;).

Delete generates rollback, period. They'd have to be doing truncate to avoid rollback, which they're not doing if they're committing every 20 records.


Folks using db.drop.table, how long does that take? it should take fairly long as well because when you issue a "drop table" to oracle it deletes each record in the table first. Unless db.drop.table truncates first, and then it's just evil that they didn't make clear.table do a truncate.

NP is there a db.truncate in LN? =) there should be!

If not, you can always do run.program('truncate.ksh') which is what i usually do. Truncate would obviously be a shell script to truncate the tables you care about.

Dave

Delete generates rollback, period. They'd have to be doing truncate to avoid rollback, which they're not doing if they're committing every 20 records.
From my findings, I don't think they are using truncate. I traced they were using the 'delete' statement, which does not seem to be correct based on the explanation.
NP is there a db.truncate in LN? =) there should be!
Dave, I checked the manual again, there is no db.truncate() function.
If not, you can always do run.program('truncate.ksh') which is what i usually do. Truncate would obviously be a shell script to truncate the tables you care about.
It is better to use the 'purge' option with the drop statement in Oracle 10g versions:
drop table table_name;
drop table table_name purge;

The drop table command moves a table into the recycle bin unless purge was also specified.

Besides checking logs and job history , u may use suspend(1000) . Suspend is very useful at times inter-process communications

Hi All,

Thanks for your valuable suggestion. here is my code

before.program:
lockedkey = "tipln8225m000"
ret.key = appl.set(lockedkey,APPL.EXCL)
if ret.key <> 0 then
appl.get.user(lockedkey,lockedby)
mess("tipln20216.01",1,lockedby)
|* %s User is using this session
end()
endif
initialize.table() | mks01.n

function initialize.table()
{
message.string = form.text$("tipln20225.14")
|* Deleting Old Data.........
display("message.string")

db.drop.table(ttipln844)
db.drop.table(ttipln839)
db.drop.table(ttipln838)
db.drop.table(ttipln837)
db.drop.table(ttipln836)
db.drop.table(ttipln835)
db.drop.table(ttipln834)
db.drop.table(ttipln840)
db.drop.table(ttipln828)
db.drop.table(ttipln827)
db.drop.table(ttipln826)
db.drop.table(ttipln825)

db.create.table(ttipln844)
db.create.table(ttipln839)
db.create.table(ttipln838)
db.create.table(ttipln837)
db.create.table(ttipln836)
db.create.table(ttipln835)
db.create.table(ttipln834)
db.create.table(ttipln840)
db.create.table(ttipln828)
db.create.table(ttipln827)
db.create.table(ttipln826)
db.create.table(ttipln825)

}

When I run manually, it works fine but through JOB, it gives me run time error.

db.drop.table takes few seconds to delete all the tables.

Thanks in advance

From my findings, I don't think they are using truncate. I traced they were using the 'delete' statement, which does not seem to be correct based on the explanation.


I agree. I didn't think they were using truncate.


Dave, I checked the manual again, there is no db.truncate() function.

"but there should be!" :D


It is better to use the 'purge' option with the drop statement in Oracle 10g versions:

That does something different and would still generate rollback.

Dave

Thanks All,

It working fine now

I agree. I didn't think they were using truncate.


"but there should be!" :D



That does something different and would still generate rollback.

Dave

****

Is this really so that you can't truncate table from baan if you don't use ksh in code?
We have a problem with multiple actions at the same time against systables with Informix. This is causing regenerative MRP to fail...it's dropping and creating a few tables every time. Any advices?

****

Is this really so that you can't truncate table from baan if you don't use ksh in code?
We have a problem with multiple actions at the same time against systables with Informix. This is causing regenerative MRP to fail...it's dropping and creating a few tables every time. Any advices?

That was an issue with informix when I was working in support a few years ago. Have they not fixed that yet? It should have been fixed in 9.4 or later?

Dave

This is version 9.4...I went through support site about MRP session. It seems that table re-creation can't be by-passed in regenerative mode.
Maybe Informix update is now an option...if it's really solved after this version.