web stats
Oracle stored procedure call does not work - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2018, 03:44 AM
shuhaibk54 shuhaibk54 is offline
Mirth Newb
 
Join Date: Jan 2018
Location: Abudhabi
Posts: 16
shuhaibk54 is on a distinguished road
Send a message via AIM to shuhaibk54 Send a message via Skype™ to shuhaibk54
Default Oracle stored procedure call does not work

I am trying to fetch value from oracle database. it always give me an error . some body will guide me on right . Is that some thing related to out parameter ,if yes how we can handle it . Even i tried diffrent link in mirth forum like ..
--https://www.mirthcorp.com/community/forums/showthread.php?t=1677
--http://www.mirthproject.org/community/forums/showthread.php?t=4075

---------------------------------------------------------------------------------
var result=0;
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbcracle:th in:@192.168.0.226:1521:dbdb25','Dbname','XXX');
var sql = 'exec MIRTH_TEST_PROCEDURE';
var str = '';

try {
objresult = dbConn.executeCachedQuery(sql);
while (objresult.next())
{
str = objresult.getString('Channename');
}
}

catch (e) {
logger.info('call to db failed: ' + e);
}
---------------------------------------------------------------------------------


My oracle stored procedure ---


create or replace PROCEDURE MIRTH_TEST_PROCEDURE (
--p_MODE in number,
P_CURSOR OUT SYS_REFCURSOR)

AS
BEGIN
OPEN p_CURSOR FOR
select * from HL7_CLIENT_CHANNEL;

END;

-------------- Error ---------------------------------------------
[2018-08-10 12:06:54,020] INFO (js-connector:?): call to db failed: JavaException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
Reply With Quote
  #2  
Old 08-12-2018, 10:53 PM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 824
siddharth is on a distinguished road
Default

try

Code:
var sql = 'exec  <schema-name>.MIRTH_TEST_PROCEDURE';
__________________
HL7v2.7 Certified Control Specialist!
Reply With Quote
  #3  
Old 08-14-2018, 06:23 AM
shuhaibk54 shuhaibk54 is offline
Mirth Newb
 
Join Date: Jan 2018
Location: Abudhabi
Posts: 16
shuhaibk54 is on a distinguished road
Send a message via AIM to shuhaibk54 Send a message via Skype™ to shuhaibk54
Default ORA-00900: invalid SQL statement and tried different way

I am getting same error even if edit the script .

[2018-08-14 18:44:45,050] INFO (js-connector:?): call to db failed: JavaException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

and I tried different way . it returns false no error
--------------------------------------------------------
importPackage(Packages.ca.uhn.hl7v2.model.primitiv e);
importPackage(Packages.oracle.jdbc.driver);
importPackage(java.sql);
java.sql.DriverManager.registerDriver (new OracleDriver());

// Change connection info here !!!
// jdbcracle:thin:@<host>/<service_name>
// jdbcracle:thin:@<host>:<sid>
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbcracle:th in:@192.168.0.226:1521:XXX','CCCCC','XXX');
conn = dbConn.getConnection();
//var spstmt = dbConn.prepareCall("{ call store_adt_event(?, ?, ?, ?, ?, ?, ?, ?) }");
var spstmt = conn.prepareCall("{ call MIRTH_TEST_PROCEDURE(?) }");

spstmt.registerOutParameter(1, OracleTypes.CURSOR);
logger.debug(spstmt.execute()); /// it returns false.

//var result=spstmt.executeQuery();

//var result = spstmt.getResultSet();//getInt("pr_key");
//logger.debug("size=="+result.size());

//logger.info("new key=" + newnewKey);


dbConn.close();
----------------------------------------------------------

Last edited by shuhaibk54; 08-14-2018 at 06:58 AM.
Reply With Quote
  #4  
Old 08-14-2018, 07:28 AM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 824
siddharth is on a distinguished road
Default

You need to specify the schema name where the SP is located for oracle. something like dbo.storedProcedure where dbo is the schema.

I don't see that anywhere on your posted code. BTW, You should wrap your code in code tags.
__________________
HL7v2.7 Certified Control Specialist!
Reply With Quote
  #5  
Old 08-30-2018, 10:09 PM
shuhaibk54 shuhaibk54 is offline
Mirth Newb
 
Join Date: Jan 2018
Location: Abudhabi
Posts: 16
shuhaibk54 is on a distinguished road
Send a message via AIM to shuhaibk54 Send a message via Skype™ to shuhaibk54
Default

I tired as you advised . Does anybody has a reference for oracle stored procedure call . I Searched entire forum and internet . Found Sql server related and it works well . Oracle related nothing found .
Reply With Quote
  #6  
Old 08-31-2018, 11:27 AM
nicovn nicovn is online now
OBX.2 Kenobi
 
Join Date: Oct 2006
Location: Belgium
Posts: 64
nicovn
Default

Hope this helps ...

Example Oracle function:

CREATE OR REPLACE FUNCTION GETPATIENTNAMEFORID
(
P_PATIENTID IN VARCHAR2
) RETURN VARCHAR2
IS
cursor V_C_PATIENT IS select PAT_LAST_NAME || ' ' || PAT_FIRST_NAME from PATIENT where PAT_PRIMARY_ID = P_PATIENTID;
V_PATIENTNAME VARCHAR2(1000);
BEGIN
open V_C_PATIENT;
fetch V_C_PATIENT into V_PATIENTNAME;

if V_C_PATIENT%notfound then
V_PATIENTNAME := 'Unknown';
end if;

close V_C_PATIENT;
RETURN V_PATIENTNAME;
END GETPATIENTNAMEFORID;



// Example javascript transformer code


var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver',
'jdbcracle:thin:@192.168.2.29:1521:XE', 'adt', 'adt');

var jdbcNativeConn = dbConn.getConnection();

var spstmt = jdbcNativeConn.prepareCall("{? = call GETPATIENTNAMEFORID (?) }");
spstmt.registerOutParameter (1, java.sql.Types.VARCHAR);
// 0000324550 is a sample value
spstmt.setString(2, '0000324550');

spstmt.execute();

var patientFullName = spstmt.getString(1);

logger.info("PatientName = " + patientFullName);

dbConn.close();
__________________
Nico Vannieuwenhuyze

Amaron.be
Reply With Quote
  #7  
Old 09-03-2018, 11:30 PM
shuhaibk54 shuhaibk54 is offline
Mirth Newb
 
Join Date: Jan 2018
Location: Abudhabi
Posts: 16
shuhaibk54 is on a distinguished road
Send a message via AIM to shuhaibk54 Send a message via Skype™ to shuhaibk54
Default

Thanks for advice . as per your advice function works well. Similarly I tried calling stored procedure . Yous simple push may help to finish this task .

I tried stored procedure for return entire table as result data .It return null .

below is sample stored procedure which I can fetch from c# code .
-------------------------------------------Stored procedure -----------
create or replace PROCEDURE MIRTH_TEST_PROCEDURE (
--p_MODE in number,
P_CURSOR OUT SYS_REFCURSOR)

AS
BEGIN
OPEN p_CURSOR FOR
select * from HL7_CLIENT_CHANNEL;

END;


-------------------------------------------------Mirth code ---------------
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbcracle:th in:@fun-host:1521:dbtest','hell','pwd');
conn = dbConn.getConnection();
var spstmt = conn.prepareCall("{call MIRTH_TEST_PROCEDURE(?) }");
spstmt.registerOutParameter(1, OracleTypes.CURSOR);
spstmt.execute();
var result = spstmt.getResultSet();
logger.debug("size=="+result.size()) // Error it return null : TypeError: Cannot call method "size" of null
-------------------------------------------------------------------------------
Error------------------
// Error it return null : TypeError: Cannot call method "size" of null

// OrcaleDBtestCONNECTOR:
SourceSOURCE CODE:
135: //logger.info("Channelname = " + Channelname);136: //var result=spstmt.executeUpdate();137: //var result=spstmt.executeQuery(); 138: 139: var result = spstmt.getResultSet();//getInt("pr_key");140: logger.debug("size=="+result.size());141: 142: //logger.info("new key=" + newnewKey);143: 144: LINE NUMBER:
140DETAILS:
TypeError: Cannot call method "size" of null

---------------------------------------------------------------------------
Reply With Quote
  #8  
Old 09-07-2018, 10:44 AM
nicovn nicovn is online now
OBX.2 Kenobi
 
Join Date: Oct 2006
Location: Belgium
Posts: 64
nicovn
Default

This should do the trick ...

// database reader javascript source code starts here
var dbConn;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver',
'jdbcracle:thin:@oracleserver:1521:XE', 'amaron', 'proctest');

var jdbcNativeConn = dbConn.getConnection();

var spstmt = jdbcNativeConn.prepareCall("{ call MIRTH_TEST_PROCEDURE (?) }");
spstmt.registerOutParameter (1, Packages.oracle.jdbc.OracleTypes.CURSOR);

// call the stored procedure
spstmt.execute();

// get the resultset object from the output parameter
var result = spstmt.getObject(1, java.sql.ResultSet);

// copy the resultset into a cachedrowset (required for mirth)
var cachedRowSet = new Packages.com.sun.rowset.CachedRowSetImpl();
cachedRowSet.populate(result);

// close the original resultset
result.close();

logger.debug("hoera! got the records");

return cachedRowSet;

} finally {
if (dbConn) {
dbConn.close();
}
}

// end of code


Best Regards

Nico
__________________
Nico Vannieuwenhuyze

Amaron.be
Reply With Quote
Reply

Tags
database error, oracle, stored procedure

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 04:45 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Mirth Corporation