Jump to content


- - - - -

Pro-IV and Oracle


11 replies to this topic

#1 Guest_Dave_*

Guest_Dave_*
  • Guests

Posted 01 June 2003 - 08:24 AM

Hello, I have few questions about Pro-IV and Oracle.

I’ve been using Pro SL with ISAM database for a while. Now I’m starting to use Pro-IV SL Development with Oracle external database. I have server version of Pro-IV and Oracle 9i SE.

1. In server pro4.ini file I have environment variables for connection to Oracle where I can define user name, password and database name. Does that mean I need only one Oracle user for whole Pro-IV? Is there any way I can have named users – can every Pro-IV client (user) be Oracle named user and how can I and where configure that?

2. When I create file in SL and define it as an external Oracle file, Pro-IV creates sql script that I can use to create Oracle table. In Pro-IV I have key fields. Oracle transforms those fields into indexes. That means I don’t have constraints (primary or foreign key). I suppose I have to create them in Oracle and I’m interested if that changes performance and the fact that I might have to use same functions with Oracle and ISAM database? For example when I use ISAM files and I have to delete parent table value I have to use LU function to delete fields in child table. In Oracle I have foreign keys and option cascade on delete.

3. Can I call Oracle packages, procedures and functions from Pro-IV and how?

4. Can I use Oracle sequences?

#2 Albert Ho

Albert Ho

    Member

  • Members
  • PipPip
  • 19 posts
  • Gender:Male

Posted 02 June 2003 - 02:13 AM

Hi Dave, you could do the following

1. You can have 1 proiv user to 1 oracle user by having a saperate bootstrap scripts for each of them. Just change the SQL_USERNAME and SQL_PASSWORD line.

2. Well generally if you include Referential Integrity (RI), it is slower for proiv to process. But nevertheless this is an important feature that RDBMS has over ISAM and in ISAM I'm sure you would have to control RI at application level. As for function compatibility, I think you have to leave out that cascade option feature and rollback to manually delete the child record. This is to ensure maximum compatibility across Oracle and ISAM database.

3. For this I have not tried it on SL, only manage to implement on VIP, but it could work also. To call a oracle stored proc or function this is what you must include in your code

for function:

SQL
CALL FUNCTION1 (:$VAR1, $VAR2) INTO :#RTNVAL
ENDSQL

for procedure:

SQL
CALL PROCEDURE1 (:$VAR1, $VAR2)
ENDSQL

Be aware that proiv is not capable of receving a return value (#RTNVAL) from oracle function which is NOT numeric. You have to include the clause SQL..ENDSQL to indicate to proiv that this is a SQL stmt execution.
For Oracle packages it is similar I supposed, I have not tried before.

4. I wish to help you on Oracle sequences, but to start off I don't even know enough to talk about it, sorry.

#3 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 02 June 2003 - 07:20 AM

1.
2. That index create statement generated by P4 is for a unique index, this effectively
is a primary key, so you do not need to create any extra index in Oracle. Unless of
course you choose to for performance reasons.
3.
4. You can access Oracle from P4 sequences using embedded SQL, 'select fieldnam.nextval from dual;'
or by using db triggers if you don't need to do anything with the value in P4.
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#4 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 02 June 2003 - 07:22 AM

4. That is 'Oracle sequences from P4'

(Monday Morning)
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#5 Guest_Dave_*

Guest_Dave_*
  • Guests

Posted 02 June 2003 - 07:56 AM

1. You can have 1 proiv user to 1 oracle user by having a saperate bootstrap scripts for each of them. Just change the SQL_USERNAME and SQL_PASSWORD line.

An example of a proiv.ini file would be most useful.

#6 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 02 June 2003 - 09:42 AM

Format varies with platform, define these environment vars
for each user - outside of the common .ini file


SQL_DBNAME somedbname
SQL_DBTYPE ORACLE
SQL_USERNAME your_username
SQL_PASSWORD your_password
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#7 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 02 June 2003 - 10:00 AM

1. I have an good example of a script...

------START OF SCRIPT--------
export ORACLE_SID=ORCL

LD_LIBRARY_PATH=/usr/app/oracle/product/8.1.7/lib; export LD_LIBRARY_PATH
FILETYPE=ORACLE; export FILETYPE
SQL_DBTYPE=ORACLE; export SQL_DBTYPE
ORACLE_HOME=/usr/app/oracle/product/8.1.7; export ORACLE_HOME
SQL_USERNAME=orauser1; export SQL_USERNAME
SQL_PASSWORD=orauser1; export SQL_PASSWORD

if [ $TERM = proiv ]
then
export PROTERM=GUIDEV
fi


PRORUNTYPE=DEV
export PRORUNTYPE

PROPATH=/home/albert/pro
export PROPATH
PRODATA=/home/albert/data/
export PRODATA
cd /home/albert/data/
V22_EODCAN=Y
export V22_EODCAN

pro OPR PTI

------END OF SCRIPT--------

You can save this script for example as /usr/bin/testapp1 then change the SQL_USERNAME AND SQL_PASSWORD and save it as another filename for the another users.

#8 Albert Ho

Albert Ho

    Member

  • Members
  • PipPip
  • 19 posts
  • Gender:Male

Posted 02 June 2003 - 10:03 AM

Oh forgot to mention that the example script above is use for Linux. So if you are running on other OS you may need to add some platform dependent stuff in it.

#9 Ralph Gadsby

Ralph Gadsby

    Expert

  • Members
  • PipPipPipPip
  • 154 posts
  • Gender:Male
  • Location:United Kingdom

Posted 02 June 2003 - 11:22 AM

If you want to have individual connections to the datbase for each user it is far easier to set up OS authentication than than to change the start up script. It is also more secure, as the passwords aren't coded into the script.

#10 Guest_Dave_*

Guest_Dave_*
  • Guests

Posted 02 June 2003 - 11:50 AM

Format varies with platform, define these environment vars
for each user - outside of the common .ini file


SQL_DBNAME somedbname
SQL_DBTYPE ORACLE
SQL_USERNAME your_username
SQL_PASSWORD your_password


Chris:

Outside of pro4.ini file - does that mean create new file for each user?
Do I have name it something like user_name.ini?
Where and how do I call these files, from pro4.ini?

#11 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 02 June 2003 - 01:40 PM

Hi,

these are environment variables. You could define them in the users
login script.

As someone pointed out, it might be safer to use o/s authentication
rather than have the passwords in a .profile or login.com or whatever
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#12 Guest_Dave_*

Guest_Dave_*
  • Guests

Posted 03 June 2003 - 06:20 AM

OK

But I would like to skip OS autentication and have PRO-IV and Oracle autentication. User must enter his login and password for P4 and his login and password are send to Oracle (it could be the same as P4 login or not)



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users