Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Code & Utilities (http://www.baanboard.com/baanboard/forumdisplay.php?f=33)
-   -   Trick to access non-baan database tables from Baan (http://www.baanboard.com/baanboard/showthread.php?t=58399)

SujithKumar 7th May 2010 19:35

Trick to access non-baan database tables from Baan
 
While playing with a scenario i figure out a trick to Access tables from other databases within Baan. Would that be something which should go here? Any one would be interested in that ? If yes, i could write up the details. By the way its pretty cool and simple

mark_h 7th May 2010 19:44

Yes - that works for me. This forum is exactly for that type of sharing.

Look forward to reading it and will probably have some questions.

steveauckly 7th May 2010 20:33

Yes
 
Yes, please do.

SujithKumar 7th May 2010 23:36

This is basically tricking Baan system.
Let say you have a shipping database (worldship) and you want to see the shipping cost in Baan (may be include it in a Baan report).
So you need Reference No (which is Baan SO no), ship to zip, state, country, total shipping cost from table ShippingInfo in WshipDb
1) First create table definition whinh999 in Baan with the above fields
2) Create the table in Baan. So this would have created a table in baandb as twhinh999xxx, if you check SQL (using SQL server example here)
3) Delete the table FROM SQL server. Poor Baan doesnnt know its table is gone
4) create a VIEW in SQL with exact table name and fields (ie twhinh999xxx). This view will access the data from the shipping system (you will need to link the sql server etc, if not sure ask your DBA if you have one). Make sure the access to theis view is exactly the same as any other Baan table
5) Since the view is exactly the same as the Baan table and baan definition is intact, go to GTM and open the table and you will be surprised to see the data from worldship in Baans GTM!, which you can incorporate in reports and other sessions.
6) I dont think this will work if you are on the old level 1 SQL driver. Level 2 driver is needed. Probably most of us would more likely be on level 2

Caution-1. This would work nicely for read. But if you know what you are doing this could be made to write (ie write to another data base from baan)
Caution-2. When you have to change the table, you will have to back track and put the SQL table back and rename the view as '-bak' or so. After the table is reconfigured in Baan rename the view back and add the new field to the view

WHAT ARE THE POTENTIAL USES
I am still exploring all the possibilities, but these are the few areas i think it will be useful
- Integrating to other system in real time, instead of batch load (eg we have a small service database outside of Baan, now we could bring them together)
- Code re-use. I always find it easier to write sql queries/views directly in SQL. So i could create a complex view. Now the same view you can made to access it from Baan

benito 10th May 2010 03:49

shared memory
 
i'm curious what happens if you re-start the shared memory. have you tried this?

SujithKumar 10th May 2010 17:43

Benito, there wont be any problem after you re-start shared memory.
Why do you think there would an issue?

mark_h 11th May 2010 18:56

Where we ran into problems(trying this exact method on 4c4) was the convert to runtime. Once the view was created - any convert to runtime on any table (not this one) would have an error. For some reason with this view there was a problem somewhere - on 4c4. OOPs we also were using Oracle. No matter what we tried we would get this error to pop up - the only way to fix it was to drop the view, do a crdd,drop the table and re-add the view. Lots of headaches for us - but the theory is sound.

SujithKumar 11th May 2010 22:09

Ummm. We didnt try the convert run time on other tables. I need to check that out. We are on 5c, so not sure if the problem exists there too.
But it will be a pain if other tables convert to run time are effected.

litrax 12th May 2010 11:17

I had it running once with Oracle 8 in BaanIV. No problems there.

You just have to make sure, that all fields are the same.
E.g. it could be a problem if the Baan table has a field of type char as index field and the created view has varchar(2) as type of the index field.
Then you get inconsistent reads.

And don't forget to add the 2 Baan- specific fields T$REFCNTD and T$REFCNTU which must be of type number.

mark_h 12th May 2010 16:40

Quote:

Originally Posted by litrax (Post 163360)
And don't forget to add the 2 Baan- specific fields T$REFCNTD and T$REFCNTU which must be of type number.

This was probably the piece that we missed. Not sure since we had the problems we found other solutions to the problem. I can't remember what we were trying to read. We may have also still been on 4c3 when we tried this - can't remember for sure. From everything I have read this should certainly work and we probably missed something at the time.

SujithKumar 12th May 2010 19:04

I tried reconfiguring other tables while this 'tricky' table is there and worked fine.
I havent tried write to this kind of tables. Any one tried that?

f.martel 2nd June 2010 10:14

Yessssssssss

It's work find Thank's,

But, for oracle, you must give right to your view :

grant select on <base>.<view name> to <role>

(source our DBA Oarcle)

Fran├žois


All times are GMT +2. The time now is 12:17.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com