JDBC :: How To Call Parameterized Stored Procedure In Jdbc

May 17, 2014

calling a parameterized stored procedure in java jdbc from sql server.The stored procedure goes like this in sql

create proc patientreg
@id int
as
begin
select [patient_id],[Psurname], [pFirstname], [pMiddlename], [reg_date], [DOB], [Sex], [Phone_num], [Addr],[Email],[dbo].[fncomputeage](DOB) from [dbo].[Patient_registration] where [patient_id] = @id
end
please note dbo.fncompute(DOB) is a function

To call it in jdbc it goes like this

try{
String str = "{call patientreg(?)}";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbcdbc:GeneralHospit al");
cstmt = con.prepareCall(str);
cstmt.setInt(1, Integer.parseInt(t.getText()));

[code]....

after doing it this way it throwing an exception: Error:java.sql.SQLException: Parameter 1 is not an OUTPUT parameter.

View Replies


ADVERTISEMENT

JDBC :: CallableStatement Stored Procedure Call Cancellation?

Feb 19, 2015

I have a very complex oracle stored procedure that searches and retrieves some data. The procedure returns an output parameter - an oracle cursor. I execute the procedure by JDBC:

CallableStatement stmt = conn.prepareCall("{call myprocedure(?,?,?,?}");

The problem is, the queries can sometimes take quite long (few minutes) and i would like user to be able to cancel the query anytime by clicking button. I have reference to stmt object, but unfortunatelly calling stmt.cancel() (from other thread) has NO EFFECT.

On the other hand when i change the CallableStatement sql to some query like:

CallableStatement stmt = conn.prepareCall("select * from all_objects");

I get
"java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation" after calling stmt.cancel()

- so thats the right reaction.

Does that mean than i cannot cancel stored procedure call via jdbc, but only simple select statements? I guess i could cancel the query by oracle kill session, but i use connection pooling (jboss) and i have plenty of sessions for the same user existing.

Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production JDBC Driver Version is 11.2.0.4.0

View Replies View Related

Call SQL Server Stored Procedure Using JDBC In Java

Mar 23, 2015

I'm new to Java. I need to run a SQL server stored procedure(that creates a unique job number) from Oracle SQL Developer (JDBC) in Java. The same Java code will be used in Applescript to run the SP. I found a code snippet online with the similar requirement. How to embed my SP in below code snippet?  Below is the Stored Procedure and Code Snippet:
 
SP
 
EXEC Int.dbo.GetNewJobNumber '6852', 'Test Job', 'Manual SQL Query'
6852- CustomerCode,
Test Job - Job Title,
Manual SQL query - Shows how new job number was created.
 
Code Snippet:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
 
public class Main {
  public static void main(String[] argv) throws Exception {
 
[Code] ....

View Replies View Related

JDBC :: Disconnected Rowset From Select Inside Stored Procedure?

Apr 21, 2015

I am using CachedRowSet returning it from a parameterised select statement and it works fine.
 
If I put the same select statement into a simple read-only stored procedure then I get this exception: "A result set was generated for update".

I am not trying to update the rowset in my code.
 
I tried to make the CachedRowSet to be read-only but it does not work, same error.
 
Question 1 : can a stored procedure returning a single result-set be called to populate a read-only CachedRowSet? (in a similar fashion to a CallableStatement prepareCall method with input/output parameters).
 
Question 2: in general is using CachedRowSet, WebRowSet, FilteredRowSet (disconnected) and JDBCRowSet (connected) something to be encouraged for future develpment or are they deprecated, or replaced by something else/better??

View Replies View Related

JSP :: How To Call Stored Procedure Through Code

May 29, 2014

I have a .csv file ,and I have written a jsp code which uploads csv file to a local folder . Now I want to upload that csv file to database using stored procedure. Below I have included the code which uploads the csv to database.

<%@page import="org.apache.commons.fileupload.*,java.util.*,java.io.*"%>
<%@page import="java.sql.*"%>
<!DOCTYPE HTML>
<html>
<head>
</head>
<body>
<img border="1" src="img/tss.JPG" alt="portal" />
<br><br><br><br><br><br>
<%
try{

[Code]....

View Replies View Related

Java Stored Procedure - Connecting To 8i Database

Sep 17, 2014

I am new to Java Stored Procedures. There is a PL/SQL package in our legacy application(Oracle 9i) which pulls data from Oracle 8i source database through a DB link. Now we have upgraded our databas to Oracle 11g R2 from 9i. So the DB lint to Oracle 8i will no longer work in 11g. Hence I have created a Java stored procedure that establishes JDBC thin connection to the source 8i database. I have loaded the java stored procedure in the database using loadjava and have created a call specifaction.

I have called this java stored procedure inside the PL/SQL package. Now while executing the PL/SQL package, it takes the default driver ojdbc6.jar to establish JDBC connection and fails to connect to the 8i database giving ArrayIndexOutOfBounds Exception. While running the code in the linux application server with classes12.jar the code succeeds. But when the same is called in the database it fails.
 
1.  How can I make my PL/SQL call use classes12.jar while calling the Java Stored Procedure?
 
2. How can I load the jar file(which includes my classes and the classes12.jar) into the datase as one object? (When I tried to load the whole jar, in database the classes and dependent jar loaded separately)
 
3. Is there a way to use classpath while calling the Java Stored Procedure like how we do from Unix?

View Replies View Related

Write Java Class Inside Oracle Stored Procedure?

Jun 21, 2013

Can we write java class or code inside Oracle Stored procedure.

View Replies View Related

Java Based Stored Procedure - Invalid Column Index

Jul 3, 2013

I am getting " Invalid column index" in java based stored procedure

    String resString;
     String resString  = ("ABCEDFG");
     byte[] splitLenArray = {1,1,1,1,1,1,1};
  int startIndex = 0;

[Code] ....

View Replies View Related

How To Call PL / SQL Procedure From Event Handler In NetBeans IDE

May 31, 2015

I want to call a procedure from a button click event handler in NetBeans and also pass a parameter to the procedure. How can I a here this?

View Replies View Related

Error In Spring Using JDBC

Aug 7, 2014

public class StudentJDBCTemplate implements StudentDao

above class giving error as

The type StudentJDBCTemplate must implement the inherited abstract method

StudentDao.listStudents()

Interface is as below

import java.util.List;
import javax.sql.DataSource;
public interface StudentDao {
public void setDataSource(DataSource ds);
public void Create(String Name,Integer age);
public void getStudent(Integer id);
public List<Student>listStudents();
public void delete(Integer id);
public void update(Integer id,Integer age);
}

View Replies View Related

EJB / EE :: Relationship Between JAAS And JDBC

Aug 9, 2014

I'm trying to understand the relationship between JAAS and JDBC..In WebSphere, when setting up a Dynamic cluster I have to first define the JAAS..Then, the datasource..The JAAS has one account/password and the datasource another..I'm not getting the relationship between needing both JAAS and JDBC docs.oracle.com/cd/E19225-01/820-5594/ahteo/index.html

View Replies View Related

JDBC :: How To Retrieve Just One Value From Database

Nov 25, 2014

I wish to retrieve just one value from db;
 
so I use this code:

@Transactional
    public long getSequenceSedeB(Long id) {
        BigDecimal seqValue = nu
        String sql = "SELECT MAX(ID) FROM SEDE_B_ALLEGATI_A WHERE ID_SEDE=:id";
        SQLQuery query = getSession().createSQLQuery(sql);

[Code] ....

But it do not works; this is the log.

type.NullableType (NullableType.java:182) - could not read column value from result set: ID; Invalid column name

2014-11-25 14:45:14,476 WARN  [btpool0-2] util.JDBCExceptionReporter (JDBCExceptionReporter.java:77) - SQL Error: 17006, SQLState: null
2014-11-25 14:45:14,478 ERROR [btpool0-2] util.JDBCExceptionReporter (JDBCExceptionReporter.java:78) - Invalid column name

 But in the database the query
 
SELECT MAX(ID) FROM SEDE_B_ALLEGATI_A WHERE ID_SEDE=:id
 
run correctly;
 
Also I need to rewrite the statement
   query.setParameter("id", 923);
in this way
   query.setParameter("id", AAA);
 
where AAA is gotten from out of the procedure;

View Replies View Related

JDBC :: Restore Database In Java

Feb 26, 2015

I'm having problem with my code when it comes to Restore Database in Java. This is my code:

public boolean restoreDB(String dbUserName, String dbPassword, String source) {
String[] executeCmd = new String[]{"C:Program Files (x86)MySQLMySQL Server 5.1inmysqldump ", "--user=" + "root", "--password=" + "1234" + source};
Process runtimeProcess;
try {
runtimeProcess = Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();

[Code] ....

View Replies View Related

JDBC In OSGI - Table Does Not Have Index

Oct 4, 2013

To insert 500 records into oracle my code is taking more than 1 minute.

I am using the below:
1) dbcp connection pool
2) Jdbc, autocommit off
3) preparestatment
4) batchupdate
5) deployed in fuse servicemix

Important thing : Table doesnot have index.

How to improve the performance. Below is the code snippet..

// Added for Transaction
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
PreparedStatement dataStmt= null;
dataStmt= conn.prepareStatement("insert statement");
for (Value value : valueList){

[Code] .....

View Replies View Related

JDBC Update Exception - Too Few Parameters

Jul 22, 2014

So, first i search the id from table and all the fields are filled as in the screenshot. then i modify in the text boxes and click on button modify but exception comes-too few parameters. expected 6.

here is the code:

try
{
JOptionPane.showMessageDialog(null,"Record succefully modified!
ID is "+id.getText()+" Password is "+lname.getText());
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("Jdbc:Odbc:Employe eDB");
String str0=id.getText();

[Code] ....

View Replies View Related

JDBC :: BatchUpdate Rows Affected Is Always -2

Mar 27, 2015

I have a batch process which does Bulk UPDATE statement.

After implementing batch support using Spring JDBC 4.1.6 and Oracle Jdbc Driver (ojdbc7 and ucp), the number of records affected for individual update requests (in the batch) are always retrieved as -2 (Statement.SUCCESS_NO_INFO).

Is there any way to know the rows affected for individual cases, as I have to retry the same argument with an INSERT statement after this. Technically trying to develop this as an UPSERT implementation

I tried this batch update in Three Different Ways, and in all three methods the results are same -- (It just tells me Statement.SUCCESS_NO_INFO (-2) )

Method 1  -- Direct UCP Connection and PreparedStatement
        connectionPoolMgr.startConnectionPool("mgr_pool");
        Connection connection = pds.getConnection();
       
        PreparedStatement pstmt = connection.prepareStatement(dmlSQL);
        pstmt.setInt(1, pkId);
        pstmt.setInt(2, idx * 10);
        pstmt.addBatch();
 
[Code] .....

View Replies View Related

JDBC :: Open Connection String

Jun 25, 2015

What I have in my tnsnames.ora and verify it all works via sqlplus
 
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
 
[Code] ....
 
I am trying to get RJDBC to work so I can connect to Oracle from R doing some data analysis..
 
This connection string works for my local db which I have it running in my Virtual Box
 
con <- dbConnect(drv, "jdbc:oracle:thin:@//localhost:1521/orcl", "demo", "demo")
 
However, it does not work when I do it for the remote db that I really need to pull data from...
 
> con <- dbConnect(drv, "jdbc:oracle:thin:@//ymsdbppr-scan:1522/YMQCTPRD", "user_read", "user_read")

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1],  :
  java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS: listener does not currently know of service requested in connect descriptor

View Replies View Related

JDBC :: Maximum Number Of Connections

Feb 29, 2012

I am using a static initialization block to register the driver and a static synchronized method to get a connection. The problem is I need to run 15 threads but always only two threads get the connection. I want to know if there is a default maximum number of concurrent connections a DriverManager can provide or is it my threading logic that may be faulty.

CODE:

static {
     try {
Class jdbcDriverClass = Class.forName( JDBC_DRIVER );
driver = (Driver) jdbcDriverClass.newInstance();
DriverManager.registerDriver( driver );

[Code] ....

View Replies View Related

JDBC :: Double Value Infinity Causes IllegalArgumentException

Feb 23, 2015

Trying to bind the value Double.POSITIVE_INFINITY in a prepared statement causes an IllegalArgumentException using ojdbc6 11.2.0.4.0 but it works fine in version 11.2.0.3.0:
 
Exception in thread "main" java.lang.IllegalArgumentException: Overflow
    at oracle.jdbc.driver.OraclePreparedStatement.setDoubleInternal(OraclePreparedStatement.java:7605)
    at oracle.jdbc.driver.OraclePreparedStatement.setDouble(OraclePreparedStatement.java:7513)
    at

[Code]....
 
The database used is 'Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production'. I don't see any mention regarding changes to this in the 11.2.0.4.0 change log. Is this a bug in the driver or is there some other explanation?

View Replies View Related

JDBC Connectivity With MySQL Database

Apr 24, 2014

I created a database in mysql, but I have problems communicating with the DB in java.

Here is the error :

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/books
at java.sql.DriverManager.getConnection(DriverManager.java:604)
at java.sql.DriverManager.getConnection(DriverManager.java:221)
at displayauthors.DisplayAuthors.main(DisplayAuthors.java:30)
java.lang.NullPointerException

[Code] ....
 
HERE IS THE CODE 

public class DisplayAuthors {   
// database URL                             
   static final String DATABASE_URL = "jdbc:mysql://localhost:3306/books";
   // launch the application
   public static void main( String args[] )

[Code] ....

View Replies View Related

JDBC :: Unable To Establish Connection To Excel

Aug 21, 2014

I have failed with all possibilities that I know to make this program success. Getting error as :

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Program goes like this.

import java.sql.*;
public class ExcelConnection {
public static void main (String[] args){
try {
Connection conn = getConnection("TEST.xls");
Statement stmt = conn.createStatement();

[Code] ....

View Replies View Related

JDBC :: Storing Images To MySQL Database?

Mar 13, 2015

This question is not about syntax but a best way to handle something. What is the best way to store a image into a database? For example converting it into binary etc. I just wanted to get some opinions.

View Replies View Related

JDBC Delete Command Locking Database?

Aug 27, 2014

I have a problem where i cant seem to get this simple delete command working. Everytime i run it it just locks the database and crashes

The id parameter exists in the database the database is small. Only a few tables. update commands work completely fine.

The id is an in and resulting command is - DELETE from Employees where ID = 2;

public static void EmployeeDeleteByID(int idIn){
Connection c = null;
Statement stmt = null;
try {
c = Connect();
c.setAutoCommit(false);
System.out.println("Opened database successfully");

[code]....

Error after running : java.sql.SQLException: database is locked

View Replies View Related

How To Connect To A Remote SQL Server Using JDBC Driver

Jan 15, 2015

I'm working on an application I made a few years ago. At that time I connected to a local database so my address was 'jdbc:mysql://localhost:3306/'. That database is long gone so I recreated it on one of my hosted servers but I'm a little unsure of how to connect to it. At the moment I'm trying "jdbc:mysql://www.mydomain.com:3306/" but it is giving me an access denied error.

java.sql.SQLException: Access denied for user 'myusername'@'c-[my-ip].hsd1.pa.comcast.net' (using password: YES)Every result on Google seems to use localhost so I'm having a little difficulty figuring out the correct format.

View Replies View Related

JDBC Update Based On HTML Page

Mar 19, 2014

i have a page where username is displayed...i want to uodate my table based on the page value i. e username..how can this be done

String sql;
sql = "UPDATE test SET username=? WHERE password=?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, "jumbomail@com");
statement.setString(2, "password");

View Replies View Related

JDBC :: Java Oracle Rac Connection URL Using Scan

Dec 30, 2014

I am trying to connect to an oracle rac using jdbc thin . when i use the scan as the host, like this

String url = "jdbc:oracle:thin:@//<scan>:port/servicename;

I get error 1153, connection refused

but when i use the ip of the rac instead of the scan

String url = "jdbc:oracle:thin:@//<ip>:port/servicename;

The connection is successful
 
Is the issue at the application level? or is the problem with the server ....

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved