Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 7th May 2010, 18:35
SujithKumar SujithKumar is offline
Senior Member
 
Join Date: Aug 2004
Location: Newyork
Posts: 112
SujithKumar is on a distinguished road
Baan: Baan 5c - DB: SQL Server 2000 - OS: Windows 2000 Advanced server
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
__________________
Sujith Kumar
Teledyne Lecroy Inc
www.teledynelecroy.com
Reply With Quote
  #2  
Old 7th May 2010, 18:44
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 7th May 2010, 19:33
steveauckly steveauckly is offline
Senior Member
 
Join Date: Sep 2001
Location: Kansas City, MO, USA
Posts: 168
steveauckly is on a distinguished road
Baan: Baan IVc4 SP26 - DB: Oracle 10g - OS: Windows XP Pro
Yes

Yes, please do.
Reply With Quote
  #4  
Old 7th May 2010, 22:36
SujithKumar SujithKumar is offline
Senior Member
 
Join Date: Aug 2004
Location: Newyork
Posts: 112
SujithKumar is on a distinguished road
Baan: Baan 5c - DB: SQL Server 2000 - OS: Windows 2000 Advanced server
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
__________________
Sujith Kumar
Teledyne Lecroy Inc
www.teledynelecroy.com
Reply With Quote
  #5  
Old 10th May 2010, 02:49
benito's Avatar
benito benito is offline
Guru
 
Join Date: Jan 2002
Location: Eastern US
Posts: 514
benito is on a distinguished road
Baan: ERPLn 10.2.1 / BaanIVc4 - DB: Oracle11/Informix - OS: Unix/Linux
shared memory

i'm curious what happens if you re-start the shared memory. have you tried this?
Reply With Quote
  #6  
Old 10th May 2010, 16:43
SujithKumar SujithKumar is offline
Senior Member
 
Join Date: Aug 2004
Location: Newyork
Posts: 112
SujithKumar is on a distinguished road
Baan: Baan 5c - DB: SQL Server 2000 - OS: Windows 2000 Advanced server
Benito, there wont be any problem after you re-start shared memory.
Why do you think there would an issue?
__________________
Sujith Kumar
Teledyne Lecroy Inc
www.teledynelecroy.com
Reply With Quote
  #7  
Old 11th May 2010, 17:56
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #8  
Old 11th May 2010, 21:09
SujithKumar SujithKumar is offline
Senior Member
 
Join Date: Aug 2004
Location: Newyork
Posts: 112
SujithKumar is on a distinguished road
Baan: Baan 5c - DB: SQL Server 2000 - OS: Windows 2000 Advanced server
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.
__________________
Sujith Kumar
Teledyne Lecroy Inc
www.teledynelecroy.com
Reply With Quote
  #9  
Old 12th May 2010, 10:17
litrax's Avatar
litrax litrax is offline
Member
 
Join Date: Sep 2005
Location: Germany
Posts: 95
litrax is on a distinguished road
Baan: ERP LN (FP8) - DB: Oracle10g (10.2.0.1.0) - OS: Solaris
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.
__________________
Have a lot of fun...

Please look at this before asking wholes in the stomach ;-)
http://www.baanboard.com/programmers_manual_baanerp
Reply With Quote
  #10  
Old 12th May 2010, 15:40
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Quote:
Originally Posted by litrax View Post
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.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #11  
Old 12th May 2010, 18:04
SujithKumar SujithKumar is offline
Senior Member
 
Join Date: Aug 2004
Location: Newyork
Posts: 112
SujithKumar is on a distinguished road
Baan: Baan 5c - DB: SQL Server 2000 - OS: Windows 2000 Advanced server
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?
__________________
Sujith Kumar
Teledyne Lecroy Inc
www.teledynelecroy.com
Reply With Quote
Sponsored Links
  #12  
Old 2nd June 2010, 09:14
f.martel's Avatar
f.martel f.martel is offline
Member
 
Join Date: Mar 2006
Location: Isère, France
Posts: 59
f.martel is on a distinguished road
Baan: BaaN 4C - DB: oracle - OS: unix
Thumbs up

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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database mirroring(Without Baan net) r_bharose Operating Systems & Databases 11 18th August 2003 07:44
Should Baan provide a level 2 database driver for SQL server 2000 on BaanIVc4? patvdv Polls and Surveys 6 28th March 2003 13:22
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 19:44


All times are GMT +2. The time now is 02:23.


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