IPB

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> execute stored procedures, how to execute stored procedures
lliuphx
post Feb 7 2007, 11:40 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 18-December 06
Member No.: 2047



I would like to declare a refcursor and execute a stored procedure from the db tool so that I can use the shared global database properties. Currently, I am using a script to accomplish the stored procedure; however since it seems that one can not access the global database properties from a script - I am forced to use an external properties file. I would like to get around this.
Go to the top of the page
 
+Quote Post
SOAPtest007
post Feb 7 2007, 01:52 PM
Post #2


Advanced Member
***

Group: Members
Posts: 37
Joined: 16-December 03
Member No.: 85



If I understand your post correctly, it appears that you are using a Method Tool in SOAtest to execute a stored procedure. It also appears that you have the database settings, i.e. driver, URL, username, password, in an external properties file. Using this set-up, you are able to run the test properly, but you would like to not have to use an external properties file.

If this is the case, at this point, we do not offer a way to access the shared global database properties from inside a script. One work around could be to put the global database properties into the application Context and access them from a script. This work around will involve creating a set-up test which puts the global database properties into the application Context and modifying the existing script to retrieve the database settings from the application Context instead of from an external properties file.

Would you be interested in this work-around? If so, I can create an example test suite that shows how to use the application Context.

Another suggestion would be to copy and paste the database settings in the scripts so that you don't need an external properties file. This suggestion is not practical if you have many scripts that require the database settings or if you have multiple database settings that you want to test against.
Go to the top of the page
 
+Quote Post
lliuphx
post Feb 7 2007, 03:35 PM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 18-December 06
Member No.: 2047



I had preferred to use the DB tool; since it is a cleaner interface and we don't use the returned results. I am able to define the ref cursor and execute the procedure though sqlplus. Couldn't that be possible though the DB tool as well?
It certainly seems roundabout to define global db properties and then can't use them directly within a method script - but have to insert them into application context and only then access them though the script.
My real problem is trying to implement exception handling within the method; I keep getting evaluation errors; so I was trying not to use the method approach.
Go to the top of the page
 
+Quote Post
SOAPtest007
post Feb 8 2007, 01:21 PM
Post #4


Advanced Member
***

Group: Members
Posts: 37
Joined: 16-December 03
Member No.: 85



Would you mind sending us the script that you're using? Currently, our DB tool is limited to accessing the DB via the JDBC interface. We would need to see how you are defining the ref cursor and executing the procedure to see whether our DB tool could support your use case.

Thanks.
Go to the top of the page
 
+Quote Post
lliuphx
post Feb 12 2007, 12:39 PM
Post #5


Newbie
*

Group: Members
Posts: 5
Joined: 18-December 06
Member No.: 2047



I'm assuming that since the refcursor is Oracle specific, that the DB tool will not support it. I have attached my script. This is what one can execute though oracle sqlplus:

SQL> var rc refcursor
SQL> exec :rc := get_emps(30)

Also, what would be the way to establish a db connection and share that within other scripts?
Go to the top of the page
 
+Quote Post
bdai
post Feb 13 2007, 07:51 PM
Post #6


Member
**

Group: Members
Posts: 25
Joined: 18-August 05
Member No.: 820



Hi lliuphx,

SOAtest ships with oracle.jar already on the class path. As you know, the Oracle JDBC implementation is defined within this jar. We have written our statement processing to be decoupled from the implementation. This makes us work well with all vendors given that they follow the API. However, the DB Tool was never designed to execute PL/SQL statements so even though we support the universal core SQL, we may not have full support for all the nuances of Oracle PL/SQL.

Another thing is that the DB Tool executes one statement at a time. For example, I cannot have two SELECT queries inside the same tool. This is done because we allow output of the queried table to be used in subsequent tests. This is just a heuristic we use to force a single output. In the PL/SQL case, I understand that this may be a usability problem in that a PL/SQL document usually has a large sequence of commands. You will have to break it up. I do have a workaround in mind but I would like you to do something first.

Can you use 2 DB Tools to run this block or something simple that uses Oracle specific commands. Please have the Fail On SQL Exception box checked and let us know if running the command causes an exception. In addition, please let us know if the command actually got executed successfully by examining through SQL*Plus:

DBTool 1:
CODE
CREATE TABLE T2 (

   a INTEGER,

   b CHAR(10)

);



DBTool 2:
CODE
CREATE PROCEDURE addtuple1(i IN NUMBER) AS

BEGIN

   INSERT INTO T2 VALUES(i, 'xxx');

END addtuple1;


This post has been edited by bdai: Feb 13 2007, 07:54 PM
Go to the top of the page
 
+Quote Post
lliuphx
post Feb 14 2007, 11:48 AM
Post #7


Newbie
*

Group: Members
Posts: 5
Joined: 18-December 06
Member No.: 2047



I added 5 DB tools:

(1) CREATE TABLE T2 ( a INTEGER, b CHAR(10) )
(2) CREATE or REPLACE PROCEDURE addtuple1(i IN NUMBER) AS BEGIN INSERT INTO T2 VALUES(i, 'xxx'); END addtuple1;
(3)drop table T2
(4)drop PROCEDURE addtuple1
(5)commit

Please note that only DB tool 2 did not error out because of the ";". The other DB tools would fail with ORA-00911: invalid character.

I added another DB tool and disabled DB tools 3 & 4
(6)select * from T2
I got an empty set back for the row data; so DB tool 2 did not work.

I get the error code ORA-00900: invalid SQL statement when I try to work with the refcursor in the DB tool. Guess I will have to stick with scripting.
Go to the top of the page
 
+Quote Post
bdai
post Feb 14 2007, 12:01 PM
Post #8


Member
**

Group: Members
Posts: 25
Joined: 18-August 05
Member No.: 820



OK, you wrote that only DB 2 did not error out. What's odd about this is that DB 1 is using standard SQL. There should be no reason what that is failing with "invalid character" error. Perhaps Oracle is more strict.

Have you tried those tests with ';' inserted at the end? What happens then?

You also wrote:
"I added another DB tool and disabled DB tools 3 & 4
(6)select * from T2
I got an empty set back for the row data; so DB tool 2 did not work."

DB Tool 2 just defines a procedure. addTuple() is not called anywhere in your list so nothing is in there. You should be expecting an empty set in this case. The goal is to find out if the procedure actually got stored.

Can you do the following with db tools:
(1) CREATE TABLE T2 ( a INTEGER, b CHAR(10) );
(2) CREATE or REPLACE PROCEDURE addtuple1(i IN NUMBER) AS BEGIN INSERT INTO T2 VALUES(i, 'xxx'); END addtuple1;

And then using sql*plus, execute the procedure and query T2.

This post has been edited by bdai: Feb 14 2007, 12:01 PM
Go to the top of the page
 
+Quote Post
bdai
post Feb 14 2007, 12:17 PM
Post #9


Member
**

Group: Members
Posts: 25
Joined: 18-August 05
Member No.: 820



As for the cursor problem, thanks for confirming our observations. The Oracle JDBC API has a method called setCreateStatementAsRefCursor() which needs to be used to handle refcursors. However our code does not use this method. Again this is vendor specific functionality and we keep our code implementation independent. I have filed an enhancement request for this: FR 61892
Go to the top of the page
 
+Quote Post
lliuphx
post Feb 14 2007, 01:41 PM
Post #10


Newbie
*

Group: Members
Posts: 5
Joined: 18-December 06
Member No.: 2047



Here is my script; the OracleTypes.CURSOR is actually equivalent to a -10. Don't actually need to use "setCreateStatementAsRefCursor()". I had used the file attachment tool previously, but it seems not to have worked as I don't see my script in the previous email.
The stored procedure is fulfilling your goal "The goal is to find out if the procedure actually got stored". Notice that I added "or Replace" within the sql - before when I ran the sql twice, the procedure was already defined and it errored out.

As for your "Have you tried those tests with ';' inserted at the end? What happens then?". All the DB tool statements return the same error whenever I use the ";" (except DB tool 2). ALL The other DB tools would fail with ORA-00911: invalid character. I have included the ojdbc14_g.jar in the SOAtest directory also.


from com.parasoft.api import Application
from java.lang import *
from java.util import *
from java.io import *
from java.sql import *
from oracle.jdbc.driver import OracleTypes

def getResultSet(inputStoredProc):

prop = Properties()
fileString = "H:/tests/soatest/environment.properties"

iss = FileInputStream(fileString)
prop.load(iss)
iss.close()

dbUserName = prop.getProperty("dbUser")
dbPassword = prop.getProperty("dbPassword")
dbConn = prop.getProperty("dpUrl")

#//Connection
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance()
conn = DriverManager.getConnection(dbConn, dbUserName, dbPassword )

query ="begin ? := %s; end;" % (inputStoredProc)

callableStmt = conn.prepareCall(query);
callableStmt.registerOutParameter(1, OracleTypes.CURSOR);
callableStmt.execute()

callableStmt.close()
conn.close()

Application.showMessage( inputStoredProc )
Go to the top of the page
 
+Quote Post
bdai
post Feb 14 2007, 05:25 PM
Post #11


Member
**

Group: Members
Posts: 25
Joined: 18-August 05
Member No.: 820



Hi lliuphx, thank you for the detailed post. "setCreateStatementAsRefCursor()" was just an example of an Oracle specific method that is never called in the current DB Tool. In your case "registerOutParameter()" may have been more relevant. The feature request I filed will look into filling in these holes we have in our Oracle PL/SQL execution. Scripting would be the only way to get 100% Oracle SQL functionality right now.

This brings us to the question of specifying data base drivers, URLs, etc. Currently a Method Tool script cannot access the global properties. As stated before you can work around this by introducing a Set-Up Method Tool that reads from a property file and stores the DB information. This gives you similar behavior to global properties since you only need to change a value in one place if need be.

def createProp():
prop = Properties()
fileString = "H:/tests/soatest/environment.properties"

iss = FileInputStream(fileString)
prop.load(iss)
iss.close()

Then in subsequent scripts you will be able to access your DB info as you do now.

P.S.: We will look into why an ';' characters causes invalid character problem. We have had many customers contacting us about using an Oracle Database with the DB Tool recently, and so far they were able to do things like create tables and update tables just fine.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 20th April 2014 - 06:52 AM