Hello,

I created some DTS-packages (SQL-Server 2000) to import data from our smd-system to baan. The package converts the informations and inserts them via "Oracle Provider for OLE DB" to a self generated table which is visible to baan. These informations are used in baan to generate outbound data with the afs.

So far so good.

On some records the db.delete statement fails with enocurr, error 112. After searching baanboard I found a similar problem which was solved by a "reorganize table". It works for me as well, but I'd really like to go deeper. This can't by my final solution...

Another hint for a different kind of database was to generate an extra baan index manually. It supposed to be a field in the table being a concatenation of the index fields as a single string. I don't see these fields in a "desc table".

Do you have some informations about these mystic index or any informations which could be interesting for solving my problem?

Thanks a lot,

Ralf Frochte

Are you refering to Baan's infamous hash columns?

There have been several myths around on how to create hash fields manually and they can be found on the board.

But didn't they finally introduce a level 2 driver for MSSQL not so long ago? If so, you might find that to be an easier solution.

Hi,

after some more reading I'm quite sure these infamous hash columns are not my problem.
For Oracle we already use the level 2 driver. So there should'nt be any hash columns, right? MSSQL is not visible for BAAN anyway. Thats why I use DTS jobs to import data.
Could invisible characters be in charge for the error 112?

Regards,

Ralf

Can you post some Code? Especialy the part with delete. On the first view it looks to me like there is a problem with the recordpointer (no current record)

Regards
csecgn

Hi,


here some code to make it more transparent:


table ttilub046

select tilub046.pdno, tilub046.item, tilub046.clot, sum(tilub046.stoc):stoc
from tilub046
group by tilub046.pdno, tilub046.item, tilub046.clot
selectdo
...
| some afs code to generate and release outbound data
...
on.main.table(delete.records, tilub046.pdno, tilub046.item, tilub046.clot)
...
endselect

functions:
function delete.records( domain tcpdno pdno,
domain tcitem item,
domain tdltc.clot clot) {
db.retry.point()
select tilub046.*
from tilub046 for update
where tilub046._index1 = {:item, :clot, :pdno}
selectdo
db.delete(ttilub046, db.retry)
commit.transaction()
endselect
}



I tried it with iterating over every record as well to eleminate the on.main.table() as a possible problem, but the behaviour is the same.

Here some more informations:

Before and after running the reorganize tables I dumped the table. I used create sequential dump and sqlplus in oracle. Both variants have the same results. The sortation is different but the data is exactly the same. I even used a hexedit to be very sure. I checked the size of the index in oracle. It has a plausible size at any time. All together a very strange thing...

Regards,

Ralf

As I understand, you fill the data in the table from outside and then you try to delete the data from a baan 4GL script. Correct?

If you do so, you have to keep track on the formatting of the fields. If you try to manipulate the data within baan you must fill all the fields according the domain definition from baan (Left/rigth adjusted, number formats, etc...)

/Lakoon

> you fill the data in the table from outside and then you try to delete the data from a baan 4GL script. Correct?

Yes. That's what I try to do :-)


> you must fill all the fields according the domain definition

In deed there was an error. I ajusted the clot left. It supposed to be right.
Thanks for this hint!!!

So I added blanks at the import mechanism. To be very sure I perform a tt.align.according.domain() on all strings of all records. The db.update() itself works fine. No errors.

One step forward two steps backword. Now I get error 111, no record found on db.delete() in function delete.records(). Hurray. I keep on working on that...

Regards,

Ralf

Hi,

it was the date field which is of type datetime in oracle and mssql. In this case Mssql inserts exact times but baan can not deal with them. Up to now I could not figure out what time to insert. A select to_char in oracle returns several values.


select distinct to_char(t$odat, 'hh:mm:ss') from baan.ttdsls041101
SQL> /

TO_CHAR(
--------
12:01:00
12:02:00
12:03:00
12:04:00
12:05:00
12:06:00
12:07:00
12:08:00
12:09:00
12:10:00
12:11:00

TO_CHAR(
--------
12:12:00

12 rows selected.


Time gets corrupted anyway when mssql/dts inserts the records. For example I forced and inserted 00:00:00 but the result was 13:00:03. A solution for this sould be easy to find... ;-)

Greetz,

Ralf