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 14th December 2007, 22:41
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
Good Practice for storing 'external' source code in BAAN

This refers to the thread I started by the same name in Tools.

In case this helps anybody, here is the concept of how I manage the 'external' source scripts within BAAN:

This is also an example of how to read a table in an external database and populate a corresponding BAAN table, so that the data can be used for further processing in BAAN.

The purpose was to read an external table and pull in the data into a table defined in BAAN, so that it can then be processed further more efficiently (than using views).

[This approach works for ORACLE backend. I am not sure if it will work for other Database environments.]

- First, define the BAAN Table as usual

- Next generate a new session of type 'Update' with the new table as its main table. Let us say the session is ppmmm9299m00c

- Next, create two new reports by getting into Maintain Reports
-- The first report is named ppmmm9299.sh
-- The second report is named ppmm9299.sql
Both these reports do NOT have any fields, layouts etc. etc. We are just going to use the Edit Script option to put in our scripts. The script (obviously) will never be compiled. The reason for using Reports is because it allows us to define multiple scripts.

- Attach the reports to the session. Thereafter, we can do everything using the Maintain Session as the 'console'. The other advantage is that when we export the session, our 'report scripts' will follow the session (depending on how the export is done - I usually do it by session).

- Next, the Program Script itself
This will basically call the shell script. It needs to obtain the TABLESPACE information from the ora_storage file and pass it on to the shell script (along with a few other arguments, like Company Number).

- Then, in Maintain Reports/ Edit Script for the first report, create the shell script. This script is required to create an environment for SQLPlus - this assumes that the /etc/profile is set up by the unix admin is such a way that when executed, it will create an environment suitable to start SQLPlus.) After that, it simply calls SQLPlus with the name of second report script, which contains the SQLPlus commands.

- Then, finally, in Maintain Reports/ Edit Script for the second report, create the SQLPlus commands to pull in the data from the external table.

That's it. The whole setup runs as a BAAN Session.

Now for some more details:

The main program script is quite simple - just calls the shell script defined in the first report:
Code:
|****************************** declaration section ***************************
declaration:

        table   tppmmm999       |* Not really required - just mnemonic

                domain  tcncmp          p.ncmp.c        |* Company Number
                        string          p.unx.cmnd.c(4000)
        extern  domain  tcstr.128.c     p.ksh.flnm.c
        extern  domain  tcstr.128.c     p.sql.flnm.c

        extern  domain  tcmcs.st80      p.dat.tablespace.c
        extern  domain  tcmcs.st80      p.idx.tablespace.c

|****************************** form section **********************************

form.1:
init.form:
        get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
before.choice:
        ppmmmdllnnnn.get.tablespace.names(
                                main.table$(2),
                                p.dat.tablespace.c,
                                p.idx.tablespace.c)
        if  isspace(p.dat.tablespace.c)
        then
            mess("tigens9009.c", 1, main.table$)
            |* Data tablespace not defined in ora_storage for %s
            choice.again()
        endif
        if  isspace(p.idx.tablespace.c)
        then
            mess("tigens9010.c", 1, main.table$)
            |* Index tablespace not defined in ora_storage for %s
            choice.again()
        endif
        if pathname("rppmmm9299.sh", "P", p.ksh.flnm.c)
        then
            mess("tigens9008.c", 1, p.ksh.flnm.c)
            |* File not found: %s
            choice.again()
        else
            display("p.ksh.flnm.c")
            refresh()
        endif
        if pathname("rppmmm9299.sql", "P", p.sql.flnm.c)
        then
            mess("tigens9008.c", 1, p.sql.flnm.c)
            |* File not found: %s
            choice.again()
        else
            display("p.sql.flnm.c")
            refresh()
        endif
on.choice:
        process.refresh()


|****************************** field section *********************************


|****************************** function section ******************************

functions:

function process.refresh()
{
        p.ncmp.c = get.compnr()

        p.unx.cmnd.c = "/bin/ksh "
                     & strip$(p.ksh.flnm.c)                     |* $0
                     & " "
                     & strip$("/tmp/" & prog.name$ & ".log")    |* $1
                     & " "
                     & strip$(p.sql.flnm.c)                     |* $2
                     & " "
                     & strip$("/tmp/" & prog.name$ & ".out")    |* $3
                     & " "
                     & str$(p.ncmp.c)                           |* $4
                     & " "
                     & strip$(p.dat.tablespace.c)               |* $5
                     & " "
                     & strip$(p.idx.tablespace.c)               |* $6
        shell(p.unx.cmnd.c, 0)
}
|******* End of Source Code *******

Next- the first report that holds the shell script which looks as follows:
Code:
#!/bin/ksh
#******************************************************************************
#* ppmmm9299.sh   VRC B40C c3 tst
#* Shell Script to fire ppmmm9299.sql
#*
#* This report script is actually SHELL script.
#* DO NOT COMPILE the report.
#*
#* Conventions:
#* - $0 = this script's full file name
#* - S1 = this script's log file name
#* - $2 = the SQLPlus script's full file name
#* - S3 = SQLPlus script's spool/output file name [need not be used]
#* - S4 = SQLPlus script's Company Number
#* - S5 = SQLPlus script's Data Tablespace
#* - S6 = SQLPlus script's Index Tablespace
#* - $7 ... = maybe more arguments to be passed on to the SQLPlus script
#******************************************************************************

. /etc/profile
LOG_FILE=$1

echo Start Time: `date` > $LOG_FILE

# Start: for debugging.
# Can be commented out when production-ready.
echo $0 >>  $LOG_FILE
echo $1 >>  $LOG_FILE
echo $2 >>  $LOG_FILE
echo $3 >>  $LOG_FILE
echo $4 >>  $LOG_FILE
echo $5 >>  $LOG_FILE
echo $6 >>  $LOG_FILE
# End: for debugging.

# Following approach for calling the SQLPlus script makes
# for the SAME MAPPING of the $n arguments in this script
# as well as the SQLPlus script, meaning $3 here is
# the same as $3 or &3 in the SQLPlus script
# The SQLPlus script or its wrapping shell scrip
# does not need to use the arguments if it does not need to.

sqlplus baan/passwordforbaan <<EOF
@$2 $*
quit
EOF

echo End Time: `date` >> $LOG_FILE
#exit

Then we have the SQLPlus commands in the second report:
Code:
--******************************************************************************
--* ppmmm9299.sql   VRC B40C c3 tst
--* SQLPlus Script:Refresh ppmmm999 from Table SCHEMA.TABLE
--*
--* This report script is actually a SQLPlus script.
--* DO NOT COMPILE the report.
--*
--* Argument Mapping:
--*
--* - $3 or &3 = Spool/Output File Name
--* - $4 or &4 = Company Number
--* - $5 or &5 = Data Tablespace
--* - $6 or &6 = Index Tablespace
--******************************************************************************

set autocommit 1000

spool &3.

drop TABLE BAAN.Tppmmm999&4.
/

CREATE TABLE BAAN.Tppmmm999&4.
(
  T$FLD1     CHAR(3 BYTE)                       NOT NULL,
  T$FLD2     CHAR(2 BYTE)                       NOT NULL,
  T$FLD3     CHAR(7 BYTE)                       NOT NULL,
  T$FLD4     CHAR(32 BYTE)                      NOT NULL,
  T$REFCNTD  NUMBER                             NOT NULL,
  T$REFCNTU  NUMBER                             NOT NULL
)
TABLESPACE &5.
STORAGE    (
            PCTINCREASE      0
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
/

insert into BAAN.Tppmmm999&4.
(T$FLD1, T$FLD2, T$FLD3, T$FLD4, T$REFCNTD, T$REFCNTU)
(select
cast(nvl(fld1, '   ') as char(3))                               t$fld1,
cast(nvl(fld2, '  ') as char(2))                                t$fld2,
cast(nvl(fld3, '       ') as char(7))                           t$fld3,
cast(substr(nvl(fld4, rpad(' ', 32, ' ')), 1, 32)  as char(32)) t$fld4,
0 t$refcntd,
0 t$refcntu
from externalschema.external_table@external_connection_link)
/

CREATE UNIQUE INDEX BAAN.Tppmmm999&4.$IDX1 ON BAAN.Tppmmm999&4.
(T$FLD1, T$FLD2, T$FLD3)
LOGGING
TABLESPACE &6.
STORAGE    (
            PCTINCREASE      0
           )
NOPARALLEL
/

CREATE UNIQUE INDEX BAAN.Tppmmm999&4.$IDX2 ON BAAN.Tppmmm999&4.
(T$FLD2, T$FLD1, T$FLD3)
LOGGING
TABLESPACE &6.
STORAGE    (
            PCTINCREASE      0
           )
NOPARALLEL
/

GRANT DELETE, INSERT, SELECT, UPDATE ON  BAAN.Tppmmm999&4. TO R_BAAN
/


spool off

That completes the objects that belong to the session. The form is just a blank screen with the <Continue> Button.

Finally, the work of obtaining the TABLESPACE information is delegated to the library:
Code:
|******************************************************************************
|* ppmmmdll9999  0  VRC B40C c3 tst
|* ORA_STORAGE Parsing
|******************************************************************************
|* Script Type: Library
|******************************************************************************


#define TEXTMODE        1       |* For Text Input (any non-zero number)
#define READ.MODE       "r"
#define MAX.RECORD.LENGTH               2048

function extern ppmmmdll9999.get.tablespace.names(
                domain  tcmcs.st20      i.tablename.c,
        ref     domain  tcmcs.st80      o.dat.tablespace.c,
        ref     domain  tcmcs.st80      o.idx.tablespace.c)
{
        |* This function is used to parse ORA_STORAGE records in order
        |* to get the tablespace names for the data and index tables.
        |* First, it is attempted to get the table specific entry
        |* from the ORA_STORAGE file. If that is not found, then
        |* the 'fall through' ORA_STORAGE entry is located.

        |* Get DATA Tablespace
        o.dat.tablespace.c = ppmmmdll9999.get.tablespace( i.tablename.c, "T")
        if  isspace(o.dat.tablespace.c)
        then
            |* Get the default
            o.dat.tablespace.c = ppmmmdll9999.get.tablespace( "*", "T")
        endif

        |* Get INDEX Tablespace
        o.idx.tablespace.c = ppmmmdll9999.get.tablespace( i.tablename.c, "I")
        if  isspace(o.idx.tablespace.c)
        then
            |* Get the default
            o.idx.tablespace.c = ppmmmdll9999.get.tablespace( "*", "I")
        endif
}


function extern domain tcmcs.st80 ppmmmdll9999.get.tablespace(
                domain  tcmcs.st20      i.tablename.c,
                domain  tcmcs.str1      i.tabletype.c)
{
        |* Here are examples of ORA_STORAGE entries:
        |* Even though multiple lines are shown for the first two records,
        |* in the ora_storage file, these are a single record.
        |       tiitm001:999:T:group:0214:5:PCTFREE 5
        |               TABLESPACE cnnn_dat15
        |               INITRANS 3 storage(pctincrease 0 initial 4m next 4m)
        |       tiitm001:999:I:group:0214:5:PCTFREE 5
        |               TABLESPACE cnnn_idx15
        |               INITRANS 3 storage(pctincrease 0 initial 4m next 4m)
        |       *:999:T:group:0214:5:PCTFREE 5 TABLESPACE cnnn_dat01
        |       *:999:I:group:0214:5:PCTFREE 5 TABLESPACE cnnn_idx01

                domain  tcncmp          p.ncmp.c        |* Company Number
                        string          p.unx.cmnd.c(4000)
                domain  tcstr.300       p.input.record.c

                domain  tcmcs.st80      p.tmp.tablespace.c

                domain  tcstr.128.c     p.unix.tmp.flnm.c
                domain  tcmcs.long      p.unix.tmp.FD.c |* File Descriptor

        p.tmp.tablespace.c = ""
        p.ncmp.c = get.compnr()
        p.unix.tmp.flnm.c = creat.tmp.file$(BSE.TMP.DIR$())

        p.unx.cmnd.c = "/bin/grep "
                     & "'"
                     & strip$(i.tablename.c)    |* Tablename
                     & ":"
                     & strip$(shiftl$(edit$(p.ncmp.c, "999")))  |* Company Nmber
                     & ":"
                     & "["
                     & tolower$(i.tabletype.c) & toupper$(i.tabletype.c)
                     & "]"
                     & "' "
                     & strip$(BSE.DIR$() & "/lib/ora/ora_storage")
                     & " | "
                     & " /bin/sed "
                     & "'"
                     & "s/^.* [tT][aA][bB][lL][eE][sS][pP][aA][cC][eE]"
                     & "/TABLESPACE/"
                     & "'"
                     & " | /bin/cut -d ' ' -f 2"
                     & " > "
                     & strip$(p.unix.tmp.flnm.c)
        shell(p.unx.cmnd.c, 0)

        p.input.record.c = ""
        p.unix.tmp.FD.c = seq.open(p.unix.tmp.flnm.c, READ.MODE)
        if  p.unix.tmp.FD.c > 0
        then
            seq.gets(p.input.record.c,
                     MAX.RECORD.LENGTH,
                     p.unix.tmp.FD.c)
            if  not seq.error(p.unix.tmp.FD.c)
            then
                p.tmp.tablespace.c = p.input.record.c
            endif
        endif

        seq.close(p.unix.tmp.FD.c)
        seq.unlink(p.unix.tmp.flnm.c)
        return(p.tmp.tablespace.c)
}
|******* End of Source Code *******

Last edited by shah_bs : 3rd April 2011 at 02:51. Reason: Added more 'keywords'
Reply With Quote
Sponsored Links
  #2  
Old 30th July 2008, 22:02
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
A small tweak to the file ppmmm9299.sh:

Right after the line
Code:
LOG_FILE=$1
add:
Code:
SPOOL_FILE=$3
and just before the bottom line
Code:
#exit
add:
Code:
chmod 666 $LOG_FILE
chmod 666 $SPOOL_FILE
This is to take care of installations where the files created in /tmp do not have sufficient over-write permissions by default.
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
DEV: Extend Baan code without sources ~Vamsi Code & Utilities 87 26th August 2014 10:43
BaaN - 114 , Baan Roles . Spain - Transacciona C. andres77 Jobs and Resumes 0 20th January 2006 00:07
Baan World Users conference in Orlando chadwickh General Discussion & Chat 7 3rd November 2001 21:55
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 14:34.


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