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
ADVERTISEMENT
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
View Related
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
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
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
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
Jun 21, 2013
Can we write java class or code inside Oracle Stored procedure.
View Replies
View Related
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
Jun 15, 2013
I am wondering where the attributes I put into the Httpsession are stored.
For example, if I do the following
HttpSession session = request.getSession();
session.setAttribute("usr", usr);
session.setAttribute("pwd", pwd);
where does the ("pwd", pwd) pair store?
In my case, my web application needs to use the usr and pwd to login to another application, so I have to store them somewhere. So for security reason, if the pwd is kept in the browser side, I need to encrypt it first before I put it in the session. On the other hand if it is stored in the server memory then encryption seems not necessary
View Replies
View Related
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
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
May 8, 2014
How can insert value into sql server column of data type 'datetime' with jdbc the methods in java.sql are 'setdate()' and 'setTime()'
How can I do this. I'm using java.time.LocalDateTime.
View Replies
View Related
Apr 8, 2014
I need to know the procedure for detecting a Unique Hardware ID of any computer using Java. All I want to do is once the software application is installed should not get installed using the same key...In short I want to do license for my application.
View Replies
View Related
Jun 3, 2014
I have a problem about this numeric or value error on my java class. Whenever I am calling for the procedure in callable statement it says that error. I don't know what is the problem with my sql code because I;ve tried running it on database alone and it runs perfectly. The results that I need came out fine. But when I'm already calling it in java that error appears. But I tried on finding the line that the said error is coming from and here is the code...
create or replace
PROCEDURE RENTING
(P_NNAME IN VARCHAR2,
P_ADD IN VARCHAR2,
P_PHONE IN NUMBER,
[Code] ...
View Replies
View Related
Apr 8, 2014
I have been having trouble with a recent hw assignment requiring us to sort user defined data types in an array. so far, i have implemented the sorting algorithms correctly but i cant seem to store a string and an integer together. I have an employee class
[public class employee {
private String Name;
private int IdNumber;
public employee(int IdNumber,String Name){
this.Name=Name;
this.IdNumber=IdNumber;}
[Code] .....
And in my app, i create a employee object-----> employee john=new employee(1020, "John");
How can I prompt my insert method to take in employee id and string name together?
View Replies
View Related
Jan 23, 2014
Which package or class i can find all the predefined keywords in java like "this" etc...
actually i want to know if this operator is itself final in nature or not from its syntax?
View Replies
View Related
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
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
Mar 8, 2015
I have an java connection pool using glassfish and mysql jdbc connector. All is fine, my datasource is working using JNDI, etc. But i need to know some info about of connection pool status (maxPoolSize, current active, etc..). I need it to monitoring the connection in the web app.
Some of my configuration.
sun-resources.xml file:
<resources>
<jdbc-resource enabled="true" jndi-name="jdbc/db" object-type="user" pool-name="dbPool">
</jdbc-resource>
<jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false"
connection-creation-retry-attempts="0"
connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false"
[Code] ....
All work fine. But i need get de pool size and MysqlDataSource class don't have a method to get it. In tomcat, i use org.apache.tomcat.jdbc.pool.DataSource with getMaxActive() and getActive(). And it work fine. How i can obtain it on glassfish server?
View Replies
View Related
Mar 5, 2012
I've got a very simple java program (J.java, see below) on my application server that successfully connects to an Oracle 11.2 database on a database server (both servers are Linux CentOS) using JDBC thin driver from Oracle.
As you can see from the setURL command in the Java code below, I've configured the application and database servers to sit next to each other, and they're on the same network (cross-cable connected to each other), so there's no network traffic on these (development) boxes except my code.
The problem is the execution time varies a lot. If I run it 5 times, it (seemingly randomly) could take 0.01 seconds, or 10 seconds, or 50 seconds, or over a minute to execute. If it takes over a minute (roughly), the program doesn't complete, but the error shown below is returned instead.
------error returned when execution take more than about 1 minute-------
gf@host9 [~/dbwork]# java -cp ./ojdbc6_g.jar:. J
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Connection reset
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:494)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:547)
[Code] ....
----------the java code for: J.java-------
// To compile: # javac -cp ./ojdbc6_g.jar:. J.java
// To run: # java -cp ./ojdbc6_g.jar:. J
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class J {
public static void main(String args[]) throws SQLException {
[Code] ....
View Replies
View Related
Apr 14, 2015
I create 2 files:
CircleCalculationMethod.javaMain.java
In Main.java, How can i call method in CircleCalculationMethod.java ?
Should I put everything in same folder ??Should i do something like "import CircleCalculationMethod.java"Should i do something like create a package ...
I use Eclipse software
View Replies
View Related
Jul 21, 2014
whether we can call DTS directly from Java.Solution which we got as below:-
1. Create dts
2. Create job in which we have to call dts
3. Call job from stored procedure
It pretty lengthy process and we are not sure how error handling can be done in this scenario.
View Replies
View Related
Jul 22, 2014
We are using MS SQL server 2000, in which we have explored calling dtsrun.exe from Runtime class in java. But it is not suggested way to call dts, as error handling will be difficult.
whether we can call DTS directly from Java.
View Replies
View Related
Apr 28, 2014
I am trying to call a java method in jsp. The main idea is to hide menu based on the user who logs in. The java class flows like this :
public class UserVerification {
public static void main(String[] args) {
UserVerification obj = new UserVerification();
System.out.print(obj.GetUserVerification("abc"));
[Code] ......
View Replies
View Related
Mar 4, 2014
I used java and jsf. I created dynamic datatable in java file. Can i call java method from setOnchange() event?
I am able to call java script function from setOnchange() event. See the below code which is working fine for java script.
HtmlSelectOneMenu selectOneMenu = new HtmlSelectOneMenu();
selectOneMenu.setStyleClass("dropdownStyleTwo");
selectOneMenu.setOnchange("openWin(this);IGNORE_UN LOAD=false");
I wrote openwin() function in java script. But i am not able to call java method change().
Code which is not working.
HtmlSelectOneMenu selectOneMenu = new HtmlSelectOneMenu();
selectOneMenu.setStyleClass("dropdownStyleTwo");
selectOneMenu.setOnchange("myclass.change();IGNORE _UNLOAD=false");
myclass is the bean of class Test. If user select any value from dropdown i want to call change java method. This function will apply the same selected dropdown value to the other record also.
View Replies
View Related
Feb 18, 2014
I am new to Java and have been learning it. I have a question here. I came across the following Java class and trying to understand it thoroughly but got confused how it is able to call an abstract method. Here is the code I am referring to :
package sampleapps.gui;
import javax.swing.*;
import java.awt.*;
public class InnerClassAnimationExample {
int x=70, y=70;
public static void main(String[] args) {
[Code] ....
So, in the code above, there is an inner class NewMyDrawPanel which has a paintComponent(Graphics g) method. I have highlighted 2 lines of code above.
Line 1 : Graphics2D g2d = (Graphics2D) g;
Line 2 : g2d.fillOval(x,y,40,40);
I understand we are type casting reference g to Graphics2D reference g2d and we are calling fillOval() method on g2d. I don't see a fillOval() method in Graphics2D class but it is there in Graphics class and fillOval method is an abstract method.
So, my question here is :
1. If we are not able to instantiate an abstract class(Graphics2D and Graphics classes), how are we able to access the fillOval() abstract method,
2. Secondly, since the fillOval() method is an abstract method, it does not have any implementation for the method.
However, when I call the method fillOval() on Graphics2D reference, I was able to draw and fill an oval of the specified co-ordinates. So, where would the actual implementation code be?
View Replies
View Related