Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: On Wednesday 05 April 2006 2:16 pm, Daniel John Debrunner wrote: Michael Segel wrote: On Wednesday 05 April 2006 12:33 pm, Daniel John Debrunner wrote: Michael Segel wrote: On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote: Daniel John Debrunner wrote: May I suggest that if you are writing samples for others to use that you demonstrate use of parameter markers in PreparedStatements. This will perfom better on Derby and all other relational database engines. Another reason to use PreparedStatements is to avoid all the security concerns that come with SQL injection issues. [SNIP] Uhm, I'm afraid this has nothing to do with the issue at hand, along with the fact that its not exactly true Could you expand on what is "not exactly true"? Sure. Your said "Another reason to use PreparedStatements is to avoid all the security concerns that come with SQL injection issues." as a benefit of a prepared statement over a regular statement. This is not 100% true. You're implying that using a Statement has some stigma of security concerns. You can effectively use a Statement in a manner that does not create a potential of SQL injection issues. Thus no benefit. Any time you build the SQL text from user input you run the risk of SQL injection, your example below even shows that. I was assuming the typical model with PreparedStatements that the SQL text is fixed by the application and parameter subsitution is performed using parameter markers and not modifying the SQL text. Ok, Look at it this way. You're going to be using user input if you use either a Prepared Statement or just a regular statement. If you don't bother to perform business logic checks and validate that input, you're going to run in to trouble. Period. The point being is that a prepared statement offers no more inherit protection that just a regular statement. Again looking at the code : String s1 = "SELECT * FROM employee WHERE emp_id = "+empID+";"; String s2 = "SELECT * FROM employee WHERE emp_id = ?"; Now in both statements, you are passing in the emp_id, presumably from the end user's input. Now... Note that there is no chance of SQL Injection, hence your argument of an advantage of using PreparedStatement is moot. With the statement case there is every chance of SQL injection. If empID is a String (or becomes a String later when someone modifies the application) then SQL injection is possible. With the PreparedStatement it isn't. Really? So I prepare a statement and I say pstmt.setString(n, empID) and its going to automatically validate the input? I really don't think so. setString public void setString(int parameterIndex, String x) throws SQLException Sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database. Parameters: parameterIndex - the first parameter is 1, the second is 2, ... x - the parameter value Throws: SQLException - if a database access error occurs Nothing here about validating the data. I agree no validation of the data will be performed when using a prepared statement, but since the input is/should be handled correctly by the driver, you won't be able to use the comment trick with a prepared statement, right? So I agree with Dan that a prepared statement removes *some* of the common SQL injection tricks, and thus can be seen to be more secure than using a regular statement. But, you are absolutely correct in claiming that the input data should be validated by the application before being passed to the database! Just to illustrate, I wrote a small cow database, storing owner, cowid and milkrating. Consider the following statements: PreparedStatement: "SELECT owner, cowid FROM herd WHERE cowrating LIKE ? AND owner LIKE 'JoeFarmer'", followed by a pStmt.setString(1, args[0]) Statement: "SELECT owner, cowid FROM herd WHERE cowrating LIKE '" + args[0] + "' AND owner LIKE 'JoeFarmer'" For the common comment trick, I am not able to get a list over the cows for other farmers than JoeFarmer with the prepared statement. A few examples: java CowTest "good" Input: good --- Using PreparedStatement Owner: JoeFarmer, cowid: 3 Owner: JoeFarmer, cowid: 4 --- Using Statement Owner: JoeFarmer, cowid: 3 Owner: JoeFarmer, cowid: 4 java -classpath CowTest "good' --" Input: good' -- --- Using PreparedStatement --- Using Statement Owner: JoeFarmer, cowid: 3 Owner: SecretFarmer, cowid: 5 Owner: JoeFarmer, cowid: 4 java CowTest "good' OR milkrating LIKE 'bad" Input: good' OR milkrating LIKE 'bad --- Using PreparedStatement --- Using Statement Owner: JoeFarmer, cowid: 1 Owner: JoeFarm
Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: > On Wednesday 05 April 2006 2:16 pm, Daniel John Debrunner wrote: > >>Michael Segel wrote: >>>Again looking at the code : >>> >>>String s1 = "SELECT * FROM employee WHERE emp_id = "+empID+";"; >>>String s2 = "SELECT * FROM employee WHERE emp_id = ?"; >>> >>>Now in both statements, you are passing in the emp_id, presumably from >>>the end user's input. >>> >>> >>> >>>Now... Note that there is no chance of SQL Injection, hence your argument >>>of an advantage of using PreparedStatement is moot. >> >>With the statement case there is every chance of SQL injection. If empID >>is a String (or becomes a String later when someone modifies the >>application) then SQL injection is possible. With the PreparedStatement >>it isn't. >> > > Really? > So I prepare a statement and I say pstmt.setString(n, empID) and its going to > automatically validate the input? Of course setString isn't going to validate the input. But no matter what value you set using setString it can't change the plan of the SQL statement when it's executed using a PreparedStatement. On the other hand modifying the text of the SQL statement with the value of a parameter can change the behaviour of the SQL statement. To make it clear, if the user supplies empID as a String with the value 13 OR TRUE then with the Statement approach the intent of the statement has been changed from return a single row to return all the rows. Thus allowing potentially someone to find out all employees. If the same string is set using PreparedStatement.setString() then some error will occur. Much more secure than leaking all employees. It's a fail safe system. Of course this is a simple SQL statement with a simple example, but SQL injection is a real problem. I'm not saying that PreparedStatement is the only solution, but the established pattern of a fixed SQL string and parameters set using the setXXX calls removes a whole set of potential SQL injections. Dan.
Re: Why projects fail... wuz... Re: unable to execute procedure
On Wednesday 05 April 2006 2:16 pm, Daniel John Debrunner wrote: > Michael Segel wrote: > > On Wednesday 05 April 2006 12:33 pm, Daniel John Debrunner wrote: > >>Michael Segel wrote: > >>>On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote: > Daniel John Debrunner wrote: > >May I suggest that if you are writing samples for others to use that > > you demonstrate use of parameter markers in PreparedStatements. This > > will perfom better on Derby and all other relational database > > engines. > > Another reason to use PreparedStatements is to avoid all the security > concerns that come with SQL injection issues. > >>> > >>>[SNIP] > >>> > >>>Uhm, I'm afraid this has nothing to do with the issue at hand, along > >>> with the fact that its not exactly true > >> > >>Could you expand on what is "not exactly true"? > > > > Sure. > > > > Your said "Another reason to use PreparedStatements is to avoid all the > > security concerns that come with SQL injection issues." as a benefit of a > > prepared statement over a regular statement. > > > > This is not 100% true. You're implying that using a Statement has some > > stigma of security concerns. You can effectively use a Statement in a > > manner that does not create a potential of SQL injection issues. Thus no > > benefit. > > Any time you build the SQL text from user input you run the risk of SQL > injection, your example below even shows that. I was assuming the > typical model with PreparedStatements that the SQL text is fixed by the > application and parameter subsitution is performed using parameter > markers and not modifying the SQL text. > Ok, Look at it this way. You're going to be using user input if you use either a Prepared Statement or just a regular statement. If you don't bother to perform business logic checks and validate that input, you're going to run in to trouble. Period. The point being is that a prepared statement offers no more inherit protection that just a regular statement. > > Again looking at the code : > > > > String s1 = "SELECT * FROM employee WHERE emp_id = "+empID+";"; > > String s2 = "SELECT * FROM employee WHERE emp_id = ?"; > > > > Now in both statements, you are passing in the emp_id, presumably from > > the end user's input. > > > > > > > > Now... Note that there is no chance of SQL Injection, hence your argument > > of an advantage of using PreparedStatement is moot. > > With the statement case there is every chance of SQL injection. If empID > is a String (or becomes a String later when someone modifies the > application) then SQL injection is possible. With the PreparedStatement > it isn't. > Really? So I prepare a statement and I say pstmt.setString(n, empID) and its going to automatically validate the input? I really don't think so. setString public void setString(int parameterIndex, String x) throws SQLException Sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database. Parameters: parameterIndex - the first parameter is 1, the second is 2, ... x - the parameter value Throws: SQLException - if a database access error occurs Nothing here about validating the data. > Dan. -- -- Michael Segel Principal Michael Segel Consulting Corp. [EMAIL PROTECTED] (312) 952-8175 [mobile]
Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: > On Wednesday 05 April 2006 12:33 pm, Daniel John Debrunner wrote: > >>Michael Segel wrote: >> >>>On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote: >>> Daniel John Debrunner wrote: >May I suggest that if you are writing samples for others to use that you >demonstrate use of parameter markers in PreparedStatements. This will >perfom better on Derby and all other relational database engines. Another reason to use PreparedStatements is to avoid all the security concerns that come with SQL injection issues. >>> >>>[SNIP] >>> >>>Uhm, I'm afraid this has nothing to do with the issue at hand, along with >>>the fact that its not exactly true >> >>Could you expand on what is "not exactly true"? >> > > Sure. > > Your said "Another reason to use PreparedStatements is to avoid all the > security concerns that come with SQL injection issues." as a benefit of a > prepared statement over a regular statement. > > This is not 100% true. You're implying that using a Statement has some > stigma > of security concerns. You can effectively use a Statement in a manner that > does not create a potential of SQL injection issues. Thus no benefit. Any time you build the SQL text from user input you run the risk of SQL injection, your example below even shows that. I was assuming the typical model with PreparedStatements that the SQL text is fixed by the application and parameter subsitution is performed using parameter markers and not modifying the SQL text. > Again looking at the code : > > String s1 = "SELECT * FROM employee WHERE emp_id = "+empID+";"; > String s2 = "SELECT * FROM employee WHERE emp_id = ?"; > > Now in both statements, you are passing in the emp_id, presumably from the > end > user's input. > > > Now... Note that there is no chance of SQL Injection, hence your argument of > an advantage of using PreparedStatement is moot. With the statement case there is every chance of SQL injection. If empID is a String (or becomes a String later when someone modifies the application) then SQL injection is possible. With the PreparedStatement it isn't. Dan.
Re: Why projects fail... wuz... Re: unable to execute procedure
On Wednesday 05 April 2006 12:33 pm, Daniel John Debrunner wrote:
> Michael Segel wrote:
> > On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote:
> >>Daniel John Debrunner wrote:
> >>>May I suggest that if you are writing samples for others to use that you
> >>>demonstrate use of parameter markers in PreparedStatements. This will
> >>>perfom better on Derby and all other relational database engines.
> >>
> >>Another reason to use PreparedStatements is to avoid all the security
> >>concerns that come with SQL injection issues.
> >
> > [SNIP]
> >
> > Uhm, I'm afraid this has nothing to do with the issue at hand, along with
> > the fact that its not exactly true
>
> Could you expand on what is "not exactly true"?
>
Sure.
Your said "Another reason to use PreparedStatements is to avoid all the
security concerns that come with SQL injection issues." as a benefit of a
prepared statement over a regular statement.
This is not 100% true. You're implying that using a Statement has some stigma
of security concerns. You can effectively use a Statement in a manner that
does not create a potential of SQL injection issues. Thus no benefit.
In addition, its possible to create a SQL injection that passes in an SQL
string, and an array[] of values. ("SELECT * FROM foo where id =? and
DATE>?", "15", "12/10/2004") as an example. I can then create a prepared
statement and then set the variables and execute it. Is this good code?
Clearly not! But that doesn't mean that it isn't possible. (See below...)
The point being that SQL Injection is more of an overall design issue and has
little to do with the actual java class structure itself.
> > While its never a good idea to accept SQL statements from a web
> > interface, it has nothing to do with the decision to use a Prepared
> > Statement vs a regular Statement.
>
> Any time you build the text of a SQL statement from user supplied values
> you run the risk that a rogue parameter value could change the intended
> behaviour of the SQL statement. This is simply not an issue with
> PreparedStatements and parameter markers. That in my mind makes it a
> factor in deciding what to use.
>
Sure, there is no argument there. This is why you build business logic to
validate the user input before using it.
However, this is true for the PreparedStatement as well. You're still taking
user input.
Again looking at the code :
String s1 = "SELECT * FROM employee WHERE emp_id = "+empID+";";
String s2 = "SELECT * FROM employee WHERE emp_id = ?";
Now in both statements, you are passing in the emp_id, presumably from the end
user's input.
The difference is that in using S1
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(s1);
vs
PreparedStatement pstmt = con.preparedStatement(s2);
pstmt.setInt(1,empID);
ResultSet rs = pstmt.executeQuery();
Then the rest of the code is the same.
Now... Note that there is no chance of SQL Injection, hence your argument of
an advantage of using PreparedStatement is moot.
> http://www.governmentsecurity.org/articles/SQLInjectionModesofAttackDefence
>andWhyItMatters.php
>
> Dan.
Thats nice, but here's a method that blows your point away...
public void fubar(String stmt, String[] values){
// Assume that you already have established a connection.
try{
PreparedStatement pstmt = con.preparedStatement(stmt);
for(int i=0; i
Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: > On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote: > >>Daniel John Debrunner wrote: >> >>>May I suggest that if you are writing samples for others to use that you >>>demonstrate use of parameter markers in PreparedStatements. This will >>>perfom better on Derby and all other relational database engines. >> >>Another reason to use PreparedStatements is to avoid all the security >>concerns that come with SQL injection issues. >> > > [SNIP] > > Uhm, I'm afraid this has nothing to do with the issue at hand, along with the > fact that its not exactly true Could you expand on what is "not exactly true"? > While its never a good idea to accept SQL statements from a web interface, it > has nothing to do with the decision to use a Prepared Statement vs a regular > Statement. Any time you build the text of a SQL statement from user supplied values you run the risk that a rogue parameter value could change the intended behaviour of the SQL statement. This is simply not an issue with PreparedStatements and parameter markers. That in my mind makes it a factor in deciding what to use. http://www.governmentsecurity.org/articles/SQLInjectionModesofAttackDefenceandWhyItMatters.php Dan.
Re: Why projects fail... wuz... Re: unable to execute procedure
[SNIP] I could be wrong, and please correct me if I am... But I thought a PreparedStatement just precompiled the statement on the database when the statement is prepared. How is that any more overhead than just using a normal Statement? When you use a normal statement and you execute the statement, it still has to be compiled on the database. So, either way, the statement is being compiled, it's just a matter of when. djm > Preparing a statement carries a bit more overhead > than just creating a > Statement. The benefit is that if you're going to > use that prepared statement > multiple times, you'll start to see cost savings and > it becomes more > efficient than a statement. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: unable to execute procedure
Daniel John Debrunner wrote: Anil Samuel wrote: So you can't use CALL for a function. I sent the examples of a function use earlier, using the VALUES or SELECT statements. Dan. I created a PROCEDURE using CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE ( EMPLOYEE CHAR(80), LEAVE_TYPE_ID INT, OUT TOTAL_LEAVE INT) LANGUAGE JAVA EXTERNAL NAME 'oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForType' PARAMETER STYLE JAVA; not a FUNCTION. I think it's because the second parameter is an OUT parameter and you are passing a value in. To call a procedure with an INOUT or OUT parameter you need to use a CallableStatement from a JDBC program, not ij. However, this procedure definition does not match the Java code you supplied, which is why I assumed you needed a CREATE FUNCTION. Your java code takes in two (IN) parameters and returns a value. A procedure needs to have a void return type, and OUT/INOUT parameters are passed using a one element array of the required type. From what you are trying to to, it looks like a FUNCTION is what you need. The current behavior does not seem very user friendly. Many compilers will in such situations give a message indicating that there are function(s) with this name, but the signature(s) does not match. I think something like that would be very helpful in identifying the cause of the problem. -- Øystein
Why projects fail... wuz... Re: unable to execute procedure
On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote:
> Daniel John Debrunner wrote:
> > May I suggest that if you are writing samples for others to use that you
> > demonstrate use of parameter markers in PreparedStatements. This will
> > perfom better on Derby and all other relational database engines.
>
> Another reason to use PreparedStatements is to avoid all the security
> concerns that come with SQL injection issues.
>
[SNIP]
Uhm, I'm afraid this has nothing to do with the issue at hand, along with the
fact that its not exactly true
While its never a good idea to accept SQL statements from a web interface, it
has nothing to do with the decision to use a Prepared Statement vs a regular
Statement.
Here's a better example.
In the code below we have a head to head comparison of a prepared statement vs
a regular statement. Note: This code was written on the fly so I'm sure I
made a gramatical mistake or two...
Note that both foo() and bar() are functionally equivalent. That is, they both
take the same input, and the output in the System.out.println() will be the
same.
So... Daniel's "security" concern is moot and we can focus on the differences
between the prepare statement and the statement classes. To show the benefit,
I prepare the preparedStatement in a separate function. So that if the class
is called multiple times its only prepared once.
Now, does this make sense?
Preparing a statement carries a bit more overhead than just creating a
Statement. The benefit is that if you're going to use that prepared statement
multiple times, you'll start to see cost savings and it becomes more
efficient than a statement.
The point of this is to help people understand how to write efficient and
*secure* code. Why prepare a statement if its only going to be used once or
only a handfull of times?
Now here's the code...
public class Retz {
// Class variables here...
Connection con = null;
PreparedStatement pstmt = null;
public void Retz(){
// Init background stuff
getCon(); // Get the connection.
}
public void foo(int colID){
// This is the method that uses a Statement
String s = " SELECT * FROM herd WHERE cowID = \""+colID+"\"";
try{
Statement stmt = con.createStatement();
stmt.executeQuery(s);
rs.next();
String cowBreed = rs.getString(breed);
System.out.println("Cow #"+colID+" is of type :"+cowBreed);
} catch( Exception e){
e.printStackTrace();
}
}
public void bar(int colID) {
// This is the method that uses a PreparedStatement
if (pstmt == null){
buildPStmt(); // Assume that it goes well...
}
try{
pstmt.setInt(1,colID);
pstmt.executeQuery();
rs.next();
String cowBreed = rs.getString(breed);
System.out.println("Cow #"+colID+" is of type :"+cowBreed);
} catch( Exception e){
e.printStackTrace();
}
}
private void buildPStmt(){
String s = " SELECT * FROM herd WHERE cowID = ?";
try{
pstmt = con.preparedStatement(s);
} catch (Exception e){
e.printStackTrace();
}
}
private void getCon() {
// Ok, you know how to build a connection ;-)
try{
Con = DriverManager.getConnection("Blah, blah, blah...");
} catch (Exception e){
e.printStackTrace();
}
}
public static void main(String argv[]){
/*
* Ok, so you know what to do
*/
}
}
-G
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]
Re: unable to execute procedure
Thanks Dan.I overloaded the static method to match for the Procedure call and it seems to work just fine.I think it would be nice-to-have a feature to add the jar file added by SQLJ.install_jar(..) automagically to the
derby.database.classpath.RegardsAnthosOn 4/5/06, Daniel John Debrunner <[EMAIL PROTECTED]> wrote:
Anil Samuel wrote:So you can't use CALL for a function. I sent the examples of a function
>>use earlier, using the VALUES or SELECT statements.Dan. I created a PROCEDURE using> CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE (> EMPLOYEE CHAR(80),
> LEAVE_TYPE_ID INT,> OUT TOTAL_LEAVE INT)> LANGUAGE JAVA> EXTERNAL NAME 'oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForType'> PARAMETER STYLE JAVA;
Ok, I see now that you have three parameters here, missed that. Still anOUT parameter does not map to the return of a Java method. Here is theJava signature and code you need to match this procedure statement.
public static void TotalLeaveForType(String employee, int leaveTypeId,int[] totalLeave){ // replace 'return total' with totalLeave[0] = total;}A procedure can have any number of INOUT or OUT parameters, so there is
no natural mapping to the returned value of the method. I believe thisall matches the SQL Standard part 13, Java routines in SQL.Sorry for the confusion,Dan.
-- Anil Samuel <[EMAIL PROTECTED]>Senior Technical MemberProduct DevelopmentOracle Corporation
Re: unable to execute procedure
Anil Samuel wrote:
>>
>>So you can't use CALL for a function. I sent the examples of a function
>>use earlier, using the VALUES or SELECT statements.
>>
>>Dan.
>
>
>
> I created a PROCEDURE using
> CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE (
> EMPLOYEE CHAR(80),
> LEAVE_TYPE_ID INT,
> OUT TOTAL_LEAVE INT)
> LANGUAGE JAVA
> EXTERNAL NAME 'oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForType'
> PARAMETER STYLE JAVA;
Ok, I see now that you have three parameters here, missed that. Still an
OUT parameter does not map to the return of a Java method. Here is the
Java signature and code you need to match this procedure statement.
public static void TotalLeaveForType(String employee, int leaveTypeId,
int[] totalLeave)
{
// replace 'return total' with
totalLeave[0] = total;
}
A procedure can have any number of INOUT or OUT parameters, so there is
no natural mapping to the returned value of the method. I believe this
all matches the SQL Standard part 13, Java routines in SQL.
Sorry for the confusion,
Dan.
Re: unable to execute procedure
Anil Samuel wrote: >> >>So you can't use CALL for a function. I sent the examples of a function >>use earlier, using the VALUES or SELECT statements. >> >>Dan. > > > > I created a PROCEDURE using > CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE ( > EMPLOYEE CHAR(80), > LEAVE_TYPE_ID INT, > OUT TOTAL_LEAVE INT) > LANGUAGE JAVA > EXTERNAL NAME 'oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForType' > PARAMETER STYLE JAVA; > > not a FUNCTION. I think it's because the second parameter is an OUT parameter and you are passing a value in. To call a procedure with an INOUT or OUT parameter you need to use a CallableStatement from a JDBC program, not ij. However, this procedure definition does not match the Java code you supplied, which is why I assumed you needed a CREATE FUNCTION. Your java code takes in two (IN) parameters and returns a value. A procedure needs to have a void return type, and OUT/INOUT parameters are passed using a one element array of the required type. >From what you are trying to to, it looks like a FUNCTION is what you need. Dan.
Re: unable to execute procedure
So you can't use CALL for a function. I sent the examples of a functionuse earlier, using the VALUES or SELECT statements. Dan.I created a PROCEDURE usingCREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE ( EMPLOYEE CHAR(80), LEAVE_TYPE_ID INT, OUT TOTAL_LEAVE INT) LANGUAGE JAVA EXTERNAL NAME ' oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForType ' PARAMETER STYLE JAVA;not a FUNCTION.-- Anil Samuel <[EMAIL PROTECTED]> Senior Technical MemberProduct DevelopmentOracle Corporation
Re: unable to execute procedure
Anil Samuel wrote:
> Thanks all for the pros/cons of PreparedStatement; That code snippet was
> only *meant* to depict the problem I had with the procedure call and no way
> a coding practise !!
>
> Ok. I tried this and still no different; below is the result
> ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','
> dtp.LeaveApp_jar');
> 0 rows inserted/updated/deleted
> ij> CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);
> ERROR 42Y03: 'DTP.TOTAL_LEAVE_FOR_TYPE' is not recognized as a function or
> procedure.
So you can't use CALL for a function. I sent the examples of a function
use earlier, using the VALUES or SELECT statements.
Dan.
Re: unable to execute procedure
Thanks all for the pros/cons of PreparedStatement; That code snippet was only *meant* to depict the problem I had with the procedure call and no way a coding practise !!Ok. I tried this and still no different; below is the result
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','dtp.LeaveApp_jar');0 rows inserted/updated/deletedij> CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);ERROR 42Y03: 'DTP.TOTAL_LEAVE_FOR_TYPE
' is not recognized as a function or procedure.In the filesystem, I see the following/tmp/DerbyDB/jar/tmp/DerbyDB/jar/DTP/tmp/DerbyDB/jar/DTP/LEAVEAPP_JAR.jar.G1144121950284RegardsAnthos
On 4/5/06,
Suresh Thalamati <[EMAIL PROTECTED]> wrote:
Anil Samuel wrote:> I also did>>> 2. Compiled and created dtpLeaveApp.jar with this class.> 3 . Added this jar to derby by> CALL SQLJ.install_jar(> 'file:/tmp/dtpLeaveApp.jar',
> 'dtp.LeaveApp_jar',> 0> );> 4. Tried to execuet this procedure by doing> CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);> but it results> ERROR 42Y03: '
SQLJ.INSTALL_JAR' is not recognized as a function or> procedure. I wonder how one can get "ERROR 42Y03: 'SQLJ.INSTALL_JAR"error when you execute CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);
May be that error message is related to unsuccessful attempts toexecute CALL SQLJ.install_jar() ...>> How do I get this working ?To execute "CALL dtp.TOTAL_LEAVE_FOR_TYPE
('TOM',3);" successfulydtp.LeaveApp_jar has to be in the database classpath.One way to do that is :callSYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','dtp.LeaveApp_jar');
hope that helps-suresh-- Anil Samuel <[EMAIL PROTECTED]
>Senior Technical MemberProduct Development
Oracle Corporation
Re: unable to execute procedure
Anil Samuel wrote:
I also did
2. Compiled and created dtpLeaveApp.jar with this class.
3 . Added this jar to derby by
CALL SQLJ.install_jar(
'file:/tmp/dtpLeaveApp.jar',
'dtp.LeaveApp_jar',
0
);
4. Tried to execuet this procedure by doing
CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);
but it results
ERROR 42Y03: 'SQLJ.INSTALL_JAR' is not recognized as a function or
procedure.
I wonder how one can get "ERROR 42Y03: 'SQLJ.INSTALL_JAR"error
when you execute CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);
May be that error message is related to unsuccessful attempts to
execute CALL SQLJ.install_jar() ...
How do I get this working ?
To execute "CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);" successfuly
dtp.LeaveApp_jar has to be in the database classpath.
One way to do that is :
callSYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath',
'dtp.LeaveApp_jar');
hope that helps
-suresh
Re: unable to execute procedure
Daniel John Debrunner wrote: > May I suggest that if you are writing samples for others to use that you > demonstrate use of parameter markers in PreparedStatements. This will > perfom better on Derby and all other relational database engines. Another reason to use PreparedStatements is to avoid all the security concerns that come with SQL injection issues. Creating the text of the SQL statement like this: "select TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and LEAVE_TYPE_ID=" + leaveTypeId allows the parameters to modify the meaning of the SQL statement. This approach has caused many security holes in PHP programs. E.g. pass in a user name that ends with the '--' comment lead in, leads to the statement being: select TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=fred -- and LEAVE_TYPE_ID=" + leaveTypeId Not what the application developer intended. Using PreparedStatements and parameter markers means the SQL statement cannot be modified by user supplied values. A much safer approach. Yes, one can try and be careful and verify all incoming data etc. etc., but using PreparedStatements is a much simpler approach. Dan. PS. Using PreparedStatements here would also have not lead to the buig in the orginal statement, not there are no quotes around the passed in employee string.
Re: unable to execute procedure
I also did3. CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE ( EMPLOYEE CHAR(80), LEAVE_TYPE_ID INT, OUT TOTAL_LEAVE INT) LANGUAGE JAVA EXTERNAL NAME 'oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForType
' PARAMETER STYLE JAVA;AnthosOn 4/4/06, Anil Samuel <
[EMAIL PROTECTED]> wrote:
Hi1. I created a Java class as belowpackage oracle.dtp.derby.sample;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement
;import java.sql.ResultSet
;import java.sql.SQLException;public class LeaveHelper { public static int TotalLeaveForType(String employee, int leaveTypeId) { int total = 0; try { Connection con =
DriverManager.getConnection( "jdbc:default:connection"); PreparedStatement ps = con.prepareStatement("select TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and LEAVE_TYPE_ID=" + leaveTypeId);
ResultSet rs = ps.executeQuery(); ps.close(); con.close(); total = rs.getInt(1); } catch (SQLException e) { e.printStackTrace
(); } return total; }}2. Compiled and created dtpLeaveApp.jar with this class.3 . Added this jar to derby byCALL SQLJ.install_jar( 'file:/tmp/dtpLeaveApp.jar',
'dtp.LeaveApp_jar', 0);4. Tried to execuet this procedure by doingCALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);but it resultsERROR 42Y03: 'SQLJ.INSTALL_JAR' is not recognized as a function or procedure.
How do I get this working ?RegardsAnthos--Anil Samuel <
[EMAIL PROTECTED]>Senior Technical MemberProduct DevelopmentOracle Corporation
-- Anil Samuel <[EMAIL PROTECTED]>
Senior Technical MemberProduct DevelopmentOracle Corporation
Re: unable to execute procedure
On Tuesday 04 April 2006 12:16 pm, Sunitha Kambhampati wrote:
> Michael Segel wrote:
> >On Tuesday 04 April 2006 11:48 am, Daniel John Debrunner wrote:
> >>Anil Samuel wrote:
> >>>PreparedStatement ps = con.prepareStatement("select
> >>>TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and
> >>>LEAVE_TYPE_ID=" + leaveTypeId);
> >>
> >>Couple of problems with the code:
> >
> >[SNIP]
> >
> >>May I suggest that if you are writing samples for others to use that you
> >>demonstrate use of parameter markers in PreparedStatements. This will
> >>perfom better on Derby and all other relational database engines.
> >>
> >>Here's a reworked version
> >
> >Why use a prepared statement at all? If he already knows the input values,
> > it would be cleaner to create a statement then excute the statement.
> >
> >Statement s = con.createStatement();
> >s.executeUpdate(string stuff);
> >
> >Less overhead.
>
> Derby compares the statement text to generate a query plan and if you
> use Statement with literal values, that will involve derby having to
> compile a query plan for each of the statements , which affects
> performance.
>
> Using prepared statements instead of statements can help avoid
> unnecessary compilation which saves time.
>
[SNIP]
Sigh...
The question was a rhetorical one
In the code presented, the user created a class and within a single method, he
opened a connection to the data base, then declared a prepared statement,
executed it only once, and then closed the connection. (Ok, Daniel pointed
out that the code needed to be fixed, so lets make the assumption that he
took Daniel's advice...)
But looking at the code, since it will only execute once, there is really no
need to create a prepared statement.
To Craig's point... even if Derby did cache prepared statements, you would
still have to consider the context of the statement. How often would the
method be instantiated and how often would it be called? I don't think it
would be a good idea to rely upon the caching of the statement. How long does
Derby/JavaDB/Cloudscape cache the statement? You also have to consider that
when writing an app in Java, you have the ability to write a cross platform
application with minimal modification. (Different JDBC driver and connection
properties...) So you need to consider the performance differences between a
prepared statement and a regular statement. This is why you would use a
Statement over a PreparedStatement.
Now, had the user created a class where one method created the database
connection, another prepared and executed the statement, and a third method
(maybe the main method) then you should consider a prepared statement. (One
method to prepare the statement, another to load the values and then execute
the query and parse the results...) Even if your code executes it only once,
there is a clear indication that the intention is to allow for multiple
executions of the prepared statement.
Does that make sense?
-G
Re: unable to execute procedure
Michael Segel wrote:
On Tuesday 04 April 2006 11:48 am, Daniel John Debrunner wrote:
Anil Samuel wrote:
PreparedStatement ps = con.prepareStatement("select
TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and
LEAVE_TYPE_ID=" + leaveTypeId);
Couple of problems with the code:
[SNIP]
May I suggest that if you are writing samples for others to use that you
demonstrate use of parameter markers in PreparedStatements. This will
perfom better on Derby and all other relational database engines.
Here's a reworked version
Why use a prepared statement at all? If he already knows the input values, it
would be cleaner to create a statement then excute the statement.
Statement s = con.createStatement();
s.executeUpdate(string stuff);
Less overhead.
Derby compares the statement text to generate a query plan and if you
use Statement with literal values, that will involve derby having to
compile a query plan for each of the statements , which affects performance.
Using prepared statements instead of statements can help avoid
unnecessary compilation which saves time.
e.g So statements like
select * from emp where i = 1;
select * from emp where i=2;
.
will involve compilation cost for each of the statements
but if you use PreparedStatement with '?' like
select * from emp where i =?
The statement will be compiled once and subsequent executions will save
the compilation step.
For more details, check the following links in the tuning manual:
http://db.apache.org/derby/docs/10.0/manuals/tuning/perf21.html#HDRSII-PERF-18705
and http://db.apache.org/derby/docs/10.0/manuals/tuning/perf34.html#IDX438.
We have seen applications show considerable improvements when using
preparedstatements with '?' rather than Statements.
HTH,
Sunitha.
Re: unable to execute procedure
Hi Dan,
May I suggest that if you are writing samples for others to use
that you
demonstrate use of parameter markers in PreparedStatements. This will
perfom better on Derby and all other relational database engines.
I think this is a very important point, and if you look at the
procedure, you might think that there is no value in using prepared
statements, as the statement appears as if it is only going to be
executed once. But preparing the statement allows Derby to cache the
statement the first time, and to find an identical statement (with
parameter markers) in the cache on subsequent invocations, even if
you close the Connection. If you bind the values into the prepared
statement, there is little chance that Derby will find the statement
in the cache.
Craig
On Apr 4, 2006, at 9:48 AM, Daniel John Debrunner wrote:
Anil Samuel wrote:
public class LeaveHelper {
public static int TotalLeaveForType(String employee, int
leaveTypeId)
{
int total = 0;
try {
Connection con = DriverManager.getConnection(
"jdbc:default:connection");
PreparedStatement ps = con.prepareStatement("select
TOTAL_TAKEN
from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and
LEAVE_TYPE_ID="
+ leaveTypeId);
ResultSet rs = ps.executeQuery();
ps.close();
con.close();
total = rs.getInt(1);
}
catch (SQLException e)
{
e.printStackTrace();
}
return total;
}
}
Couple of problems with the code:
- You close the PreparedStatement ps before using the ResultSet
rs. By
JDBC rules the close of ps will also close rs.
- You don't call rs.next() on the ResultSet, thus the rs.getInt
will fail.
May I suggest that if you are writing samples for others to use
that you
demonstrate use of parameter markers in PreparedStatements. This will
perfom better on Derby and all other relational database engines.
Here's a reworked version
public static int TotalLeaveForType(String employee, int
leaveTypeId) throws SQLException
{
int total = 0;
Connection con = DriverManager.getConnection(
"jdbc:default:connection");
PreparedStatement ps = con.prepareStatement("select
TOTAL_TAKEN
from DTP.LEAVE_APPROV where EMPLOYEE_ID=? and LEAVE_TYPE_ID=?");
ps.setString(1, employee);
ps.setInt(2, leaveTypeId);
ResultSet rs = ps.executeQuery();
rs.next();
total = rs.getInt(1);
rs.close();
ps.close();
con.close();
return total;
}
}
Dan.
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
Re: unable to execute procedure
On Tuesday 04 April 2006 11:48 am, Daniel John Debrunner wrote:
> Anil Samuel wrote:
> > PreparedStatement ps = con.prepareStatement("select
> > TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and
> > LEAVE_TYPE_ID=" + leaveTypeId);
>
>
> Couple of problems with the code:
>
[SNIP]
> May I suggest that if you are writing samples for others to use that you
> demonstrate use of parameter markers in PreparedStatements. This will
> perfom better on Derby and all other relational database engines.
>
> Here's a reworked version
>
Why use a prepared statement at all? If he already knows the input values, it
would be cleaner to create a statement then excute the statement.
Statement s = con.createStatement();
s.executeUpdate(string stuff);
Less overhead.
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]
Re: unable to execute procedure
Anil Samuel wrote:
> public class LeaveHelper {
>
> public static int TotalLeaveForType(String employee, int leaveTypeId)
> {
> int total = 0;
> try {
> Connection con = DriverManager.getConnection(
> "jdbc:default:connection");
> PreparedStatement ps = con.prepareStatement("select TOTAL_TAKEN
> from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and LEAVE_TYPE_ID="
> + leaveTypeId);
> ResultSet rs = ps.executeQuery();
> ps.close();
> con.close();
> total = rs.getInt(1);
> }
> catch (SQLException e)
> {
> e.printStackTrace();
> }
>
> return total;
> }
> }
Couple of problems with the code:
- You close the PreparedStatement ps before using the ResultSet rs. By
JDBC rules the close of ps will also close rs.
- You don't call rs.next() on the ResultSet, thus the rs.getInt will fail.
May I suggest that if you are writing samples for others to use that you
demonstrate use of parameter markers in PreparedStatements. This will
perfom better on Derby and all other relational database engines.
Here's a reworked version
> public static int TotalLeaveForType(String employee, int
leaveTypeId) throws SQLException
> {
> int total = 0;
> Connection con = DriverManager.getConnection(
> "jdbc:default:connection");
> PreparedStatement ps = con.prepareStatement("select
TOTAL_TAKEN
> from DTP.LEAVE_APPROV where EMPLOYEE_ID=? and LEAVE_TYPE_ID=?");
ps.setString(1, employee);
ps.setInt(2, leaveTypeId);
> ResultSet rs = ps.executeQuery();
rs.next();
> total = rs.getInt(1);
rs.close();
ps.close();
> con.close();
>
> return total;
> }
> }
Dan.
Re: unable to execute procedure
Anil Samuel wrote:
> 4. Tried to execuet this procedure by doing
> CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3);
> but it results
> ERROR 42Y03: 'SQLJ.INSTALL_JAR' is not recognized as a function or
> procedure.
>
> How do I get this working ?
Did you create the SQL function using CREATE FUNCTION?
The wiki has a page on this:
http://wiki.apache.org/db-derby/DerbySQLroutines
For this routine it would be something like:
CREATE FUNCTION
dtp.TOTAL_LEAVE_FOR_TYPE(NAME VARCHAR(20), LEAVE_TYPE INT)
RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLK JAVA
READS SQL DATA
EXTERNAL NAME 'oracle.dtp.derby.sample.TotalLeaveForType'
This is a function not a procedure because it returns a value.
To execute it it can be called like any othjer function.
VALUES dtp.TOTAL_LEAVE_FOR_TYPE(?, ?)
or
SELECT dtp.TOTAL_LEAVE_FOR_TYPE(E.NAME, ?) from employees E
Dan.
