Using a prepared statement for any SQL that you execute over and over is a
very good thing to do, especially with Oracle. This is true even if you are
creating and deleting the prepared statement over and over at the client,
however it is possible to keep the prepared statement open at the client and
reuse it, or the latest Oracle JDBC drivers include a statement cache
feature which you could enable to avoid having to do anything in your
application.
The difference between a statement "insert into foo (name) values
('O''Brian')" and a prepared statement "insert into foo (name) values (?)"
is that the SQL parser doesn't have to parse the value you 'bind' to the
placeholder '?' in the SQL statement so you don't need to worry about
escaping things.
Within Oracle, the server kernel caches quite a bit of information on the
execution of SQL statements, but the cache is keyed by the SQL statement
text. Using a prepared statement for an insert for example means that the
statement will be cached and execution at the server will be quicker because
the server won't have to parse the statement and compute access plans, etc.
If you didn't use a prepared statement, then because of the data every
statement would look different to Oracle and the server would have to do all
the work everytime.
Hope this helps.
Cheers
-----Original Message-----
From: Lee Turner [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 29, 2001 8:36 AM
To: [EMAIL PROTECTED]
Subject: Error when entering ' in user data
Hi
I was wondering how people were handling the entry of the single quote
character in their user data. The reason I ask is because I have a user
bean that uses a combination of CMP and some JDBC to handle a few extra bits
that I need the bean to do. The problem occurred when I entered o'brian as
the username in the system I am developing. The data was stored in the
system so I take it that CMP handles the fact that a ' was in the username
(am I correct in this assumption), but when it came to the JDBC to insert
the username in a different table it threw and error. The error was the
fact that Oracle didn't think that my SQL statement was formed properly as
it uses single quotes around the data values in the INSERT statement.
Now as I understand it, you can put another single quote in front of the one
in the user data (o''brian) and the database will handle it, so what I was
wondering was if this is what people are doing in their systems or there is
a better way of doing it. Wouldn't it slow the system down to have to check
every piece of data being entered into the system ??? I think that I have
read somewhere that using a PreparedStatement handles this problem for you.
Is this correct, as this could be a solution ??
I am sorry that this is more of a JDBC problem than a EJB problem, but I
couldn't find a JDBC-INTEREST list on the Java web site.
Thanks in advance
Lee
_________________________________
Lee Turner
Systems Developer
Information Technology Leeds
_________________________________
Watt Gilchrist Ltd
Ring Road, West Park
Leeds, LS16 6RA
Tel: 0113 288 3200
Fax: 0113 275 1690
http://www.wattgilchrist.co.uk
_________________________________
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
