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?
Click the link below to see the new game I'm developing!
Pro-IV and Oracle
Started by
Guest_Dave_*
, Jun 01 2003 08:24 AM
11 replies to this topic
#2
Albert Ho
-
- Members
-
- 19 posts
Member
- 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.
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
-
- Members
-
- 372 posts
ProIV Guru
- 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.
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.
of the poster and do not represent those of any organisation.
#4
Chris Mackenzie
-
- Members
-
- 372 posts
ProIV Guru
- Gender:Male
- Location:Bristol, United Kingdom
Posted 02 June 2003 - 07:22 AM
4. That is 'Oracle sequences from P4'
(Monday Morning)
(Monday Morning)
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.
of the poster and do not represent those of any organisation.
#6
Chris Mackenzie
-
- Members
-
- 372 posts
ProIV Guru
- 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
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.
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.
------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.
#9
Ralph Gadsby
-
- Members
-
- 154 posts
Expert
- 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?
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
-
- Members
-
- 372 posts
ProIV Guru
- 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
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.
of the poster and do not represent those of any organisation.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
