You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by sivagururaja <si...@gmail.com> on 2009/04/13 14:17:34 UTC
Derby Stored Procedure Help!
Hi All,
I am creating the stored procedure in Derby DB. Here it is,
CREATE PROCEDURE insertStud(IN RollNo Integer, in FirstName varchar(10))
PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA
EXTERNAL NAME 'javadbsp.DBClass.insertStud'
And the method is,
public class DBClass
{
public static void insertStud(int rollno, String name)
{
try
{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); //or
ClientDriver
Connection conn =
DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
"passwd");
PreparedStatement ps1 = conn.prepareStatement("insert into
SAM.STUD values(?,?)");
ps1.setInt(1, rollno);
ps1.setString(2, name);
ps1.executeUpdate();
conn.close();
}
catch (Exception ex)
{
System.out.println("Error: "+ex.getMessage());
}
}
}
My table structure is,
create table "SAM".STUD
(
ROLLNO INTEGER,
FIRSTNAME VARCHAR(10)
)
My class is,
public class MyClass1
{
.......
..........
public void insert()
{
try
{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); // or
ClientDriver
Connection Con1 =
DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
"passwd");
CallableStatement cst = Con1.prepareCall("call
insertStud(?,?)");
cst.setInt(1, Integer.valueOf(jTextField1.getText().trim()));
cst.setString(2, jTextField2.getText().trim());
int i=cst.executeUpdate();
System.out.println("Rows Updated: "+i);
}
catch (Exception ex)
{
System.out.println("Error: "+ex.getMessage());
}
}
......
}
And i install the JAR files as per the document,
http://wiki.apache.org/db-derby/DerbySQLroutines
http://wiki.apache.org/db-derby/DerbySQLroutines
I can able to run the application successful.
But nothing can be inserted.
Please let me know what could be the problem.
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23021611.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hello,
If the code is running inside a database procedure, then I think that an
OUT parameter is a good solution.
Regards,
-Rick
sivagururaja wrote:
> Hello,
>
> I need one more help. Here is my code,
>
> String str="insert into sam.Stud values(1,'Sivagururaja')";
> Statement st=con.createStatement();
> int PatientRowsUpdated = st.executeUpdate(str);
> if(PatientRowsUpdated>0)
> {
> System.out.println('Inserted Successfully');
> }
>
> Using the above code, i know whether the record is getting inserted or not.
> Now i'm going to use the stored procedure. Is it possible to check whether
> the record
> is inserted or not. If yes, Can you please tell me, where should i write the
> code.
> Should i use one OUT parameter for the status or it can be achieved through
> my java method something
> like the above.
>
> Thanks in advance.
>
Re: Derby Stored Procedure Help!
Posted by sivagururaja <si...@gmail.com>.
Hello,
I need one more help. Here is my code,
String str="insert into sam.Stud values(1,'Sivagururaja')";
Statement st=con.createStatement();
int PatientRowsUpdated = st.executeUpdate(str);
if(PatientRowsUpdated>0)
{
System.out.println('Inserted Successfully');
}
Using the above code, i know whether the record is getting inserted or not.
Now i'm going to use the stored procedure. Is it possible to check whether
the record
is inserted or not. If yes, Can you please tell me, where should i write the
code.
Should i use one OUT parameter for the status or it can be achieved through
my java method something
like the above.
Thanks in advance.
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23039989.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by Rick Hillegas <Ri...@Sun.COM>.
sivagururaja wrote:
> Hello Mr.Rick,
>
> Normally, database procedures use the special server-side connection url:
> "jdbc:default:connection".
> I tried this one,
> Connection conn = DriverManager.getConnection("jdbc:derby:sample"); //sample
> is my database without uname and passwd
>
> Error: Database 'sample' is not found.
> Can you please help me.:-)
>
Hello,
This is the error you see when your database does not exist in the
expected directory. That connection url tells Derby to look for a
subdirectory named "sample". This is supposed to be a subdirectory of
the top level directory identified by the system property
"derby.system.home" (if your application does not set that property,
then derby.system.home defaults to the current directory of the VM). I'm
assuming you haven't set that property. If that is the case, then Derby
is complaining that the subdirectory named "sample" does not exist in
the current directory of the VM. Maybe you need to create the database
(to do that, add ";create=true" to the end of your connection url). Or
maybe your database lives in some other directory subsystem and you need
to point derby.system.home at that subsystem.
Hope this helps,
-Rick
Re: Derby Stored Procedure Help!
Posted by sivagururaja <si...@gmail.com>.
Hello All,
Can someone explain the below code,
Connection conn = DriverManager.getConnection("jdbc:derby:sample");
Statement s = conn.createStatement();
s.execute("set schema 'SAMP'");
//note that autocommit is on--it is on by default in JDBC
ResultSet rs = s.executeQuery("SELECT empno, firstnme, lastname, salary,
bonus, comm "
+ "FROM samp.employee");
/** a standard JDBC ResultSet. It maintains a
* cursor that points to the current row of data. The cursor
* moves down one row each time the method next() is called.
* You can scroll one way only--forward--with the next()
* method. When auto-commit is on, after you reach the
* last row the statement is considered completed
* and the transaction is committed.
*/
System.out.println( "last name" + "," + "first name" + ": earnings");
/* here we are scrolling through the result set
with the next() method.*/
while (rs.next()) {
// processing the rows
String firstnme = rs.getString("FIRSTNME");
String lastName = rs.getString("LASTNAME");
BigDecimal salary = rs.getBigDecimal("SALARY");
BigDecimal bonus = rs.getBigDecimal("BONUS");
BigDecimal comm = rs.getBigDecimal("COMM");
System.out.println( lastName + ", " + firstnme + ": "
+ (salary.add(bonus.add(comm))));
}
rs.close();
// once we've iterated through the last row,
// the transaction commits automatically and releases
//shared locks
s.close();
The above code is from the following URL,
http://db.apache.org/derby/docs/10.4/devguide/devguide-single.html#rdevconcepts88082
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23037819.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by sivagururaja <si...@gmail.com>.
Hello Mr.Rick,
Normally, database procedures use the special server-side connection url:
"jdbc:default:connection".
I tried this one,
Connection conn = DriverManager.getConnection("jdbc:derby:sample"); //sample
is my database without uname and passwd
Error: Database 'sample' is not found.
Can you please help me.:-)
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23037781.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by sivagururaja <si...@gmail.com>.
Rick Hillegas-2 wrote:
>
> Hello,
>
> The error means that a subdirectory named Test doesn't exist in the
> current directory of the application's VM. It appears that the VM
> running the server has a different current directory than the VM running
> your application. Another way around this mismatch is to use full
> absolute paths as your database names, rather than relative paths whose
> resolution relies on VM context.
>
> Hope this helps,
> -Rick
>
Thank you Mr.Rick,
Instead of the relative paths, i suppose to use the absolute path.
Now i can able to connect successfully. Thanks. :-)
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23053827.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by Rick Hillegas <Ri...@Sun.COM>.
sivagururaja wrote:
> Rick Hillegas-2 wrote:
>
>> Hello,
>>
>> This is just the special string ("jdbc:default:connection") which tells
>> the DriverManager that your code is running inside a server session and
>> wants to get a handle on that session. A session has two pieces: 1) an
>> external piece which is visible to your application (this is the
>> Connection), and 2) an internal piece which is only visible inside
>> Derby. The two pieces talk to one another either via method calls (in
>> the embedded case) or over a network protocol (in the remote case). When
>> your database procedure is actually running inside Derby, you no longer
>> have a handle on the external piece. If you want to issue SQL statements
>> from inside a database procedure, then you need a special, thin wrapper
>> around (2). This thin wrapper is what I mean by the "server-side
>> connection".
>>
>> Hope this helps,
>> -Rick
>>
>>
>
> Thank you Mr.Rick,
>
> It's working fine. Thanks a lot once again. :handshake:
> And i have one more doubt. I'm using NetBeans 6.5 with Derby DB 10.4.2.1.
>
> I can able to use the following code without error,
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
> Connection Con1 =
> DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
> "passwd");
>
> But if i try use like this, it throws "Database 'Test' is not found".
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
> Connection Con1 = DriverManager.getConnection("jdbc:derby:Test", "uname",
> "passwd");
>
> What could be the problem?
>
>
Hello,
The error means that a subdirectory named Test doesn't exist in the
current directory of the application's VM. It appears that the VM
running the server has a different current directory than the VM running
your application. Another way around this mismatch is to use full
absolute paths as your database names, rather than relative paths whose
resolution relies on VM context.
Hope this helps,
-Rick
Re: Derby Stored Procedure Help!
Posted by sivagururaja <si...@gmail.com>.
Rick Hillegas-2 wrote:
>
> Hello,
>
> This is just the special string ("jdbc:default:connection") which tells
> the DriverManager that your code is running inside a server session and
> wants to get a handle on that session. A session has two pieces: 1) an
> external piece which is visible to your application (this is the
> Connection), and 2) an internal piece which is only visible inside
> Derby. The two pieces talk to one another either via method calls (in
> the embedded case) or over a network protocol (in the remote case). When
> your database procedure is actually running inside Derby, you no longer
> have a handle on the external piece. If you want to issue SQL statements
> from inside a database procedure, then you need a special, thin wrapper
> around (2). This thin wrapper is what I mean by the "server-side
> connection".
>
> Hope this helps,
> -Rick
>
Thank you Mr.Rick,
It's working fine. Thanks a lot once again. :handshake:
And i have one more doubt. I'm using NetBeans 6.5 with Derby DB 10.4.2.1.
I can able to use the following code without error,
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection Con1 =
DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
"passwd");
But if i try use like this, it throws "Database 'Test' is not found".
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection Con1 = DriverManager.getConnection("jdbc:derby:Test", "uname",
"passwd");
What could be the problem?
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23039742.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by Rick Hillegas <Ri...@Sun.COM>.
sivagururaja wrote:
> Thanks,
>
> But i can also try the ClientDriver. But nothing can be happened.
>
> Mr.Rick:
> Normally, database procedures use the special server-side connection url:
> "jdbc:default:connection". I know these two types of Derby Drivers,
> EmbeddedDriver and ClientDriver. Can you please tell me, what do you mean by
> special server-side connection url.
>
> Thanks in advance.
>
Hello,
This is just the special string ("jdbc:default:connection") which tells
the DriverManager that your code is running inside a server session and
wants to get a handle on that session. A session has two pieces: 1) an
external piece which is visible to your application (this is the
Connection), and 2) an internal piece which is only visible inside
Derby. The two pieces talk to one another either via method calls (in
the embedded case) or over a network protocol (in the remote case). When
your database procedure is actually running inside Derby, you no longer
have a handle on the external piece. If you want to issue SQL statements
from inside a database procedure, then you need a special, thin wrapper
around (2). This thin wrapper is what I mean by the "server-side
connection".
Hope this helps,
-Rick
Re: Derby Stored Procedure Help!
Posted by sivagururaja <si...@gmail.com>.
Thanks,
But i can also try the ClientDriver. But nothing can be happened.
Mr.Rick:
Normally, database procedures use the special server-side connection url:
"jdbc:default:connection". I know these two types of Derby Drivers,
EmbeddedDriver and ClientDriver. Can you please tell me, what do you mean by
special server-side connection url.
Thanks in advance.
--
View this message in context: http://www.nabble.com/Derby-Stored-Procedure-Help%21-tp23021611p23033876.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby Stored Procedure Help!
Posted by Francois Orsini <fr...@gmail.com>.
To add on Rick's comments:
Sivagururaja,
In your code, you are loading Derby's "embedded" driver but your connection
URL is to connect to a remote (server) Derby instance...
If you want to connect to a remote Derby instance, you would have to load
Derby's network client driver:
"org.apache.derby.jdbc.ClientDriver" instead of the embedded one...
--francois
On Mon, Apr 13, 2009 at 5:58 AM, Rick Hillegas <Ri...@sun.com>wrote:
> Hello,
>
> The connection url in the database procedure looks suspicious. Normally,
> database procedures use the special server-side connection url:
> "jdbc:default:connection". This ensures that the procedure's work happens
> inside the same transaction as the calling code. This topic is discussed in
> the Derby Developer's Guide:
> http://db.apache.org/derby/docs/10.4/devguide/devguide-single.html#cdevspecial29620
>
> The procedure below opens a new connection to the database. Is there some
> reason that the procedure does its work in a separate connection/transaction
> as coded below?
>
> Thanks,
> -Rick
>
>
> sivagururaja wrote:
>
>> Hi All,
>>
>> I am creating the stored procedure in Derby DB. Here it is,
>>
>> CREATE PROCEDURE insertStud(IN RollNo Integer, in FirstName varchar(10))
>> PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA
>> EXTERNAL NAME 'javadbsp.DBClass.insertStud'
>>
>> And the method is,
>>
>> public class DBClass
>> {
>> public static void insertStud(int rollno, String name)
>> {
>> try
>> {
>> Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); //or
>> ClientDriver
>> Connection conn =
>> DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
>> "passwd");
>> PreparedStatement ps1 = conn.prepareStatement("insert into
>> SAM.STUD values(?,?)");
>> ps1.setInt(1, rollno);
>> ps1.setString(2, name);
>> ps1.executeUpdate();
>> conn.close();
>> }
>> catch (Exception ex)
>> {
>> System.out.println("Error: "+ex.getMessage());
>> }
>> } }
>>
>> My table structure is,
>>
>> create table "SAM".STUD
>> (
>> ROLLNO INTEGER,
>> FIRSTNAME VARCHAR(10)
>> )
>>
>> My class is,
>>
>> public class MyClass1
>> {
>> .......
>> ..........
>> public void insert()
>> {
>> try
>> {
>> Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); // or
>> ClientDriver
>> Connection Con1 =
>> DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
>> "passwd");
>> CallableStatement cst = Con1.prepareCall("call
>> insertStud(?,?)");
>> cst.setInt(1, Integer.valueOf(jTextField1.getText().trim()));
>> cst.setString(2, jTextField2.getText().trim());
>> int i=cst.executeUpdate();
>> System.out.println("Rows Updated: "+i);
>> }
>> catch (Exception ex)
>> {
>> System.out.println("Error: "+ex.getMessage());
>> }
>> }
>> ......
>> }
>>
>> And i install the JAR files as per the document,
>> http://wiki.apache.org/db-derby/DerbySQLroutines
>> http://wiki.apache.org/db-derby/DerbySQLroutines
>> I can able to run the application successful.
>> But nothing can be inserted.
>>
>> Please let me know what could be the problem.
>>
>>
>
>
Re: Derby Stored Procedure Help!
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hello,
The connection url in the database procedure looks suspicious. Normally,
database procedures use the special server-side connection url:
"jdbc:default:connection". This ensures that the procedure's work
happens inside the same transaction as the calling code. This topic is
discussed in the Derby Developer's Guide:
http://db.apache.org/derby/docs/10.4/devguide/devguide-single.html#cdevspecial29620
The procedure below opens a new connection to the database. Is there
some reason that the procedure does its work in a separate
connection/transaction as coded below?
Thanks,
-Rick
sivagururaja wrote:
> Hi All,
>
> I am creating the stored procedure in Derby DB. Here it is,
>
> CREATE PROCEDURE insertStud(IN RollNo Integer, in FirstName varchar(10))
> PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA
> EXTERNAL NAME 'javadbsp.DBClass.insertStud'
>
> And the method is,
>
> public class DBClass
> {
> public static void insertStud(int rollno, String name)
> {
> try
> {
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); //or
> ClientDriver
> Connection conn =
> DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
> "passwd");
> PreparedStatement ps1 = conn.prepareStatement("insert into
> SAM.STUD values(?,?)");
> ps1.setInt(1, rollno);
> ps1.setString(2, name);
> ps1.executeUpdate();
> conn.close();
> }
> catch (Exception ex)
> {
> System.out.println("Error: "+ex.getMessage());
> }
> }
> }
>
> My table structure is,
>
> create table "SAM".STUD
> (
> ROLLNO INTEGER,
> FIRSTNAME VARCHAR(10)
> )
>
> My class is,
>
> public class MyClass1
> {
> .......
> ..........
> public void insert()
> {
> try
> {
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); // or
> ClientDriver
> Connection Con1 =
> DriverManager.getConnection("jdbc:derby://localhost:1527/Test", "uname",
> "passwd");
> CallableStatement cst = Con1.prepareCall("call
> insertStud(?,?)");
> cst.setInt(1, Integer.valueOf(jTextField1.getText().trim()));
> cst.setString(2, jTextField2.getText().trim());
> int i=cst.executeUpdate();
> System.out.println("Rows Updated: "+i);
> }
> catch (Exception ex)
> {
> System.out.println("Error: "+ex.getMessage());
> }
> }
> ......
> }
>
> And i install the JAR files as per the document,
> http://wiki.apache.org/db-derby/DerbySQLroutines
> http://wiki.apache.org/db-derby/DerbySQLroutines
>
> I can able to run the application successful.
> But nothing can be inserted.
>
> Please let me know what could be the problem.
>