db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From david myers <david.myers.scibearsp...@gmail.com>
Subject Re: Exception while inserting a record
Date Mon, 17 Sep 2012 19:20:50 GMT
Suat

If you are doing insertions that use the exact same statement that many 
times in succession you should see a performance improvement using a 
prepared statement.

Also the prepared statement will negate the need to worry about escaping 
any other 'special characters' as part of the idea is that the prepared 
statement will deal with them at the level of the DBMS. Rather than 
forcing you to deal with it in your code. The same is also true with 
Callable statements.

That said I would be interested to see how much of a difference it makes 
using a normal or prepared statement, mainly to see where the break 
point is in such things.

David




On 13/09/12 13:14, Suat Gonul wrote:
> On 09/13/2012 01:54 PM, Knut Anders Hatlen wrote:
>> Suat Gonul <suatgonul@gmail.com> writes:
>>
>>> Hi Knut,
>>>
>>> It seems that is the problem, thanks. But, then I think I should escape
>>> special characters contained the values. Is there standard procedure for
>>> this? Is there a list of of special characters? What do you suggest?
>> I'd suggest that you use prepared statements with parameter markers
>>
>> INSERT INTO t (id, revision) VALUES (?, 1)
>>
>> and use ps.setString(1, "string value") to set the value. Then you don't
>> need to worry about special characters in the string.
>>
>> If you want to specify the string literally in your SQL statement, only
>> the single-quote character is a special character, as far as I know, and
>> it can be escaped with an extra single-quote character. For example, to
>> insert the string «It's safer with PreparedStatement», you would have to
>> do something like this:
>>
>> INSERT INTO t (id, revision) VALUES ('It''s safer with PreparedStatement', 1)
>>
> Thank you very much. Escaping the ' character with another ' has solved
> my problem.
>
> Indeed I am doing a bulk insertion operation (1000 insertion at a time
> (Values > 1000 causes stackoverflow exception)). So I prepare the query
> in advance and execute it in one step. In total, I have ~1M records.
> However, I could not decide on which one would be more efficient. So,
> I'm trying both options now.
>
> Thanks again,
> Best,
> Suat
>


Mime
View raw message