db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Derby Stored Procedure Help!
Date Mon, 13 Apr 2009 12:58:53 GMT
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.
>   


Mime
View raw message